Possible error in SHOW CREATE TABLE

[erizocosmico]

SQLAlchemy says this:

/Users/erizocosmico/Library/Python/2.7/lib/python/site-packages/sqlalchemy/dialects/mysql/reflection.py:62: SAWarning: Unknown schema content: u'`ref_name` TEXT NOT NULL,'
  util.warn("Unknown schema content: %r" % line)
/Users/erizocosmico/Library/Python/2.7/lib/python/site-packages/sqlalchemy/dialects/mysql/reflection.py:62: SAWarning: Unknown schema content: u'`commit_hash` TEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
  util.warn("Unknown schema content: %r" % line)
Traceback (most recent call last):
  File "foo.py", line 7, in <module>
    repo_df = pd.read_sql_table("refs", con=conn)
  File "/Users/erizocosmico/Library/Python/2.7/lib/python/site-packages/pandas/io/sql.py", line 242, in read_sql_table
    raise ValueError("Table %s not found" % table_name)
ValueError: Table refs not found

It's possible we have some kind of error in our SHOW CREATE TABLE implementation.

Reproduction case:

import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://root@127.0.0.1:3306/gitbase')
with engine.connect() as conn:
    repo_df = pd.read_sql_table("refs", con=conn)

[kuba--]

This what this python test gives us:

INFO:sqlalchemy.engine.base.Engine:SHOW VARIABLES LIKE 'sql_mode'
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('Variable_name', 'Value')
DEBUG:sqlalchemy.engine.base.Engine:Row ('sql_mode', '')
INFO:sqlalchemy.engine.base.Engine:SHOW VARIABLES LIKE 'lower_case_table_names'
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('Variable_name', 'Value')
INFO:sqlalchemy.engine.base.Engine:SELECT DATABASE()
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('DATABASE()',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('gitbase',)
INFO:sqlalchemy.engine.base.Engine:show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('Collation', 'Charset', 'Id', 'Default', 'Compiled', 'Sortlen')
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:SHOW FULL TABLES FROM `gitbase`
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('Table', 'Table_type')
DEBUG:sqlalchemy.engine.base.Engine:Row ('refs', 'BASE TABLE')
INFO:sqlalchemy.engine.base.Engine:SHOW FULL TABLES FROM `gitbase`
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('Table', 'Table_type')
DEBUG:sqlalchemy.engine.base.Engine:Row ('refs', 'BASE TABLE')
INFO:sqlalchemy.engine.base.Engine:SHOW CREATE TABLE `refs`
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('Table', 'Create Table')
DEBUG:sqlalchemy.engine.base.Engine:Row ('refs', 'CREATE TABLE `refs` (`name` TEXT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4')
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/pandas/io/sql.py", line 240, in read_sql_table
    meta.reflect(only=[table_name], views=True)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 4160, in reflect
    Table(name, self, **reflect_opts)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 470, in __new__
    metadata._remove_table(name, schema)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 67, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 277, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 465, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 548, in _init
    metadata, autoload_with, include_columns, _extend_on=_extend_on
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 570, in _autoload
    _extend_on=_extend_on,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1590, in run_callable
    return callable_(self, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 415, in reflecttable
    table, include_columns, exclude_columns, **opts
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 643, in reflecttable
    raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: refs

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "test.py", line 11, in <module>
    repo_df = pd.read_sql_table("refs", con=conn)
  File "/usr/local/lib/python3.7/site-packages/pandas/io/sql.py", line 242, in read_sql_table
    raise ValueError("Table %s not found" % table_name)
ValueError: Table refs not found

It can be also a bug in sqlalchemy reflection:
sqlalchemy/sqlalchemy#1117

[erizocosmico]

But that bug was fixed... 10 years ago.

…

On 18 Jan 2019, 15:59 +0100, Kuba Podgórski ***@***.***>, wrote: This what this python test gives us: SHOW VARIABLES LIKE 'sql_mode' SHOW VARIABLES LIKE 'lower_case_table_names' SELECT VERSION() SELECT DATABASE() SELECT @@transaction_isolation show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 ROLLBACK SHOW FULL TABLES FROM `gitbase` SHOW FULL TABLES FROM `gitbase` SHOW CREATE TABLE `refs` ROLLBACK It can be also a bug in sqlalchemy reflection: sqlalchemy/sqlalchemy#1117 — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[kuba--]

I think it was closed in 2008 by some bot but added to the milestone in Nov 2018:
sqlalchemy-bot added this to the 0.5.0 milestone on Nov 27, 2018

[kuba--]

I dug into sqlalhemy reflection package and I found the first (from many) issue.
The parses use following regex to match create table syntax:

^CREATE (?:\w+ +)?TABLE +`(?P<name>(?:``|[^`])+)` +\($

what requires a new line after first '('. In other words, instead of one liner we have to return:

CREATE TABLE `refs` (
`name` TEXT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

---

Another silly parsing issue is in sqlalchemy MySQLTableDefinitionParser.parse method, where we parse column names:

 for line in re.split(r"\r?\n", show_create):                            
             if line.startswith("  " + self.preparer.initial_quote):             
                self._parse_column(line, state)  

Condition if line.startswith(" " + self.preparer.initial_quote): requires two spaces before column name.
At the end correct syntax is as follows:

CREATE TABLE `refs` (
  `name` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

[kuba--]

Btw. here is a nice tool to play with: https://regex101.com

[kuba--]

Anyway, I also submitted an new issue for sqlalchemy:
sqlalchemy/sqlalchemy#4456