MYSQL ERROR 1823 When SET FOREIGN_KEY_CHECKS=1

Question

I have an odd bug that is occuring when FOREIGN_KEY_CHECKS = 0 and 2 queries are executed (same in both cases except for FOREIGN_CHECK_CHECKS.

The bug results in error ERROR 1823 (HY000): Failed to add the foreign key constraint... (full error below)

If I run the queries with SET FOREIGN_KEY_CHECKS=1 it works as expected. (This almost seems backwords as turning off foreign key checks should let things go though that should NOT)

Is this a bug in mysql or am I not understanding? I couldn't find much info on this mysql error code.

MYSQL VERISON: 5.6.33

ERROR (LAST QUERY):

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE phppos_people ADD INDEX phppos_people_ibfk_1 (image_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE phppos_customers ADD CONSTRAINT phppos_customers_ibfk_1 FOREIGN KEY person_id (person_id) REFERENCES phppos_people (person_id) ON UPDATE NO ACTION ON DELETE NO ACTION;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE phppos_employees ADD CONSTRAINT phppos_employees_ibfk_1 FOREIGN KEY person_id (person_id) REFERENCES phppos_people (person_id) ON UPDATE NO ACTION ON DELETE NO ACTION;
ERROR 1823 (HY000): Failed to add the foreign key constraint 'migrate/person_id' to system tables

SUCCESS:

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE phppos_people ADD INDEX phppos_people_ibfk_1 (image_id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE phppos_customers ADD CONSTRAINT phppos_customers_ibfk_1 FOREIGN KEY person_id (person_id) REFERENCES phppos_people (person_id) ON UPDATE NO ACTION ON DELETE NO ACTION;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE phppos_employees ADD CONSTRAINT phppos_employees_ibfk_1 FOREIGN KEY person_id (person_id) REFERENCES phppos_people (person_id) ON UPDATE NO ACTION ON DELETE NO ACTION;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

SCHEMA:

mysql> show create table phppos_people;
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_people | CREATE TABLE `phppos_people` (
  `first_name` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `last_name` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `phone_number` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `email` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `address_1` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `address_2` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `city` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `state` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `zip` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `country` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `comments` text CHARACTER SET ucs2 NOT NULL,
  `image_id` int(11) DEFAULT NULL,
  `person_id` int(11) NOT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table phppos_customers;
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_customers | CREATE TABLE `phppos_customers` (
  `id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `account_number` varchar(255) CHARACTER SET ucs2 DEFAULT NULL,
  `override_default_tax` int(11) NOT NULL,
  `company_name` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `balance` decimal(23,10) NOT NULL,
  `credit_limit` decimal(23,10) DEFAULT NULL,
  `points` decimal(23,10) NOT NULL,
  `current_spend_for_points` decimal(23,10) NOT NULL,
  `current_sales_for_discount` int(11) NOT NULL,
  `taxable` int(11) NOT NULL,
  `tax_certificate` varchar(255) CHARACTER SET ucs2 NOT NULL,
  `cc_token` varchar(255) CHARACTER SET ucs2 DEFAULT NULL,
  `cc_preview` varchar(255) CHARACTER SET ucs2 DEFAULT NULL,
  `card_issuer` varchar(255) CHARACTER SET ucs2 DEFAULT NULL,
  `tier_id` int(11) DEFAULT NULL,
  `deleted` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Answer 1

Got the same Error number: 1823; Symbol: ER_FK_FAIL_ADD_SYSTEM; SQLSTATE: HY00063 Message: Failed to add the foreign key constraint '%s' to system tables

In my case the FK name was too long when I reduced the length of the constraint name the query worked.

Answer 2

Yes, this is seems to be a bug. I could verify it in MySQL 5.6+ (and something similar in MySQL 8), while 5.5 is not affected. The problem here is which constraint name MySQL uses.

In the syntax

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)

the constraint name should be either symbol or, if not given, is automatically created:

If the CONSTRAINT symbol clause is given, the symbol value, if used, must be unique in the database. A duplicate symbol will result in an error similar to: ERROR 1022 (2300): Can't write; duplicate key in table '#sql- 464_1'. If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, a name for the constraint is created automatically.

This will usually work like this:

• Use symbol
• Otherwise automatically create a name (in the form tablename_ibfk_id)

If you disable FOREIGN_KEY_CHECKS, for ALTER TABLE, but not for CREATE TABLE, it will additionally use the index_name:

• Use index_name
• Use symbol if no index_name is given
• Otherwise automatically create a name (in the form tablename_ibfk_id)

So while the index_name usually just has to be unique per table, here, the common index_name person_id in your two ALTER statements will cause the troubles, because MySQL tries to create two constraints with the same (wrong) name.

As a workaround, in your case, you could just remove the index_name person_id from your alter statements, since the index, if everything works correctly, should get the name given by symbol anyway:

The index_name value is ignored if there is already an explicitly defined index on the child table that can support the foreign key. Otherwise, MySQL implicitly creates a foreign key index that is named according to the following rules:

• If defined, the CONSTRAINT symbol value is used. Otherwise, the FOREIGN KEY index_name value is used.

• If neither a CONSTRAINT symbol or FOREIGN KEY index_name is defined, the foreign key index name is generated using the name of the referencing foreign key column.

If you want to explicitly name your index, but use a default constraint name, you can add the index before you add the foreign key, e.g. use

ALTER TABLE phppos_customers ADD INDEX person_id (person_id);
ALTER TABLE phppos_customers ADD CONSTRAINT FOREIGN KEY (person_id) REFERENCES ...

MySQL 8 shows a slightly different behaviour (when FOREIGN_KEY_CHECKS is disabled and only for ALTER TABLE): it also incorrectly uses the index_name, but only if no symbol is given - which in your case would work, since you set the symbol.