MySQL Bugs: #90669: Information_schema referential

Bug #90669	Information_schema referential_constraints problem
Submitted:	27 Apr 2018 13:20	Modified:	14 Mar 2019 10:54
Reporter:	Emmanuel CARVIN	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.7.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I did my dump with MySQL Workbench 6.3.10
I tried to restore it with MySQL Workbench 8.0.11 (included with the msi of MySQL Community Server 8.0.11) wich had some error for other reason (https://bugs.mysql.com/bug.php?id=90624).

Once my dump corrected and restored, I realized something weird.

In schema information all my constraints were in "NO ACTION" mode as you can see in this picture: https://ibb.co/jQxbtc

But when I was doing an ALTER table in the workbench my constraints were in "RESTRICT" mode as you can see in this picture: https://ibb.co/btuvmx

By removing the constraints and recreating them, this problem disappeared and I had the same thing on both sides.

How to repeat:
- Make a dump on MySQL 5.7.22 with Workbench 6.3.10
- Restore it on MySQL 8.0.11 with Workbench 8.0.11
- Check constraint with ALTER graphical function on Workbench 8.0.11 
- Check constraint with this request : SELECT  * FROM information_schema.referential_constraints

Hello Emmanuel,

Thank you for the bug report.
Verified this behavior on MySQL Workbench in 8.0.11 version.

Thanks,
Chiranjeevi.

Screenshot

Attachment: Bug_90669.PNG (image/png, text), 33.01 KiB.

Unfortunately we had trouble reproducing this issue based on the information given.

Is it still a problem in 8.0.13? One theory is that it has been fixed by

Bug#91110 Foreign key reference option RESTRICT ignored after mysqldump and reload.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hello,

It is not really MySQL8 which has this problem but the version 5.7 also Here is an example of reproduction of this error.

the problem is caused by "SET FOREIGN_KEY_CHECKS = 0;" wich is created foreign key with default option even if we specify "RESTRICT" 

CREATE DATABASE IF NOT EXISTS testmysql DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;

USE testmysql;

CREATE TABLE `abonnement` (
  `idabonnement` INT NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idabonnement`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_general_ci;

CREATE TABLE `abonnementcustomer` (
  `idabonnementcustomer` INT NOT NULL AUTO_INCREMENT,
  `idabonnement` INT NULL,
  `nom` VARCHAR(45) NULL,
  PRIMARY KEY (`idabonnementcustomer`));

SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO abonnement (`idabonnement`, `code`) VALUES (1, 'test');
INSERT INTO abonnementcustomer (`idabonnementcustomer`, `idabonnement`, `nom`) VALUES (1, 1, 'test');

  
ALTER TABLE `abonnementcustomer`
ADD CONSTRAINT `fk_abonnementcustomer_1`
	FOREIGN KEY (`idabonnement`)
	REFERENCES `abonnement` (`idabonnement`)
	ON DELETE RESTRICT
	ON UPDATE RESTRICT
;

SET FOREIGN_KEY_CHECKS = 1;

SELECT  OptForeignKey.constraint_name AS NomContrainte, 
OptForeignKey.table_name AS TableSource,
(SELECT column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneSource, 
OptForeignKey.referenced_table_name AS TableReference, 
(SELECT referenced_column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneReference, 
OptForeignKey.update_rule AS OptionUpdate, 
OptForeignKey.delete_rule AS OptionDelete

FROM information_schema.referential_constraints AS OptForeignKey 

WHERE OptForeignKey.constraint_schema = 'testmysql'
AND OptForeignKey.table_name = 'abonnementcustomer'
GROUP BY OptForeignKey.constraint_name;

Please disregard my last comment.

mysql [localhost:5725] {msandbox} ((none)) >
mysql [localhost:5725] {msandbox} ((none)) > CREATE DATABASE IF NOT EXISTS testmysql DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5725] {msandbox} ((none)) >
mysql [localhost:5725] {msandbox} ((none)) > USE testmysql;
Database changed
mysql [localhost:5725] {msandbox} (testmysql) >
  `idabonnement` INT NOT NULL AUTO_INCREMENT,
mysql [localhost:5725] {msandbox} (testmysql) > CREATE TABLE `abonnement` (
    ->   `idabonnement` INT NOT NULL AUTO_INCREMENT,
    ->   `code` VARCHAR(45) NULL DEFAULT NULL,
    ->   PRIMARY KEY (`idabonnement`))
    -> ENGINE = InnoDB
    -> DEFAULT CHARACTER SET = latin1
    -> COLLATE = latin1_general_ci;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > CREATE TABLE `abonnementcustomer` (
    ->   `idabonnementcustomer` INT NOT NULL AUTO_INCREMENT,
    ->   `idabonnement` INT NULL,
    ->   `nom` VARCHAR(45) NULL,
    ->   PRIMARY KEY (`idabonnementcustomer`));
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) > INSERT INTO abonnement (`idabonnement`, `code`) VALUES (1, 'test');
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) > INSERT INTO abonnementcustomer (`idabonnementcustomer`, `idabonnement`, `nom`) VALUES (1, 1, 'test');
Query OK, 1 row affected (0.01 sec)

mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > ALTER TABLE `abonnementcustomer`
    -> ADD CONSTRAINT `fk_abonnementcustomer_1`
    -> FOREIGN KEY (`idabonnement`)
    -> REFERENCES `abonnement` (`idabonnement`)
    -> ON DELETE RESTRICT
    -> ON UPDATE RESTRICT
    -> ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) > SELECT  OptForeignKey.constraint_name AS NomContrainte,
    -> OptForeignKey.table_name AS TableSource,
    -> (SELECT column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneSource,
    -> OptForeignKey.referenced_table_name AS TableReference,
    -> (SELECT referenced_column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneReference,
    -> OptForeignKey.update_rule AS OptionUpdate,
    -> OptForeignKey.delete_rule AS OptionDelete
    ->
    -> FROM information_schema.referential_constraints AS OptForeignKey
    ->
    -> WHERE OptForeignKey.constraint_schema = 'testmysql'
    -> AND OptForeignKey.table_name = 'abonnementcustomer'
    -> GROUP BY OptForeignKey.constraint_name;
ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'information_schema.OptForeignKey.REFERENCED_TABLE_NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql [localhost:5725] {msandbox} (testmysql) > select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) > set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) > SELECT  OptForeignKey.constraint_name AS NomContrainte,
    -> OptForeignKey.table_name AS TableSource,
    -> (SELECT column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneSource,
    -> OptForeignKey.referenced_table_name AS TableReference,
    -> (SELECT referenced_column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneReference,
    -> OptForeignKey.update_rule AS OptionUpdate,
    -> OptForeignKey.delete_rule AS OptionDelete
    ->
    -> FROM information_schema.referential_constraints AS OptForeignKey
    ->
    -> WHERE OptForeignKey.constraint_schema = 'testmysql'
    -> AND OptForeignKey.table_name = 'abonnementcustomer'
    -> GROUP BY OptForeignKey.constraint_name;
+-------------------------+--------------------+---------------+----------------+------------------+--------------+--------------+
| NomContrainte           | TableSource        | ColonneSource | TableReference | ColonneReference | OptionUpdate | OptionDelete |
+-------------------------+--------------------+---------------+----------------+------------------+--------------+--------------+
| fk_abonnementcustomer_1 | abonnementcustomer | NULL          | abonnement     | NULL             | NO ACTION    | NO ACTION    |
+-------------------------+--------------------+---------------+----------------+------------------+--------------+--------------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) >

mysql [localhost:5725] {msandbox} (mysql) > CREATE DATABASE IF NOT EXISTS testmysql DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5725] {msandbox} (mysql) >
mysql [localhost:5725] {msandbox} (mysql) > USE testmysql;
  `idabonnementcustomer` INT NOT NULL AUTO_INCREMENT,
  `idabonnement` INT NULL,
  `nom` VARCHAR(45) NULL,
  PRIMARY KEY (`idabonnementcustomer`));

INSERT INTO abonnement (`idabonnement`, `code`) VALUES (1, 'test');
INSERT INTO abonnementcustomer (`idabonnementcustomer`, `idabonnement`, `nom`) VALUES (1, 1, 'test');

ALTER TABLE `abonnementcustomer`
ADD CONSTRAINT `fk_abonnementcustomer_1`
        FOREIGN KEY (`idabonnement`)
        REFERENCES `abonnement` (`idabonnement`)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
;

SELECT  OptForeignKey.constraint_name AS NomContrainte,
OptForeignKey.table_name AS TableSource,
(SELECT column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneSource,
OptForeignKey.referenced_table_name AS TableReference,
(SELECT referenced_column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneReference,
OptForeignKey.update_rule AS OptionUpdate,
OptForeignKey.delete_rule AS OptionDelete

FROM information_schema.referential_constraints AS OptForeignKey

WHERE OptForeignKey.constraint_schema = 'testmysql'
AND OptForeignKey.table_name = 'abonnementcustomer'
GROUP BY OptForeignKey.constraint_name;
Database changed
mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > CREATE TABLE `abonnement` (
    ->   `idabonnement` INT NOT NULL AUTO_INCREMENT,
    ->   `code` VARCHAR(45) NULL DEFAULT NULL,
    ->   PRIMARY KEY (`idabonnement`))
    -> ENGINE = InnoDB
    -> DEFAULT CHARACTER SET = latin1
    -> COLLATE = latin1_general_ci;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > CREATE TABLE `abonnementcustomer` (
    ->   `idabonnementcustomer` INT NOT NULL AUTO_INCREMENT,
    ->   `idabonnement` INT NULL,
    ->   `nom` VARCHAR(45) NULL,
    ->   PRIMARY KEY (`idabonnementcustomer`));
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > INSERT INTO abonnement (`idabonnement`, `code`) VALUES (1, 'test');
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) > INSERT INTO abonnementcustomer (`idabonnementcustomer`, `idabonnement`, `nom`) VALUES (1, 1, 'test');
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > ALTER TABLE `abonnementcustomer`
    -> ADD CONSTRAINT `fk_abonnementcustomer_1`
    -> FOREIGN KEY (`idabonnement`)
    -> REFERENCES `abonnement` (`idabonnement`)
    -> ON DELETE RESTRICT
    -> ON UPDATE RESTRICT
    -> ;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) >
mysql [localhost:5725] {msandbox} (testmysql) > SELECT  OptForeignKey.constraint_name AS NomContrainte,
    -> OptForeignKey.table_name AS TableSource,
    -> (SELECT column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneSource,
    -> OptForeignKey.referenced_table_name AS TableReference,
    -> (SELECT referenced_column_name FROM information_schema.key_column_usage WHERE constraint_schema = 'rebelle_prod' AND table_name = '001abonnementclient' AND constraint_name = OptForeignKey.constraint_name LIMIT 1) AS ColonneReference,
    -> OptForeignKey.update_rule AS OptionUpdate,
    -> OptForeignKey.delete_rule AS OptionDelete
    ->
    -> FROM information_schema.referential_constraints AS OptForeignKey
    ->
    -> WHERE OptForeignKey.constraint_schema = 'testmysql'
    -> AND OptForeignKey.table_name = 'abonnementcustomer'
    -> GROUP BY OptForeignKey.constraint_name;
+-------------------------+--------------------+---------------+----------------+------------------+--------------+--------------+
| NomContrainte           | TableSource        | ColonneSource | TableReference | ColonneReference | OptionUpdate | OptionDelete |
+-------------------------+--------------------+---------------+----------------+------------------+--------------+--------------+
| fk_abonnementcustomer_1 | abonnementcustomer | NULL          | abonnement     | NULL             | RESTRICT     | RESTRICT     |
+-------------------------+--------------------+---------------+----------------+------------------+--------------+--------------+
1 row in set (0.01 sec)

mysql [localhost:5725] {msandbox} (testmysql) >

- changed version to 5.7.25
- changed os to any (reproduced on linux)
- changed severity to S3 (I disagree this is "critical")

I'm verifying the bug for 5.7.25 (cannot reproduce this on 8.0.15)

Thanks for report and a test case
Bogdan

Confirmed that this exact behavior is happening with me when restoring DB with MySQL 8.0.22