MySQL Bugs: #88718: Foreign key is always in lower case

Bug #88718	Foreign key is always in lower case
Submitted:	30 Nov 2017 14:26	Modified:	10 Oct 2018 21:51
Reporter:	Bogdan Stepanenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	8.0.1, 8.0.2, 8.0.3	OS:	Other (Docker images of MySQL)
Assigned to:		CPU Architecture:	Any
Tags:	8.0.1 changelog, foreign key, lower case

Description:
I run unit tests of yii2 php framework
Test fails on MySQL schema tests
I can see some differences with latest version of MySQL

see `foreignColumnNames`
```
--- Expected
+++ Actual
@@ @@
 Array (
-    '{"columnNames":["C_fk_id_1","C_fk_id_2"],"foreignColumnNames":["C_id_1","C_id_2"],"foreignTableName":"t_constraints_2","onDelete":"CASCADE","onUpdate":"CASCADE"}' => yii\db\ForeignKeyConstraint Object (...)
+    '{"columnNames":["C_fk_id_1","C_fk_id_2"],"foreignColumnNames":["c_id_1","c_id_2"],"foreignTableName":"t_constraints_2","onDelete":"CASCADE","onUpdate":"CASCADE"}' => yii\db\ForeignKeyConstraint Object (...)
 )
```

I try it with another versions: 8.0.3, 8.0.2. 8.0.1
Problem still alive.
I try with older version 5.7.22. All is Ok.
I find in changelog
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html
>  referenced_table_schema and referenced_table_name field values in the mysql.foreign_keys data dictionary table were not stored in lowercase when lower_case_table_names was enabled. (Bug #25495714)

I try with 8.0.0. All is Ok.

How to repeat:
I think we need exec sql from this file
[https://github.com/yiisoft/yii2/blob/master/tests/data/mysql.sql](https://raw.githubuserco...)
and 

error was happens with table 
```sql
CREATE TABLE `T_constraints_3`
(
    `C_id` INT NOT NULL,
    `C_fk_id_1` INT NOT NULL,
    `C_fk_id_2` INT NOT NULL,
    CONSTRAINT `CN_constraints_3` FOREIGN KEY (`C_fk_id_1`, `C_fk_id_2`) REFERENCES `T_constraints_2` (`C_id_1`, `C_id_2`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = 'InnoDB' DEFAULT CHARSET = 'utf8';
```

My way

I clone repo
```
git clone https://github.com/yiisoft/yii2.git
```

I prepare common composition `yii2-phpunit-docker-composition.yml` outside of repo
```yml
# Local run unit tests
# for docker-compose CLI like this manual https://blog.jetbrains.com/phpstorm/2016/11/docker-remote-interpreters/
# run directly:
# docker-compose -f ../yii2-phpunit-docker-composition.yml run --rm --entrypoint bash php
# and run `composer install` before first time use
version: '2'
services:
  php:
    image: bscheshir/codeception:php7.1.12-fpm-yii2 #contain phpunit
    volumes:
      - ./yii2/tests/data/config-docker.php:/var/www/html/tests/data/config.php # use named connection for db
      - ./yii2:/var/www/html #src and tests shared to container
      - ~/.composer/cache:/root/.composer/cache
    environment:
      TZ: Europe/Moscow
      XDEBUG_CONFIG: "remote_host=192.168.0.83 remote_port=9002 remote_enable=On"
      PHP_IDE_CONFIG: "serverName=codeception"
    depends_on:
      - pgsql
      - mysql
# example for pgsql
# also add ./tests/data/config.local.php
# <?php
# $config['databases']['pgsql']['dsn'] = 'pgsql:host=pgsql;port=5432;dbname=yii2advanced';
# $config['databases']['pgsql']['username'] = 'yii2advanced';
# $config['databases']['pgsql']['password'] = 'yii2advanced';
  pgsql:
    image: postgres:10.1
    environment:
      TZ: Europe/Moscow
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: yiitest
      POSTGRES_USER: postgres
    networks:
      default:
        aliases:
         - postgres
  mysql:
    image: mysql:8.0.3
    entrypoint: ['/entrypoint.sh', '--character-set-server=utf8', '--collation-server=utf8_general_ci']
    environment:
      TZ: Europe/Moscow
      MYSQL_ROOT_PASSWORD: secret
      MYSQL_DATABASE: yiitest
      MYSQL_USER: travis
      MYSQL_PASSWORD: travis
```

I install dependencies
```sh
docker-compose -f ../yii2-phpunit-docker-composition.yml run --rm --entrypoint composer php install
```

I run tests
```sh
docker-compose -f ../yii2-phpunit-docker-composition.yml run --rm --entrypoint bash php
root@28852ab0ce9a:/var/www/html# vendor/bin/phpunit --filter testTableSchemaConstraints tests/framework/db/mysql/SchemaTest.php

```

https://github.com/yiisoft/yii2/pull/15257#issuecomment-348428679

Here is a test case for this issue that might be simpler to reproduce:

CREATE DATABASE `my_db`;
USE `my_db`;
CREATE TABLE `table_one` (
  `Id` INTEGER PRIMARY KEY
);
CREATE TABLE `table_two` (
  `TABLE_ONE_ID` INTEGER,
  FOREIGN KEY (`TABLE_ONE_ID`) REFERENCES `table_one` (`Id`)
);
SHOW CREATE TABLE `table_two`;

The actual output of `SHOW CREATE TABLE table_two;` includes:
  CONSTRAINT `table_two_ibfk_1` FOREIGN KEY (`TABLE_ONE_ID`) REFERENCES `table_one` (`id`)

The expected output would have instead included:
  CONSTRAINT `table_two_ibfk_1` FOREIGN KEY (`TABLE_ONE_ID`) REFERENCES `table_one` (`Id`)

The only difference between the actual and the expected output is (`Id`) vs (`id`), that is the capital `I` vs. lower case `i`.

The documentation does state that column names are case-insensitive (https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html -- "Column [...] names are not case sensitive on any platform [...]"). So technically speaking, `id` is the same as `Id`. However, I decided to report this anyways because the behavior change between 5.7.20 and 8.0 seems unintentional.

MySQL Versions:
I've seen this issue in MySQL 8.0 and 8.0.3
I've seen that MySQL 5.7.20 does *not* have this issue.

System:
Running MySQL in docker, with the docker host being my Ubuntu 17.10 system.

Output of `$ uname -a` of the docker host:
Linux teddy-lenovo 4.13.0-19-generic #22-Ubuntu SMP Mon Dec 4 11:58:07 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

Exact commands to spin up MySQL Docker container:
$ docker create --name mixed_case_db -p 3306:3306 --env MYSQL_ALLOW_EMPTY_PASSWORD=true mysql:8.0.3
$ docker start mixed_case_db
$ docker exec -it mixed_case_db mysql
... then run the SQL statements from above ...

In case it's relevant:
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.02 sec)

Hello Bogdan, Teddy,

Thank you for the report and simplified test case.

Thanks,
Umesh

Posted by developer:
 
Hello!

Let me clarify that server branches before 8.0 normally use in FK definition version
of referenced column name which comes from parent table definition, and not from
FK definition itself.

For example on 5.7.25:

CREATE TABLE parent (Pk VARCHAR(10) PRIMARY KEY);
CREATE TABLE child (fk VARCHAR(10), FOREIGN KEY (fk) REFERENCES parent(pK));
SHOW CREATE TABLE child;
#Table	Create Table
#child	CREATE TABLE `child` (
#  `fk` varchar(10) DEFAULT NULL,
#  KEY `fk` (`fk`),
#  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `parent` (`Pk`)
#) ENGINE=InnoDB DEFAULT CHARSET=latin1

Notice `Pk` in the REFERENCES clause in SHOW CREATE TABLE output and not 'pK'.

are there plans to fix this or should downstream tools try to work around this issue for the foreseeable future?  SQLAlchemy maintainer here.

Hello Mike!

Yes, we are working on the fix for this issue.

Posted by developer:
 
Fixed in 8.0.14.

Names of referenced columns of foreign keys were always shown in
lowercase in SHOW CREATE TABLE output and the
INFORMATION_SCHEMA.KEY_COLUMN_USAGE table.

https://bugs.mysql.com/bug.php?id=93765 marked as duplicate of this one.

I've found a similar issue related to table names with lower_case_table_names=2, which nonetheless does not seem to occur on MySQL 5.7, which I've reported at https://bugs.mysql.com/bug.php?id=96365 .