Alembic migration: "DROP": syntax error u'ALTER TABLE ipallocations DROP COLUMN expiration' ()

[Thomas Goirand]

After applying this patch: https://review.openstack.org/#/c/52636 (including the 'True' instead of True which I wrote in my comment), then another error appears:

INFO [alembic.migration] Running upgrade c88b6b5fea3 -> f9263d6df56
Traceback (most recent call last):
  File "/usr/bin/neutron-db-manage", line 10, in <module>
    sys.exit(main())
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 143, in main
    CONF.command.func(config, CONF.command.name)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 80, in do_upgrade_downgrade
    do_alembic_command(config, cmd, revision, sql=CONF.command.sql)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/cli.py", line 59, in do_alembic_command
    getattr(alembic_command, cmd)(config, *args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/alembic/command.py", line 124, in upgrade
    script.run_env()
  File "/usr/lib/python2.7/dist-packages/alembic/script.py", line 191, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/lib/python2.7/dist-packages/alembic/util.py", line 186, in load_python_file
    module = imp.load_source(module_id, path, open(path, 'rb'))
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/alembic_migrations/env.py", line 105, in <module>
    run_migrations_online()
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/alembic_migrations/env.py", line 89, in run_migrations_online
    options=build_options())
  File "<string>", line 7, in run_migrations
  File "/usr/lib/python2.7/dist-packages/alembic/environment.py", line 494, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/lib/python2.7/dist-packages/alembic/migration.py", line 211, in run_migrations
    change(**kw)
  File "/usr/lib/python2.7/dist-packages/neutron/db/migration/alembic_migrations/versions/f9263d6df56_remove_dhcp_lease.py", line 41, in upgrade
    op.drop_column('ipallocations', u'expiration')
  File "<string>", line 7, in drop_column
  File "/usr/lib/python2.7/dist-packages/alembic/operations.py", line 380, in drop_column
    **kw
  File "/usr/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 129, in drop_column
    self._exec(base.DropColumn(table_name, column, schema=schema))
  File "/usr/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 75, in _exec
    conn.execute(construct, *multiparams, **params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1542, in _execute_ddl
    compiled
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 331, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) near "DROP": syntax error u'ALTER TABLE ipallocations DROP COLUMN expiration' ()

[Eugene Nikanorov (enikanorov)]

Hi Thomas, I believe DROP statement just not supported in sqlite: http://www.sqlite.org/lang_altertable.html

[Eugene Nikanorov (enikanorov)]

I remember there were attempts to fix migrations for sqlite, but due to lack of support for ALTER statements in sqlite, those attempts could complicate migrations a lot.
So I'm marking the bug as 'Won't fix'

[Thomas Goirand (thomas-goirand)]

Hi,

Please do not mark this bug as "Won't fix". This is not acceptable. We need a resolution, as this is otherwise an RC bug in Debian, preventing the package to migrate to Debian testing (Jessie).

[Eugene Nikanorov (enikanorov)]

Ok, I'll leave the decision up to Neutron PTL.

[Salvatore Orlando (salvatore-orlando)]

Discussion about migrations and sqlite already came up in the past; I agree it has to be fixed, but I recall that we already agree it's not going to be super easy.

Instead of sprinkling migration errors on sqlite in several bug reports, I would rather have a single blueprint and a clearly defined assignee. What is your opinion?

[Thomas Goirand (thomas-goirand)]

Hi,

Thanks Salvatore for the above comment.

I don't mind either way, though the first bug is already solved, so I believe it should be merged, so we can move on with the rest (in other words, there's no need to delay that one). Moreover, I currently don't know how many of these issues there is in the code, since you can only see the next one once the current one is solved. Though if you think it's more easy to manage with a single bug and patch, I don't mind (as long as we have a final result which works...).

Also, I was able to do that first one because I had the beginning of a patch from Eugene Nikanorov, though I don't think I know enough Alembic to be able to produce a patch for the rest of the problem (even though I'm currently trying).

[Thomas Goirand (thomas-goirand)]

FYI, there is also a problem in neutron/db/migration/alembic_migrations/versions/63afba73813_ovs_tunnelendpoints_id_unique.py, then we're good to go. So only 2 more errors to fix.

[Ann Taraday (akamyshnikova)]

Do we still have this issues? Migrations changed a lot.

[Thomas Goirand (thomas-goirand)]

Yes, we still have the issue, and my Debian package carries a lot of patches to fix the migration with SQLite. But I've given-up sending these patches upstream, as it's been 2 years I've tried to do so, and they always have been rejected. I think that's a stupid policy, but never mind, I give up.

[Armando Migliaccio (armando-migliaccio)]

This bug is > 172 days without activity. We are unsetting assignee and milestone and setting status to Incomplete in order to allow its expiry in 60 days.

If the bug is still valid, then update the bug status.

[Launchpad Janitor (janitor)]

[Expired for neutron because there has been no activity for 60 days.]