1

I have a bash script that imports wordpress dbs for me pretty reliably, but one of my sites came from a former wordpress multisite, therefore the database includes DEFINERS within the database that prevents the following code from executing correctly

gunzip -c $destination_path_to_wordpress/folder/db-export.sql.gz | mysql -u $destination_db_user -p$destination_db_pass $destination_db_name

The above results in ERROR 1227 (42000) at line 3224: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

If I manually export the db then run sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i db-export.sql and then attempt to import the db then I can successfully import the db, search-replace the user name, and site it live.

Question:
What is the correct syntax to to combine my current db import command and the sed command that gets rid of the definer problem? something like the following is what I would like to achieve:

clearly incorrect, but you get the gist...

gunzip -c $destination_path_to_wordpress/folder/db-export.sql.gz | `sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i db-export.sql | mysql -u $destination_db_user -p$destination_db_pass $destination_db_name

any tips?

thanks
roaima
  • 107,089
  • 14
  • 139
  • 261
Time-Bandit
  • 202
  • 2
  • 10
  • Do you REALLY want to have backticks in your sed command? Post sample input and expected output. – Ed Morton Sep 18 '22 at 18:16
  • @EdMorton yes almost certainly in this instance. MySQL uses backticks as quote marks for table and column names that don't adhere to standard SQL naming conventions. See [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/a/11321508/2344631) – roaima Sep 18 '22 at 18:46
  • @roaima thanks for the info. – Ed Morton Sep 18 '22 at 20:22

1 Answers1

1

In this case you want sed to process the data from the gunzip rather than from a file. Omit the file name and it will happily read from stdin (i.e. from the gunip) and write to stdout (i.e. the mysql).

For readability I've split this over three lines. It will still work like this but you can join the lines together as one if you really prefer:

gunzip -c "$destination_path_to_wordpress/folder/db-export.sql.gz" |
    sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' |
    mysql -u "$destination_db_user" -p"$destination_db_pass" "$destination_db_name"
roaima
  • 107,089
  • 14
  • 139
  • 261