At the moment I am working again on phpliteadmin, a php-based web GUI for database administration of sqlite databases. While debugging, I stumbled across a problem that only occurred with the PDO extension (not with SQLiteDatabase or SQLite3). I got the following error message while trying to drop a table:
HY000 / 6 / database table is locked
By the way, you can use PDO::errorInfo() to get these error messages. So as the error correctly explains, the table I tried to drop seemed to be in use. PDO documentation for PDO::query() also explains the problem (even though DROP TABLE statements are fired using PDO::exec()):
If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().
So I hunted for the open cursor on a resultset of the table in question and could not find any. Finally, I found the SQL statement which still had an open cursor:
SELECT * FROM sqlite_master
Thinking about this, it is obvious: When querying sqlite_master, you request information about database tables. If one of the table gets altered or dropped, this might change the data listed in the resultset on which I still have a cursor.
Maybe this is a special case as usually you do not query sqlite_master a lot. But in case you do, this might be useful information.
To solve the problem, as the manual says, simply release the cursor using PDOStatement::closeCursor() before dropping/altering tables.