Database error when migrating from 1.4.2 to 1.5.1 under MySQL 8

[PatBriPerso]

A migration failed to apply on my MySQL database when migrating from superset 1.4.2 to 1.5.1

How to reproduce the bug

1. Setup a superset 1.4.2 that use a MySQL 8 database
2. Update superset to version 1.5.1
3. Run superset db upgrade

Expected results

MySQL database upgrade succeeded.

Actual results

rename_csv_to_file migration fails with the following log:

Running upgrade aea15018d53b -> b92d69a6643c, rename_csv_to_file
...
sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 3959 (HY000): Check constraint 'dbs_chk_9' uses column 'allow_csv_upload', hence column cannot be dropped or renamed.
[SQL: ALTER TABLE dbs CHANGE allow_csv_upload allow_file_upload BOOL NULL]

See below for full log with stack traces.

Screenshots

N/A

Environment

(please complete the following information):

• browser type and version: n/a
• superset version: 1.5.1
• python version: 3.8.12
• node.js version: n/a
• any feature flags active: FEATURE_FLAGS = {"ALERT_REPORTS": True}

Checklist

Make sure to follow these steps 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.

Installing local overrides at /app/docker/requirements-local.txt
Collecting clickhouse-driver>=0.2.0
  Downloading clickhouse_driver-0.2.4-cp38-cp38-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (732 kB)
Collecting clickhouse-sqlalchemy<0.2.0,>=0.1.6
  Downloading clickhouse-sqlalchemy-0.1.10.tar.gz (34 kB)
Collecting mysql-connector-python
  Downloading mysql_connector_python-8.0.29-cp38-cp38-manylinux1_x86_64.whl (25.2 MB)
Collecting flask-oidc==1.3.0
  Downloading flask-oidc-1.3.0.tar.gz (29 kB)
Requirement already satisfied: Flask in /usr/local/lib/python3.8/site-packages (from flask-oidc==1.3.0->-r /app/docker/requirements-local.txt (line 4)) (1.1.4)
Requirement already satisfied: itsdangerous in /usr/local/lib/python3.8/site-packages (from flask-oidc==1.3.0->-r /app/docker/requirements-local.txt (line 4)) (1.1.0)
Collecting oauth2client
  Downloading oauth2client-4.1.3-py2.py3-none-any.whl (98 kB)
Requirement already satisfied: six in /usr/local/lib/python3.8/site-packages (from flask-oidc==1.3.0->-r /app/docker/requirements-local.txt (line 4)) (1.16.0)
Collecting tzlocal
  Downloading tzlocal-4.2-py3-none-any.whl (19 kB)
Requirement already satisfied: pytz in /usr/local/lib/python3.8/site-packages (from clickhouse-driver>=0.2.0->-r /app/docker/requirements-local.txt (line 1)) (2021.1)
Requirement already satisfied: sqlalchemy<1.4,>=1.3 in /usr/local/lib/python3.8/site-packages (from clickhouse-sqlalchemy<0.2.0,>=0.1.6->-r /app/docker/requirements-local.txt (line 2)) (1.3.24)
Requirement already satisfied: requests in /usr/local/lib/python3.8/site-packages (from clickhouse-sqlalchemy<0.2.0,>=0.1.6->-r /app/docker/requirements-local.txt (line 2)) (2.26.0)
Collecting protobuf>=3.0.0
  Downloading protobuf-4.21.2-cp37-abi3-manylinux2014_x86_64.whl (407 kB)
Requirement already satisfied: click<8.0,>=5.1 in /usr/local/lib/python3.8/site-packages (from Flask->flask-oidc==1.3.0->-r /app/docker/requirements-local.txt (line 4)) (7.1.2)
Requirement already satisfied: Jinja2<3.0,>=2.10.1 in /usr/local/lib/python3.8/site-packages (from Flask->flask-oidc==1.3.0->-r /app/docker/requirements-local.txt (line 4)) (2.11.3)
Requirement already satisfied: Werkzeug<2.0,>=0.15 in /usr/local/lib/python3.8/site-packages (from Flask->flask-oidc==1.3.0->-r /app/docker/requirements-local.txt (line 4)) (1.0.1)
Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.8/site-packages (from Jinja2<3.0,>=2.10.1->Flask->flask-oidc==1.3.0->-r /app/docker/requirements-local.txt (line 4)) (2.0.1)
Collecting httplib2>=0.9.1
  Downloading httplib2-0.20.4-py3-none-any.whl (96 kB)
Collecting pyasn1-modules>=0.0.5
  Downloading pyasn1_modules-0.2.8-py2.py3-none-any.whl (155 kB)
Collecting rsa>=3.1.4
  Downloading rsa-4.8-py3-none-any.whl (39 kB)
Collecting pyasn1>=0.1.7
  Downloading pyasn1-0.4.8-py2.py3-none-any.whl (77 kB)
Requirement already satisfied: pyparsing!=3.0.0,!=3.0.1,!=3.0.2,!=3.0.3,<4,>=2.4.2 in /usr/local/lib/python3.8/site-packages (from httplib2>=0.9.1->oauth2client->flask-oidc==1.3.0->-r /app/docker/requirements-local.txt (line 4)) (3.0.6)
Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.8/site-packages (from requests->clickhouse-sqlalchemy<0.2.0,>=0.1.6->-r /app/docker/requirements-local.txt (line 2)) (3.2)
Requirement already satisfied: charset-normalizer~=2.0.0 in /usr/local/lib/python3.8/site-packages (from requests->clickhouse-sqlalchemy<0.2.0,>=0.1.6->-r /app/docker/requirements-local.txt (line 2)) (2.0.4)
Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.8/site-packages (from requests->clickhouse-sqlalchemy<0.2.0,>=0.1.6->-r /app/docker/requirements-local.txt (line 2)) (2021.5.30)
Requirement already satisfied: urllib3<1.27,>=1.21.1 in /usr/local/lib/python3.8/site-packages (from requests->clickhouse-sqlalchemy<0.2.0,>=0.1.6->-r /app/docker/requirements-local.txt (line 2)) (1.26.6)
Collecting pytz-deprecation-shim
  Downloading pytz_deprecation_shim-0.1.0.post0-py2.py3-none-any.whl (15 kB)
Collecting backports.zoneinfo
  Downloading backports.zoneinfo-0.2.1-cp38-cp38-manylinux1_x86_64.whl (74 kB)
Collecting tzdata
  Downloading tzdata-2022.1-py2.py3-none-any.whl (339 kB)
Building wheels for collected packages: flask-oidc, clickhouse-sqlalchemy
  Building wheel for flask-oidc (setup.py): started
  Building wheel for flask-oidc (setup.py): finished with status 'done'
  Created wheel for flask-oidc: filename=flask_oidc-1.3.0-py2.py3-none-any.whl size=17189 sha256=b44def1b75138b178af4550a01a287b4554ad286bd6927e52e1d6a6c16691293
  Stored in directory: /root/.cache/pip/wheels/db/b6/13/1a8597c4b0687cc46c2f48a81e5921b7d670bd1e9e69db9b14
  Building wheel for clickhouse-sqlalchemy (setup.py): started
  Building wheel for clickhouse-sqlalchemy (setup.py): finished with status 'done'
  Created wheel for clickhouse-sqlalchemy: filename=clickhouse_sqlalchemy-0.1.10-py3-none-any.whl size=44827 sha256=683ad54664be2f5c8c94ccad99a585981297be2b691abf393768816663e10943
  Stored in directory: /root/.cache/pip/wheels/02/e4/af/014e5992ebffb3dff64ebf16af5aed3fcf018305f5b0c04919
Successfully built flask-oidc clickhouse-sqlalchemy
Installing collected packages: tzdata, backports.zoneinfo, pytz-deprecation-shim, pyasn1, tzlocal, rsa, pyasn1-modules, httplib2, protobuf, oauth2client, clickhouse-driver, mysql-connector-python, flask-oidc, clickhouse-sqlalchemy
Successfully installed backports.zoneinfo-0.2.1 clickhouse-driver-0.2.4 clickhouse-sqlalchemy-0.1.10 flask-oidc-1.3.0 httplib2-0.20.4 mysql-connector-python-8.0.29 oauth2client-4.1.3 protobuf-4.21.2 pyasn1-0.4.8 pyasn1-modules-0.2.8 pytz-deprecation-shim-0.1.0.post0 rsa-4.8 tzdata-2022.1 tzlocal-4.2
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
WARNING: You are using pip version 21.2.4; however, version 22.1.2 is available.
You should consider upgrading via the '/usr/local/bin/python -m pip install --upgrade pip' command.

######################################################################


Init Step 1/4 [Starting] -- Applying DB migrations


######################################################################

logging was configured successfully
2022-07-12 14:01:04,452:INFO:superset.utils.logging_configurator:logging was configured successfully
2022-07-12 14:01:04,470:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
Falling back to the built-in cache, that stores data in the metadata database, for the followinng cache: `FILTER_STATE_CACHE_CONFIG`. It is recommended to use `RedisCache`, `MemcachedCache` or another dedicated caching backend for production deployments
2022-07-12 14:01:04,472:WARNING:superset.utils.cache_manager:Falling back to the built-in cache, that stores data in the metadata database, for the followinng cache: `FILTER_STATE_CACHE_CONFIG`. It is recommended to use `RedisCache`, `MemcachedCache` or another dedicated caching backend for production deployments
Falling back to the built-in cache, that stores data in the metadata database, for the followinng cache: `EXPLORE_FORM_DATA_CACHE_CONFIG`. It is recommended to use `RedisCache`, `MemcachedCache` or another dedicated caching backend for production deployments
2022-07-12 14:01:04,476:WARNING:superset.utils.cache_manager:Falling back to the built-in cache, that stores data in the metadata database, for the followinng cache: `EXPLORE_FORM_DATA_CACHE_CONFIG`. It is recommended to use `RedisCache`, `MemcachedCache` or another dedicated caching backend for production deployments
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade aea15018d53b -> b92d69a6643c, rename_csv_to_file
Loaded your LOCAL configuration at [/app/pythonpath/superset_config.py]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 535, in cmd_query
    self._cmysql.query(query,
_mysql_connector.MySQLInterfaceError: Check constraint 'dbs_chk_9' uses column 'allow_csv_upload', hence column cannot be dropped or renamed.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/mysql/connector/cursor_cext.py", line 269, in execute
    result = self._cnx.cmd_query(stmt, raw=self._raw,
  File "/usr/local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 540, in cmd_query
    raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.DatabaseError: 3959 (HY000): Check constraint 'dbs_chk_9' uses column 'allow_csv_upload', hence column cannot be dropped or renamed.

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

Traceback (most recent call last):
  File "/usr/local/bin/superset", line 33, in <module>
    sys.exit(load_entry_point('apache-superset', 'console_scripts', 'superset')())
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 586, in main
    return super(FlaskGroup, self).main(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/decorators.py", line 21, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask/cli.py", line 426, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask_migrate/cli.py", line 149, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
  File "/usr/local/lib/python3.8/site-packages/flask_migrate/__init__.py", line 98, in wrapped
    f(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/flask_migrate/__init__.py", line 185, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/usr/local/lib/python3.8/site-packages/alembic/command.py", line 294, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.8/site-packages/alembic/script/base.py", line 490, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/app/superset/extensions/../migrations/env.py", line 126, in <module>
    run_migrations_online()
  File "/app/superset/extensions/../migrations/env.py", line 118, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/environment.py", line 813, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.8/site-packages/alembic/runtime/migration.py", line 561, in run_migrations
    step.migration_fn(**kw)
  File "/app/superset/migrations/versions/b92d69a6643c_rename_csv_to_file.py", line 37, in upgrade
    batch_op.alter_column(
  File "/usr/local/lib/python3.8/contextlib.py", line 120, in __exit__
    next(self.gen)
  File "/usr/local/lib/python3.8/site-packages/alembic/operations/base.py", line 336, in batch_alter_table
    impl.flush()
  File "/usr/local/lib/python3.8/site-packages/alembic/operations/batch.py", line 86, in flush
    fn(*arg, **kw)
  File "/usr/local/lib/python3.8/site-packages/alembic/ddl/mysql.py", line 70, in alter_column
    self._exec(
  File "/usr/local/lib/python3.8/site-packages/alembic/ddl/impl.py", line 146, in _exec
    return conn.execute(construct, multiparams)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1068, in _execute_ddl
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/mysql/connector/cursor_cext.py", line 269, in execute
    result = self._cnx.cmd_query(stmt, raw=self._raw,
  File "/usr/local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 540, in cmd_query
    raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 3959 (HY000): Check constraint 'dbs_chk_9' uses column 'allow_csv_upload', hence column cannot be dropped or renamed.
[SQL: ALTER TABLE dbs CHANGE allow_csv_upload allow_file_upload BOOL NULL]
(Background on this error at: http://sqlalche.me/e/13/4xp6)

• 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

I try also to update my superset 1.4.2 to 1.5.0 but I have the same error.

[lefred]

I've the same issue with a fresh install of SuperSet 2.0

Python 3.8.12
Flask 2.1.3
Werkzeug 2.0.3

[qlands]

This happens because the migration uses the exception "sa.exc.OperationalError" but in MySQL the exception should be "sa.exc.DatabaseError". The migration passes if you change line 42 to read "except sa.exc.DatabaseError:"