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. On Sunday, November 11, 2018 at 11:09:06 PM UTC+8, Mike Bayer wrote: > > On Sat, Nov 10, 2018 at 11:34 PM <ai.rese...@gmail.com <javascript:>> > wrote: > > > > 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. > > 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) > > > > > > > > 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. > > 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. > > > > > 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. > > > > > > > > I highly appreciate your effort in SQLAlchemy and Alembic and try to do > a small help with improving your excellent work :) > > > > > > > > On Saturday, November 10, 2018 at 10:57:22 AM UTC+8, Mike Bayer wrote: > >> > >> 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. > >> On Fri, Nov 9, 2018 at 7:57 PM <ai.rese...@gmail.com> wrote: > >> > > >> > 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) > >> > > >> > > >> > > >> > On Thursday, November 8, 2018 at 11:17:12 PM UTC+8, Mike Bayer wrote: > >> >> > >> >> 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) > >> >> > >> >> > >> >> On Thu, Nov 8, 2018 at 10:10 AM Mike Bayer <mik...@zzzcomputing.com> > wrote: > >> >> > > >> >> > 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. > >> >> > On Thu, Nov 8, 2018 at 9:58 AM Mike Bayer <mik...@zzzcomputing.com> > wrote: > >> >> > > > >> >> > > On Thu, Nov 8, 2018 at 9:56 AM Mike Bayer < > mik...@zzzcomputing.com> wrote: > >> >> > > > > >> >> > > > On Thu, Nov 8, 2018 at 3:36 AM <ai.rese...@gmail.com> wrote: > >> >> > > > > > >> >> > > > > 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? > >> >> > > > >> >> > > > > >> >> > > > > >> >> > > > > > >> >> > > > > _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 > sqlal...@googlegroups.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 sqlal...@googlegroups.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 <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.