Details
-
Bug
-
Status: Done
-
High
-
Resolution: Fixed
-
3.1.0
Description
I have a MariaDB 10.2 table without foreign keys and also no foreign keys pointing to it. When I run the following command I get an error:
pt-online-schema-change --print --alter "CHANGE COLUMN type type ENUM('multi-contract') CHARACTER SET 'utf8' NOT NULL" D=db1,t=t1 --user abc --host localhost --statistics --ask-pass --dry-run{{Enter MySQL password: }}
Operation, tries, wait:
{{ analyze_table, 10, 1}}
{{ copy_rows, 10, 0.25}}
{{ create_triggers, 10, 1}}
{{ drop_triggers, 10, 1}}
{{ swap_tables, 10, 1}}
{{ update_foreign_keys, 10, 1}}
There is an error in MySQL that makes the server to die when trying to rename a table with FKs. See https://bugs.mysql.com/bug.php?id=96145
Since pt-online-schema change needs to rename the old <-> new tables as the final step, and the requested table has FKs, it cannot be executed under the current MySQL version
}}{{SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db1' AND REFERENCED_TABLE_NAME = 't1';
Empty set (0.01 sec)
SELECT TABLE_NAME,
{{ -> COLUMN_NAME,}}
{{ -> CONSTRAINT_NAME,}}
{{ -> REFERENCED_TABLE_NAME,}}
{{ -> REFERENCED_COLUMN_NAME}}
{{ -> FROM KEY_COLUMN_USAGE}}
{{ -> WHERE TABLE_SCHEMA = "db1" }}
{{ -> AND TABLE_NAME = "t1" }}
{{ -> AND REFERENCED_COLUMN_NAME IS NOT NULL;}}
Empty set (0.00 sec)
SHOW VARIABLES LIKE 'version%';
---------------------------------------------------------+
| Variable_name | Value |
---------------------------------------------------------+
| version | 10.2.25-MariaDB-log |
| version_comment | SUSE package |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_malloc_library | system |
| version_ssl_library | OpenSSL 1.1.0i-fips 14 Aug 2018 |
---------------------------------------------------------+
6 rows in set (0.00 sec)