Search code examples
mysqlprestotrino

Presto show tables shows a nonexistent table due to case sensitivity with mysqldb

For the purposes of this question, I'm talking about prestosql.io or Trino after the rebranding.

I have a table (details redacted) here:

presto> SHOW TABLES FROM a.b;
     Table
----------------
 foo
...

presto> DESCRIBE a.b.foo;
Query <id> failed: line 1:1: Table 'a.b.foo' does not exist
DESCRIBE a.b.foo

and it lives inside of a mysql db. I have a presto catalog a that has a mysqldb connector to query its data from presto.

Strangely, it says foo even though my connection to mysql tells me it's Foo (f capitalized):

mysql> describe b.Foo;
+--------------+------------------+------+-----+---------------------+----------------+
| Field        | Type             | Null | Key | Default             | Extra          |
+--------------+------------------+------+-----+---------------------+----------------+
...

mysql> describe b.foo;
ERROR 1146 (42S02): Table 'b.foo' doesn't exist

And when I try to DESCRIBE this table in presto, it fails:

presto> DESCRIBE a.b.Foo;
Query <id> failed: line 1:1: Table 'a.b.foo' does not exist
DESCRIBE a.b.Foo

Upon inspection, notice how it says "Table 'a.b.foo' does not exist", not 'a.b.Foo'. Seeing how mysql is case sensitive in most unices, it seems to me that the connector is passing in an invalid table name from Presto and there's no way for me to access the actual table.

If my hypothesis is right, how can I fix this situation?

Solution

  • In order to handle non-lowercase table names in MySQL you currently need to set the case-insensitive-name-matching=true catalog property.

    The more proper support for mixed-case table and column names will be provided in https://github.com/trinodb/trino/issues/17 .