hMailServer 6.2.26: SQL Server Compact upgrades to schema 6030 reported as failed
-
If you run hMailServer on SQL Server Compact, the 6.2.25 installer told you the database could not be upgraded. It had been upgraded. Every Compact Edition upgrade through schema 6030 was reported as failed after it had succeeded (#114), and two seconds later the service ended and service recovery started it again.
What the upgrade involved
If the 6.2.25 installer failed on your database, that database is at schema 6030 with its foreign keys in place, and this installer finds nothing left to upgrade. If you restored a backup from before the attempt, the whole chain runs and the verification passes. From 6.2.24 or earlier, the 6.2.25 notes and the 6.2.24 notes before them describe what changes on the way, and everything there still applies.
What was wrong
After each upgrade script the database updater proves the schema changed by running a probe statement, and reads a failed probe as a missing object. The four probes for schema 6030, the foreign keys, used
case when exists (subquery)in the SET expression. That is valid on SQL Server, MySQL and PostgreSQL. On SQL Server Compact it is an access violation inside the OLE DB provider, on a correct database with every constraint present. The server reported HM10045 Unknown error, the updater declared that Upgrade6029to6030MSSQLCE.sql had not created fk_hm_accounts_domain and blamed an [IGNORE-ERRORS] marker the statement does not carry.The fix
The probes now read
update hm_dbversion set value = value / (value - value) where not exists (select 1 from information_schema.table_constraints where constraint_name = '...' and constraint_type = 'FOREIGN KEY'). With the constraint present no row matches and nothing is evaluated. With it absent the one row matches and the division by zero fails the statement on every backend, leaving hm_dbversion untouched. The updater's message no longer asserts a cause it cannot see. It gives the backend's own words and says how to read them.This was reproduced from a Compact Edition database created at schema 6011 and upgraded with the shipped scripts: it reaches 6030 with all seventeen foreign keys, and the probe then faults the provider. build/check-db-scripts.ps1 now runs every probe through the provider the server uses, with a negative control that must fail, and a regression fixture runs them through the same COM path the updater takes. Both fail on the 6.2.25 statement.
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better 💗
Register Login