Running superset db upgrade results is about a SQLite syntax error.

[zzxVv]

Bug description

I encountered a ‘sqlite3.OperationalError: near “extra”: syntax error’ while performing superset db upgrade during the process of building superset with PyPI. The detailed error is as follows. How can I resolve this?
Error:
INFO [alembic.runtime.migration] Running upgrade 409c7b420ab0 -> ffa79af61a56, rename report_schedule.extra to extra_json
Traceback (most recent call last):
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: near "extra": syntax error

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

Traceback (most recent call last):
File "/root/venv/bin/superset", line 33, in
sys.exit(load_entry_point('apache-superset==4.0.1', 'console_scripts', 'superset')())
File "/root/venv/lib/python3.10/site-packages/click/core.py", line 1157, in call
return self.main(*args, **kwargs)
File "/root/venv/lib/python3.10/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
File "/root/venv/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/root/venv/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/root/venv/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/root/venv/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/root/venv/lib/python3.10/site-packages/click/decorators.py", line 33, in new_func
return f(get_current_context(), *args, **kwargs)
File "/root/venv/lib/python3.10/site-packages/flask/cli.py", line 358, in decorator
return __ctx.invoke(f, *args, **kwargs)
File "/root/venv/lib/python3.10/site-packages/click/core.py", line 783, in invoke
return __callback(*args, **kwargs)
File "/root/venv/lib/python3.10/site-packages/flask_migrate/cli.py", line 149, in upgrade
_upgrade(directory, revision, sql, tag, x_arg)
File "/root/venv/lib/python3.10/site-packages/flask_migrate/init.py", line 98, in wrapped
f(*args, **kwargs)
File "/root/venv/lib/python3.10/site-packages/flask_migrate/init.py", line 185, in upgrade
command.upgrade(config, revision, sql=sql, tag=tag)
File "/root/venv/lib/python3.10/site-packages/alembic/command.py", line 403, in upgrade
script.run_env()
File "/root/venv/lib/python3.10/site-packages/alembic/script/base.py", line 583, in run_env
util.load_python_file(self.dir, "env.py")
File "/root/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
module = load_module_py(module_id, path)
File "/root/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
spec.loader.exec_module(module) # type: ignore
File "", line 883, in exec_module
File "", line 241, in call_with_frames_removed
File "/root/venv/lib/python3.10/site-packages/superset/extensions/../migrations/env.py", line 127, in
run_migrations_online()
File "/root/venv/lib/python3.10/site-packages/superset/extensions/../migrations/env.py", line 119, in run_migrations_online
context.run_migrations()
File "", line 8, in run_migrations
File "/root/venv/lib/python3.10/site-packages/alembic/runtime/environment.py", line 948, in run_migrations
self.get_context().run_migrations(**kw)
File "/root/venv/lib/python3.10/site-packages/alembic/runtime/migration.py", line 627, in run_migrations
step.migration_fn(**kw)
File "/root/venv/lib/python3.10/site-packages/superset/migrations/versions/2022-07-11_11-26_ffa79af61a56_rename_report_schedule_extra_to_extra.py", line 36, in upgrade
op.alter_column(
File "", line 8, in alter_column
File "", line 3, in alter_column
File "/root/venv/lib/python3.10/site-packages/alembic/operations/ops.py", line 1943, in alter_column
return operations.invoke(alt)
File "/root/venv/lib/python3.10/site-packages/alembic/operations/base.py", line 445, in invoke
return fn(self, operation)
File "/root/venv/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 53, in alter_column
operations.impl.alter_column(
File "/root/venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 316, in alter_column
self._exec(
File "/root/venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 207, in _exec
return conn.execute(construct, multiparams)
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1385, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
return connection._execute_ddl(
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1477, in _execute_ddl
ret = self._execute_context(
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
self.handle_dbapi_exception(
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2134, in handle_dbapi_exception
util.raise(
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/root/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "extra": syntax error
[SQL: ALTER TABLE report_schedule RENAME extra TO extra_json]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

How to reproduce the bug

1. running "superset db upgrade"

Screenshots/recordings

No response

Superset version

4.0.1

Python version

3.10

Node version

Not applicable

Browser

Not applicable

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

The error you're encountering is due to an attempt to rename a column in SQLite using syntax that SQLite does not support. The correct way to rename a column in an Alembic migration, especially when using SQLite, involves specifying the old and new column names correctly within the op.alter_column method, ensuring compatibility with the database schema and Alembic's migration handling.

Here's a snippet that demonstrates the correct approach:

op.alter_column(
    "report_schedule",
    "extra",
    new_column_name="extra_json",
    existing_type=Text,
    existing_nullable=True,
)

This method is designed to handle column renaming in a way that avoids the syntax error you've encountered. It's important to ensure that the migration script is correctly written to prevent such errors during the database upgrade process.

Additionally, it's worth noting that there have been similar issues reported with SQLite during database migrations in Superset. Some users have found that switching to a more robust database system like PostgreSQL or MySQL can provide a smoother experience with Superset, especially for production environments. If possible, consider migrating your database to avoid these kinds of issues in the future.

For more details on the migration script and handling such errors, you can refer to the Superset migration documentation and the specific migration script that caused the error.

Details

Sources

• Failed run with 'superset db upgrade'
• superset/migrations/versions/2022-07-11_11-26_ffa79af61a56_rename_report_schedule_extra_to_extra_.py

About Dosu
This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.