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.