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

In-memory SQLite forgets DDL transaction #44

Open
wsanchez opened this issue Jun 8, 2018 · 3 comments
Open

In-memory SQLite forgets DDL transaction #44

wsanchez opened this issue Jun 8, 2018 · 3 comments

Comments

@wsanchez
Copy link

wsanchez commented Jun 8, 2018

I've got some code that creates a SQLite DB, populates a schema, then writes some data.

If the SQLite DB is a file, this seems to work as expected:

2018-06-08T16:10:40-0700 [__main__.Index#info] Importing transmissions...
2018-06-08T16:10:40-0700 [__main__.Index#info] Initializing SQLite DB...
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,028 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,029 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,029 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,029 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,030 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("TRANSMISSION")
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,030 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,031 INFO sqlalchemy.engine.base.Engine INSERT INTO "TRANSMISSION" ("STATION", "SYSTEM", "CHANNEL") VALUES (?, ?, ?)
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,031 INFO sqlalchemy.engine.base.Engine (('Radio 1', 'A', 'Ops'), ('Radio 4', 'B', 'Talk'))
2018-06-08T16:10:40-0700 [stdout#info] 2018-06-08 16:10:40,032 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-08T16:10:40-0700 [-] Main loop terminated.

However, if the SQLite DB is an in-memory DB, it fails, complaining that the table being written to doesn't exist:

2018-06-08T16:12:34-0700 [__main__.Index#info] Importing transmissions...
2018-06-08T16:12:34-0700 [__main__.Index#info] Initializing SQLite DB...
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,912 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,912 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,913 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,913 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,914 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("TRANSMISSION")
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,914 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,914 INFO sqlalchemy.engine.base.Engine 
2018-06-08T16:12:34-0700 [stdout#info] CREATE TABLE "TRANSMISSION" (
2018-06-08T16:12:34-0700 [stdout#info] 	"STATION" VARCHAR NOT NULL, 
2018-06-08T16:12:34-0700 [stdout#info] 	"SYSTEM" VARCHAR NOT NULL, 
2018-06-08T16:12:34-0700 [stdout#info] 	"CHANNEL" VARCHAR NOT NULL
2018-06-08T16:12:34-0700 [stdout#info] )
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,915 INFO sqlalchemy.engine.base.Engine ()
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,915 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,916 INFO sqlalchemy.engine.base.Engine INSERT INTO "TRANSMISSION" ("STATION", "SYSTEM", "CHANNEL") VALUES (?, ?, ?)
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,916 INFO sqlalchemy.engine.base.Engine (('Radio 1', 'A', 'Ops'), ('Radio 4', 'B', 'Talk'))
2018-06-08T16:12:34-0700 [stdout#info] 2018-06-08 16:12:34,916 INFO sqlalchemy.engine.base.Engine ROLLBACK
2018-06-08T16:12:34-0700 [-] main function encountered error
	Traceback (most recent call last):
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 500, in errback
	    self._startRunCallbacks(fail)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 567, in _startRunCallbacks
	    self._runCallbacks()
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 653, in _runCallbacks
	    current.result = callback(current.result, *args, **kw)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 1442, in gotResult
	    _inlineCallbacks(r, g, deferred)
	--- <exception caught here> ---
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/internet/defer.py", line 1384, in _inlineCallbacks
	    result = result.throwExceptionIntoGenerator(g)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/twisted/python/failure.py", line 422, in throwExceptionIntoGenerator
	    return g.throw(self.type, self.value, self.tb)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/mixer/alch_test.py", line 94, in addTransmissions
	    for transmission in transmissions
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/alchimia/engine.py", line 33, in container
	    result = work(*args, **kwargs)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2075, in execute
	    return connection.execute(statement, *multiparams, **params)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
	    return meth(self, multiparams, params)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
	    return connection._execute_clauseelement(self, multiparams, params)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
	    compiled_sql, distilled_params
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
	    context)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
	    exc_info
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
	    reraise(type(exception), exception, tb=exc_tb, cause=cause)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
	    raise value.with_traceback(tb)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
	    context)
	  File "/Volumes/data/Users/wsanchez/Documents/Developer/BurningMan/radio-mixer/.tox/coverage-py36/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 505, in do_executemany
	    cursor.executemany(statement, parameters)
	sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: TRANSMISSION [SQL: 'INSERT INTO "TRANSMISSION" ("STATION", "SYSTEM", "CHANNEL") VALUES (?, ?, ?)'] [parameters: (('Radio 1', 'A', 'Ops'), ('Radio 4', 'B', 'Talk'))] (Background on this error at: http://sqlalche.me/e/e3q8)
	
2018-06-08T16:12:34-0700 [-] Main loop terminated.

The difference between these two runs is whether line 71 or line 72 is commented out.
@wsanchez
Copy link
Author

wsanchez commented Oct 5, 2018

IIRC, according to @glyph, the issue here is that SQLite doesn't work at all with Alchmia, because SQLite doesn't get along with multiple threads.

So… either Alchmia should prevent the use of threads when one is using SQLite, or, the example code on the README file should stop using SQLite as an example, because presently its lying.

@glyph
Copy link
Collaborator

glyph commented Oct 15, 2018

IIRC, according to @glyph, the issue here is that SQLite doesn't work at all with Alchmia, because SQLite doesn't get along with multiple threads.

Wait, what? SQLite works fine with Alchimia. There are lots of unit tests showing that it does. SQLite's in-memory store can't easily be accessed by multiple threads, because there's no way that I'm aware of to get discrete "Connection" objects pointing at the same data structure.

@glyph
Copy link
Collaborator

glyph commented Oct 15, 2018

So… either Alchmia should prevent the use of threads when one is using SQLite, or, the example code on the README file should stop using SQLite as an example, because presently its lying.

The example code should probably be modified to use a disk file, yeah.

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
@wsanchez @glyph