db-command
db-command copied to clipboard
wp-cli
Exclude DEFINER on Database Export
Feature Request
- [x ] Yes, I reviewed the contribution guidelines.
Describe your use case and the problem you are facing
When exporting databases, it is a common issue that sometimes the restoration cannot be completed due to this line (or similar:
/*!50013 DEFINER=user@% SQL SECURITY DEFINER */
When this happens, the import normally fails with error when trying to import it. Some links with the issue (from 2010 one of them): https://dbperf.wordpress.com/2010/04/12/removing-definer-from-mysql-dump/ https://stackoverflow.com/questions/9446783/remove-definer-clause-from-mysql-dumps
Describe the solution you'd like
I think that adding an option to exclude the DEFINER from the export or to remove it after the export automatically will improve the reliability of the export data.
Alternatively could be that the IMPORT ignore that line, but I think this is more an export issue than a import one. Thanks!
The WordPress schema is very primitive, it doesn't contain any stored procedures or similar.
Therefore I think we should be safe by either stripping the DEFINER and falling back to the defaults, or setting the DEFINER to CURRENT_USER(), which should be the same as the defaults. This could indeed be done via an export switch like --skip-definer or --adapt-definer.
I'd love for more feedback on this by database experts, though, as this can potentially be a risky proposition...
I agree. I am not a db expert either so it would be great to have some other input on this. :) We can wait. It is not a critical thing to have at all.
Hi, One year since this one I have another issue again with this. It is clearly not a big deal with most websites, but when it fails, it fails completely. It would be great to have this option or any alternative.
https://aws.amazon.com/premiumsupport/knowledge-center/definer-error-mysqldump/
For now, you should be able to use a work-around by stripping the offending line with sed:
wp @production db export - | sed 's#/\*[^/]*DEFINER[^/]*\*/##' | wp @staging db import -