← Back to team overview

maria-discuss team mailing list archive

How to get rid of foreign keys to non existant temporary tables?



I have a server running MariaDB 10.1.29-MariaDB-6+b1 (on Debian).

I recently tried upgrading a concrete5 installation from 8.3.2 to 8.4.1 which failed running a SQL statement: ALTER TABLE AreaLayoutsUsingPresets ADD CONSTRAINT FK_7A9049A1385521EA FOREIGN KEY (arLayoutID) REFERENCES AreaLayouts (arLayoutID) ON UPDATE CASCADE ON DELETE CASCADE

With an error:
SQLSTATE[HY000]: General error: 1005 Can't create table `concrete5`.`#sql-215_264a4` (errno: 121 "Duplicate key on write or update")

Now after investigating that command should probably be done with foreign key checks disabled - but I'm not sure on that one.

The problem which I'm having now is, that I have two foreign key entries in INNODB_SYS_FOREIGN which are for temporary tables which don't exist, so I have no idea how to remove them:

concrete5/FK_3322FA75FD71026C concrete5/#sql-2a01_48f4 concrete5/Users 1 1
concrete5/FK_7A9049A1385521EA concrete5/#sql-215_26264 concrete5/AreaLayouts 1 5

I've tried creating a table with the corresponding name (#mysql50##sql-2a01_48f4) and referenced colums (uID, int(10)) for the first one. But executing

ALTER TABLE `#mysql50##sql-2a01_48f4` DROP FOREIGN KEY FK_3322FA75FD71026C

Results in error #1091. I'm a bit lost now - the update will certainly not work while these foreign keys are in the system blocking the unique name.

Any ideas?

Bernhard Weller