2

I have already googled and tried may suggestions but none seem to be able to remove all 'DEFINER=' occurrences.

Remove DEFINER clause from MySQL Dumps

These are some of the occurrences

/*!50013 DEFINER=`root`@`localhost`*/
/*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */
/*!50013 DEFINER=`root`@`192.168.42.20` SQL SECURITY DEFINER */

And I have tried a different approach by attempting to replace everything up until the first space and while the regex works on regex101 it will not work in the command line with sed:

cat mydb.sql | grep DEFINER= | sed -e 's/DEFINER=[^\s]+/ /'

Any suggestions on how to get this to work with sed ?

Edit

After looking through this huge dump file I have also discovered som occurrences where there is no space at the end, instead there is a *, so I updated the test text above.

This is what appears to work:

's/DEFINER=[^ |\*]*//'
Community
  • 1
  • 1
crankshaft
  • 2,607
  • 4
  • 45
  • 77

4 Answers4

2

You don't need to use cat nor grep. Try this:

$ sed -e 's/DEFINER=[^[:space:]]\+ //' mydb.sql
/*!50013  `root`@`localhost` SQL SECURITY DEFINER */
/*!50013  `root`@`%` SQL SECURITY DEFINER */
/*!50013  `root`@`192.168.42.20` SQL SECURITY DEFINER */
/*!50003 CREATE OR REPLACE*/ /*!50017 */ /*!50003 TRIGGER WORKORDER_BU BEFORE UPDATE ON WORK_ORDER

To edit the file in place, use the -i option:

$ sed -i 's/DEFINER=[^[:space:]]\+ //' mydb.sql

With your pattern [^\s]+, you search for a non-space character [^\s] followed by + sign. To match one or more character with +, you must escape it \+ in BRE mode:

sed -e 's/DEFINER=[^[:space:]]\+ //' mydb.sql

Moreover the character class \s i as GNU sed extension and might not work on your system. Use [:space:] instead (or [:blank:] to just match spaces and tabs).

SLePort
  • 15,211
  • 3
  • 34
  • 44
1
$ cat mydb.sql | grep DEFINER= | sed -e 's/DEFINER=[^ ]\+/ /g'
/*!50013   SQL SECURITY DEFINER */
/*!50013   SQL SECURITY DEFINER */
/*!50013   SQL SECURITY DEFINER */

If using \s instead of just space, first line is:

/*!50013  st` SQL SECURITY DEFINER */

$ sed --version
sed (GNU sed) 4.2.2
James Brown
  • 36,089
  • 7
  • 43
  • 59
1

As a slightly different solution - I started out with sed, but tried a different approach because it had such limited regexes (I wanted to use lookahead) so used a perl one-liner instead where I could use full pcre that I could easily test in notepad+ etc.:

#strip DEFINER out of dump (tmp SQL file)
perl -pi -e 's/\bDEFINER=.*?@.*?(?=[\*\s])//g' "${FILE}"
rm "${FILE}".bak     2> /dev/null

This is part of a bash script hence the FILE variable. For some reason perl in mingw (on w10) insists on creating a .bak file despite not specifying one. The rm could be left out if this isn't happening.

The (?= lookahead means I can find the * or whitespace that ends the term without having to find a way of putting it back in the replace. [as with any regex solution I bet there are lots of other ways of doing this!]

This handles the definers between /*! ... */ and ones between CREATE ... FUNCTION etc. I have only tried it on the output of mysqldump where it works fine (I tried file sizes up to a 165Mb SQL dump and it chomped through that very quickly).

The snag with all these simple regex approaches is that they would find the regex term and strip it out if it happened to exist in data, so a safer solution would divide the structure and data dumps into separate files and only process the structure file. I haven't had to do this, but it's something to bear in mind. (I made the regex quite specific with the @ etc. to try and avoid the issue)

neilmcgann
  • 71
  • 1
  • 5
0

Are you on MacOS? This regexp works on Linux:

 cat mydb.sql | grep DEFINER= | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/'
olegsv
  • 1,422
  • 1
  • 14
  • 21