Details
-
New Feature
-
Status: Done
-
Medium
-
Resolution: Fixed
-
3.0.3
-
None
-
None
Description
tested on
pt-online-schema-change 3.0.2
mysql Ver 14.14 Distrib 5.6.35-80.0, for debian-linux-gnu (x86_64) using readline 6.3
pt-online-schema-change D=himalaya_novocar_brainstorm,t=Estimation,u=root,p=***** --alter-foreign-keys-method=auto --alter="MODIFY type set('INVOICE','PLANNING','ATR')" --dry-run
There are a couple of tickets referencing the inherit slowness of information schema. Most are closed because you can set --alter-foreign-keys-method to none, so it doesn't do the check.
But, this does not solve the problem. It simply avoids it. What if you actually do want the foreign-keys ?
The real problem is that the query in pt-online-schema-change that is used to find the child tables in sub find_child_tables is slow:
Currently:
my $sql = "SELECT table_schema, table_name "
. "FROM information_schema.key_column_usage "
. "WHERE referenced_table_schema='$tbl->
. "AND referenced_table_name='$tbl->{tbl}'";
But... simply adding "table_schema='$tbl->{db}
'" will speed this up.
I propose:
my $sql = "SELECT table_schema, table_name "
. "FROM information_schema.key_column_usage "
. "WHERE table_schema='$tbl->
' "
. "AND referenced_table_name='$tbl->
'";
In the current version, foreign keys that are made across databases will be included. I my proposal, only foreign keys from the same database will be checked.
I can't find a reason why anyone would make a foreign key cross databases, but I'm sure there are some that do have a valid use case for this.
I believe the default way should only check the same table_schema. And if people want to check cross database they can do this using a extra flag?
I'm reporting this as a bug and not a blueprint, because the current code is simply not usable when working with a lot of databases. I hope this is ok.
Launchpad: https://bugs.launchpad.net/percona-toolkit/+bug/1690122