MYSQL Connector does not identifies Upper Case Database Name and Table Name

[ashish6976]

For reference please refer #2863
My MYSQL Server is running on Centos.
I am using presto to query my MYSQL database using MYSQL connector where my catalog name is mysql.

Scenario 1 - DataBase name and Table name is combination of upper case and lower case letters

Database Name - TestDB
Table Names - EmployeeDetails, EmployeeTable
Query 1 - show schemas from mysql;
Output -
Schema

information_schema
performance_schema
testdb
(3 rows)

Query 20150818_064410_00003_837eu, FINISHED, 1 node
Splits: 2 total, 2 done (100.00%)
0:00 [3 rows, 61B] [25 rows/s, 524B/s]

Query 2 - show tables from mysql.testdb;
Output -
Table

(0 rows)

Query 20150818_064532_00004_837eu, FINISHED, 1 node
Splits: 2 total, 2 done (100.00%)
0:00 [0 rows, 0B] [0 rows/s, 0B/s]

In this case presto is not able to Fetch the table names which are present in database TestDB.

The Mysql output
mysql> show tables from TestDB;
+------------------+
| Tables_in_TestDB |
+------------------+
| EmployeeDetails |
| EmployeeTable |
+------------------+
2 rows in set (0.00 sec)

Scenario 2 - DataBase name is in lower case , Table name is combination of upper case and lower case letters

Database Name - lowercasedb
Table Names - TableOne, TableTwo
Query 1 - show schemas from mysql;
Output -
Schema

information_schema
lowercasedb
performance_schema
testdb
(4 rows)

Query 20150818_065347_00005_837eu, FINISHED, 1 node
Splits: 2 total, 2 done (100.00%)
0:00 [4 rows, 77B] [27 rows/s, 522B/s]

Query 2 - show tables from mysql.lowercasedb;
Output -
Table

tableone
tabletwo
(2 rows)

Query 20150818_065432_00006_837eu, FINISHED, 1 node
Splits: 2 total, 2 done (100.00%)
0:00 [2 rows, 66B] [15 rows/s, 505B/s]

Query 3 - select * from mysql.lowercasedb.tableone;
Output -
Query 20150818_065535_00007_837eu failed: Table mysql.lowercasedb.tableone does not exist

In this scenario presto is able to fetch the table names but when I am accessing the table the its giving me an error as shown above.

The Mysql output
mysql> select * from lowercasedb.TableOne;
+-----------+-----------+
| ColumnOne | ColumnTwo |
+-----------+-----------+
| 1 | Row 1 |
| 2 | Row 2 |
+-----------+-----------+
2 rows in set (0.00 sec)

Scenario 3 - DataBase name and Table name is in lower case letters

Database Name - lowercasedb
Table Names - tableone, tabletwo
Query 1 - show schemas from mysql;
Output -
Schema

information_schema
lowercasedb
lowercasetabledb
performance_schema
testdb
(5 rows)

Query 20150818_070234_00008_837eu, FINISHED, 1 node
Splits: 2 total, 2 done (100.00%)
0:00 [5 rows, 98B] [30 rows/s, 597B/s]

Query 2 - show tables from mysql.lowercasetabledb;
Output -
Table

tableone
tabletwo
(2 rows)

Query 20150818_070253_00009_837eu, FINISHED, 1 node
Splits: 2 total, 2 done (100.00%)
0:00 [2 rows, 76B] [17 rows/s, 652B/s]

Query 3 - select * from mysql.lowercasetabledb.tableone;
Output -
columnone | columntwo
-----------+-----------
1 | Row 1
2 | Row 2
(2 rows)

Query 20150818_070319_00010_837eu, FINISHED, 1 node
Splits: 2 total, 2 done (100.00%)
0:00 [2 rows, 0B] [8 rows/s, 0B/s]

In this scenario I am able to access the tables in the database.

[rschlussel-zz]

see #2863

[leolorenzoluis]

Why was this closed? In that issue thread, he was asked to file a new bug for mysql-connector and he mentioned the reference #2863 in the beginning of the post, and you're just going to point him back to what he referenced?

[rschlussel-zz]

Sorry about that. Should have payed more attention.

[hamlet-lee]

I also ran into this problem. I made a simple fix based on version 0.185:
https://github.com/hamlet-lee/presto/tree/try_fix_mysql_uppercase_dbname_2

Not a perfect fix, but seems work in most case. If you have a existing database with uppercase name can try this fix.

[lvijay]

I believe there's another PR which addresses this #8674

[tooptoop4]

any update? hive metastore tables in mysql like DBS, PARTITIONS can't be queried.

presto> select * from mysql.metastore.DBS;
Query 20180913_015448_00010_774as failed: line 1:15: Table mysql.metastore.dbs does not exist