Mysql connector cannot retrieve tables which includes underscore in table name

[darkrwe]

presto-server-0.139 still have mysql connector problem .

Create database in mysql:

CREATE DATABASE TUTORIAL_1;
USE TUTORIAL_1;
CREATE TABLE USERS (userid VARCHAR(15), gender CHAR(1), Age SMALLINT,country VARCHAR(50), SIGN_UP VARCHAR(50));

presto> SHOW TABLES FROM mysql.TUTORIAL_1;

Table

(0 rows)

Query 20160301_082814_00005_fjdbm, FINISHED, 2 nodes
Splits: 2 total, 2 done (100.00%)
0:00 [0 rows, 0B] [0 rows/s, 0B/s]

[petroav]

@darkrwe this should have been fixed in dbd9288 so I don't know why you're running into this.

@kokosing when you get a chance, can you take a look since you have the most context?

[kokosing]

I reproduced that. Which is strange as dbd9288 have tests for that case and it is passing. I need to investigate this.

@martint Can you please assign me to this issue, I will fix it.

[kokosing]

Somehow it is related with capital letters:
mysql:

mysql> create database UPPER;
Query OK, 1 row affected (0,00 sec)

mysql> use UPPER;
Database changed
mysql> create table lower (a int);
Query OK, 0 rows affected (0,03 sec)

mysql> create table UPPER (a int);
Query OK, 0 rows affected (0,03 sec)

presto:

presto> show tables from mysql.upper;
 Table
-------
(0 rows)

[kokosing]

For sure it is related with capital letters in mysql database name. Here is a test to reproduce the problem: kokosing/trino@c94675f

@martint Currently presto converts all the schema (database) names to lower case. Therefore when source database have different name casing (? I am not sure this word) presto is losing mapping between its schema and source schema. This also leads to ambiguities errors when source database have two schemas which differ only in letter casing. In order to fix that issue we would need to make presto to be case sensitive. It could be done in the same way as other databases do: quoted name - case sensitive, not quoted case insensitive. If you agree I will create an issue for that.

@darkrwe As a workaround you can use 'tutorial_1' instead of 'TUTORIAL_1' and your queries should work.

[cberner]

Yep, it's a known issue that Presto only supports lowercase identifiers: #2863

[kokosing]

@cberner We could close this as a duplicate.