alembic
alembic copied to clipboard
sqlalchemy
generalized "recreate foreign key" / "recreate constraint" / "drop constraint if needed" types of directives + autogenerate?
Migrated issue, originally created by Stéphane Klein (@harobed)
With MSSql, drop_column have some specific options https://github.com/zzzeek/alembic/blob/master/alembic/operations.py#L504 like : "mssql_drop_foreign_key".
I suggest to append specifics options for Mysql :
- "mysql_drop_contraints"
- "mysql_drop_foreign_key"
What do you think about this suggest ?
Michael Bayer (@zzzeek) wrote:
whats the error mysql gives for both of these conditions when the drop column is otherwise emitted?
Stéphane Klein (@harobed) wrote:
#!
Traceback (most recent call last):
File "/home/myproject/webapp-venv/bin/alembic", line 9, in <module>
load_entry_point('alembic==0.6.5', 'console_scripts', 'alembic')()
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/config.py", line 298, in main
CommandLine(prog=prog).main(argv=argv)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/config.py", line 293, in main
self.run_cmd(cfg, options)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/config.py", line 279, in run_cmd
**dict((k, getattr(options, k)) for k in kwarg)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/command.py", line 125, in upgrade
script.run_env()
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/script.py", line 203, in run_env
util.load_python_file(self.dir, 'env.py')
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/util.py", line 212, in load_python_file
module = load_module_py(module_id, path)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/compat.py", line 58, in load_module_py
mod = imp.load_source(module_id, path, fp)
File "/home/myproject/src/myproject-api-webapp/alembic/env.py", line 67, in <module>
run_migrations_online()
File "/home/myproject/src/myproject-api-webapp/alembic/env.py", line 60, in run_migrations_online
context.run_migrations()
File "<string>", line 7, in run_migrations
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/environment.py", line 688, in run_migrations
self.get_context().run_migrations(**kw)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/migration.py", line 258, in run_migrations
change(**kw)
File "/home/myproject/src/myproject-api-webapp/alembic/versions/45dd19c3a9e5_remove_application_id_price_by_mounth_.py", line 24, in upgrade
op.drop_column('rel_end_user_plans_history', 'application_id')
File "<string>", line 7, in drop_column
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/operations.py", line 477, in drop_column
**kw
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 130, in drop_column
self._exec(base.DropColumn(table_name, column, schema=schema))
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 76, in _exec
conn.execute(construct, *multiparams, **params)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute
params)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in _execute_ddl
compiled
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
context)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
exc_info
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 163, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
context)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
cursor.execute(statement, parameters)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
self.errorhandler(self, exc, value)
File "/home/myproject/webapp-venv/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (OperationalError) (1025, "Error on rename of './api/#sql-a1d_4c' to './api/rel_end_user_plans_history' (errno: 150)") 'ALTER TABLE rel_end_user_plans_history DROP COLUMN application_id' ()
Stéphane Klein (@harobed) wrote:
This is without :
op.drop_constraint(
'rel_end_user_plans_history_ibfk_3',
'rel_end_user_plans_history',
type_='foreignkey'
)
Michael Bayer (@zzzeek) wrote:
and there's no cascade option, OK. I'd go with just mysql_drop_constraints if possible; an FK is a constraint. However, the trick here is that the whole thing ideally can work in offline mode so has to figure out the names of these constraints in an inline fashion.
Michael Bayer (@zzzeek) wrote:
looking at #247, these constraints are the ones that the column to be dropped owns, right? It seems like #247 would help a lot with this if we implement autogenerate as including the constraints explicitly first. that might not replace the desirability of these flags but it would be a more general solution.
Michael Bayer (@zzzeek) wrote:
seems to be just foreign keys owned by the column. I can drop cols mentioned in unique constraints and indexes.
Michael Bayer (@zzzeek) wrote:
note also #184 which proposes allowing dialect-specific flags to render within autogenerate.
Michael Bayer (@zzzeek) wrote:
we need CHECK constraints because MariaDB both adds them and also temporarily won't let us drop a column that has them (will be fixed in an upcoming 10.2).
Michael Bayer (@zzzeek) wrote:
well at the moment I don't see how this is possible for CHECK constraints - trying mariadb 10.2, there's no way to get the list of columns inside the CHECK other than using SHOW CREATE TABLE and parsing; and it's not possible to use SHOW CREATE TABLE within server-side SQL. So only adding CHECK constraints to SQLAlchemy reflection, regexing on the Python side would make this possible, which rules out this being a SQL side flag like we do for SQL Server.
Michael Bayer (@zzzeek) wrote:
note that CHECK constraints is an issue repaired on the MariaDB side.
for this issue need to :
-
re-confirm that we can't rename a MySQL column that is referred to by a foreign key, and also maybe check this for mariadb / mysql / versions etc.
-
determine if inline SQL can handle this without the name of the constraint needing to be passed, e.g. a DROP that is serviced from getting the constraint names in the information schema.
-
assuming #2 is a no-go, consider having autogenerate be intelligent about this kind of thing and to actually generate the migration like this:
with op.conditional_recreate_fk("foobar", ["c1", "c2"], ["remote.c1" ,"remote.c2"], onupdate="cascade"):
op.rename_constraint(....)
that is, we introduce "condtiional_recreate" as well as "conditional_drop" directives, which look at things like if we are on MySQL, or SQL Server, etc. and then emit the DROP/CREATE if necessary.
this is a much more generalized feature than the "mssql_drop_constraint" flag. More thought would need to go into this.
Changes by Michael Bayer (@zzzeek):
- changed title from "Append "mysql_drop_contraints" and "mysql_drop_for" to "generalized "recreate foreign key" / "recreate con"