[PYTHON] Precautions when using sqlite3 on macOS Sierra (10.12) with multiprocessing

conditions

Addendum (2016-10-25): 10.2.1 has been released, but it seems that the situation has not improved in this case. Postscript (2017-03-31): Confirmed in 10.12.4. It seems that it was fixed. You did it!

Verification code

sqlite3_sierra_exp.py


#!/usr/bin/env python

"""\
An example that crashes on macOS Sierra.
"""

from __future__ import unicode_literals
from __future__ import print_function
from __future__ import division
from __future__ import absolute_import

from logging import getLogger, StreamHandler, Formatter
from logging import DEBUG
from multiprocessing import Process, freeze_support
from threading import Thread
import os
import sqlite3
import sys
import time

_DB_PATH = os.path.join(os.path.dirname(__file__), 'state.db')


def _connect(db_path):
    conn = sqlite3.connect(db_path)
    return conn


def get_count(db_path,  name):
    conn = _connect(db_path)
    c = conn.cursor()
    c.execute('''\
    SELECT count FROM test_table
    WHERE name = ?
    ''', (name,))
    (count,) = c.fetchone()
    conn.commit()
    conn.close()
    return count


def incrementer_process(db_path, name):
    conn = _connect(db_path)
    c = conn.cursor()
    c.execute('''\
    SELECT count FROM test_table
    WHERE name = ?
    ''', (name,))
    (count,) = c.fetchone()
    c.execute('''\
    INSERT OR REPLACE INTO test_table
    (name, count)
    VALUES (?, ?)
    ''', (name, count + 1))
    conn.commit()
    conn.close()


def main():
    logger = getLogger(__name__)
    handler = StreamHandler()
    logger.addHandler(handler)
    logger.setLevel(DEBUG)
    handler.setLevel(DEBUG)
    handler.setFormatter(Formatter('%(asctime)s %(message)s'))
    logger.info('python version: {}'.format(sys.version))
    logger.info('module_version: {}'.format(sqlite3.version))
    logger.info('sqlite3_version: {}'.format(sqlite3.sqlite_version))
    name = 'name'
    db_path = _DB_PATH
    conn = _connect(db_path)
    c = conn.cursor()
    c.execute('''\
    DROP TABLE IF EXISTS
    test_table
    ''')
    c.execute('''\
    CREATE TABLE IF NOT EXISTS
    test_table (name text, count integer)
    ''')
    c.execute('''\
    CREATE UNIQUE INDEX IF NOT EXISTS
    files_index ON test_table (name)
    ''')
    c.execute('''\
    INSERT OR REPLACE INTO test_table
    (name, count)
    VALUES (?, ?)
    ''', (name, 0))
    conn.commit()
    conn.close()
    logger.debug('Creating a child')
    child = Process(target=incrementer_process,
                    args=(db_path, name))
    #child = Thread(target=incrementer_process,
    #           args=process_args,
    #           kwargs=process_kwargs)
    child.start()
    child.join()
    logger.info('value: {}'.format(get_count(db_path, name)))


if __name__ == '__main__':
    freeze_support()
    main()

Create a sqlite3 DB in the main process and increment the value by 1 via multiprocessing.Process. It is not a situation where race conditions occur. It's a bit sloppy, but therading.Thread doesn't cause any problems.

Operation result

$ pyenv shell 3.5.2-nativesqlite3
$ ./sqlite3_sierra_exp.py
2016-10-24 22:14:29,065 python version: 3.5.2 (default, Oct 24 2016, 22:06:56)
[GCC 4.2.1 Compatible Apple LLVM 8.0.0 (clang-800.0.38)]
2016-10-24 22:14:29,065 module_version: 2.6.0
2016-10-24 22:14:29,065 sqlite3_version: 3.14.0
2016-10-24 22:14:29,072 Creating a child
2016-10-24 22:14:29,081 value: 0

Even though I'm using pyenv, I suddenly got some unbelievable results. Is it 0? ??

If you use Sierra's built-in Python here ...

$ ./sqlite3_sierra_exp.py
2016-10-24 22:15:05,994 python version: 2.7.10 (default, Jul 30 2016, 18:31:42)
[GCC 4.2.1 Compatible Apple LLVM 8.0.0 (clang-800.0.34)]
2016-10-24 22:15:05,994 module_version: 2.6.0
2016-10-24 22:15:05,994 sqlite3_version: 3.14.0
2016-10-24 22:15:06,001 Creating a child
2016-10-24 22:15:06,009 value: 0
スクリーンショット 2016-10-24 22.15.14.png

Hey! ??

Compare with the execution result on Debian Jessie for comparison

$ lsb_release -a
No LSB modules are available.
Distributor ID:	Debian
Description:	Debian GNU/Linux 8.6 (jessie)
Release:	8.6
Codename:	jessie
$ pyenv shell system
$ ./sqlite3_sierra_exp.py
2016-10-24 22:16:21,316 python version: 2.7.9 (default, Jun 29 2016, 13:08:31)
[GCC 4.9.2]
2016-10-24 22:16:21,316 module_version: 2.6.0
2016-10-24 22:16:21,316 sqlite3_version: 3.8.7.1
2016-10-24 22:16:21,352 Creating a child
2016-10-24 22:16:21,368 value: 1

Well, that's right. It's 1 ...

I will also try it on Windows. It's a screenshot because it's troublesome to copy and paste ...

スクリーンショット 2016-10-24 22.22.36.png

It's 1 ... (sighs

Cause

To conclude, it seems to be the result of going through the following bug (exactly the problem with libsqlite3 that comes with macOS): Apple-supplied libsqlite3 on OS X is not fork safe; can cause crashes

Countermeasures

Waiting for the library attached to the OS to be modified, one. Or bring sqlite3 3.15.0 from Homebrew and use it to get rid of this symptom. Conversely, if you don't change this part, any Python version can get caught up in the above problem.

$ brew info sqlite3
sqlite: stable 3.15.0 (bottled) [keg-only]
Command-line interface for SQLite
https://sqlite.org/
/usr/local/Cellar/sqlite/3.9.2 (10 files, 2.8M)
  Poured from bottle on 2016-08-03 at 14:21:08
/usr/local/Cellar/sqlite/3.15.0 (11 files, 2.9M)
  Poured from bottle on 2016-10-24 at 18:12:13
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/sqlite.rb
==> Dependencies
Recommended: readline ✔
Optional: icu4c ✔
==> Options
--universal
	Build a universal binary
--with-dbstat
	Enable the 'dbstat' virtual table
--with-docs
	Install HTML documentation
--with-fts
	Enable the FTS3 module
--with-fts5
	Enable the FTS5 module (experimental)
--with-functions
	Enable more math and string functions for SQL queries
--with-icu4c
	Enable the ICU module
--with-json1
	Enable the JSON1 extension
--with-secure-delete
	Defaults secure_delete to on
--with-session
	Enable the session extension
--with-unlock-notify
	Enable the unlock notification feature
--without-readline
	Build without readline support
--without-rtree
	Disable the R*Tree index module
==> Caveats
This formula is keg-only, which means it was not symlinked into /usr/local.

macOS provides an older sqlite3.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you'll need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/sqlite/lib
    CPPFLAGS: -I/usr/local/opt/sqlite/include
    PKG_CONFIG_PATH: /usr/local/opt/sqlite/lib/pkgconfig

$ LDFLAGS="-L/usr/local/opt/sqlite/lib" \
  CPPFLAGS="-I/usr/local/opt/sqlite/include" \
  PKG_CONFIG_PATH="/usr/local/opt/sqlite/lib/pkgconfig" \
  pyenv install 3.5.2
(Omission)
$ pyenv shell 3.5.2
$ ./sqlite3_sierra_exp.py
2016-10-24 22:19:30,973 python version: 3.5.2 (default, Oct 24 2016, 21:47:12)
[GCC 4.2.1 Compatible Apple LLVM 8.0.0 (clang-800.0.38)]
2016-10-24 22:19:30,973 module_version: 2.6.0
2016-10-24 22:19:30,973 sqlite3_version: 3.15.0
2016-10-24 22:19:30,978 Creating a child
2016-10-24 22:19:30,992 value: 1

The point is that the part of sqlite3_version has changed.

bonus

Under the Python environment of pyenv, the pop-up indicating that it crashed does not appear. Even ʻexcept BaseException` crashes the Process at a level that cannot be caught, so the crash itself is not noticed.

I was tired

Recommended Posts

Precautions when using sqlite3 on macOS Sierra (10.12) with multiprocessing
Precautions when using six with Python 2.5
Precautions when using google-cloud library with GAE / py
Precautions when using Chainer
Troublesome story when using Python3 with VScode on ubuntu
When using optparse with iPython
IP spoof using tor on macOS and check with python
I got an error when pip install tweepy on macOS Sierra, so I dealt with it
When coverage fails with _sqlite3 error
Precautions when using pit in Python
Dealing with PermissionError [Error 1] for pip install -U pip on macOS Sierra
Precautions when installing tensorflow with anaconda
Get started with MicroPython (on macOS)
Precautions when using codecs and pandas
Precautions when using the urllib.parse.quote function
Notes on using rstrip with python.
Precautions when using phantomjs from python
How to quickly create a machine learning environment using Jupyter Notebook on macOS Sierra with anaconda
When using MeCab with virtualenv python
Install Java2Python on macOS High Sierra (10.13)
Settings when using Python 3 requests and Beautiful Soup with crostini on Chromebook
Error resolution when installing numba on macOS
Precautions when solving DP problems with Python
[python, multiprocessing] Behavior for exceptions when using multiprocessing
Precautions when using for statements in pandas
Application development using SQLite with Django (PTVS)
Error, warning when using TensorFlow on Mac
Installing PIL with Python 3.x on macOS
How to install cx_Oracle on macOS Sierra
Error that occurred in OpenCV3 and its solution Precautions when using OpenCV3 on Mac
CPU usage is 100% when using pygame on Ubuntu 16.04
A addictive story when using tensorflow on Android
Precautions when using OpenCV from Power Automate Desktop
About WARNING when packaging with pyinstaller using pyocr
Using MLflow with Databricks ① --Experimental tracking on notebook -
Install Python 3.8.6 on macOS Big Sur using pyenv
Precautions when dealing with control structures in Python 2.6
Secret key generation when using EncryptedCookieStorage with aiohttp_session
How to deal with SessionNotCreatedException when using Selenium
Precautions when using tf.keras.layers.TimeDistributed for tf.keras custom layer
Get started with Python on macOS Big Sur
Install Python 3 on MacOS Catalina (with Homebrew only)
I tried using a database (sqlite3) with kivy
[Web development with Python] Precautions when saving cookies
[Python] Notes on accelerating genetic algorithms using multiprocessing
What are you using when testing with Python?