running lhm with binlog on

[simao]

Hello,

How can you run lhm with binlog turned on?

Creating/Dropping triggers fails because the user running the migration does not have SUPER privileges.

[nburkley]

Setting the global variable log_bin_trust_function_creators should resolve the issue without having to grant SUPER privileges to the user running the migration.

mysql> set GLOBAL log_bin_trust_function_creators = 1;

[simao]

Yeah we tried this at the time and it did work, but not really a solution as you need to set this var and unset it after running the migration. It's not really safe to have it set at 1..

[arthurnn]

maybe we should enable and disable this before/after the migration is done.
Can anyone confirm if pt-online-schema does that ?! cc @camilo

[avit]

Setting GLOBAL variables requires SUPER privilege in the first place. This would need to be granted by the DB admin anyway so we can't automatically change it before/after the Lhm run.

show global variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+

I guess we could check for log_bin == "ON" && trust == "OFF" and abort the migration before starting, but I'm not sure that's worth it either. I'd say let it fail and leave it up to the user to decide.

Just for background:

MySQL's paranoia here is because the binary log records the SQL commands and not the actual stored data so functions must always be deterministic -- avoid NOW(), UUID() etc. Since MySQL can't determine if your triggers are designed safely (regardless of your trusted user privilege) it has this extra layer of security to only allow users with SUPER to add functions when binary log is turned on.

Using set GLOBAL log_bin_trust_function_creators = 1 just means users who already have ALTER ROUTINE are trusted to create functions. It's still up to you to design sane stored procedures either way... if you trust the migration script you should allow it on your server, or just run migrations with a user that has SUPER.

[camilo]

if you trust the migration script you should allow it on your server, or just run migrations with a user that has SUPER.

I agree, there is no way LHM can do its job without enough perms, and

Setting GLOBAL variables requires SUPER privilege in the first place.