mysql 8.0 fix likely breaks on case insensitive

[sqlalchemy-bot]

Migrated issue, originally created by Michael Bayer (@zzzeek)

e.g. #4344, we have this:

            correct_for_wrong_fk_case = connection.execute(
                sql.text("""
                    select table_schema, table_name, column_name
                    from information_schema.columns
                    where (table_schema, table_name, lower(column_name)) in
                    :table_data;
                """).bindparams(
                    sql.bindparam("table_data", expanding=True)
                ), table_data=col_tuples
            )

if table names/schema are stored in lower case that will fail. user reporting this stack trace:

#!


INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
Traceback (most recent call last):
  File "---/venv/bin/alembic", line 11, in <module>
    load_entry_point('alembic==1.0.2', 'console_scripts', 'alembic')()
  File "---/venv/lib/python3.7/site-packages/alembic/config.py", line 502, in main
    CommandLine(prog=prog).main(argv=argv)
  File "---/venv/lib/python3.7/site-packages/alembic/config.py", line 496, in main
    self.run_cmd(cfg, options)
  File "---/venv/lib/python3.7/site-packages/alembic/config.py", line 479, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "---/venv/lib/python3.7/site-packages/alembic/command.py", line 176, in revision
    script_directory.run_env()
  File "---/venv/lib/python3.7/site-packages/alembic/script/base.py", line 427, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "---/venv/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "---/venv/lib/python3.7/site-packages/alembic/util/compat.py", line 82, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 728, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 75, in <module>
    run_migrations_online()
  File "alembic/env.py", line 70, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "---/venv/lib/python3.7/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "---/venv/lib/python3.7/site-packages/alembic/runtime/migration.py", line 321, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "---/venv/lib/python3.7/site-packages/alembic/command.py", line 156, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 415, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 451, in _run_environment
    autogen_context, migration_script)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 22, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 48, in _produce_net_changes
    autogen_context, upgrade_ops, schemas
  File "---/venv/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 313, in go
    fn(*arg, **kw)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 75, in _autogen_for_tables
    inspector, upgrade_ops, autogen_context)
  File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 137, in _compare_tables
    inspector.reflecttable(t, None)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 633, in reflecttable
    exclude_columns, _extend_on, reflection_options)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 729, in _reflect_fk
    table_name, schema, **table.dialect_kwargs)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 447, in get_foreign_keys
    **kw)
  File "<string>", line 2, in get_foreign_keys
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 54, in cache
    ret = fn(self, con, *args, **kw)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2081, in get_foreign_keys
    self._correct_for_mysql_bug_88718(fkeys, connection)
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2126, in _correct_for_mysql_bug_88718
    for col in fkey['referred_columns']
  File "---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2126, in <listcomp>
    for col in fkey['referred_columns']
KeyError: 'id'

working on reproducing with an 8.0 with lower_case_table_names set to 1

release immediately upon fix

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

if lower_case_table_names is zero, this is legal:

#!


CREATE TABLE `SomeA` (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	data VARCHAR(50), 
	PRIMARY KEY (id)
)


2018-11-08 10:40:34,396 INFO sqlalchemy.engine.base.Engine ()
2018-11-08 10:40:34,450 INFO sqlalchemy.engine.base.Engine COMMIT
2018-11-08 10:40:34,453 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE somea (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	data VARCHAR(50), 
	PRIMARY KEY (id)
)

so we cant unconditionally lower case everything. we have to check that flag

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

well it doesn't reproduce with lower_case_table_names=1 because everything is lower case everywhere. I can't test with 2 beacuse I don't have a mac.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

yeah im not finding any way to see what this really is, comparison of strings, etc. i need info from the user.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

it breaks on lower_case_table_names=2 only because information_schema.columns preserves the original case of the schema name and tablename but SHOW CREATE TABLE does not, so the lookup fails.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

https://gerrit.sqlalchemy.org/#/q/I748549bc4c27fad6394593f8ec93fc22bfd01f6c

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Use case insensitive matching on lower_case_table_names=1,2

Fixed regression caused by 🎫4344 released in 1.2.13, where the fix
for MySQL 8.0's case sensitivity problem with referenced column names when
reflecting foreign key referents is worked around using the
information_schema.columns view. The workaround was failing on OSX /
lower_case_table_names=2 which produces non-matching casing for the
information_schema.columns vs. that of SHOW CREATE TABLE, so in
case-insensitive SQL modes case-insensitive matching is now used.

Fixes: #4361
Change-Id: I748549bc4c27fad6394593f8ec93fc22bfd01f6c

→ af159c5

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Use case insensitive matching on lower_case_table_names=1,2

Fixed regression caused by 🎫4344 released in 1.2.13, where the fix
for MySQL 8.0's case sensitivity problem with referenced column names when
reflecting foreign key referents is worked around using the
information_schema.columns view. The workaround was failing on OSX /
lower_case_table_names=2 which produces non-matching casing for the
information_schema.columns vs. that of SHOW CREATE TABLE, so in
case-insensitive SQL modes case-insensitive matching is now used.

Fixes: #4361
Change-Id: I748549bc4c27fad6394593f8ec93fc22bfd01f6c
(cherry picked from commit af159c5)

→ 7f1294c