Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

inability to concurrently open same sqlite db on windows #1871

Closed
springmeyer opened this Issue · 10 comments

4 participants

@springmeyer
Owner

On windows the system read locks prevent opening the same db (or doing operations like attachdb). This causes various known test failures on windows and is the cause for downstream reports lik http://support.mapbox.com/discussions/tilemill/6460-adding-multiple-layers-from-a-single-sqlite-db-again.

Example errors are:

capture

capture2

@springmeyer
Owner

can be replicated on linux with make test & make test:

ubuntu@ip-10-197-25-139:~/mapnik2$ make test & make test
[1] 2300
*** Running visual tests...
*** Running visual tests...
....................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................
Visual rendering: 7 failed / 438 passed
1) failure to run test: tests/visual_tests/images/simple-400-100-2.0-cairo-reference.png (RuntimeError('Could not read image',))
2) failure to run test: tests/visual_tests/images/tiff-nodata-edge-gdal-969-793-1.0-cairo-reference.png (RuntimeError('failed to read invalid png',))
3) failure to run test: tests/visual_tests/images/tiff-nodata-edge-gdal-969-793-2.0-cairo-reference.png (RuntimeError(' File or steam is not a png',))
4) 40000 different pixels:
    /tmp/mapnik-visual-images/tiff-alpha-gdal-600-400-1.0-agg.png (actual)
    tests/visual_tests/images/tiff-alpha-gdal-600-400-1.0-agg-reference.png (expected)
5) 40000 different pixels:
    /tmp/mapnik-visual-images/tiff-alpha-gdal-600-400-1.0-cairo.png (actual)
    tests/visual_tests/images/tiff-alpha-gdal-600-400-1.0-cairo-reference.png (expected)
6) 40000 different pixels:
    /tmp/mapnik-visual-images/tiff-alpha-gdal-600-400-2.0-agg.png (actual)
    tests/visual_tests/images/tiff-alpha-gdal-600-400-2.0-agg-reference.png (expected)
7) 40000 different pixels:
    /tmp/mapnik-visual-images/tiff-alpha-gdal-600-400-2.0-cairo.png (actual)
    tests/visual_tests/images/tiff-alpha-gdal-600-400-2.0-cairo-reference.png (expected)
View failures by opening /tmp/mapnik-visual-images/visual-test-results/comparison.html
*** Running C++ tests...
....C++ CSV parse: ✓ 
......
Visual rendering: 7 failed / 438 passed
1) failure to run test: tests/visual_tests/images/tiff-nodata-edge-gdal-600-400-1.0-cairo-reference.png (RuntimeError(' File or steam is not a png',))
2) failure to run test: tests/visual_tests/images/tiff-nodata-edge-gdal-600-400-2.0-cairo-reference.png (RuntimeError('failed to read invalid png',))
3) failure to run test: tests/visual_tests/images/tiff-nodata-edge-gdal-969-793-1.0-cairo-reference.png (RuntimeError('failed to read invalid png',))
4) 40000 different pixels:
    /tmp/mapnik-visual-images/tiff-alpha-gdal-600-400-1.0-agg.png (actual)
    tests/visual_tests/images/tiff-alpha-gdal-600-400-1.0-agg-reference.png (expected)
5) 40000 different pixels:
    /tmp/mapnik-visual-images/tiff-alpha-gdal-600-400-1.0-cairo.png (actual)
    tests/visual_tests/images/tiff-alpha-gdal-600-400-1.0-cairo-reference.png (expected)
6) 40000 different pixels:
    /tmp/mapnik-visual-images/tiff-alpha-gdal-600-400-2.0-agg.png (actual)
    tests/visual_tests/images/tiff-alpha-gdal-600-400-2.0-agg-reference.png (expected)
7) 40000 different pixels:
    /tmp/mapnik-visual-images/tiff-alpha-gdal-600-400-2.0-cairo.png (actual)
    tests/visual_tests/images/tiff-alpha-gdal-600-400-2.0-cairo-reference.png (expected)
View failures by opening /tmp/mapnik-visual-images/visual-test-results/comparison.html
*** Running C++ tests...
...C++ CSV parse: ✓ 
.........
*** Running python tests...
........
*** Running python tests...
Notice: skipping postgis tests (connection)
..................Notice: skipping postgis tests (connection)
........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................Exception in thread Thread-6:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 551, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 504, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_rtree_test.py", line 22, in create_ds
    ds = mapnik.SQLite(file=DB,table=TABLE)
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

Exception in thread Thread-3:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 551, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 504, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_rtree_test.py", line 22, in create_ds
    ds = mapnik.SQLite(file=DB,table=TABLE)
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

Exception in thread Thread-4:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 551, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 504, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_rtree_test.py", line 22, in create_ds
    ds = mapnik.SQLite(file=DB,table=TABLE)
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

Exception in thread Thread-5:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 551, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 504, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_rtree_test.py", line 22, in create_ds
    ds = mapnik.SQLite(file=DB,table=TABLE)
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

Exception in thread Thread-7:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 551, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 504, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_rtree_test.py", line 22, in create_ds
    ds = mapnik.SQLite(file=DB,table=TABLE)
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

Exception in thread Thread-8:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 551, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 504, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_rtree_test.py", line 22, in create_ds
    ds = mapnik.SQLite(file=DB,table=TABLE)
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

.F.E..........EEEEEEEE........E...
======================================================================
ERROR: python_tests.sqlite_rtree_test.test_geometry_round_trip
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_rtree_test.py", line 97, in test_geometry_round_trip
    ('point_table','geometry','WKB', 1, 1, 4326)''')
OperationalError: disk I/O error

======================================================================
ERROR: python_tests.sqlite_test.test_attachdb_with_sql_join
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 97, in test_attachdb_with_sql_join
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

======================================================================
ERROR: python_tests.sqlite_test.test_attachdb_with_sql_join_count
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 148, in test_attachdb_with_sql_join_count
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

======================================================================
ERROR: sqlite3 world.sqlite
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 163, in test_attachdb_with_sql_join_count2
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

======================================================================
ERROR: select count(*) from (select * from world_merc where 1=1) as world_merc INNER JOIN business on world_merc.iso3 = business.ISO3;
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 176, in test_attachdb_with_sql_join_count3
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

======================================================================
ERROR: select count(*) from (select * from world_merc where 1=1) as world_merc INNER JOIN business on world_merc.iso3 = business.ISO3;
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 189, in test_attachdb_with_sql_join_count4
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database idx_world_merc_GEOMETRY is already in use' (../data/sqlite/world.sqlite)
Full sql was: 'attach database '../data/sqlite/world.sqlite.index' as "idx_world_merc_GEOMETRY"'

======================================================================
FAIL: python_tests.sqlite_rtree_test.test_rtree_creation
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_rtree_test.py", line 42, in test_rtree_creation
    eq_(os.path.exists(index),True)
  File "/usr/lib/python2.7/dist-packages/nose/tools.py", line 31, in eq_
    assert a == b, msg or "%r != %r" % (a, b)
AssertionError: False != True

----------------------------------------------------------------------
Ran 452 tests in 16.331s

FAILED (errors=6, failures=1)
EEmake: *** [test] Error 2
E.........
======================================================================
ERROR: python_tests.sqlite_test.test_attachdb_with_sql_join
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 97, in test_attachdb_with_sql_join
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'COMMIT'

======================================================================
ERROR: python_tests.sqlite_test.test_attachdb_with_sql_join_count
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 148, in test_attachdb_with_sql_join_count
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

======================================================================
ERROR: sqlite3 world.sqlite
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 163, in test_attachdb_with_sql_join_count2
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database is locked' (../data/sqlite/world.sqlite.index)
Full sql was: 'BEGIN IMMEDIATE TRANSACTION'

======================================================================
ERROR: select count(*) from (select * from world_merc where 1=1) as world_merc INNER JOIN business on world_merc.iso3 = business.ISO3;
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 176, in test_attachdb_with_sql_join_count3
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database idx_world_merc_GEOMETRY is already in use' (../data/sqlite/world.sqlite)
Full sql was: 'attach database '../data/sqlite/world.sqlite.index' as "idx_world_merc_GEOMETRY"'

======================================================================
ERROR: select count(*) from (select * from world_merc where 1=1) as world_merc INNER JOIN business on world_merc.iso3 = business.ISO3;
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 189, in test_attachdb_with_sql_join_count4
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database idx_world_merc_GEOMETRY is already in use' (../data/sqlite/world.sqlite)
Full sql was: 'attach database '../data/sqlite/world.sqlite.index' as "idx_world_merc_GEOMETRY"'

======================================================================
ERROR: select count(*) from (select * from world_merc where 1=1) as world_merc INNER JOIN business on world_merc.iso3 = business.ISO3;
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 202, in test_attachdb_with_sql_join_count5
    attachdb='busines@business.sqlite'
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database idx_world_merc_GEOMETRY is already in use' (../data/sqlite/world.sqlite)
Full sql was: 'attach database '../data/sqlite/world.sqlite.index' as "idx_world_merc_GEOMETRY"'

======================================================================
ERROR: python_tests.sqlite_test.test_subqueries
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
    self.test(*self.arg)
  File "/home/ubuntu/mapnik2/tests/python_tests/sqlite_test.py", line 212, in test_subqueries
    table='world_merc',
  File "/usr/lib/python2.7/dist-packages/mapnik/__init__.py", line 564, in SQLite
    return CreateDatasource(keywords)
RuntimeError: Sqlite Plugin: 'database idx_world_merc_GEOMETRY is already in use' (../data/sqlite/world.sqlite)
Full sql was: 'attach database '../data/sqlite/world.sqlite.index' as "idx_world_merc_GEOMETRY"'

----------------------------------------------------------------------
Ran 452 tests in 16.965s

FAILED (errors=7)
make: *** [test] Error 2
[1]+  Exit 2                  make test
@ccarse

This same issue happens on iOS but only on the device. On the simulator it works.

@matthewtf

related to https://groups.google.com/forum/#!topic/mapnik/G5Q5bWCYmmY

I believe I have the exact same issue.
I am using windows 7 64.

Let me know, if any other information is needed.

@springmeyer
Owner

as you are using windows, then this is know and understood, no more info is needed.

@talaj
Collaborator

Also occurs on Debian Wheezy. Cannot reproduce on Debian Sid so probably newer sqlite version solves the problem.

I could fix it by disabling SQLITE_OPEN_SHAREDCACHE in sqlite plugin.

@talaj
Collaborator

I reproduced it also on Debian Sid finally. I can put some effort into fix. We can take #2461 as a test case.

@talaj
Collaborator

Any progress on Windows after #2477?

@springmeyer springmeyer added this to the Mapnik 3.x milestone
@springmeyer
Owner

forgot to try last time on windows, assigned to 3.x milestone so I will remember next build.

@springmeyer
Owner

windows testing has revealed many fewer sqlite related errors now, but still two:

======================================================================
ERROR: python_tests.sqlite_rtree_test.test_rtree_creation
----------------------------------------------------------------------
Traceback (most recent call last):
  File "c:\code\mapnik-deps-64\tmp-bin\python2\lib\site-packages\nose\case.py", line 197, in runTest
    self.test(*self.arg)
  File "c:\code\mapnik-deps-64\packages\mapnik-master\tests\python_tests\sqlite_rtree_test.py", line 58, in test_rtree_creation
    os.unlink(index)
WindowsError: [Error 32] The process cannot access the file because it is being used by another process: '../data/sqlite/world.sqlite.index'

======================================================================
ERROR: python_tests.sqlite_rtree_test.test_geometry_round_trip
----------------------------------------------------------------------
Traceback (most recent call last):
  File "c:\code\mapnik-deps-64\tmp-bin\python2\lib\site-packages\nose\case.py", line 197, in runTest
    self.test(*self.arg)
  File "c:\code\mapnik-deps-64\packages\mapnik-master\tests\python_tests\sqlite_rtree_test.py", line 154, in test_geometry_round_trip
    os.unlink(test_db)
WindowsError: [Error 32] The process cannot access the file because it is being used by another process: '/tmp/mapnik-sqlite-point.db'
@springmeyer
Owner

tests are now passing on windows with 4c93d14 so it looks like this was (mostly?) a bug in the python tests.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.