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
>>>
>>
>
