Seems to be an issue with mysql.performance_schema. Filed a JIRA-4312. --Andries
> On Jan 26, 2016, at 10:01 AM, Magnus Pierre <[email protected]> wrote: > > Ok, then I was wrong. :) > I leave it to the experts to figure out this one. > > Regards, > Magnus >> 26 jan 2016 kl. 18:55 skrev Andries Engelbrecht <[email protected]>: >> >> MySQL shows up the the following tables in INFORMATION_SCHEMA >> >> - SCHEMATA >> - TABLES >> >> But when I query COLUMNS it works for all mysql tables, apart from when I >> try to query mysql.performance_schema. >> >> 0: jdbc:drill:> select * from INFORMATION_SCHEMA.`COLUMNS` where >> TABLE_SCHEMA = 'mysql.information_schema`; >> >> +----------------+---------------------------+----------------------------------------+---------------------------+-------------------+-----------------+--------------+--------------------+---------------------------+-------------------------+--------------------+--------------------------+----------------+---------------------+----------------+---------------------+ >> | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME >> | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | >> IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | >> CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | >> NUMERIC_SCALE | DATETIME_PRECISION | INTERVAL_TYPE | INTERVAL_PRECISION | >> +----------------+---------------------------+----------------------------------------+---------------------------+-------------------+-----------------+--------------+--------------------+---------------------------+-------------------------+--------------------+--------------------------+----------------+---------------------+----------------+---------------------+ >> | DRILL | mysql.information_schema | CHARACTER_SETS >> | CHARACTER_SET_NAME | 1 | null | >> NO | CHARACTER VARYING | 32 | 128 >> | null | null | null >> | null | null | null | >> | DRILL | mysql.information_schema | CHARACTER_SETS >> | DEFAULT_COLLATE_NAME | 2 | null | >> NO | CHARACTER VARYING | 32 | 128 >> | null | null | null >> | null | null | null | >> | DRILL | mysql.information_schema | CHARACTER_SETS >> | DESCRIPTION | 3 | null | >> NO | CHARACTER VARYING | 60 | 240 >> | null | null | null >> | null | null | null | >> | DRILL | mysql.information_schema | CHARACTER_SETS >> | MAXLEN | 4 | null | >> NO | BIGINT | null | null >> | 64 | 2 | 0 >> | null | null | null | >> | DRILL | mysql.information_schema | COLLATIONS >> | COLLATION_NAME | 1 | null | >> NO | CHARACTER VARYING | 32 | 128 >> | null | null | null >> | null | null | null | >> | DRILL | mysql.information_schema | COLLATIONS >> | CHARACTER_SET_NAME | 2 | null | >> NO | CHARACTER VARYING | 32 | 128 >> | null | null | null >> | null | null | null | >> . >> . >> . >> . >> . >> +---------------+---------------------------+------------------+-----------------------+------------------+----------------+-------------+--------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+ >> 315 rows selected (0.148 seconds) >> >> >> Seems to be specific to mysql.performance_schema only. >> >> >> --Andries >> >> >>> On Jan 26, 2016, at 9:45 AM, Magnus Pierre <[email protected]> wrote: >>> >>> Do you have it in the drill info_schema? If so it would prove my guess. >>> >>> Regards, >>> Magnus >>>> 26 jan 2016 kl. 18:43 skrev Andries Engelbrecht >>>> <[email protected]>: >>>> >>>> Thx for the input. I'll file a JIRA as it seems to be a bug vs a >>>> configuration issue. >>>> >>>> In my case it seems to complain about PERFORMANCE_SCHEMA in MySQL not >>>> actually the info_schema. >>>> >>>> --Andries >>>> >>>>> On Jan 26, 2016, at 9:35 AM, Magnus Pierre <[email protected]> wrote: >>>>> >>>>> I’ve seen it as well. My unqualified guess is that the engine gets >>>>> confused with multiple databases named INFORMATION_SCHEMA and which makes >>>>> it combine metadata from two different engines. Gets the metadata of a >>>>> table from one and tries to use on the other… >>>>> >>>>> Regards, >>>>> Magnus >>>>> >>>>>> 26 jan 2016 kl. 17:19 skrev Andries Engelbrecht >>>>>> <[email protected]>: >>>>>> >>>>>> Anyone run into issues with Drill INFORMATION_SCHEMA queries when using >>>>>> the JDBC plugin with MySQL? >>>>>> >>>>>> In my case some tools are interrogating Drill's Metadata, which then >>>>>> fails when enabling the JDBC plugin with MySQL. >>>>>> Using Drill 1.4 and MySQL 5.1.73 >>>>>> >>>>>> {query} >>>>>> SELECT DISTINCT TABLE_SCHEMA as NAME_SPACE, TABLE_NAME as TAB_NAME FROM >>>>>> INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA <>'INFORMATION_SCHEMA' and >>>>>> TABLE_SCHEMA <> 'sys'; >>>>>> {/query} >>>>>> >>>>>> {result} >>>>>> Error: SYSTEM ERROR: MySQLSyntaxErrorException: Unknown table engine >>>>>> 'PERFORMANCE_SCHEMA' >>>>>> >>>>>> Fragment 0:0 >>>>>> {/result} >>>>>> >>>>>> Thanks >>>>>> --Andries >>>>> >>>> >>> >> >
