Bug #85947 I_S column name is upper case and breaks app compatibility
Submitted: 14 Apr 2017 8:45 Modified: 11 Jul 2017 11:33
Reporter: Oli Sennhauser Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S2 (Serious)
Version:8.0.0 OS:Any
Assigned to: CPU Architecture:Any

[14 Apr 2017 8:45] Oli Sennhauser
Description:
PHP CLI application breaks with following error:

PHP Notice:  Undefined index: schema_name in /home/mysql/product/fromdual_brman-rev662/lib/fromdual_bman.inc on line 3208

How to repeat:
$sql = sprintf("SELECT schema_name
  FROM information_schema.schemata
 WHERE schema_name NOT IN ('information_schema', 'PERFORMANCE_SCHEMA')");

if ( ! ($result = $mysqli->query($sql)) ) {
...
}
while ( $record = $result->fetch_assoc() ) {
  array_push($aSchemas, $record['schema_name']);
}

This worked perfectly fine in MySQL 5.7 and MariaDB 10.2

Result in 5.7

SELECT schema_name
  FROM information_schema.schemata
 WHERE schema_name NOT IN ('information_schema', 'PERFORMANCE_SCHEMA');

+-------------+
| schema_name |
+-------------+
| d1          |
| test        |
| world       |
+-------------+

Result in 8.0

+-------------+
| SCHEMA_NAME |
+-------------+
| d1          |
| test        |
| world       |
+-------------+

Suggested fix:
do not break with the past.
[14 Apr 2017 16:53] Miguel Solorzano
Thank you for the bug report.

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile  --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.19 Source distribution - pull: 2017-apr-05

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > SELECT schema_name
    ->   FROM information_schema.schemata
    ->  WHERE schema_name NOT IN ('information_schema', 'PERFORMANCE_SCHEMA');
+-------------+
| schema_name |
+-------------+
| d1          |
| my_db_1     |
| my_db_2     |
| mysql       |
| speed       |
| sys         |
| test        |
| test\.test  |
| x           |
| xx          |
+-------------+
10 rows in set (0.87 sec)

mysql 5.7 > exit
Bye

c:\dbs>80c

c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.2-dmr Source distribution - pull: 2017-apr-05

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > SELECT schema_name
    ->   FROM information_schema.schemata
    ->  WHERE schema_name NOT IN ('information_schema', 'PERFORMANCE_SCHEMA');
+-------------+
| SCHEMA_NAME |
+-------------+
| mysql       |
| sys         |
| x           |
+-------------+
3 rows in set (0.63 sec)

mysql 8.0 >
[15 Apr 2017 13:43] Shane Bester
Oli, take a look at
https://bugs.mysql.com/bug.php?id=84456
[11 Jul 2017 11:33] Erlend Dahl
Duplicate of

Bug#84456 column names in metadata appearing as uppercase when selecting from I_S