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 <[email protected] <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 <[email protected]> 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 <[email protected]>
> 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 <[email protected]>
> wrote:
> >> >> > >
> >> >> > > On Thu, Nov 8, 2018 at 9:56 AM Mike Bayer <
> [email protected]> wrote:
> >> >> > > >
> >> >> > > > On Thu, Nov 8, 2018 at 3:36 AM <[email protected]> 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 [email protected].
> >> >> > > > > To post to this group, send email to
> [email protected].
> >> >> > > > > 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 [email protected].
> >> > To post to this group, send email to [email protected].
> >> > 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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
