Remove definer users from db:dump files

[hostep]

Hi

This is based on my experience with the magento-cloud cli tool, which seems to be a clone of this tool, but I can't find a public repo to report this to, so if this is inappropriate here, feel free to close.

Magento 2 uses mysql triggers on certain tables, which have a DEFINER user defined. When dumping the mysql database using db:dump and then trying to import this sql file in another environment where the mysql username is different, you can get this error: ERROR 1227 (42000) at line <number>: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

A way to work around this is to remove the DEFINER statements from the mysql dump file. In a community maintained cli tool for Magento, they add some post dump commands to work around this by removing those DEFINER statements, see: https://github.com/netz98/n98-magerun2/blob/1394169/src/N98/Magento/Command/Database/DumpCommand.php#L420 As to why those LANG environment variables are there, you can see this ticket: https://github.com/netz98/n98-magerun/issues/771 (but not sure if this is relevant for the platform.sh environment though)

So my proposal would be to also try to strip these DEFINER statements from the generated sql file (maybe add a new option to db:dump command for this?)

Is there interest in adding something like this?

Thanks!

[tylers-username]

I second this. As a Magento user, I often have to reset the definer after installing a Platform DB dump.

[lsmith77]

This is likely caused by https://bugs.mysql.com/bug.php?id=24680

A fix can be:

cat foo.sql | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > foo-without-definer.sql