Duplicate foreign key constraint name
cjohansson opened this issue · 13 comments
I get following error with Magento 2.3.7 and Ampersand_DisableStockReservation 1.1.2
The command "bin/magento setup:upgrade --keep-generated" failed.
Exit Code: 1 (General error)
================
Cache cleared successfully
Updating modules:
Schema creation/updates:
SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID',
query was:
ALTER TABLE `order_sources` MODIFY COLUMN `extension_id` int(11) NOT NULL AUTO_INCREMENT COMMENT "Extension ID", MODIFY COLUMN `order_id` int(10) UNSIGNED NOT NULL COMMENT "Order ID", ADD CONSTRAINT `ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_order` (`entity_id`) ON DELETE CASCADEI have tried resolving with
bin/magento setup:db-declaration:generate-whitelist --module-name=Ampersand_DisableStockReservationand
bin/magento setup:db-declaration:generate-whitelistBut problem persists
Hi @cjohansson thanks for the issue.
What does your show create table order_sources look like?
I have added the missing db_schema_whitelist.json file here #66, that branch doesn't change anything for you I suppose?
Our tests are passing on the latest 2.4 as well as 2.3.7-p2 so I cannot yet reproduce this.
It looks like this:
mysql> SHOW CREATE TABLE order_sources\G
*************************** 1. row ***************************
Table: order_sources
Create Table: CREATE TABLE `order_sources` (
`extension_id` int NOT NULL AUTO_INCREMENT COMMENT 'Extension ID',
`order_id` int unsigned NOT NULL COMMENT 'Order ID',
`sources` text NOT NULL COMMENT 'Sources',
PRIMARY KEY (`extension_id`),
KEY `ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID` (`order_id`),
CONSTRAINT `ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_order` (`entity_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='Order Sources Table'
1 row in set (0.00 sec)@cjohansson shouldn't that be showing as something more like so? I thought the default length is int(11) which is what the system is trying to do ALTER TABLE order_sourcesMODIFY COLUMNextension_id int(11)
- `extension_id` int NOT NULL AUTO_INCREMENT COMMENT 'Extension ID',
+ `extension_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Extension ID', What is your database and version? Do you know of anything that would have modified these columns to change the int(11) to int?
My db_schema_whitelist.json looks identical to your proposed file.. The strange thing is I get this error in 2 environments on the same server, on another server I don't get the error..
Error server MySQL version: mysql Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)
Successful server MySQL version: Server version: 10.4.15-MariaDB-1:10.4.15+maria~focal-log mariadb.org binary distribution
hmm very strange. does the output of show create table order_sources look identical on each of the three environments?
I'm wondering if some mysql ini setting somewhere is creating the table at int and then magento tries to alter to int(11) or something
Successful MySQL server output:
MariaDB [db]> show create table order_sources\G
*************************** 1. row ***************************
Table: order_sources
Create Table: CREATE TABLE `order_sources` (
`extension_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Extension ID',
`order_id` int(10) unsigned NOT NULL COMMENT 'Order ID',
`sources` text NOT NULL COMMENT 'Sources',
PRIMARY KEY (`extension_id`),
KEY `ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID` (`order_id`),
CONSTRAINT `ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_order` (`entity_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Order Sources Table'
1 row in set (0.000 sec)I think you are right.. if I modify line
<column xsi:type="int" name="extension_id" identity="true"
padding="11" `nullable="false" comment="Extension ID"/>maybe the problem goes away on all servers... will try it out later
@cjohansson I dont know what to say 😅
It looks like the table is wrongly defined missing the int(11) information for the extension_id column, which causes magento to detect that it needs to fix with db schema, which breaks because the constraint already exists.
We havent changed the schema file so I dont know how your system has ended up with int instead of int(11) defined. It doesnt make sense as the magento system itself is clearly trying to put it back to int(11)
Maybe something to do with mysql 8, maybe something like mysqldump removed this data when its been ported across environments? https://stackoverflow.com/questions/60892749/mysql-8-ignoring-integer-lengths
I would imagine if you altered this table to be int(11) it would work
And looking at some magento core examples they do not define the padding size so it shouldn't be necessary.
With the exception of TINYINT(1), all integer padding (TINYINT > 1, SMALLINT, MEDIUMINT, INT, BIGINT) should be removed from the db_schema.xml file.
I wonder if we're in some weird middle ground, where parts of the magento app itself are still trying to make the padding size of int(11) by default but your mysql 8.0 install is removing them when it was created the first time
mysql> CREATE TABLE `order_sources` ( `extension_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Extension ID', PRIMARY KEY (`extension_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Order Sources Table';
Query OK, 0 rows affected, 2 warnings (0.12 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> show create table order_sources;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| order_sources | CREATE TABLE `order_sources` (
`extension_id` int NOT NULL AUTO_INCREMENT COMMENT 'Extension ID',
PRIMARY KEY (`extension_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='Order Sources Table' |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)
On the faulty server I ran this
mysql> ALTER TABLE `order_sources` MODIFY COLUMN `extension_id` int(11) NOT NULL AUTO_INCREMENT COMMENT "Extension ID", MODIFY COLUMN `order_id` int(10) UNSIGNED NOT NULL COMMENT "Order ID";
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 2
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE order_sources\G
*************************** 1. row ***************************
Table: order_sources
Create Table: CREATE TABLE `order_sources` (
`extension_id` int NOT NULL AUTO_INCREMENT COMMENT 'Extension ID',
`order_id` int unsigned NOT NULL COMMENT 'Order ID',
`sources` text NOT NULL COMMENT 'Sources',
PRIMARY KEY (`extension_id`),
KEY `ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID` (`order_id`),
CONSTRAINT `ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_order` (`entity_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='Order Sources Table'
1 row in set (0.00 sec)So MySQL strips away the padding..
Then ran
$ bin/magento setup:upgrade --keep-generated
Cache cleared successfully
Updating modules:
Schema creation/updates:
SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID', query was: ALTER TABLE `order_sources` MODIFY COLUMN `extension_id` int(11) NOT NULL AUTO_INCREMENT COMMENT "Extension ID", MODIFY COLUMN `order_id` int(10) UNSIGNED NOT NULL COMMENT "Order ID", ADD CONSTRAINT `ORDER_SOURCES_ORDER_ID_SALES_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_order` (`entity_id`) ON DELETE CASCADE
Also tried changing to padding="11" but it made no difference.. Magento 2 is trying to define INT(11) but the MySQL server wants a INT definition....
Turns out Magento 2.3.7 does not support MySQL Server 8.0, you can close this issue
at least that's the mystery solved @cjohansson thanks