[sqlalchemy] Problem with Alembic autogenerate after updating to 1.0.2

[a***@gmail.com]

Hi

I had a problem with alembic autogenerate after updating to version 1.0.2.
I'm running MySQL 8.0.11 and Python 3.7.1
No matter how my base is defined, I always get the same error. I went
through the error and found out it comes from function "

_correct_for_mysql_bug_88718(self, fkeys, connection)" in module "base.py" with this explaination:

# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy


This is log of error:

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'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

Hi
I had a problem with alembic autogenerate after updating to version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1
No matter how my base is defined, I always get the same error. I went through the error and found out it comes from function "

Did you also update SQLAlchemy version? The check you see has to do
with SQLAlchemy version 1.2.13. Can you confirm that using SQLAlchemy
1.2.12 resolves the issue? I can work towards making this check more
defensive. Additionally let me run the tests on MySQL 8 to see if
I'm missing something.

# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
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
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'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

Post by Mike Bayer

Hi
I had a problem with alembic autogenerate after updating to version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1
No matter how my base is defined, I always get the same error. I went through the error and found out it comes from function "

Did you also update SQLAlchemy version? The check you see has to do
with SQLAlchemy version 1.2.13. Can you confirm that using SQLAlchemy
1.2.12 resolves the issue? I can work towards making this check more
defensive. Additionally let me run the tests on MySQL 8 to see if
I'm missing something.

no failures here, can you please provide a sample database model that
produces this error?

Post by Mike Bayer

# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
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
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'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

easier, if you can give me a "SHOW CREATE TABLE" for a table here that
has foreign key constraints and refers to a column named "id", "ID",
"iD", something with those two letters in it.

Post by Mike Bayer

Post by Mike Bayer

Hi
I had a problem with alembic autogenerate after updating to version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1
No matter how my base is defined, I always get the same error. I went through the error and found out it comes from function "

Did you also update SQLAlchemy version? The check you see has to do
with SQLAlchemy version 1.2.13. Can you confirm that using SQLAlchemy
1.2.12 resolves the issue? I can work towards making this check more
defensive. Additionally let me run the tests on MySQL 8 to see if
I'm missing something.

no failures here, can you please provide a sample database model that
produces this error?

Post by Mike Bayer

# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
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
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'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

What operating system platform are you on ?

can you run this for me please?

MariaDB [(none)]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
easier, if you can give me a "SHOW CREATE TABLE" for a table here that
has foreign key constraints and refers to a column named "id", "ID",
"iD", something with those two letters in it.

Post by Mike Bayer

Post by Mike Bayer

Hi
I had a problem with alembic autogenerate after updating to version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1
No matter how my base is defined, I always get the same error. I went through the error and found out it comes from function "

Did you also update SQLAlchemy version? The check you see has to do
with SQLAlchemy version 1.2.13. Can you confirm that using SQLAlchemy
1.2.12 resolves the issue? I can work towards making this check more
defensive. Additionally let me run the tests on MySQL 8 to see if
I'm missing something.

no failures here, can you please provide a sample database model that
produces this error?

Post by Mike Bayer

# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
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
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'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[a***@gmail.com]

I forgot to mention that I'm using all lowercase names for my tables and
columns. The primary keys are all "id"
BTW I ran the command and got this result:

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 2 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
What operating system platform are you on ?
can you run this for me please?
MariaDB [(none)]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
easier, if you can give me a "SHOW CREATE TABLE" for a table here that
has foreign key constraints and refers to a column named "id", "ID",
"iD", something with those two letters in it.

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
Hi
I had a problem with alembic autogenerate after updating to

version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
No matter how my base is defined, I always get the same error. I

went through the error and found out it comes from function "

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
Did you also update SQLAlchemy version? The check you see has to do
with SQLAlchemy version 1.2.13. Can you confirm that using

SQLAlchemy

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
1.2.12 resolves the issue? I can work towards making this check

more

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
defensive. Additionally let me run the tests on MySQL 8 to see

if

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
I'm missing something.

no failures here, can you please provide a sample database model that
produces this error?

Post by Mike Bayer

Post by a***@gmail.com
_correct_for_mysql_bug_88718(self, fkeys, connection)" in module
# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional

DDL.

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File "---/venv/bin/alembic", line 11, in <module>
load_entry_point('alembic==1.0.2', 'console_scripts',

'alembic')()

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File "---/venv/lib/python3.7/site-packages/alembic/config.py",

line 502, in main

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
CommandLine(prog=prog).main(argv=argv)
File "---/venv/lib/python3.7/site-packages/alembic/config.py",

line 496, in main

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
self.run_cmd(cfg, options)
File "---/venv/lib/python3.7/site-packages/alembic/config.py",

line 479, in run_cmd

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
**dict((k, getattr(options, k, None)) for k in kwarg)
File "---/venv/lib/python3.7/site-packages/alembic/command.py",

line 176, in revision

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
script_directory.run_env()
File

"---/venv/lib/python3.7/site-packages/alembic/script/base.py", line 427, in
run_env

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
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

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
module = load_module_py(module_id, path)
File

"---/venv/lib/python3.7/site-packages/alembic/util/compat.py", line 82, in
load_module_py

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
spec.loader.exec_module(module)
File "<frozen importlib._bootstrap_external>", line 728, in

exec_module

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File "<frozen importlib._bootstrap>", line 219, in

_call_with_frames_removed

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
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

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
self.get_context().run_migrations(**kw)
File

"---/venv/lib/python3.7/site-packages/alembic/runtime/migration.py", line
321, in run_migrations

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File "---/venv/lib/python3.7/site-packages/alembic/command.py",

line 156, in retrieve_migrations

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
revision_context.run_autogenerate(rev, context)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line
415, in run_autogenerate

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
self._run_environment(rev, migration_context, True)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line
451, in _run_environment

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
autogen_context, migration_script)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 22, in _populate_migration_script

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
_produce_net_changes(autogen_context, upgrade_ops)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 48, in _produce_net_changes

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
autogen_context, upgrade_ops, schemas
File

"---/venv/lib/python3.7/site-packages/alembic/util/langhelpers.py", line
313, in go

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
fn(*arg, **kw)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 75, in _autogen_for_tables

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
inspector, upgrade_ops, autogen_context)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 137, in _compare_tables

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
inspector.reflecttable(t, None)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 633, in reflecttable

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
exclude_columns, _extend_on, reflection_options)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 729, in _reflect_fk

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
table_name, schema, **table.dialect_kwargs)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 447, in get_foreign_keys

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
**kw)
File "<string>", line 2, in get_foreign_keys
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 54, in cache

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
ret = fn(self, con, *args, **kw)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py",
line 2081, in get_foreign_keys

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
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

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
for col in fkey['referred_columns']
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py",
line 2126, in <listcomp>

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
for col in fkey['referred_columns']
KeyError: 'id'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete,

and Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
---
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
To unsubscribe from this group and stop receiving emails from it,

<javascript:>.

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

OK, it's the filesystem type 2 combined with the fact that the
database is named "Test", capital T, on a case insensitive filesystem.
ill have a fix momentarily. SQLAlhcemy 1.2.12 will work for you for
now, just skip 1.2.13.

I forgot to mention that I'm using all lowercase names for my tables and columns. The primary keys are all "id"
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 2 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
What operating system platform are you on ?
can you run this for me please?
MariaDB [(none)]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
easier, if you can give me a "SHOW CREATE TABLE" for a table here that
has foreign key constraints and refers to a column named "id", "ID",
"iD", something with those two letters in it.

Post by Mike Bayer

Post by Mike Bayer

Hi
I had a problem with alembic autogenerate after updating to version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1
No matter how my base is defined, I always get the same error. I went through the error and found out it comes from function "

Did you also update SQLAlchemy version? The check you see has to do
with SQLAlchemy version 1.2.13. Can you confirm that using SQLAlchemy
1.2.12 resolves the issue? I can work towards making this check more
defensive. Additionally let me run the tests on MySQL 8 to see if
I'm missing something.

no failures here, can you please provide a sample database model that
produces this error?

Post by Mike Bayer

# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
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
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'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

this is https://bitbucket.org/zzzeek/sqlalchemy/issues/4361/mysql-80-fix-likely-breaks-on-case
and a patch is working through at
https://gerrit.sqlalchemy.org/#/q/I748549bc4c27fad6394593f8ec93fc22bfd01f6c.

Post by Mike Bayer
OK, it's the filesystem type 2 combined with the fact that the
database is named "Test", capital T, on a case insensitive filesystem.
ill have a fix momentarily. SQLAlhcemy 1.2.12 will work for you for
now, just skip 1.2.13.

I forgot to mention that I'm using all lowercase names for my tables and columns. The primary keys are all "id"
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 2 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
What operating system platform are you on ?
can you run this for me please?
MariaDB [(none)]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
easier, if you can give me a "SHOW CREATE TABLE" for a table here that
has foreign key constraints and refers to a column named "id", "ID",
"iD", something with those two letters in it.

Post by Mike Bayer

Post by Mike Bayer

Hi
I had a problem with alembic autogenerate after updating to version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1
No matter how my base is defined, I always get the same error. I went through the error and found out it comes from function "

Did you also update SQLAlchemy version? The check you see has to do
with SQLAlchemy version 1.2.13. Can you confirm that using SQLAlchemy
1.2.12 resolves the issue? I can work towards making this check more
defensive. Additionally let me run the tests on MySQL 8 to see if
I'm missing something.

no failures here, can you please provide a sample database model that
produces this error?

Post by Mike Bayer

# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
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
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'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[a***@gmail.com]

Dear Mike,

Thank you very much. Your solution worked for me.

I have noticed a few minor issues as well:

1. When I try to change the type of a primary key (for example from
BigInteger to Integer), if I use auto generate, the corresponding function
has this key "existing_nullable=True" but does not have "nullable=True". As
a result, running that function causes the MySQL error "Primary key can not
be null". I always manually add "nullable=True" where it is needed.

2. When I try to drop a column which is a foreign key to another table, two
actions should be taken:
a. Removing foreign key constraint
b. Dropping the column
The sequence of these action is important. When I use Autogenerate option,
usually dropping column is before removing the constraint which causes
error. Again I change the sequence manually if needed.

3. When the migration code is generated and we run it, if an error comes up
in midst of the migration function, it means that the code has been
executed partially and the schema has been changed partially. I'm wondering
if there is a solution to roll back the partial changes (similar to roll
back function in sqlalchemy). The problem is that even after fixing the
migration code, it is not possible to run it since it has been executed
partially!


I highly appreciate your effort in SQLAlchemy and Alembic and try to do a
small help with improving your excellent work :)

Post by Mike Bayer
OK, it's the filesystem type 2 combined with the fact that the
database is named "Test", capital T, on a case insensitive filesystem.
ill have a fix momentarily. SQLAlhcemy 1.2.12 will work for you for
now, just skip 1.2.13.

Post by a***@gmail.com
I forgot to mention that I'm using all lowercase names for my tables and

columns. The primary keys are all "id"

Post by a***@gmail.com
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 2 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
What operating system platform are you on ?
can you run this for me please?
MariaDB [(none)]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
easier, if you can give me a "SHOW CREATE TABLE" for a table here

that

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer
has foreign key constraints and refers to a column named "id", "ID",
"iD", something with those two letters in it.

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
Hi
I had a problem with alembic autogenerate after updating to

version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
No matter how my base is defined, I always get the same error.

I went through the error and found out it comes from function "

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
Did you also update SQLAlchemy version? The check you see has to

do

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
with SQLAlchemy version 1.2.13. Can you confirm that using

SQLAlchemy

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
1.2.12 resolves the issue? I can work towards making this check

more

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
defensive. Additionally let me run the tests on MySQL 8 to

see if

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
I'm missing something.

no failures here, can you please provide a sample database model

that

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
produces this error?

Post by Mike Bayer

Post by a***@gmail.com
_correct_for_mysql_bug_88718(self, fkeys, connection)" in
# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional

DDL.

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File "---/venv/bin/alembic", line 11, in <module>
load_entry_point('alembic==1.0.2', 'console_scripts',

'alembic')()

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File

"---/venv/lib/python3.7/site-packages/alembic/config.py", line 502, in main

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
CommandLine(prog=prog).main(argv=argv)
File

"---/venv/lib/python3.7/site-packages/alembic/config.py", line 496, in main

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
self.run_cmd(cfg, options)
File

"---/venv/lib/python3.7/site-packages/alembic/config.py", line 479, in
run_cmd

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
**dict((k, getattr(options, k, None)) for k in kwarg)
File

"---/venv/lib/python3.7/site-packages/alembic/command.py", line 176, in
revision

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
script_directory.run_env()
File

"---/venv/lib/python3.7/site-packages/alembic/script/base.py", line 427, in
run_env

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
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

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
module = load_module_py(module_id, path)
File

"---/venv/lib/python3.7/site-packages/alembic/util/compat.py", line 82, in
load_module_py

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
spec.loader.exec_module(module)
File "<frozen importlib._bootstrap_external>", line 728, in

exec_module

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File "<frozen importlib._bootstrap>", line 219, in

_call_with_frames_removed

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
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

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
self.get_context().run_migrations(**kw)
File

"---/venv/lib/python3.7/site-packages/alembic/runtime/migration.py", line
321, in run_migrations

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File

"---/venv/lib/python3.7/site-packages/alembic/command.py", line 156, in
retrieve_migrations

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
revision_context.run_autogenerate(rev, context)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line
415, in run_autogenerate

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
self._run_environment(rev, migration_context, True)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line
451, in _run_environment

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
autogen_context, migration_script)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 22, in _populate_migration_script

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
_produce_net_changes(autogen_context, upgrade_ops)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 48, in _produce_net_changes

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
autogen_context, upgrade_ops, schemas
File

"---/venv/lib/python3.7/site-packages/alembic/util/langhelpers.py", line
313, in go

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
fn(*arg, **kw)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 75, in _autogen_for_tables

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
inspector, upgrade_ops, autogen_context)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 137, in _compare_tables

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
inspector.reflecttable(t, None)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 633, in reflecttable

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
exclude_columns, _extend_on, reflection_options)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 729, in _reflect_fk

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
table_name, schema, **table.dialect_kwargs)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 447, in get_foreign_keys

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
**kw)
File "<string>", line 2, in get_foreign_keys
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 54, in cache

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
ret = fn(self, con, *args, **kw)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py",
line 2081, in get_foreign_keys

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
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

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
for col in fkey['referred_columns']
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py",
line 2126, in <listcomp>

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
for col in fkey['referred_columns']
KeyError: 'id'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal,

Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve
for a full description.

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
---
You received this message because you are subscribed to the

Google Groups "sqlalchemy" group.

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
To unsubscribe from this group and stop receiving emails from
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and

Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.

Post by a***@gmail.com
---
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.

Post by a***@gmail.com
To unsubscribe from this group and stop receiving emails from it, send

<javascript:>.

Post by a***@gmail.com
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

Post by a***@gmail.com
Dear Mike,
Thank you very much. Your solution worked for me.
1. When I try to change the type of a primary key (for example from BigInteger to Integer), if I use auto generate, the corresponding function has this key "existing_nullable=True" but does not have "nullable=True". As a result, running that function causes the MySQL error "Primary key can not be null". I always manually add "nullable=True" where it is needed.

it's not valid to have "nullable=True" on a primary key column.
MySQL creates a NOT NULL constraint implicitly for primary key
columns. you would need to let me know specifically what you are
trying to do, I'm not able to create a table here that does not have a
NOT NULL constraint although it does let the syntax go through:

MariaDB [test]> create table foo (id bigint primary key null);
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> show create table foo;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+------------------------------------------------------------------------------------------------------------+
| foo | CREATE TABLE `foo` (
`id` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Post by a***@gmail.com
a. Removing foreign key constraint
b. Dropping the column
The sequence of these action is important. When I use Autogenerate option, usually dropping column is before removing the constraint which causes error. Again I change the sequence manually if needed.

Alembic ensures that this ordering is correct by first emitting for
any column additions, then emitting for changes to the table including
constraint adds or drops, then emitting for column drops. given the
model below, where I comment out the FK column and generate a
migration:

Table(
'a', target_metadata,
Column('id', Integer, primary_key=True)
)

Table(
'b', target_metadata,
Column('id', Integer, primary_key=True),
#Column('bid', ForeignKey('a.id'))
)

I get the migration as expected:

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint('b_ibfk_1', 'b', type_='foreignkey')
op.drop_column('b', 'bid')
# ### end Alembic commands ###

so again, you would need to provide complete specifics for the
conditions where Alembic is not doing the right thing so that we can
file a bug report. The one case Alembic doesn't handle right now
are CREATE/DROP TABLE statements which have mutually dependent foreign
key constraints, where the constraint adds and drops would need to be
moved outside of the CREATE/DROP TABLE statements.

Post by a***@gmail.com
3. When the migration code is generated and we run it, if an error comes up in midst of the migration function, it means that the code has been executed partially and the schema has been changed partially. I'm wondering if there is a solution to roll back the partial changes (similar to roll back function in sqlalchemy). The problem is that even after fixing the migration code, it is not possible to run it since it has been executed partially!

That's true, which is a limitation of MySQL that does not have
transactional DDL. if you use a backend that does have transactional
DDL like Postgresql or MS SQL Server, then if any migration fails, the
whole operation rolls back. For a non-transactional-DDL backend like
MySQL, you need to keep your individual migrations small enough so
that if one fails, you can manually revert the parts that failed.

Post by a***@gmail.com
I highly appreciate your effort in SQLAlchemy and Alembic and try to do a small help with improving your excellent work :)

Post by Mike Bayer
OK, it's the filesystem type 2 combined with the fact that the
database is named "Test", capital T, on a case insensitive filesystem.
ill have a fix momentarily. SQLAlhcemy 1.2.12 will work for you for
now, just skip 1.2.13.

I forgot to mention that I'm using all lowercase names for my tables and columns. The primary keys are all "id"
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 2 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
What operating system platform are you on ?
can you run this for me please?
MariaDB [(none)]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
easier, if you can give me a "SHOW CREATE TABLE" for a table here that
has foreign key constraints and refers to a column named "id", "ID",
"iD", something with those two letters in it.

Post by Mike Bayer

Post by Mike Bayer

Hi
I had a problem with alembic autogenerate after updating to version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1
No matter how my base is defined, I always get the same error. I went through the error and found out it comes from function "

Did you also update SQLAlchemy version? The check you see has to do
with SQLAlchemy version 1.2.13. Can you confirm that using SQLAlchemy
1.2.12 resolves the issue? I can work towards making this check more
defensive. Additionally let me run the tests on MySQL 8 to see if
I'm missing something.

no failures here, can you please provide a sample database model that
produces this error?

Post by Mike Bayer

# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
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
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'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[a***@gmail.com]

Sorry I think my explanation was not clear enough especially for the first
issue. I faced these issues in a complex schema. I will let you know all
details if I can reproduce the error.

Although the situation roughly looks like this:

1. I had a 'test' table with a column 'id' which was a primary key and
BigInteger.

2. I changed the type from BigInteger to Integer
The corresponding auto-generated row in my system was like this:

op.alter_column('test', 'id', existing_type=..., type_=..., autoincrement=..., existing_nullable=False)

3. When I ran the upgrade function, I got this error:

INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> caeaaf0eb751, 945
Traceback (most recent call last):
File "*/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "*/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
cursor.execute(statement, parameters)
File "*/venv/lib/python3.7/site-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "*/venv/lib/python3.7/site-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "*/venv/lib/python3.7/site-packages/pymysql/connections.py", line 516, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "*/venv/lib/python3.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
result.read()
File "*/venv/lib/python3.7/site-packages/pymysql/connections.py", line 1066, in read
first_packet = self.connection._read_packet()
File "*/venv/lib/python3.7/site-packages/pymysql/connections.py", line 683, in _read_packet
packet.check_error()
File "*/venv/lib/python3.7/site-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "*/venv/lib/python3.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.DataError: (1171, 'All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead')

The above exception was the direct cause of the following exception:

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 254, in upgrade
script.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 330, in run_migrations
step.migration_fn(**kw)
File "*/alembic/versions/caeaaf0eb751_945.py", line 25, in upgrade
autoincrement=True)
File "<string>", line 8, in alter_column
File "<string>", line 3, in alter_column
File "*/venv/lib/python3.7/site-packages/alembic/operations/ops.py", line 1444, in alter_column
return operations.invoke(alt)
File "*/venv/lib/python3.7/site-packages/alembic/operations/base.py", line 319, in invoke
return fn(self, operation)
File "*/venv/lib/python3.7/site-packages/alembic/operations/toimpl.py", line 53, in alter_column
**operation.kw
File "*/venv/lib/python3.7/site-packages/alembic/ddl/mysql.py", line 68, in alter_column
else existing_autoincrement
File "*/venv/lib/python3.7/site-packages/alembic/ddl/impl.py", line 115, in _exec
return conn.execute(construct, *multiparams, **params)
File "*/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
return meth(self, multiparams, params)
File "*/venv/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "*/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
compiled
File "*/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
context)
File "*/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
exc_info
File "*/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "*/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
raise value.with_traceback(tb)
File "*/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "*/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
cursor.execute(statement, parameters)
File "*/venv/lib/python3.7/site-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "*/venv/lib/python3.7/site-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "*/venv/lib/python3.7/site-packages/pymysql/connections.py", line 516, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "*/venv/lib/python3.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
result.read()
File "*/venv/lib/python3.7/site-packages/pymysql/connections.py", line 1066, in read
first_packet = self.connection._read_packet()
File "*/venv/lib/python3.7/site-packages/pymysql/connections.py", line 683, in _read_packet
packet.check_error()
File "*/venv/lib/python3.7/site-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "*/venv/lib/python3.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.DataError: (pymysql.err.DataError) (1171, 'All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead') [SQL: 'ALTER TABLE phone MODIFY id SMALLINT NULL AUTO_INCREMENT'] (Background on this error at: http://sqlalche.me/e/9h9h)


4. I changed the row as follows and it solved the problem:

op.alter_column('test', 'id', existing_type=..., type_=..., autoincrement=..., existing_nullable=False, nullable=False)



I understand that I have not fully given all details (I copied the error
log directly from my terminal history) I will let you once I face the same
issue.

Post by a***@gmail.com

Post by a***@gmail.com
Dear Mike,
Thank you very much. Your solution worked for me.
1. When I try to change the type of a primary key (for example from

BigInteger to Integer), if I use auto generate, the corresponding function
has this key "existing_nullable=True" but does not have "nullable=True". As
a result, running that function causes the MySQL error "Primary key can not
be null". I always manually add "nullable=True" where it is needed.
it's not valid to have "nullable=True" on a primary key column.
MySQL creates a NOT NULL constraint implicitly for primary key
columns. you would need to let me know specifically what you are
trying to do, I'm not able to create a table here that does not have a
MariaDB [test]> create table foo (id bigint primary key null);
Query OK, 0 rows affected (0.03 sec)
MariaDB [test]> show create table foo;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+------------------------------------------------------------------------------------------------------------+
| foo | CREATE TABLE `foo` (
`id` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Post by a***@gmail.com
2. When I try to drop a column which is a foreign key to another table,
a. Removing foreign key constraint
b. Dropping the column
The sequence of these action is important. When I use Autogenerate

option, usually dropping column is before removing the constraint which
causes error. Again I change the sequence manually if needed.
Alembic ensures that this ordering is correct by first emitting for
any column additions, then emitting for changes to the table including
constraint adds or drops, then emitting for column drops. given the
model below, where I comment out the FK column and generate a
Table(
'a', target_metadata,
Column('id', Integer, primary_key=True)
)
Table(
'b', target_metadata,
Column('id', Integer, primary_key=True),
#Column('bid', ForeignKey('a.id'))
)
# ### commands auto generated by Alembic - please adjust! ###
op.drop_constraint('b_ibfk_1', 'b', type_='foreignkey')
op.drop_column('b', 'bid')
# ### end Alembic commands ###
so again, you would need to provide complete specifics for the
conditions where Alembic is not doing the right thing so that we can
file a bug report. The one case Alembic doesn't handle right now
are CREATE/DROP TABLE statements which have mutually dependent foreign
key constraints, where the constraint adds and drops would need to be
moved outside of the CREATE/DROP TABLE statements.

Post by a***@gmail.com
3. When the migration code is generated and we run it, if an error comes

up in midst of the migration function, it means that the code has been
executed partially and the schema has been changed partially. I'm wondering
if there is a solution to roll back the partial changes (similar to roll
back function in sqlalchemy). The problem is that even after fixing the
migration code, it is not possible to run it since it has been executed
partially!
That's true, which is a limitation of MySQL that does not have
transactional DDL. if you use a backend that does have transactional
DDL like Postgresql or MS SQL Server, then if any migration fails, the
whole operation rolls back. For a non-transactional-DDL backend like
MySQL, you need to keep your individual migrations small enough so
that if one fails, you can manually revert the parts that failed.

Post by a***@gmail.com
I highly appreciate your effort in SQLAlchemy and Alembic and try to do

a small help with improving your excellent work :)

Post by a***@gmail.com

Post by Mike Bayer
OK, it's the filesystem type 2 combined with the fact that the
database is named "Test", capital T, on a case insensitive filesystem.
ill have a fix momentarily. SQLAlhcemy 1.2.12 will work for you for
now, just skip 1.2.13.

Post by a***@gmail.com
I forgot to mention that I'm using all lowercase names for my tables

and columns. The primary keys are all "id"

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 2 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
What operating system platform are you on ?
can you run this for me please?
MariaDB [(none)]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

Post by Mike Bayer
easier, if you can give me a "SHOW CREATE TABLE" for a table here

that

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer
has foreign key constraints and refers to a column named "id",

"ID",

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer
"iD", something with those two letters in it.

On Thu, Nov 8, 2018 at 9:56 AM Mike Bayer <

Post by Mike Bayer

Post by a***@gmail.com
Hi
I had a problem with alembic autogenerate after updating to

version 1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
No matter how my base is defined, I always get the same

error. I went through the error and found out it comes from function "

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
Did you also update SQLAlchemy version? The check you see has

to do

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
with SQLAlchemy version 1.2.13. Can you confirm that using

SQLAlchemy

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
1.2.12 resolves the issue? I can work towards making this

check more

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
defensive. Additionally let me run the tests on MySQL 8 to

see if

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer
I'm missing something.

no failures here, can you please provide a sample database model

that

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

produces this error?

Post by Mike Bayer

Post by a***@gmail.com
_correct_for_mysql_bug_88718(self, fkeys, connection)" in
# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume

non-transactional DDL.

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File "---/venv/bin/alembic", line 11, in <module>
load_entry_point('alembic==1.0.2', 'console_scripts',

'alembic')()

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File

"---/venv/lib/python3.7/site-packages/alembic/config.py", line 502, in main

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
CommandLine(prog=prog).main(argv=argv)
File

"---/venv/lib/python3.7/site-packages/alembic/config.py", line 496, in main

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
self.run_cmd(cfg, options)
File

"---/venv/lib/python3.7/site-packages/alembic/config.py", line 479, in
run_cmd

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
**dict((k, getattr(options, k, None)) for k in kwarg)
File

"---/venv/lib/python3.7/site-packages/alembic/command.py", line 176, in
revision

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
script_directory.run_env()
File

"---/venv/lib/python3.7/site-packages/alembic/script/base.py", line 427, in
run_env

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
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

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
module = load_module_py(module_id, path)
File

"---/venv/lib/python3.7/site-packages/alembic/util/compat.py", line 82, in
load_module_py

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
spec.loader.exec_module(module)
File "<frozen importlib._bootstrap_external>", line 728,

in exec_module

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File "<frozen importlib._bootstrap>", line 219, in

_call_with_frames_removed

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
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

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
self.get_context().run_migrations(**kw)
File

"---/venv/lib/python3.7/site-packages/alembic/runtime/migration.py", line
321, in run_migrations

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
File

"---/venv/lib/python3.7/site-packages/alembic/command.py", line 156, in
retrieve_migrations

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
revision_context.run_autogenerate(rev, context)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line
415, in run_autogenerate

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
self._run_environment(rev, migration_context, True)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line
451, in _run_environment

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
autogen_context, migration_script)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 22, in _populate_migration_script

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
_produce_net_changes(autogen_context, upgrade_ops)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 48, in _produce_net_changes

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
autogen_context, upgrade_ops, schemas
File

"---/venv/lib/python3.7/site-packages/alembic/util/langhelpers.py", line
313, in go

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
fn(*arg, **kw)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 75, in _autogen_for_tables

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
inspector, upgrade_ops, autogen_context)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 137, in _compare_tables

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
inspector.reflecttable(t, None)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 633, in reflecttable

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
exclude_columns, _extend_on, reflection_options)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 729, in _reflect_fk

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
table_name, schema, **table.dialect_kwargs)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 447, in get_foreign_keys

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
**kw)
File "<string>", line 2, in get_foreign_keys
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 54, in cache

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
ret = fn(self, con, *args, **kw)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py",
line 2081, in get_foreign_keys

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
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

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
for col in fkey['referred_columns']
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py",
line 2126, in <listcomp>

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
for col in fkey['referred_columns']
KeyError: 'id'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal,

Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve
for a full description.

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
---
You received this message because you are subscribed to the

Google Groups "sqlalchemy" group.

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
To unsubscribe from this group and stop receiving emails
To post to this group, send email to
Visit this group at

https://groups.google.com/group/sqlalchemy.

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com

Post by Mike Bayer

Post by Mike Bayer

Post by Mike Bayer

Post by a***@gmail.com
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and

Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com
---
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.

Post by a***@gmail.com

Post by Mike Bayer

Post by a***@gmail.com
To unsubscribe from this group and stop receiving emails from it,
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and

Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.

Post by a***@gmail.com
---
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.

Post by a***@gmail.com
To unsubscribe from this group and stop receiving emails from it, send

<javascript:>.

Post by a***@gmail.com
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[a***@gmail.com]

Dear Mike,

Thank you very much for the time you put to resolve this issue.
Last 2 days, I tried many times to see when I get this error. This is the
procedure:

1. I created a new empty database called "Test"
2. I wrote declarative base for 3 tables
3. I used alembic autogenerate to produce migration code and then migrated
to the latest version. In this stage I have 3 tables+1 alembic_version
4. I changed my base such that drop 2 existing tables and keep just one of
them
5. I tried alembic autogenerate and got the error! Actually no matter my
base definition is, I get the same error with alembic autogenerate unless I
remove all tables from the database and build it from the scratch!

Points:
1. I don't experience this error with sqlite!
2. I don't have this problem if I don't use autogenerate
3. I'm running MacOS Mojave 10.14.1 and MySQL 8.0.11
4. I'm using Python, SQLALchemy, alembic, all the latest version
5. I provide you my problematic project including base definition, alembic
folder and dump of current database.
6. If you need, this is the virtual environment which I'm using:
https://www.dropbox.com/s/w55iujvg0fbn16k/venv.zip?dl=0

Sorry that my virtual environment is large! I hope you can reproduce the
error. Thank you again :)

Post by Mike Bayer

Post by a***@gmail.com
Hi
I had a problem with alembic autogenerate after updating to version

1.0.2. I'm running MySQL 8.0.11 and Python 3.7.1

Post by Mike Bayer

Post by a***@gmail.com
No matter how my base is defined, I always get the same error. I went

through the error and found out it comes from function "

Post by Mike Bayer
Did you also update SQLAlchemy version? The check you see has to do
with SQLAlchemy version 1.2.13. Can you confirm that using SQLAlchemy
1.2.12 resolves the issue? I can work towards making this check more
defensive. Additionally let me run the tests on MySQL 8 to see if
I'm missing something.

no failures here, can you please provide a sample database model that
produces this error?

Post by Mike Bayer

Post by a***@gmail.com
_correct_for_mysql_bug_88718(self, fkeys, connection)" in module
# Foreign key is always in lower case (MySQL 8.0)
# https://bugs.mysql.com/bug.php?id=88718
# issue #4344 for SQLAlchemy
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
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

Post by Mike Bayer

Post by a***@gmail.com
CommandLine(prog=prog).main(argv=argv)
File "---/venv/lib/python3.7/site-packages/alembic/config.py", line

496, in main

Post by Mike Bayer

Post by a***@gmail.com
self.run_cmd(cfg, options)
File "---/venv/lib/python3.7/site-packages/alembic/config.py", line

479, in run_cmd

Post by Mike Bayer

Post by a***@gmail.com
**dict((k, getattr(options, k, None)) for k in kwarg)
File "---/venv/lib/python3.7/site-packages/alembic/command.py", line

176, in revision

Post by Mike Bayer

Post by a***@gmail.com
script_directory.run_env()
File "---/venv/lib/python3.7/site-packages/alembic/script/base.py",

line 427, in run_env

Post by Mike Bayer

Post by a***@gmail.com
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

Post by Mike Bayer

Post by a***@gmail.com
module = load_module_py(module_id, path)
File "---/venv/lib/python3.7/site-packages/alembic/util/compat.py",

line 82, in load_module_py

Post by Mike Bayer

Post by a***@gmail.com
spec.loader.exec_module(module)
File "<frozen importlib._bootstrap_external>", line 728, in

exec_module

Post by Mike Bayer

Post by a***@gmail.com
File "<frozen importlib._bootstrap>", line 219, in

_call_with_frames_removed

Post by Mike Bayer

Post by a***@gmail.com
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

Post by Mike Bayer

Post by a***@gmail.com
self.get_context().run_migrations(**kw)
File

"---/venv/lib/python3.7/site-packages/alembic/runtime/migration.py", line
321, in run_migrations

Post by Mike Bayer

Post by a***@gmail.com
File "---/venv/lib/python3.7/site-packages/alembic/command.py", line

156, in retrieve_migrations

Post by Mike Bayer

Post by a***@gmail.com
revision_context.run_autogenerate(rev, context)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line
415, in run_autogenerate

Post by Mike Bayer

Post by a***@gmail.com
self._run_environment(rev, migration_context, True)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line
451, in _run_environment

Post by Mike Bayer

Post by a***@gmail.com
autogen_context, migration_script)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 22, in _populate_migration_script

Post by Mike Bayer

Post by a***@gmail.com
_produce_net_changes(autogen_context, upgrade_ops)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 48, in _produce_net_changes

Post by Mike Bayer

Post by a***@gmail.com
autogen_context, upgrade_ops, schemas
File

"---/venv/lib/python3.7/site-packages/alembic/util/langhelpers.py", line
313, in go

Post by Mike Bayer

Post by a***@gmail.com
fn(*arg, **kw)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 75, in _autogen_for_tables

Post by Mike Bayer

Post by a***@gmail.com
inspector, upgrade_ops, autogen_context)
File

"---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py",
line 137, in _compare_tables

Post by Mike Bayer

Post by a***@gmail.com
inspector.reflecttable(t, None)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 633, in reflecttable

Post by Mike Bayer

Post by a***@gmail.com
exclude_columns, _extend_on, reflection_options)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 729, in _reflect_fk

Post by Mike Bayer

Post by a***@gmail.com
table_name, schema, **table.dialect_kwargs)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 447, in get_foreign_keys

Post by Mike Bayer

Post by a***@gmail.com
**kw)
File "<string>", line 2, in get_foreign_keys
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py",
line 54, in cache

Post by Mike Bayer

Post by a***@gmail.com
ret = fn(self, con, *args, **kw)
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py",
line 2081, in get_foreign_keys

Post by Mike Bayer

Post by a***@gmail.com
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

Post by Mike Bayer

Post by a***@gmail.com
for col in fkey['referred_columns']
File

"---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py",
line 2126, in <listcomp>

Post by Mike Bayer

Post by a***@gmail.com
for col in fkey['referred_columns']
KeyError: 'id'
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and

Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.

Post by Mike Bayer

Post by a***@gmail.com
---
You received this message because you are subscribed to the Google

Groups "sqlalchemy" group.

Post by Mike Bayer

Post by a***@gmail.com
To unsubscribe from this group and stop receiving emails from it, send

<javascript:>.

Post by Mike Bayer

Post by a***@gmail.com
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+***@googlegroups.com.
To post to this group, send email to ***@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.