[MDEV-18139] ALTER IGNORE ... ADD FOREIGN KEY ... ALGORITHM=COPY causes bogus ER_TABLE_EXISTS_ERROR and lengthy InnoDB error message - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.24, 5.5.64, 10.1.39, 10.3.15, 10.4.5
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB, Storage Engine - XtraDB
Labels:
None

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.

Attachments

Issue Links

relates to

MDEV-18221 Failed to execute action for entry = ... from ddl log upon normal shutdown while running ALTER .. PARTITION

Open

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä added a comment - 2019-01-05 15:38

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’.

Marko Mäkelä added a comment - 2019-01-05 15:38 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’.

Elena Stepanova added a comment - 2019-01-13 12:48

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

Elena Stepanova added a comment - 2019-01-13 12:48 See MDEV-18221 for another (non-deterministic) occurrence of the same error messages. The test case there doesn't even involve foreign keys.

Ryan Southwell added a comment - 2019-01-31 15:51 - edited

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.

Ryan Southwell added a comment - 2019-01-31 15:51 - edited 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.

Marko Mäkelä added a comment - 2019-04-23 11:36

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.

Marko Mäkelä added a comment - 2019-04-23 11:36 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.

Marko Mäkelä added a comment - 2019-04-23 14:31

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.

Marko Mäkelä added a comment - 2019-04-23 14:31 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.

Ryan Southwell added a comment - 2019-04-23 15:24

Will this be backported to 10.0?

Ryan Southwell added a comment - 2019-04-23 15:24 Will this be backported to 10.0?

People

Assignee:: Marko Mäkelä

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2019-01-04 22:15

Updated:: 2019-04-23 15:24

Resolved:: 2019-04-23 15:02

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server