[MDEV-25935] Misleading error message due to FOREIGN KEY on RENAME TABLE Created: 2021-06-16  Updated: 2023-11-16

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Marko Mäkelä Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16417 Store Foreign Key metadata outside of... In Review
relates to MDEV-25506 Atomic DDL: .frm file is removed and ... Closed

 Description   

mleich reported that RENAME TABLE is reporting a misleading error message about the table existing, when in reality there is a duplicate FOREIGN KEY constraint. The following should be close to a minimal test case:

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
 
CREATE TABLE t2 (a INT PRIMARY KEY, CONSTRAINT t3_ibfk_1 FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB;
 
CREATE TABLE t2b (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB;
 
--error ER_TABLE_EXISTS_ERROR
 
RENAME TABLE t2b TO t3;
 
 
 
DROP TABLE t2b,t2,t1;

Note: the internal SYS_FOREIGN.NAME='test/t3_ibfk_1' is already occupied by table t2, and renaming the auto-assigned constraint test/t2b_ibfk_1 to test/t3_ibfk_1 would fail. The client only sees a misleading error message:

ERROR 42S01: Table './test/t3' already exists

More details are spammed to the server error log (but MDEV-25506 part 3 removed that spam in 10.6.2):

10.2 c307dc6efde682c0768dc900818f4c0b418f9c6f

 
2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: Possible reasons:
 
2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: (1) Table rename would cause two FOREIGN KEY constraints to have the same internal name in case-insensitive comparison.
 
2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: (2) Table `test`.`t3` exists in the InnoDB internal data dictionary though MySQL is trying to rename table `test`.`t2b` to it. Have you deleted the .frm file and not used DROP TABLE?
 
2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: If table `test`.`t3` is a temporary table #sql..., then it can be that there are still queries running on the table, and it will be dropped automatically when the queries end. You can drop the orphaned table inside InnoDB by creating an InnoDB table with the same name in another database and copying the .frm file to the current database. Then MySQL thinks the table exists, and DROP TABLE will succeed.

I think that we will need a dedicated error message for this case. But, I have understood that we want to avoid having storage engine specific error messages.

As far as I understand, this error source should be removed by MDEV-16417 in a future version.

 Comments   
Comment by Daniel Black [ 2023-11-16 ]

Related from MDEV-32814

 alter table user_abonnements    drop primary key;
 
ERROR 1025 (HY000): Error on rename of './test/#sql-alter-1-3' to './test/user_abonnements' (errno: 150 "Foreign key constraint is incorrectly formed")

The error message in the log (and why is it there anyway), is better than the message to the user

sage_1  | 2023-11-16  0:09:17 3 [ERROR] InnoDB: In ALTER TABLE `test`.`user_abonnements` has or is referenced in foreign key constraints which are not compatible with the new table definition.

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