Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.2.6, 11.4, 11.7(EOL)
-
None
Description
In the scenario below, starting from 11.2 and up, ALTER TABLE t2 RENAME INDEX ... fails with ER_ALTER_OPERATION_NOT_SUPPORTED. I don't know whether it should, I suppose the change is a result or a side-effect of online alter development; anyway, it fails.
But even though it fails and it doesn't visibly affect the table structure, it affects the next ALTER TABLE t2 ADD FOREIGN KEY .... Without the first one, the second one fails with ER_DUP_KEYNAME (again, not sure whether it should, but it fails on all versions, so it's not related to online alter). However with the prior failed ALTER .. RENAME, it works.
Naturally it shouldn't happen this way.
A direct effect of it is a replication abort – the failed ALTER isn't written into the binary log, so the slave only executes the 2nd one, and thus fails. I kept it as a part of the test case for demonstrative purposes.
--source include/have_innodb.inc
|
--source include/have_binlog_format_mixed.inc
|
--source include/master-slave.inc
|
|
|
CREATE TABLE t1 (f1 INT, f2 INT, KEY(f1), KEY(f2)) ENGINE=InnoDB; |
CREATE TABLE t2 (pk INT PRIMARY KEY, a INT, b INT, KEY ind1(a), FOREIGN KEY fk1 (b) REFERENCES t1 (f1)) ENGINE=InnoDB; |
--error ER_ALTER_OPERATION_NOT_SUPPORTED
|
ALTER TABLE t2 RENAME INDEX fk1 TO fk, ALGORITHM=INSTANT, ORDER BY a; |
ALTER TABLE t2 ADD FOREIGN KEY ind1 (b) REFERENCES t1 (f2); |
|
|
--sync_slave_with_master
|
|
|
--connection master
|
DROP TABLE t1, t2; |
--source include/rpl_end.inc |
|
11.4 9e7762e718b5f39bc38cebfabc3ff4cb57b1cc68 |
Last_Error Error 'Duplicate key name 'ind1'' on query. Default database: 'test'. Query: 'ALTER TABLE t2 ADD FOREIGN KEY ind1 (b) REFERENCES t1 (f2)'
|
The essential difference is here:
|
With the failed ALTER .. RENAME |
MariaDB [test]> CREATE TABLE t2 (pk INT PRIMARY KEY, a INT, b INT, KEY ind1(a), FOREIGN KEY fk1 (b) REFERENCES t1 (f1)) ENGINE=InnoDB; |
Query OK, 0 rows affected (0.109 sec) |
|
|
MariaDB [test]> ALTER TABLE t2 RENAME INDEX fk1 TO fk, ALGORITHM=INSTANT, ORDER BY a; |
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY |
MariaDB [test]>
|
MariaDB [test]> ALTER TABLE t2 ADD FOREIGN KEY ind1 (b) REFERENCES t1 (f2); |
Query OK, 0 rows affected (0.098 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
Without the failed ALTER .. RENAME |
MariaDB [test]> CREATE TABLE t1 (f1 INT, f2 INT, KEY(f1), KEY(f2)) ENGINE=InnoDB; |
Query OK, 0 rows affected (0.049 sec) |
|
|
MariaDB [test]> CREATE TABLE t2 (pk INT PRIMARY KEY, a INT, b INT, KEY ind1(a), FOREIGN KEY fk1 (b) REFERENCES t1 (f1)) ENGINE=InnoDB; |
Query OK, 0 rows affected (0.045 sec) |
|
|
MariaDB [test]> ALTER TABLE t2 ADD FOREIGN KEY ind1 (b) REFERENCES t1 (f2); |
ERROR 1061 (42000): Duplicate key name 'ind1' |