Upgrading an sqlite db fails
Closed this issue · 18 comments
In GitLab by @PekkaSavolainen on Sep 3, 2020, 14:10
Data Store upgrade process fails when upgrading from alembic version 070a0eb89e88 to the latest (39e860a11b05). Here's a db that can be used to test this.
Spine_db_-_070a0eb89e88.sqlite
Here's the same db after the upgrade process
It contains tables alternative, scenario and alternative_scenario but the alembic version is one before the latest one (9da58d2def22).
Here's the traceback when hooking up Spine_db_-_070a0eb89e88.sqlite to a Data Store, clicking on Open Editor and then Upgrade.
C:\Python36\lib\site-packages\sqlalchemy\orm\relationships.py:3053: SAWarning: relationship 'parameter_value.parameter_definition' will copy column parameter_definition.entity_class_id to column parameter_value.entity_class_id, which conflicts with relationship(s): 'parameter_value.entity' (copies entity.class_id to parameter_value.entity_class_id). Consider applying viewonly=True to read-only relationships, or provide a primaryjoin condition marking writable columns with the foreign() annotation.
for (pr, fr_) in other_props
C:\Python36\lib\site-packages\sqlalchemy\orm\relationships.py:3053: SAWarning: relationship 'relationship_entity.relationship_entity_class' will copy column relationship_entity_class.member_class_id to column relationship_entity.member_class_id, which conflicts with relationship(s): 'relationship_entity.entity' (copies entity.class_id to relationship_entity.member_class_id). Consider applying viewonly=True to read-only relationships, or provide a primaryjoin condition marking writable columns with the foreign() annotation.
for (pr, fr_) in other_props
C:\Python36\lib\site-packages\sqlalchemy\orm\relationships.py:3053: SAWarning: relationship 'relationship_entity.relationship' will copy column relationship.entity_class_id to column relationship_entity.entity_class_id, which conflicts with relationship(s): 'relationship_entity.relationship_entity_class' (copies relationship_entity_class.entity_class_id to relationship_entity.entity_class_id). Consider applying viewonly=True to read-only relationships, or provide a primaryjoin condition marking writable columns with the foreign() annotation.
for (pr, fr_) in other_props
Traceback (most recent call last):
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 237, in get_db_map
return self._do_get_db_map(url, upgrade, codename)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\helpers.py", line 150, in new_function
return func(*args, **kwargs)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 280, in _do_get_db_map
db_map = self._db_maps[url] = DiffDatabaseMapping(url, upgrade=upgrade, codename=codename)
File "C:\Python36\lib\site-packages\spinedb_api\diff_db_mapping_add_mixin.py", line 32, in __init__
super().__init__(*args, **kwargs)
File "C:\Python36\lib\site-packages\spinedb_api\diff_db_mapping_base.py", line 44, in __init__
super().__init__(*args, **kwargs)
File "C:\Python36\lib\site-packages\spinedb_api\db_mapping_base.py", line 61, in __init__
self._check_db_version(upgrade=upgrade)
File "C:\Python36\lib\site-packages\spinedb_api\db_mapping_base.py", line 201, in _check_db_version
raise SpineDBVersionError(url=self.db_url, current=current, expected=head)
spinedb_api.exception.SpineDBVersionError: The database at 'sqlite:///C:\data\SpineToolboxProjects\Database Viewer\.spinetoolbox\items\pekan_db\Spine db - 070a0eb89e88 - Copy.sqlite' is not the expected version.
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Python36\lib\site-packages\alembic\operations\batch.py", line 508, in drop_constraint
const = self.named_constraints.pop(const.name)
KeyError: 'uq_parameter_value_parameter_definition_identity_id'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\project_items\data_store\data_store.py", line 301, in open_ds_form
self._project.db_mngr.show_data_store_form({self._sa_url: self.name}, self._logger)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 211, in show_data_store_form
db_maps = [self.get_db_map(url, logger, codename=codename) for url, codename in db_url_codenames.items()]
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 211, in <listcomp>
db_maps = [self.get_db_map(url, logger, codename=codename) for url, codename in db_url_codenames.items()]
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 257, in get_db_map
return self.get_db_map(url, logger, upgrade=True, codename=codename)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 237, in get_db_map
return self._do_get_db_map(url, upgrade, codename)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\helpers.py", line 150, in new_function
return func(*args, **kwargs)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 280, in _do_get_db_map
db_map = self._db_maps[url] = DiffDatabaseMapping(url, upgrade=upgrade, codename=codename)
File "C:\Python36\lib\site-packages\spinedb_api\diff_db_mapping_add_mixin.py", line 32, in __init__
super().__init__(*args, **kwargs)
File "C:\Python36\lib\site-packages\spinedb_api\diff_db_mapping_base.py", line 44, in __init__
super().__init__(*args, **kwargs)
File "C:\Python36\lib\site-packages\spinedb_api\db_mapping_base.py", line 61, in __init__
self._check_db_version(upgrade=upgrade)
File "C:\Python36\lib\site-packages\spinedb_api\db_mapping_base.py", line 212, in _check_db_version
environment_context.run_migrations()
File "C:\Python36\lib\site-packages\alembic\runtime\environment.py", line 846, in run_migrations
self.get_context().run_migrations(**kw)
File "C:\Python36\lib\site-packages\alembic\runtime\migration.py", line 520, in run_migrations
step.migration_fn(**kw)
File "C:\Python36\lib\site-packages\spinedb_api\alembic\versions\39e860a11b05_add_alternatives_and_scenarios.py", line 129, in upgrade
alter_tables_after_update()
File "C:\Python36\lib\site-packages\spinedb_api\alembic\versions\39e860a11b05_add_alternatives_and_scenarios.py", line 81, in alter_tables_after_update
batch_op.drop_constraint("uq_parameter_value_parameter_definition_identity_id")
File "C:\Python36\lib\contextlib.py", line 88, in __exit__
next(self.gen)
File "C:\Python36\lib\site-packages\alembic\operations\base.py", line 354, in batch_alter_table
impl.flush()
File "C:\Python36\lib\site-packages\alembic\operations\batch.py", line 114, in flush
fn(*arg, **kw)
File "C:\Python36\lib\site-packages\alembic\operations\batch.py", line 516, in drop_constraint
raise ValueError("No such constraint: '%s'" % const.name)
ValueError: No such constraint: 'uq_parameter_value_parameter_definition_identity_id'
If you click on Open Editor in the Data Store properties after the failed upgrade process, another traceback ensues.
Traceback (most recent call last):
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 237, in get_db_map
return self._do_get_db_map(url, upgrade, codename)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\helpers.py", line 150, in new_function
return func(*args, **kwargs)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 280, in _do_get_db_map
db_map = self._db_maps[url] = DiffDatabaseMapping(url, upgrade=upgrade, codename=codename)
File "C:\Python36\lib\site-packages\spinedb_api\diff_db_mapping_add_mixin.py", line 32, in __init__
super().__init__(*args, **kwargs)
File "C:\Python36\lib\site-packages\spinedb_api\diff_db_mapping_base.py", line 44, in __init__
super().__init__(*args, **kwargs)
File "C:\Python36\lib\site-packages\spinedb_api\db_mapping_base.py", line 61, in __init__
self._check_db_version(upgrade=upgrade)
File "C:\Python36\lib\site-packages\spinedb_api\db_mapping_base.py", line 201, in _check_db_version
raise SpineDBVersionError(url=self.db_url, current=current, expected=head)
spinedb_api.exception.SpineDBVersionError: The database at 'sqlite:///C:\data\SpineToolboxProjects\Exporter Testing\.spinetoolbox\items\abc\abc.sqlite' is not the expected version.
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "C:\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: table alternative already exists
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\project_items\data_store\data_store.py", line 301, in open_ds_form
self._project.db_mngr.show_data_store_form({self._sa_url: self.name}, self._logger)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 211, in show_data_store_form
db_maps = [self.get_db_map(url, logger, codename=codename) for url, codename in db_url_codenames.items()]
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 211, in <listcomp>
db_maps = [self.get_db_map(url, logger, codename=codename) for url, codename in db_url_codenames.items()]
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 257, in get_db_map
return self.get_db_map(url, logger, upgrade=True, codename=codename)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 237, in get_db_map
return self._do_get_db_map(url, upgrade, codename)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\helpers.py", line 150, in new_function
return func(*args, **kwargs)
File "C:\data\GIT\SPINETOOLBOX\spinetoolbox\spine_db_manager.py", line 280, in _do_get_db_map
db_map = self._db_maps[url] = DiffDatabaseMapping(url, upgrade=upgrade, codename=codename)
File "C:\Python36\lib\site-packages\spinedb_api\diff_db_mapping_add_mixin.py", line 32, in __init__
super().__init__(*args, **kwargs)
File "C:\Python36\lib\site-packages\spinedb_api\diff_db_mapping_base.py", line 44, in __init__
super().__init__(*args, **kwargs)
File "C:\Python36\lib\site-packages\spinedb_api\db_mapping_base.py", line 61, in __init__
self._check_db_version(upgrade=upgrade)
File "C:\Python36\lib\site-packages\spinedb_api\db_mapping_base.py", line 212, in _check_db_version
environment_context.run_migrations()
File "C:\Python36\lib\site-packages\alembic\runtime\environment.py", line 846, in run_migrations
self.get_context().run_migrations(**kw)
File "C:\Python36\lib\site-packages\alembic\runtime\migration.py", line 520, in run_migrations
step.migration_fn(**kw)
File "C:\Python36\lib\site-packages\spinedb_api\alembic\versions\39e860a11b05_add_alternatives_and_scenarios.py", line 121, in upgrade
create_new_tables()
File "C:\Python36\lib\site-packages\spinedb_api\alembic\versions\39e860a11b05_add_alternatives_and_scenarios.py", line 29, in create_new_tables
sa.UniqueConstraint("name"),
File "<string>", line 8, in create_table
File "<string>", line 3, in create_table
File "C:\Python36\lib\site-packages\alembic\operations\ops.py", line 1252, in create_table
return operations.invoke(op)
File "C:\Python36\lib\site-packages\alembic\operations\base.py", line 374, in invoke
return fn(self, operation)
File "C:\Python36\lib\site-packages\alembic\operations\toimpl.py", line 101, in create_table
operations.impl.create_table(table)
File "C:\Python36\lib\site-packages\alembic\ddl\impl.py", line 258, in create_table
self._exec(schema.CreateTable(table))
File "C:\Python36\lib\site-packages\alembic\ddl\impl.py", line 140, in _exec
return conn.execute(construct, *multiparams, **params)
File "C:\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 988, in execute
return meth(self, multiparams, params)
File "C:\Python36\lib\site-packages\sqlalchemy\sql\ddl.py", line 72, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "C:\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1050, in _execute_ddl
compiled,
File "C:\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
File "C:\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "C:\Python36\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "C:\Python36\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
raise value.with_traceback(tb)
File "C:\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "C:\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 552, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table alternative already exists
[SQL:
CREATE TABLE alternative (
id INTEGER NOT NULL,
name TEXT NOT NULL,
description TEXT,
commit_id INTEGER,
CONSTRAINT pk_alternative PRIMARY KEY (id),
CONSTRAINT uq_alternative_name UNIQUE (name),
CONSTRAINT fk_alternative_commit_id_commit FOREIGN KEY(commit_id) REFERENCES "commit" (id)
)
]
(Background on this error at: http://sqlalche.me/e/e3q8)
spinedb_api version 0.7.13.
In GitLab by @soininen on Sep 3, 2020, 15:04
spinedb_api is trying to update a database that already contains the alternative table. This is very confusing.
Are you sure that that particular commit caused this? What is the history of this database? Did you ever update it to any of the intermediate database schemas we were trying out during the alternatives/scenarios development? Can you check what the alembic_version table in that particular database contains? You can use e.g. DB Browser for that.
In GitLab by @PekkaSavolainen on Sep 3, 2020, 15:12
alembic version 9da58d2def22. Actually, it does not work in my gimlet branch either so I guess it's not the fault of the commit in description.
In GitLab by @PekkaSavolainen on Sep 3, 2020, 15:17
9da58d2def22 is the version before alternatives and scenarios were added if I'm reading the alembic stuff correctly?
In GitLab by @soininen on Sep 3, 2020, 15:19
Yes, you are. Does it also contain alternative, scenario or scenario_alternative tables? If it does, then something is seriously wrong with the database. These tables should appear only after a database upgrade.
In GitLab by @PekkaSavolainen on Sep 3, 2020, 15:23
Yes, the tables are there. It might be that the upgrade process failed for some reason in the middle of the upgrade process and the alembic version was never updated.
Here's the sqlite file in question
In GitLab by @soininen on Sep 3, 2020, 15:37
Thanks for the database file! Exporter tries to read the database when it is connected to a Data store, also during project loading. Perhaps spinedb_api fails the upgrade process at that point. I can investigate this further at some point.
In GitLab by @PekkaSavolainen on Sep 3, 2020, 15:43
Since I don't have the original abc.sqlite file anymore, here's another way to debug this.
Below is another Spine db with alembic version 070a0eb89e88.
Spine_db_-_070a0eb89e88.sqlite
If you try and upgrade this to current version it creates this sqlite file.
where the alembic version is 9da58d2def22 and it contains tables alternative, scenario and scenario_alternative.
EDIT
I don't think this is related to the changes in the Exporter after all. My project where I tried this does not even have an Exporter.
In GitLab by @PekkaSavolainen on Sep 3, 2020, 16:05
changed the description
In GitLab by @soininen on Sep 3, 2020, 16:06
I don't think this is related to the changes in the Exporter after all.
Yes, it seems that the upgrade process fails somehow for this database. Basically, line 81 in the description's Traceback in 39e860a11b05_add_alternatives_and_scenarios.py fails. @manuelma any ideas what may cause this?
In GitLab by @PekkaSavolainen on Sep 3, 2020, 16:06
changed the description
In GitLab by @manuelma on Sep 3, 2020, 16:27
What's the SQLAlchemy version?
In GitLab by @PekkaSavolainen on Sep 3, 2020, 16:34
SQLAlchemy 1.3.7
In GitLab by @manuelma on Sep 3, 2020, 16:37
Thanks, will try and look into it soon. To be sure, this is not stopping anybody to do work at the moment, right?
In GitLab by @PekkaSavolainen on Sep 3, 2020, 16:39
Not that I'm aware of.
In GitLab by @manuelma on Sep 14, 2020, 17:43
mentioned in commit data@e39db69873d7dbed39c1450e8daeeb6a19e8ef08
In GitLab by @manuelma on Sep 14, 2020, 17:50
The problem was the upgrade script was trying to drop a constraint that didn't exist. But the constraint should have existed. We definitely messed up the migration process at some point in time, not sure where, so there was some dbs without the constraint being produced. I've handled the issue in commit data@e39db698 so the problem is gone. We'll just have to live with that mistake around.
I think at some point we should reset our migration history, say, for the 1.0 release, and never support older db versions anymore. Users can always 'export' their older dbs into the JSON parcel format, create a new db with the new schema, and then 'import' that JSON to persist all their data.
In GitLab by @PekkaSavolainen on Sep 14, 2020, 18:07
Great job, @manuelma.
So, to get this fix to release-0.5 branch, all I need to do is move the spinedb_api repo tag 0.7.15 to point to commit data@e39db698, right?
EDIT
That seems to do the trick, thanks.