sqlite error on macOS: cannot change into wal mode from within a transaction

[mattmaddux]

I'm getting this error repeatedly on Mac OS 10.11 (El Capitan). Haven't been able to get it working correctly yet. I actually totally wiped the system after seeing the error the first time (for another reason) and figured I'd try again after the new install. I see the same thing every time, though not always on the same command. After running into the error I found the suggestion in the FAQ to use env no_proxy='*' on macOS, so I've done so, but it didn't solve the problem.

Any suggestions?

Here's my command line:

Media-Server:~ *****$ pip3 install --upgrade --pre acdcli Collecting acdcli   Using cached acdcli-0.3.2-py3-none-any.whl Requirement already up-to-date: python-dateutil in /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages (from acdcli) Requirement already up-to-date: requests!=2.9.0,>=2.1.0 in /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages (from acdcli) Requirement already up-to-date: requests-toolbelt!=0.5.0 in /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages (from acdcli) Requirement already up-to-date: fusepy in /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages (from acdcli) Requirement already up-to-date: appdirs in /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages (from acdcli) Requirement already up-to-date: colorama in /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages (from acdcli) Requirement already up-to-date: six>=1.5 in /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages (from python-dateutil->acdcli) Installing collected packages: acdcli Successfully installed acdcli-0.3.2 Media-Server:~ $ env no_proxy='' acd_cli init For the one-time authentication a browser (tab) will be opened at https://tensile-runway-92512.appspot.com/. Please accept the request and save the plaintext response data into a file called "oauth_data" in the directory "/Users//Library/Caches/acd_cli". Press a key to open a browser.

Press a key if you have saved the "oauth_data" file into "/Users/******/Library/Caches/acd_cli".

16-12-29 00:13:30.294 [CRITICAL] [acd_cli] - Root node not found. Please sync. Media-Server:~ ****$ env no_proxy='' acd_cli sync Getting changes.. Inserting nodes. Media-Server:~ $ env no_proxy='' acd_cli -nl mount -ro /Users//cloud_drive Traceback (most recent call last):   File "/Library/Frameworks/Python.framework/Versions/3.6/bin/acd_cli", line 11, in     sys.exit(main())   File "/Library/Frameworks/Python.framework/Versions/3.6/bin/acd_cli.py", line 1574, in main     cache = db.NodeCache(CACHE_PATH, SETTINGS_PATH, args.check)   File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/acdcli/cache/db.py", line 78, in init     self._execute_pragma('journal_mode', self._conf['sqlite']['journal_mode'])   File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/acdcli/cache/db.py", line 88, in _execute_pragma     c.execute('PRAGMA %s=%s;' % (key, value)) sqlite3.OperationalError: cannot change into wal mode from within a transaction Media-Server:~ ******$

[mattmcdev]

I found the same issue on OS X 10.12 (Sierra) using Python 3.6.

Installing Python 3.4 and running via that older version seems to work fine.

[yadayada]

I presume the autocommit feature is not working. Could someone please test the small patch below and report back?

diff --git a/acdcli/cache/db.py b/acdcli/cache/db.py
index 35eab35..0ea048f 100644
--- a/acdcli/cache/db.py
+++ b/acdcli/cache/db.py
@@ -84,7 +84,7 @@ class NodeCache(SchemaMixin, QueryMixin, SyncMixin, FormatterMixin):
         return self.tl._conn
 
     def _execute_pragma(self, key, value) -> str:
-        with cursor(self._conn) as c:
+        with mod_cursor(self._conn) as c:
             c.execute('PRAGMA %s=%s;' % (key, value))
             r = c.fetchone()
         if r:

[mattmcdev]

Somewhat inconclusively, yes - but it's also now working for me on 3.6 without the patch (possibly because I'm running a version on 3.4 alongside)

[jlarson82]

I tested the small patch on my system and it didn't resolve the "sqlite3.OperationalError: cannot change into wal mode from within a transaction" issue on my laptop. My OS is macOS Sierra 10.12.2 with Python 3.6.0 installed. I have acdcli 0.3.2 installed with api 0.9.2.

[sspilleman]

@yadayada, I can also confirm the small patch is NOT working.... Sierra 10.13.3, Python 3.6, acdcli 0.3.2, api 0.9.2.

[spaccewalker]

Exactly the same problem. It just worked again after 12 days of errors! Donno why it worked but maybe the thing amazon just gives out a certain number of nods during 12 days or something like that (I suppose it could be that)... But I really don't know. (I'm not a programmer and a also a totally newbie to working in the terminal). Sierra 10.12.2, acdcli 0.3.2, api 0.9.2.

[yadayada]

Another shot in the dark:

diff --git a/acdcli/cache/db.py b/acdcli/cache/db.py
index 35eab35..5bab3d2 100644
--- a/acdcli/cache/db.py
+++ b/acdcli/cache/db.py
@@ -84,6 +84,7 @@ class NodeCache(SchemaMixin, QueryMixin, SyncMixin, FormatterMixin):
         return self.tl._conn
 
     def _execute_pragma(self, key, value) -> str:
+        self._conn.commit()
         with cursor(self._conn) as c:
             c.execute('PRAGMA %s=%s;' % (key, value))
             r = c.fetchone()

[sspilleman]

Hi @yadayada . Sorry, this one doesn't work either :-(

[sspilleman]

See code below.... that seems to work when executing

acdcli ls

Not sure however what i'm risking/breaking just disabling the 'PRAGMA journal_mode=wal;'

def _execute_pragma(self, key, value) -> str:
        """bug"""
        with cursor(self._conn) as c:
            if key is 'journal_mode':
                logger.info('PRAGMA %s=%s;' % (key, value))
            else:
                c.execute('PRAGMA %s=%s;' % (key, value))
            r = c.fetchone()

[yadayada]

Yes, you may just disable WAL mode.

diff --git a/acdcli/cache/db.py b/acdcli/cache/db.py
index 35eab35..194b0f7 100644
--- a/acdcli/cache/db.py
+++ b/acdcli/cache/db.py
@@ -75,7 +75,6 @@ class NodeCache(SchemaMixin, QueryMixin, SyncMixin, FormatterMixin):
             self.root_id = first_id
 
         self._execute_pragma('busy_timeout', self._conf['sqlite']['busy_timeout'])
-        self._execute_pragma('journal_mode', self._conf['sqlite']['journal_mode'])
 
     @property
     def _conn(self) -> sqlite3.Connection:

[RinCat]

Same error on Linux.

sqlite3.OperationalError: cannot change into wal mode from within a transaction

[mattmaddux]

Can confirm problem is fixed by rolling back to Python 3.4.4

[yadayada]

Let's have a look at the sqlite3 module documentation for Python 3.6...

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command.

But the What's New in Python 3.6 page states

sqlite3 no longer implicitly commits an open transaction before DDL statements.

I'm still looking for a missing commit statement.

[yadayada]

diff --git a/acdcli/cache/db.py b/acdcli/cache/db.py
index 35eab35..aac79ed 100644
--- a/acdcli/cache/db.py
+++ b/acdcli/cache/db.py
@@ -35,7 +35,7 @@ class IntegrityError(Exception):
 
 
 def _create_conn(path: str) -> sqlite3.Connection:
-    c = sqlite3.connect(path)
+    c = sqlite3.connect(path, isolation_level='IMMEDIATE')
     c.row_factory = sqlite3.Row # allow dict-like access on rows with col name
     return c

I did not encounter any problems using the above patch, can anyone confirm?

[ahom]

The patch with isolation_level='IMMEDIATE' did not work on Linux/Python3.6. However, if I completely disable wal mode by removing the corresponding PRAGMA it works fine.

[yadayada]

@ahom Strange, I tested on Debian testing with a freshly compiled Python 3.6.0 and it made the intermittent wal mode change errors reliably disappear.

[juanrodgil]

The patch with isolation_level='IMMEDIATE' did not work on Archlinux fully updated with the AUR acd_cli package.

[ahom]

@yadayada I was using archlinux and the acd_cli-git AUR package. I rebuilt from scratch this morning with the commit you submitted and it now works fine without patches.

[juanrodgil]

@yadayada I changed the acd_cli to the acd_cli-git AUR package, with the last commit, and it works for me too!

[rodrigc]

Looks like this is the change in Python which changed the behavior: https://github.com/python/cpython/commit/3a96828b60afb528a5bf07e168c1b54f15df783f

[rexhunt]

I just also changes from acd_cli to acd_cli-git AUR packages which has fixed this problem for me.