← Back to team overview

maria-discuss team mailing list archive

SHOW CREATE TABLE returns binary data



one puzzling feature that we have observed with mariadb is the encoding of the result returned from "SHOW CREATE TABLE" query. In our Python-based service we issue that query and we expect the result to be returned as Python string, that worked OK except that we discovered that it breaks for one large/wide table. The query returns Python bytes object for that particular table for some reason. I looked at the result returned from query and it looks like the data is returned using different data type, here is a Python example for two different tables:

This is a regular (narrow) table and it works as expected:
>>> c.execute("SHOW CREATE TABLE `database`.`table1`")
>>> c.description
(('Table', 253, 13, 64, 64, 39, 0), ('Create Table', 253, 375, 1024, 1024, 39, 0))

253 in second tuple means that this is a MYSQL_TYPE_VAR_STRING type, which is converted into Python string.

this is for very wide table:
>>> c.execute("SHOW CREATE TABLE `qservTest_case130_qserv`.`deepCoadd_meas`")
>>> c.description
(('Table', 253, 14, 64, 64, 39, 0), ('Create Table', 250, 26235, 78705, 78705, 39, 0))

in this case type code is 250 which means MYSQL_TYPE_MEDIUM_BLOB, and Python (correctly) refuses to understand that this is really a string and keeps it as bytes.

It looks like this is triggered by the size of the string, but I could not find any document that mentions this unexpected behavior. Is there any documentation that explains what to expect in this case? Can this behavior be changed with some session or server variable?

To convert that BLOB into string I'd also need to know the encoding for its contents, is it supposed to be the same as encoding used to send VAR_STRING type or is it some mariadb-internal encoding used by information schema?