Getting SQLite Error HY000-17: database schema has changed on .../dev/build/

[erdnussflips]

PHP: 5.6.23 Silverstripe version: 3.4.0 pdo-sqlite version: 3.8.10.2 silverstripe-sqlite3 version: 1.4.x-dev Connector type: SQLite3PDODatabase

When I do a dev/build I get the following error:

[User Error] Uncaught SS_DatabaseException: Couldn't run query: SELECT name FROM sqlite_master WHERE type = ? HY000-17: database schema has changed

With silverstripe version 4.0.0-alpha1 and silverstripe-sqlite3 version 2.0.0 this error doesn't occur (PHP 7).

I debugged it and it seems to be occur, when SQLite3SchemaManager->tableList() is called a second one. Maybe a timing problem? (https://www.sqlite.org/rescode.html#schema)

PS: It's a clear silverstripe installation

Trace:

DBConnector->databaseError(HY000-17: database schema has changed,256,SELECT name FROM sqlite_master WHERE type = ?,Array) PDOConnector.php:344 PDOConnector->prepareResults(PDOStatement,256,SELECT name FROM sqlite_master WHERE type = ?) PDOConnector.php:310 PDOConnector->preparedQuery(SELECT name FROM sqlite_master WHERE type = ?,Array,256) Database.php:134 SS_Database->{closure}(SELECT name FROM sqlite_master WHERE type = ?) Database.php:178 SS_Database->benchmarkQuery(SELECT name FROM sqlite_master WHERE type = ?,Closure) Database.php:136 SS_Database->preparedQuery(SELECT name FROM sqlite_master WHERE type = ?,Array,256) DBSchemaManager.php:99 DBSchemaManager->preparedQuery(SELECT name FROM sqlite_master WHERE type = ?,Array) SQLite3SchemaManager.php:384 SQLite3SchemaManager->tableList() DBSchemaManager.php:114 DBSchemaManager->schemaUpdate(Closure) SQLite3SchemaManager.php:94 SQLite3SchemaManager->schemaUpdate(Closure) DatabaseAdmin.php:246 DatabaseAdmin->doBuild(,1) DatabaseAdmin.php:103 DatabaseAdmin->build() DatabaseAdmin.php:80 DatabaseAdmin->index(SS_HTTPRequest) RequestHandler.php:288 RequestHandler->handleAction(SS_HTTPRequest,index) Controller.php:202 Controller->handleAction(SS_HTTPRequest,index) RequestHandler.php:200 RequestHandler->handleRequest(SS_HTTPRequest,DataModel) Controller.php:158 Controller->handleRequest(SS_HTTPRequest,DataModel) DevBuildController.php:25 DevBuildController->build(SS_HTTPRequest) RequestHandler.php:288 RequestHandler->handleAction(SS_HTTPRequest,build) Controller.php:202 Controller->handleAction(SS_HTTPRequest,build) RequestHandler.php:200 RequestHandler->handleRequest(SS_HTTPRequest,DataModel) Controller.php:158 Controller->handleRequest(SS_HTTPRequest,DataModel) RequestHandler.php:222 RequestHandler->handleRequest(SS_HTTPRequest,DataModel) Controller.php:158 Controller->handleRequest(SS_HTTPRequest,DataModel) Director.php:385 Director::handleRequest(SS_HTTPRequest,Session,DataModel) Director.php:149 Director::direct(/dev/build,DataModel) main.php:188

[tractorcow]

Ah, it's to do with cached prepared statements. You can't prepare a statement, update schema, and then re-use it. :D

We fixed this for PDO with https://github.com/silverstripe/silverstripe-framework/pull/4288.

Please copy the same solution for this module and it'll just work. :P

[tractorcow]

Hm, once I switched my brain on, I noticed you were using PDO already (sqlite3 pdo).

Perhaps some digging is still needed? Maybe the existing solution just needs tweaking a bit.

[tractorcow]

That PR above took me a year to fix... >_>

[erdnussflips]

The sqlite docs says:

If a prepared statement is generated from sqlite3_prepare_v2() then the statement is automatically re-prepared if the schema changes, up to SQLITE_MAX_SCHEMA_RETRY times (default: 50). The sqlite3_step() interface will only return SQLITE_SCHEMA back to the application if the failure persists after these many retries.

[tractorcow]

Ok, so potentially this is a different problem causing this failure?

http://stackoverflow.com/questions/27659719/pdo-3-multiple-runnings-error-17-database-schema-has-changed

Could you be running multiple dev/builds in separate threads?

Maybe we should run dev/build in a transaction with an exclusive lock?

[dhensby]

2 years and no update, not sure this is still a problem.

feel free to reopen if it is