[Maria-discuss] How to get rid of foreign keys to non existant temporary tables?
Hello,
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:
ID FOR_NAME REF_NAME N_COLS TYPE 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
participants (1)
-
Bernhard Weller