Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Cannot VACUUM from within a transaction (hides another exception) #127

Open
ccomb opened this issue Mar 10, 2023 · 4 comments
Open

Cannot VACUUM from within a transaction (hides another exception) #127

ccomb opened this issue Mar 10, 2023 · 4 comments

Comments

@ccomb
Copy link
Contributor

ccomb commented Mar 10, 2023

Something new at the end of the import of agribalyse 3.1.1:

(...)
Applying strategy: link_iterable_by_fields
Applying strategy: link_iterable_by_fields
46830 datasets
5064938 exchanges
15667 unlinked exchanges
  Type production: 15663 unique unlinked exchanges
Vacuuming database 
Vacuuming database 
Traceback (most recent call last):
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3236, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: cannot VACUUM from within a transaction

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/.../data/food/import_agb/./import_agribalyse.py", line 236, in <module>
    agb_importer.write_database()
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2io/importers/simapro_csv.py", line 103, in write_database
    db = super(SimaProCSVImporter, self).write_database(data, name, *args, **kwargs)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2io/importers/base_lci.py", line 269, in write_database
    db.write(existing)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/project.py", line 358, in writable_project
    return wrapped(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/peewee/database.py", line 260, in write
    self._efficient_write_many_data(data)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/peewee/database.py", line 193, in _efficient_write_many_data
    self.delete(keep_params=True, warn=False)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/project.py", line 358, in writable_project
    return wrapped(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/peewee/database.py", line 349, in delete
    sqlite3_lci_db.vacuum()
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/sqlite.py", line 57, in vacuum
    self.execute_sql('VACUUM;')
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/sqlite.py", line 50, in execute_sql
    return self.db.execute_sql(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3234, in execute_sql
    with __exception_wrapper__:
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3010, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 192, in reraise
    raise value.with_traceback(tb)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3236, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: cannot VACUUM from within a transaction

Looks like there is something wrong trying to delete data with VACUUM inside a transaction. I'm on the bw2legacy branch:
https://github.com/brightway-lca/brightway2-data/blob/bw2legacy/bw2data/backends/peewee/database.py#L261

There is a first Exception in self._efficient_write_many_data(data) which does not appear and is then shadowed by a second exception due to vacuumin inside the transaction in self.delete(warn=False).

If I try to remove the try: except: to trigger the first exception, I fall on a second one elsewhere similar to the first one:

Vacuuming database 
Traceback (most recent call last):
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3236, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: cannot VACUUM from within a transaction

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/.../data/food/import_agb/./import_agribalyse.py", line 92, in <module>
    agb_importer.add_unlinked_flows_to_biosphere_database("agribalyse3.1 biosphere")
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2io/importers/base_lci.py", line 411, in add_unlinked_flows_to_biosphere_database
    bio.write(data)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/project.py", line 358, in writable_project
    return wrapped(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/peewee/database.py", line 260, in write
    self._efficient_write_many_data(data)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/peewee/database.py", line 193, in _efficient_write_many_data
    self.delete(keep_params=True, warn=False)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/project.py", line 358, in writable_project
    return wrapped(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/peewee/database.py", line 349, in delete
    sqlite3_lci_db.vacuum()
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/sqlite.py", line 57, in vacuum
    self.execute_sql('VACUUM;')
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/sqlite.py", line 50, in execute_sql
    return self.db.execute_sql(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3234, in execute_sql
    with __exception_wrapper__:
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3010, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 192, in reraise
    raise value.with_traceback(tb)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3236, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: cannot VACUUM from within a transaction

If I remove the self.delete to understand I finally fall on the original Exception, which is a constraint error:

0% [##############################] 100% | ETA: 00:00:00
Total time elapsed: 00:00:00
Title: Writing activities to SQLite3 database:
  Started: 03/10/2023 11:06:31
  Finished: 03/10/2023 11:06:31
  Total time elapsed: 00:00:00
  CPU %: 95.00
  Memory %: 30.01
Traceback (most recent call last):
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3236, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.IntegrityError: UNIQUE constraint failed: activitydataset.database, activitydataset.code

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/.../data/food/import_agb/./import_agribalyse.py", line 92, in <module>
    agb_importer.add_unlinked_flows_to_biosphere_database("agribalyse3.1 biosphere")
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2io/importers/base_lci.py", line 411, in add_unlinked_flows_to_biosphere_database
    bio.write(data)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/project.py", line 358, in writable_project
    return wrapped(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/peewee/database.py", line 260, in write
    self._efficient_write_many_data(data)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/peewee/database.py", line 223, in _efficient_write_many_data
    self._add_indices()
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/peewee/database.py", line 149, in _add_indices
    sqlite3_lci_db.execute_sql('CREATE UNIQUE INDEX IF NOT EXISTS "activitydataset_key" ON "activitydataset" ("database", "code")')
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/sqlite.py", line 50, in execute_sql
    return self.db.execute_sql(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3234, in execute_sql
    with __exception_wrapper__:
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3010, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 192, in reraise
    raise value.with_traceback(tb)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3236, in execute_sql
    cursor.execute(sql, params or ())
peewee.IntegrityError: UNIQUE constraint failed: activitydataset.database, activitydataset.code
@ccomb
Copy link
Contributor Author

ccomb commented Mar 10, 2023 •
edited
Loading

Trying with Brightway 2.5, I've managed to progress up to the end of the import once, and the next time I got the VACUUM error again:

Applying strategy: link_iterable_by_fields
18641 datasets
5064938 exchanges
0 unlinked exchanges
  
Not able to determine geocollections for all datasets. This database is not ready for regionalization.
Vacuuming database 
Vacuuming database 
Traceback (most recent call last):
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3236, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: cannot VACUUM from within a transaction

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/.../data/food/import_agb/./import_agribalyse.py", line 240, in <module>
    agb_importer.write_database()
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2io/importers/simapro_csv.py", line 108, in write_database
    db = super(SimaProCSVImporter, self).write_database(data, name, *args, **kwargs)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2io/importers/base_lci.py", line 276, in write_database
    db.write(existing)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/project.py", line 446, in writable_project
    return wrapped(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/base.py", line 535, in write
    self._efficient_write_many_data(data)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/base.py", line 461, in _efficient_write_many_data
    self.delete(keep_params=True, warn=False)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/project.py", line 446, in writable_project
    return wrapped(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/backends/base.py", line 661, in delete
    sqlite3_lci_db.vacuum()
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/sqlite.py", line 48, in vacuum
    self.execute_sql("VACUUM;")
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/bw2data/sqlite.py", line 41, in execute_sql
    return self.db.execute_sql(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3234, in execute_sql
    with __exception_wrapper__:
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3010, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 192, in reraise
    raise value.with_traceback(tb)
  File "/home/.../data/venv3.11/lib/python3.11/site-packages/peewee.py", line 3236, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: cannot VACUUM from within a transaction

The set of versions I'm using is: brightway25-1.0.6 bw2analyzer-0.11.4 bw2calc-2.0.dev12 bw2data-4.0.dev19 bw2io-0.9.dev10 bw2parameters-1.0.0 bw_migrations-0.2 bw_processing-0.8.2
through this requirements.txt:

git+https://github.com/brightway-lca/brightway25
git+https://github.com/brightway-lca/brightway2-calc
git+https://github.com/brightway-lca/[email protected]
git+https://github.com/ccomb/brightway2-io
git+https://github.com/brightway-lca/brightway2-parameters
fjson==0.1.6
matplotlib==3.6.3
numpy==1.24.2
olca-ipc==0.0.12
pandas==1.5.3
scipy==1.10.1
xlrd==2.0.1

@ccomb ccomb changed the title Cannot VACUUM from inside a transaction (hides another exception) Cannot VACUUM from within a transaction (hides another exception) Mar 10, 2023
ccomb added a commit to ccomb/brightway2-data that referenced this issue Mar 10, 2023
@ccomb
Move the vacuum after the commit (fix brightway-lca#127)
9d508fd
@ccomb ccomb mentioned this issue Mar 10, 2023
Merged
cmutel added a commit that referenced this issue Mar 15, 2023
@cmutel
Merge pull request #128 from ccomb/4.0.DEV19
9f23c52 
Move the vacuum after the commit (fix #127)
ccomb added a commit to ccomb/brightway2-data that referenced this issue Mar 23, 2023
@ccomb
Backport brightway-lca#128 to fix brightway-lca#127 on bw2legacy
c8f80e8
@ccomb ccomb mentioned this issue Mar 23, 2023
Merged
cmutel added a commit that referenced this issue Apr 8, 2023
@cmutel
Merge pull request #130 from ccomb/vacuum
4609290 
Backport #128 to fix #127 on bw2legacy
@ccomb
Copy link
Contributor Author

ccomb commented Jun 15, 2023

Could not reproduce anymore. I'll reopen if it happens again

@ccomb ccomb closed this as completed Jun 15, 2023
ccomb added a commit to ccomb/brightway2-data that referenced this issue Jul 4, 2023
@ccomb
Move the vacuum after the commit (fix brightway-lca#127)
0ed6b7d
@romainsacchi
Copy link

Exact same issue here, exporting databases from premise back to BW2.
Note the print statement "Vacuuming database" printed twice.

bw2data 3.6.6
peewee 3.16.3

Write new database(s) to Brightway2.
One or multiple duplicates detected. Removing them...
Database test1 already exists: it will be overwritten.
Vacuuming database 
Vacuuming database 
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/peewee.py:3251, in Database.execute_sql(self, sql, params, commit)
   3250     cursor = self.cursor()
-> 3251     cursor.execute(sql, params or ())
   3252 return cursor

OperationalError: cannot VACUUM from within a transaction

During handling of the above exception, another exception occurred:

OperationalError                          Traceback (most recent call last)
Cell In[4], line 65
     58 #ndb.update_buses()
     59 #ndb.update_cars()
     60 #ndb.update_trucks()
     61 #ndb.update_two_wheelers()
     64 if export == "brightway":
---> 65     ndb.write_db_to_brightway([
     66         "test1",
     67         "test2"
     68         ]
     69     )
     70     lca = bw2calc.LCA({bw.Database("test1").random(): 1})
     71     lca.lci()

File ~/GitHub/premise/premise/ecoinvent_modification.py:1294, in NewDatabase.write_db_to_brightway(self, name)
   1291     cache.update(results[s][1])
   1293 for scen, scenario in enumerate(self.scenarios):
-> 1294     write_brightway2_database(
   1295         scenario["database"],
   1296         name[scen],
   1297     )
   1298 # generate scenario report
   1299 self.generate_scenario_report()

File ~/GitHub/premise/premise/utils.py:307, in write_brightway2_database(data, name, reset_codes)
    305 link_internal(data)
    306 check_internal_linking(data)
--> 307 PremiseImporter(name, data).write_database()

File ~/GitHub/premise/premise/utils.py:281, in PremiseImporter.write_database(self)
    279 if self.db_name in databases:
    280     print(f"Database {self.db_name} already exists: " "it will be overwritten.")
--> 281 super().write_database()

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/bw2io/importers/base_lci.py:268, in LCIImporter.write_database(self, data, delete_existing, backend, activate_parameters, **kwargs)
    266 self.write_database_parameters(activate_parameters, delete_existing)
    267 existing.update(data)
--> 268 db.write(existing)
    270 if activate_parameters:
    271     self._write_activity_parameters(activity_parameters)

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/bw2data/project.py:358, in writable_project(wrapped, instance, args, kwargs)
    356 if projects.read_only:
    357     raise ReadOnlyProject(READ_ONLY_PROJECT)
--> 358 return wrapped(*args, **kwargs)

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/bw2data/backends/peewee/database.py:260, in SQLiteBackend.write(self, data, process)
    258 if data:
    259     try:
--> 260         self._efficient_write_many_data(data)
    261     except:
    262         # Purge all data from database, then reraise
    263         self.delete(warn=False)

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/bw2data/backends/peewee/database.py:193, in SQLiteBackend._efficient_write_many_data(self, data, indices)
    191 try:
    192     sqlite3_lci_db.db.begin()
--> 193     self.delete(keep_params=True, warn=False)
    194     exchanges, activities = [], []
    196     if not getattr(config, "is_test", None):

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/bw2data/project.py:358, in writable_project(wrapped, instance, args, kwargs)
    356 if projects.read_only:
    357     raise ReadOnlyProject(READ_ONLY_PROJECT)
--> 358 return wrapped(*args, **kwargs)

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/bw2data/backends/peewee/database.py:349, in SQLiteBackend.delete(self, keep_params, warn)
    346     DatabaseParameter.delete().where(DatabaseParameter.database == self.name).execute()
    348 if vacuum_needed:
--> 349     sqlite3_lci_db.vacuum()

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/bw2data/sqlite.py:57, in SubstitutableDatabase.vacuum(self)
     55 def vacuum(self):
     56     print("Vacuuming database ")
---> 57     self.execute_sql('VACUUM;')

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/bw2data/sqlite.py:50, in SubstitutableDatabase.execute_sql(self, *args, **kwargs)
     49 def execute_sql(self, *args, **kwargs):
---> 50     return self.db.execute_sql(*args, **kwargs)

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/peewee.py:3249, in Database.execute_sql(self, sql, params, commit)
   3247     __deprecated__('"commit" has been deprecated and is a no-op.')
   3248 logger.debug((sql, params))
-> 3249 with __exception_wrapper__:
   3250     cursor = self.cursor()
   3251     cursor.execute(sql, params or ())

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/peewee.py:3019, in ExceptionWrapper.__exit__(self, exc_type, exc_value, traceback)
   3017 new_type = self.exceptions[exc_type.__name__]
   3018 exc_args = exc_value.args
-> 3019 reraise(new_type, new_type(exc_value, *exc_args), traceback)

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/peewee.py:192, in reraise(tp, value, tb)
    190 def reraise(tp, value, tb=None):
    191     if value.__traceback__ is not tb:
--> 192         raise value.with_traceback(tb)
    193     raise value

File /opt/homebrew/Caskroom/miniforge/base/envs/premise/lib/python3.10/site-packages/peewee.py:3251, in Database.execute_sql(self, sql, params, commit)
   3249 with __exception_wrapper__:
   3250     cursor = self.cursor()
-> 3251     cursor.execute(sql, params or ())
   3252 return cursor

OperationalError: cannot VACUUM from within a transaction

@romainsacchi romainsacchi reopened this Sep 14, 2023
@ccomb
Copy link
Contributor Author

ccomb commented Sep 14, 2023

@romainsacchi I also got the bug on 3.6.6 and it was fixed in the branch bw2legacy. So you need to switch to bw2legacy or a version 3.6.7 with this bugfix need to be released.

@Stew-McD Stew-McD mentioned this issue Oct 2, 2023
Closed
@BastienPais BastienPais mentioned this issue Oct 24, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@ccomb @romainsacchi