Incubator-superset: Unable to upgrade mysql database from 0.29.0rc8

[zoneyang]

I have tried to upgrade mysql database from 0.29.0rc8 to 0.31.0rc18, 0.32.0rc2 or 0.33.0rc1, but the same error raised.After I input "superset db upgrade", then the error shows below:

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 55e910a74826 -> 4ce8df208545, empty message
scanning dashboard (1/1) >>>>
INFO  [alembic.runtime.migration] Running upgrade 4ce8df208545 -> 46f444d8b9b7, remove_coordinator_from_druid_cluster_model.py
INFO  [alembic.runtime.migration] Running upgrade 46f444d8b9b7 -> a61b40f9f57f, remove allow_run_sync
INFO  [alembic.runtime.migration] Running upgrade a61b40f9f57f -> 6c7537a6004a, models for email reports
INFO  [alembic.runtime.migration] Running upgrade 6c7537a6004a -> 3e1b21cd94a4, change_owner_to_m2m_relation_on_datasources.py
Traceback (most recent call last):
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 312, in _query
    db.query(q)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/MySQLdb/connections.py", line 224, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1091, "Can't DROP 'user_id'; check that column/key exists")

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

Traceback (most recent call last):
  File "/home/test/work/ccsuperset/venv/bin/superset", line 6, in <module>
    exec(compile(open(__file__).read(), __file__, 'exec'))
  File "/home/test/work/ccsuperset/superset/bin/superset", line 35, in <module>
    cli()
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/flask/cli.py", line 569, in main
    return super(FlaskGroup, self).main(*args, **kwargs)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/flask/cli.py", line 419, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/flask_migrate/cli.py", line 134, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/flask_migrate/__init__.py", line 95, in wrapped
    f(*args, **kwargs)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/flask_migrate/__init__.py", line 280, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/command.py", line 276, in upgrade
    script.run_env()
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/script/base.py", line 475, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 90, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/util/compat.py", line 156, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/home/test/work/ccsuperset/superset/migrations/env.py", line 116, in <module>
    run_migrations_online()
  File "/home/test/work/ccsuperset/superset/migrations/env.py", line 109, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/runtime/environment.py", line 839, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/runtime/migration.py", line 361, in run_migrations
    step.migration_fn(**kw)
  File "/home/test/work/ccsuperset/superset/migrations/versions/3e1b21cd94a4_change_owner_to_m2m_relation_on_.py", line 102, in upgrade
    batch_op.drop_column('user_id')
  File "/usr/lib/python3.6/contextlib.py", line 88, in __exit__
    next(self.gen)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/operations/base.py", line 325, in batch_alter_table
    impl.flush()
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/operations/batch.py", line 73, in flush
    fn(*arg, **kw)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/ddl/mysql.py", line 107, in drop_constraint
    super(MySQLImpl, self).drop_constraint(const)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/ddl/impl.py", line 240, in drop_constraint
    self._exec(schema.DropConstraint(const))
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/alembic/ddl/impl.py", line 134, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1050, in _execute_ddl
    compiled,
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
    raise value.with_traceback(tb)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 312, in _query
    db.query(q)
  File "/home/test/work/ccsuperset/venv/lib/python3.6/site-packages/MySQLdb/connections.py", line 224, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1091, "Can't DROP 'user_id'; check that column/key exists")
**[SQL: ALTER TABLE `tables` DROP FOREIGN KEY user_id]**
(Background on this error at: http://sqlalche.me/e/e3q8)

Expected results

input "superset db upgrade",
then it works successfully.

Actual results

error comes out : [SQL: ALTER TABLE tables DROP FOREIGN KEY user_id]

Screenshots

If applicable, add screenshots to help explain your problem.

How to reproduce the bug

1. use mysql8.x to install '0.29.0rc8'
   2.swith branch to '0.31.0rc18' or '0.32.0rc2' or '0.33.0rc1' and install
   3.use 'superset db upgrade'
   4.error comes out.

Environment

(please complete the following information):

• superset version: 0.29.0rc8
• python version: 3.6.7
• node.js version: v8.10.0
• npm version: 3.5.2

Checklist

Make sure these boxes are checked before submitting your issue - thank you!

• [*] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [*] I have reproduced the issue with at least the latest released version of superset.
• [*] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

[issue-label-bot[bot]]

Issue-Label Bot is automatically applying the label #bug to this issue, with a confidence of 0.72. Please mark this comment with :thumbsup: or :thumbsdown: to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[elukey]

@zoneyang I did the same upgrade a while ago and it worked fine, strange! Is superset configured to use the right database in the config? Also, if you check in the db schema, do you see the the foreign key? It could be useful to get the output of show create table tables from mysql as well.

[zoneyang]

@zoneyang I did the same upgrade a while ago and it worked fine, strange! Is superset configured to use the right database in the config? Also, if you check in the db schema, do you see the the foreign key? It could be useful to get the output of show create table tables from mysql as well.

my config is SQLALCHEMY_DATABASE_URI = 'mysql://superset_dev:[email protected]/superset_dev'

[zoneyang]

@zoneyang I did the same upgrade a while ago and it worked fine, strange! Is superset configured to use the right database in the config? Also, if you check in the db schema, do you see the the foreign key? It could be useful to get the output of show create table tables from mysql as well.

mysqlclient 1.4.2.post1

[zoneyang]

@zoneyang I did the same upgrade a while ago and it worked fine, strange! Is superset configured to use the right database in the config? Also, if you check in the db schema, do you see the the foreign key? It could be useful to get the output of show create table tables from mysql as well.

Is there something wrong in my config？

[mistercrunch]

Sometimes you fail a migration half way and rerun it and it fails at an earlier operation than the first time, and it looks like that's what's happening here. I'm guessing the first time you failed you migration, you got a different stack trace. Alembic stamps the last fully succeeded migration, and has no way to checkpoint in-between, so that creates problems.

Probably the right thing to do here is to manually skip that line that is deleting that column (File "/home/test/work/ccsuperset/superset/migrations/versions/3e1b21cd94a4_change_owner_to_m2m_relation_on_.py", line 102) and re run the migration. There might be more of these lines that "can't get executed twice" but already run on that first pass.

Chances are you get through, or to the real problem.

This is painful, I feel sorry for others getting stuck in migration-limbo-land 😢

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[mosche]

I'm consistently running into the same issue migrating from 0.29.0rc7. All attempts are on a fresh copy restored from a backup. Did you make any progress here @zoneyang?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.