Get the database busy when create a table
(1) By Jerry Liu (jerry_szcn) on 2020-11-30 12:47:22 [link] [source]
Hi, I get strange problem,who can help me.
I porting the SQLite to embedded OS(FreeRTOS),it's OK after adapte the IO method/mutex/VFS api. After create a default database,I try to create a table with sqlite3Prepare_v2 api, I always get the a error code(database is busy),failed to create a table. What's the root cause?
version: 3.323, the source files all in sqlite3.c/h statement: CREATE TABLE student(id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(32) NOT NULL,score INT NOT NULL);
(2) By Larry Brasfield (LarryBrasfield) on 2020-11-30 14:17:02 in reply to 1 [link] [source]
A couple possibilities:
Your VFS is not as OK as you think, and its mutex provision is flawed such that the mutex release does not work. Did you test it carefully?
It should be relatively easy to put breakpoints on your VFS mutex related entry points and see what is happening. Until you have done that, it is quite premature to be asking for guesses as to what has gone wrong. After all, SQLite works on quite a few platforms already, making yours most likely to be what is different. And given that, guesses obtained from this forum are bound to be random, no better than your own.
BTW, your 'varchar(32)' as a data type probably shows a missing understanding of SQLite's type scheme. If you are doing that to save storage, it is unnecessary (and useless.)
(4) By Jerry Liu (jerry_szcn) on 2020-12-01 01:16:41 in reply to 2 [link] [source]
Thank you very much!
I already confirm the mutex work well, and also try to disable the mutex sytem to close the thread safe, but get same result.
Before create the table, I successfuly connect to the database by sqlite3_open API. I implement the "begin transaction" statement by sqlite3_exec api, it's work well, but a get a busy infomation when create the table follow aother statement.I try to modify some configuration and compile option,it seems that nothing changed.
I didn't familiart with sqlite, can you give more suggestion to me?
(5) By Larry Brasfield (LarryBrasfield) on 2020-12-01 02:36:50 in reply to 4 [link] [source]
I would look carefully to see if Warren's idea (in post #3) applies. It is a common error for early users of SQLite to neglect to call finalize() after getting the useful work done with a prepared statement. If this is your problem, it would be a problem on any platform, independent of your VFS implementation.
(7) By Simon Slavin (slavin) on 2020-12-01 12:16:09 in reply to 4 [source]
BEGIN EXCLUSIVE TRANSACTION
. Does anything change ?
(9) By Jerry Liu (jerry_szcn) on 2020-12-03 00:33:38 in reply to 7 [link] [source]
Thank you!I alredy corrected this problem that come from the IO methods.
(3) By Warren Young (wyoung) on 2020-11-30 15:33:15 in reply to 1 [link] [source]
database is busy
That usually means you didn’t finalize the prior statement on the connection before preparing the next.
(6) By Gunter Hick (gunter_hick) on 2020-12-01 08:37:13 in reply to 1 [link] [source]
I strongly suggest you use an already established port of SQLite as a control environment for the application that calls SQLite. If it works on the control, but not on your VFS, then your VFS is not correct. If it does not work on the control, then your application is not correct. This will reduce the amount of guesswork involved when running an untested application against SQLite with an untested VFS. And you can even develop both in paralell.
(8) By Jerry Liu (jerry_szcn) on 2020-12-03 00:32:32 in reply to 6 [link] [source]
Thank you very much!
I already found the root cause that it come from the semaphore of IO methods, the database openning should obtain the semaphore,but always return the busy status.I modified the IO methods API and try it again, it work correctly.