[MDEV-18139] ALTER IGNORE ... ADD FOREIGN KEY ... ALGORITHM=COPY causes bogus ER_TABLE_EXISTS_ERROR and lengthy InnoDB error message Created: 2019-01-04  Updated: 2019-04-23  Resolved: 2019-04-23

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.2.24, 5.5.64, 10.1.39, 10.3.15, 10.4.5

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-18221 Failed to execute action for entry = ... Open

 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB;
 
CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB;
 
 
 
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f);
 
ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1), ALGORITHM=COPY;
 
 
 
# Cleanup
 
DROP TABLE t1, t2;

10.0 802ce9672f

 
CURRENT_TEST: bug.t5a
 
mysqltest: At line 7: query 'ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1), ALGORITHM=COPY' failed: 1050: Table './test/t1' already exists


2019-01-05 00:14:36 7f391882c700  InnoDB: Error; possible reasons:
 
InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
 
InnoDB: to have the same internal name in case-insensitive comparison.
 
InnoDB: 2) table `test`.`t1` exists in the InnoDB internal data
 
InnoDB: dictionary though MySQL is trying to rename table `test`.`#sql-5a68_3` to it.
 
InnoDB: Have you deleted the .frm file and not used DROP TABLE?
 
InnoDB: You can look for further help from
 
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
 
InnoDB: If table `test`.`t1` is a temporary table #sql..., then it can be that
 
InnoDB: there are still queries running on the table, and it will be
 
InnoDB: dropped automatically when the queries end.
 
InnoDB: You can drop the orphaned table inside InnoDB by
 
InnoDB: creating an InnoDB table with the same name in another
 
InnoDB: database and copying the .frm file to the current database.
 
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
 
InnoDB: succeed.

Also reproducible on MySQL 5.6.
MySQL 5.7 doesn't understand ALTER IGNORE, so the test case is not applicable.

 Comments   
Comment by Marko Mäkelä [ 2019-01-05 ]

On a related note, I think that the following should be fixed at the same time:

ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1), ALGORITHM=INPLACE;

This is unnecessarily being rejected; after all, IGNORE should imply that we do not care whether the constraint holds, and could simply add it without any checking.

query 'ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1), ALGORITHM=INPLACE' failed: 1846: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY

This message should not be issued if IGNORE was specified, and the reason should be changed to ‘needs IGNORE or foreign_key_checks=OFF’.

Comment by Elena Stepanova [ 2019-01-13 ]

See MDEV-18221 for another (non-deterministic) occurrence of the same error messages. The test case there doesn't even involve foreign keys.

Comment by Ryan Southwell [ 2019-01-31 ]

May be related to https://bugs.mysql.com/bug.php?id=72751
May be related to https://bugs.mysql.com/bug.php?id=88082
Confirmed on v10.0.38 using test case from https://bugs.mysql.com/bug.php?id=88082

Input: 

CREATE DATABASE testdb;
 
USE testdb;
 
CREATE TABLE Foreign1(Foreign1Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
 
CREATE TABLE Foreign2(Foreign2Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
 
CREATE TABLE Test(Foreign1Id INT, Foreign2Id INT, CONSTRAINT Test_ibfk_1 FOREIGN KEY (Foreign1Id) REFERENCES Foreign1 (Foreign1Id));
 
ALTER TABLE Test ADD FOREIGN KEY (Foreign2Id) REFERENCES Foreign2 (Foreign2Id);


Client Returns: 

Table '.\test\test' already exists


Error Log Captures: 

Version: '10.0.38-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution
 
2019-02-01 04:43:26 96c  InnoDB: Error; possible reasons:
 
InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
 
InnoDB: to have the same internal name in case-insensitive comparison.
 
InnoDB: 2) table `test`.`test` exists in the InnoDB internal data
 
InnoDB: dictionary though MySQL is trying to rename table `test`.`#sql-2cb8_67` to it.
 
InnoDB: Have you deleted the .frm file and not used DROP TABLE?
 
InnoDB: You can look for further help from
 
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
 
InnoDB: If table `test`.`test` is a temporary table #sql..., then it can be that
 
InnoDB: there are still queries running on the table, and it will be
 
InnoDB: dropped automatically when the queries end.
 
InnoDB: You can drop the orphaned table inside InnoDB by
 
InnoDB: creating an InnoDB table with the same name in another
 
InnoDB: database and copying the .frm file to the current database.
 
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
 
InnoDB: succeed.

Comment by Marko Mäkelä [ 2019-04-23 ]

The error occurs when ALTER IGNORE TABLE invokes ha_innobase::rename_table() to rename the rebuilt table to t1. InnoDB internally reports DB_DUPLICATE_KEY when it is about to rename the FOREIGN KEY constraints in row_rename_table_for_mysql():

		err = que_eval_sql(
 
			info,
 
			"PROCEDURE RENAME_CONSTRAINT_IDS () IS\n"

At the time of the failure, the table SYS_FOREIGN contains the following records:
(ID,FOR_NAME,REF_NAME,N_COLS)=
('test/#sql-64a3_2_ibfk_1','test/t1','test/t1',1),
'test/t1_ibfk_1','test/t1','test/t2',1)).
The code attempts to rename from old_name="test/#sql-64a3_2" to new_name="test/t1".

The bug appears to be that the _ibfk_1 suffix was used for both FOREIGN KEY constraints. The ALTER IGNORE should have assigned the suffix _ibfk_2 instead, and this function should only replace the first part of the name.

Comment by Marko Mäkelä [ 2019-04-23 ]

The bug is in the hacky InnoDB parser for FOREIGN KEY constraints. The following fix appears to work:

diff --git a/storage/xtradb/dict/dict0dict.cc b/storage/xtradb/dict/dict0dict.cc
 
index b587aec5370..344f58decd0 100644
 
--- a/storage/xtradb/dict/dict0dict.cc
 
+++ b/storage/xtradb/dict/dict0dict.cc
 
@@ -4454,11 +4454,19 @@ dict_create_foreign_constraints_low(
 
 	}
 
 
 	orig = ptr;
 
-	ptr = dict_accept(cs, ptr, "TABLE", &success);
 
-
 
-	if (!success) {
 
-
 
-		goto loop;
 
+	for (;;) {
 
+		ptr = dict_accept(cs, ptr, "TABLE", &success);
 
+		if (success) {
 
+			break;
 
+		}
 
+		ptr = dict_accept(cs, ptr, "ONLINE", &success);
 
+		if (success) {
 
+			continue;
 
+		}
 
+		ptr = dict_accept(cs, ptr, "IGNORE", &success);
 
+		if (!success) {
 
+			goto loop;
 
+		}
 
 	}
 
 
 	/* We are doing an ALTER TABLE: scan the table name we are altering */

This bug should affect MariaDB 5.5 as well; its grammar allows both ONLINE and IGNORE between the words ALTER TABLE: ALTER alter_options TABLE_SYM. If I remove the offending , ALGORITHM=COPY from the test case, indeed I can repeat this on 5.5:

5.5 370886a9e247d8151dfe340a5e7b8b6929ccfacb

 
mysqltest: At line 5: query 'ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1)' failed: 1050: Table './test/t1' already exists

With a port of the fix to 5.5, the operation will succeed.

Comment by Ryan Southwell [ 2019-04-23 ]

Will this be backported to 10.0?

Generated at Thu Feb 08 08:41:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.