Bug #25026 `information_schema.KEY_COLUMN_USAGE`.`REFERENCED_TABLE_NAME` returns garbage
Submitted: 13 Dec 2006 5:22 Modified: 23 Jan 2007 20:44
Reporter: Jared S (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S1 (Critical)
Version:5.1.14 BETA OS:Microsoft Windows (WinXP/Linux)
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: foreign keys, info schema, MYSQL SERVER, Q1, REFERENCED_TABLE_NAME

[13 Dec 2006 5:22] Jared S
Description:
Hi.  My software is now totally broken cause of MySQL 5.1.14 BETA.  Where I expect query to return table name it instead returns garbage.

This bug is caused by the '-' unicode character in the table name.  

How to repeat:
--
-- 1. Laod my attached sample DB
--

--
-- 2. Run this script to see wierd values like 'table@002d2'
--

SELECT REFERENCED_TABLE_NAME  FROM KEY_COLUMN_USAGE where constraint_schema = 'db1'

Suggested fix:
It needs to store accurate name of the referenced table.
[13 Dec 2006 5:22] Jared S
Sample DB

Attachment: Unicode Bug.sql (text/plain), 2.06 KiB.

[13 Dec 2006 9:26] Miguel Solorzano
Thank you for the bug report.

miguel@hegel:~/dbs/5.1> bin/mysql -uroot db1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.15-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| table-1       | 
| table-2       | 
+---------------+
2 rows in set (0.00 sec)

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT REFERENCED_TABLE_NAME  FROM KEY_COLUMN_USAGE where constraint_schema =
    -> 'db1';
+-----------------------+
| REFERENCED_TABLE_NAME |
+-----------------------+
| NULL                  | 
| table@002d2           | 
| NULL                  | 
+-----------------------+
3 rows in set (0.13 sec)

mysql> 
---------------------------------------------------------------------------------
Database changed
mysql> SELECT REFERENCED_TABLE_NAME  FROM KEY_COLUMN_USAGE where constraint_schema =
    -> 'db1';
+-----------------------+
| REFERENCED_TABLE_NAME |
+-----------------------+
| NULL                  | 
| table-2               | 
| NULL                  | 
+-----------------------+
3 rows in set (0.09 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.32-debug | 
+--------------+
1 row in set (0.00 sec)

mysql>
[14 Dec 2006 12:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16944

ChangeSet@1.2359, 2006-12-14 17:35:28+04:00, gluh@mysql.com +3 -0
  Fix for bug#25026 `information_schema.KEY_COLUMN_USAGE`.`REFERENCED_TABLE_NAME` returns garbage
  unencode referenced table name
[19 Dec 2006 13:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17162

ChangeSet@1.2361, 2006-12-19 17:43:57+04:00, gluh@mysql.com +3 -0
  Fix for bug#25026 `information_schema.KEY_COLUMN_USAGE`.`REFERENCED_TABLE_NAME` returns garbage
  Unencode internal db & table name
[22 Dec 2006 7:56] Alexander Barkov
The patch is ok to push.
[9 Jan 2007 14:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17782

ChangeSet@1.2382, 2007-01-09 18:14:04+04:00, gluh@mysql.com +3 -0
  Fix for bug#25026 `information_schema.KEY_COLUMN_USAGE`.`REFERENCED_TABLE_NAME` returns garbage
  Unencode internal db & table name
[15 Jan 2007 8:23] Sergey Glukhov
Fixed in 5.1.15-beta
[23 Jan 2007 20:44] Paul Dubois
Noted in 5.1.15 changelog.

In the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table, the value displayed
for the REFERENCED_TABLE_NAME column was the table name as encoded
for disk storage, not the actual table name.