Feature #1821
Errors in a schema upgrade script that contains multiple SQL statements are not detected
0%
Description
I created a schema upgrade script with multiple statements in one file. When the upgrade started, one of the statements failed with an error visible in the PostgreSQL log, but quasselcore didn't show any errors and just finished the upgrade and increased the schemaversion number in the database anyway.
I believe this is due to the use of watchQuery https://github.com/quassel/quassel/blob/4251006ea8f4433e1c139d380cd6c748b0dfcc84/src/core/abstractsqlstorage.cpp#L272
which calls 'query.lastError().isValid()' just once, which may return the status of one of the successfully-executed statements inside the entire multi-statement query while it has not yet finished executing.
I believe it is not written to work with multi-statement upgrade scripts, each statement must be broken out into a separate file.
History
#1 Updated by khtmhai5 over 2 years ago
I have same issue..
#2 Updated by sarah54casiano 3 days ago
khtmhai5 wrote:
I have same issue.. https://www.summithealthpatient-portal.com
Here are a few potential solutions to address this problem:
Break Down Multi-Statement Scripts: As you mentioned, one way to handle this is to break the upgrade script into individual statement files. This way, each statement is executed separately, making it easier to identify which specific statement fails.
Modify watchQuery: Another approach could be to modify the watchQuery function to check for errors after each statement within the multi-statement query. This would ensure that any error within the script is caught and reported correctly.
Post-Execution Error Check: Implement a post-execution check that verifies the integrity of the entire schema after the script runs. If any part of the schema is not as expected, an error is raised, even if the individual statements appear successful.
Logging and Monitoring: Enhance logging and monitoring to capture detailed information about each statement's execution within the script. This can help in diagnosing and fixing issues that arise during the upgrade process.
If you choose to modify the watchQuery function, here’s an example of how you might adjust it to check for errors after each statement:
bool AbstractSqlStorage::watchQuery(QSqlQuery &query) {
do {
if (query.lastError().isValid()) {
qWarning() << "SQL query error:" << query.lastError();
return false;
}
} while (query.next());
return true;
}