dosubot[bot]
commented
5 months ago 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'](https://github.com/apache/superset/issues/23622) - [superset/migrations/versions/2022-07-11_11-26_ffa79af61a56_rename_report_schedule_extra_to_extra_.py](https://github.com/apache/superset/blob/master/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.
krishanthvs
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_toextra.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_dbapiexception
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
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