cannot VACUUM from within a transaction [SQL: 'vacuum;']

[tardyp]

Opening an issue here, as I am out of ideas for this:
buildbot/buildbot#2705

I can see in f254c53
that checkVacuum unit test have been removed, which looks suspicious to me.

Anyway, in the buildbot code, I am trying to compact the database by first compressing some of of a table, then I call the vacuum; function in order to get actual database size improvement.
It did work until python3.6, and now whatever I do it will raise:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) cannot VACUUM from within a transaction [SQL: 'vacuum;']

I did try to add conn.commit() before vacuum, but it says we are not in a transaction, which is also confirmed by Connection.in_transaction == False

The only way to fix this is to restore old behaviour, as implemented in the PR.

please advise

[ghost]

It's a bug in pysqlite and Python 3.6.0's sqlite3 module.
(fixed in Python 3.6.1, see http://bugs.python.org/issue28518)

A workaround is:

You can set isolation_level = None in sqlite3.connect() parameters, then sqlite3 module will not begin a transaction implicitly.

Another way is disabling auto-begin-transaction temporarily:
sql.isolation_level = None
sql.execute('VACUUM')
sql.isolation_level = '' # <- note that this is the default value of isolation_level

[tardyp]

thanks! the workaround actually worked for us buildbot/buildbot#2738

[berkerpeksag]

FYI: This bug has already been fixed in the standard library version of sqlite3.

[ghaering]

You are much better off using either the standard library sqlite3 or apsw at this point. I haven't maintained this project for years.

[MarkC-b3d]

I encountered a similar issue with python 3.10.4 and sqlite3

I was able to fix it with isolation_level=None:

def remove_active_job():
    conn = sqlite3.connect('queue.db', isolation_level=None)
    c = conn.cursor()
    c.execute("DELETE FROM active WHERE ROWID=1")
    c.execute("VACUUM")
    conn.commit()
    conn.close()