Comments (5)
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;
from lhm.
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
..
from lhm.
maybe we should enable and disable this before/after the migration is done.
Can anyone confirm if pt-online-schema does that ?! cc @camilo
from lhm.
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.
from lhm.
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.
from lhm.
Related Issues (20)
- Run ANALYZE TABLE after table copy HOT 5
- support for sequel? HOT 1
- Feature request: Support tables with gaps in the primary key column 'id' HOT 1
- lock_wait_timeout is readonly in older versions of SQL
- Any release coming that includes the slave_lag throttler? HOT 6
- Calculate Stride Size dynamically to avoid DeadLocks HOT 2
- How well does LHM play with Galera clusters? HOT 1
- Feature Request: Verification Step HOT 2
- Forgotten line HOT 2
- Completion percentage is wrong when IDs don't start at 1 HOT 2
- Undefined method 'rename_table' for Lhm
- Replication problems when running LHM
- Does LHM rebuild external constraints?
- table_exists deprecation warning
- LHM doesn't copy data when only 1 row and ID isn't 1 HOT 3
- Flag the repo as unmaintained? HOT 4
- Is there a way to get the no of records that were updated?
- p
- During LHM migration, Table is getting locked for bigger table and all our connections are holding up HOT 1
- Mysql 8 no display attribute on fields HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from lhm.