[sqlite-dev] "No Such Table" after Create
Alexander Brueck
Hi everybody,
I’m about to port sqlite to a proprietary platform with currently having some issues. It might have to do with my own implementation of the VFS / IO / memory mgmt layers.
It seems that after creating database, inserting data and flushing pages to disk successfully, the table cannot be found next time when the database is opened.
Below I attached a small log with the pager trace turned on found at the top of pager.c.
It also shows my own debug messages of the sqlite3_io_methods.xRead() and xWrite() functions, where you can see that about 12K Bytes are written to disk.
The file starts as valid sqlite file with “SQLite format 3”.
I compile with following preprocessor definitions:
SQLITE_CORE
SQLITE_ZERO_MALLOC
SQLITE_OS_OTHER=1
SQLITE_TEMP_STORE=3
SQLITE_THREADSAFE=0
SQLITE_4_BYTE_ALIGNED_MALLOC
In my implementation of xDeviceCharacteristics, this returns SQLITE_IOCAP_UNDELETABLE_WHEN_OPEN | SQLITE_IOCAP_SEQUENTIAL | SQLITE_IOCAP_SAFE_APPEND.
I tested this with versions 3.24 and 3.19.3. Both came up in the end with “no such table”.
Any help / hint is totally appreciated to track this down.
Thank you very much and best regards!
Alexander Brück
sqlite3_open FLASH\A
OPEN 37106544 FLASH\A
read 0 (0x0000) / 100 bytes at offset 0x0000
sqlite3_exec: CREATE TABLE myvic ( first varchar(64), second varchar(64) );
read error: seeking at position 0x0018, but got 0x0000
TRANSACTION 37106544
write 512 (0x0200) / 512 bytes at offset 0x0000
APPEND 37106544 page 1 needSync=1
APPEND 37106544 page 2 needSync=1
DATABASE SYNC: File=FLASH\A zMaster=(null) nSize=2
write 4096 (0x1000) / 4096 bytes at offset 0x0000
STORE 37106544 page 1 hash(00000000)
write 4096 (0x1000) / 4096 bytes at offset 0x1000
STORE 37106544 page 2 hash(00000000)
COMMIT 37106544
sqlite3_exec: INSERT INTO myvic VALUES ( 'asd', 'qwe' );
read 16 (0x0010) / 16 bytes at offset 0x0018
TRANSACTION 37106544
write 512 (0x0200) / 512 bytes at offset 0x0000
write 4 (0x0004) / 4 bytes at offset 0x0200
write 4096 (0x1000) / 4096 bytes at offset 0x0204
write 4 (0x0004) / 4 bytes at offset 0x1204
JOURNAL 37106544 page 2 needSync=1 hash(00000000)
DATABASE SYNC: File=FLASH\A zMaster=(null) nSize=2
write 4 (0x0004) / 4 bytes at offset 0x1208
write 4096 (0x1000) / 4096 bytes at offset 0x120c
write 4 (0x0004) / 4 bytes at offset 0x220c
JOURNAL 37106544 page 1 needSync=1 hash(00000000)
write 4096 (0x1000) / 4096 bytes at offset 0x0000
STORE 37106544 page 1 hash(00000000)
write 4096 (0x1000) / 4096 bytes at offset 0x1000
STORE 37106544 page 2 hash(00000000)
COMMIT 37106544
sqlite3_exec: CREATE INDEX yIndex ON myvic ( first );
read 16 (0x0010) / 16 bytes at offset 0x0018
read 4096 (0x1000) / 4096 bytes at offset 0x0000
FETCH 37106544 page 1 hash(00000000)
TRANSACTION 37106544
write 512 (0x0200) / 512 bytes at offset 0x0000
write 4 (0x0004) / 4 bytes at offset 0x0200
write 4096 (0x1000) / 4096 bytes at offset 0x0204
write 4 (0x0004) / 4 bytes at offset 0x1204
JOURNAL 37106544 page 1 needSync=1 hash(00000000)
APPEND 37106544 page 3 needSync=1
read 4096 (0x1000) / 4096 bytes at offset 0x1000
FETCH 37106544 page 2 hash(00000000)
DATABASE SYNC: File=FLASH\A zMaster=(null) nSize=3
write 4096 (0x1000) / 4096 bytes at offset 0x0000
STORE 37106544 page 1 hash(00000000)
write 4096 (0x1000) / 4096 bytes at offset 0x2000
STORE 37106544 page 3 hash(00000000)
COMMIT 37106544
sqlite3_close
CLOSE 37106544
sqlite3_open FLASH\A
OPEN 37106544 FLASH\A
read 100 (0x0064) / 100 bytes at offset 0x0000
sqlite3_exec: INSERT INTO myvic VALUES ( 'yes', 'no' ); SELECT * FROM myvic;
read 4096 (0x1000) / 4096 bytes at offset 0x0000
FETCH 37106544 page 1 hash(00000000)
read 16 (0x0010) / 16 bytes at offset 0x0018
sqlite err: no such table: myvic
sqlite3_close
CLOSE 37106544
Utimaco IS GmbH
Germanusstr. 4, D.52080 Aachen, Germany, Tel: +49-241-1696-0, www.utimaco.com
Seat: Aachen – Registergericht Aachen HRB 18922
VAT ID No.: DE 815 496 496
Managementboard: Stefan Auerbach (Chairman) CEO, Malte Pollmann CSO, Dr. Frank J. Nellissen CFO
This communication is confidential. If you are not the intended recipient, any use, interference with, disclosure or copying of this material is unauthorised and prohibited. Please inform us immediately and destroy the email.