Missing information_schema tables from MySQL gateway

[benvogan]

Hi there,

I tried pointing Tableau at the MySQL gateway (harsh, I know) and unsurprisingly it failed. It did manage to handshake and identify the databases, but then it failed when trying to get the schema information. There are at least 2 problems and I'm logging them separately. The first is that it is looking for a key_column_usage table in the information_schema that does not exist. I've included the first connection where all appears well in case it has any information that is useful, but the error happens on connection 2.

New connection 1
[1] Got SQL: SET NAMES utf8
[1] Got SQL: SET character_set_results = NULL
[1] Got SQL: SET SQL_AUTO_IS_NULL = 0
[1] Got SQL: set @@sql_select_limit=DEFAULT
[1] Got SQL: SELECT CONNECTION_ID()
[1] Got SQL: SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type='enum' AND table_schema=''
[1] Got SQL: DROP TABLE #Tableau_sid_00000001_1_Connect
[1] Got SQL: SELECT CONNECTION_ID()
[1] Got SQL: SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type='enum' AND table_schema=''
[1] Got SQL: SHOW DATABASES LIKE '%'
New connection 2
[2] Got SQL: SET NAMES utf8
[2] Got SQL: SET character_set_results = NULL
[2] Got SQL: SET SQL_AUTO_IS_NULL = 0
[2] Got SQL: set @@sql_select_limit=DEFAULT
[2] Got SQL: SELECT CONNECTION_ID()
[2] Got SQL: SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'plyql1' AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' )
[2] Got SQL: SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type='enum' AND table_schema='plyql1'
[2] Got SQL: DROP TABLE #Tableau_sid_00000002_1_Connect
[2] Got SQL: SELECT TABLE_NAME, TABLE_COMMENT, TABLE_TYPE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'plyql1' AND ( TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' )
[2] Got SQL: set @@sql_select_limit=1
[2] Got SQL: SELECT *
FROM client_logs
WHERE 1=0
[2] Got SQL: set @@sql_select_limit=DEFAULT
[2] Got SQL: SELECT COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME,
ORDINAL_POSITION,
CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME IS NOT NULL
AND TABLE_NAME = 'client_logs'
AND TABLE_SCHEMA = 'plyql1'

Failed to resolve query with Plywood.
Query:
SELECT COLUMN_NAME,
 REFERENCED_TABLE_NAME,
 REFERENCED_COLUMN_NAME,
 ORDINAL_POSITION,
 CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME IS NOT NULL
 AND TABLE_NAME = 'client_logs'
 AND TABLE_SCHEMA = 'plyql1'
If you believe this query should work please create an issue on PlyQL and include this section
Issue url: https://github.com/implydata/plyql/issues
Message:
could not resolve $KEY_COLUMN_USAGE
Stack:
Error: could not resolve $KEY_COLUMN_USAGE
    at RefExpression._fillRefSubstitutions (/home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/plywood/build/plywood.js:6683:23)
    at ChainExpression._fillRefSubstitutions (/home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/plywood/build/plywood.js:7111:49)
    at ChainExpression.Expression.referenceCheckInTypeContext (/home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/plywood/build/plywood.js:6183:18)
    at ChainExpression.Expression.referenceCheck (/home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/plywood/build/plywood.js:6176:25)
    at ChainExpression.Expression._initialPrepare (/home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/plywood/build/plywood.js:6277:18)
    at /home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/plywood/build/plywood.js:6318:45
    at _fulfilled (/home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/q/q.js:834:54)
    at self.promiseDispatch.done (/home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/q/q.js:863:30)
    at Promise.promise.promiseDispatch (/home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/q/q.js:796:13)
    at /home/ben/imply-1.2.1/imply-1.2.1/dist/pivot/node_modules/q/q.js:604:44

[vogievetsky]

I guess there is no going around adding all the information_schema tables

[davidagee]

Please note that this project is now deprecated. Please see the Druid SQL docs for information on using SQL syntax with Druid.