Database auto-updates shouldn't use SVN revision numbers

[Nicolas]

I installed a BOINC server from pretty recent code from trunk, with my source directory not being a SVN working copy. Then I made a custom change to a PHP script and ran the upgrade tool to get it installed.

The output was:

Staying in DISABLED mode
Stopping all daemons
 (No processes stopped)
Upgrading files...
Upgrading files... done
update_translations finished
Checking for DB updates...
#! /usr/bin/env php
need update update_6_16_2009
need update update_9_3_2009
need update update_3_5_2010
need update update_3_17_2010
need update update_4_21_2010
need update update_6_10_2010
Do you want to apply these updates? (y/n)

Obviously it's not possible I'm missing all those updates, since I just installed the server.

The problem is the DB updating mechanism relies on the db_revision file in the project root directory, which is only valid if make_project was run from a real SVN working copy. My source directory is not a SVN working copy (for reasons that aren't relevant here). So db_version contains "exported", and upgrade_db.php thinks I'm missing all six versioned DB updates.

Here is another situation where this could cause serious problems (hypothetical, I didn't actually try it). Suppose last week I installed a server from the server_stable branch. I'm now using r21800 of branches/server_stable (which is actually identical to r19996 in that branch). ~/projects/dbfail/db_revision contains "21800".

Then suppose today I decide to switch my server to trunk. I run svn switch to get the trunk branch, which gets me the latest code from trunk, r21867. I compile the code, and run tools/upgrade. The database update script looks at my db_revision, and sees that it's "21800". According to ops/db_update.php, the most recent DB change was in r21728, so the script thinks there are no DB updates to perform (because 21800 > 21728), and it quits without doing anything.

However, my "real" database version should have been r19453 (the revision 'trunk' had when it was copied into 'server_trunk' eight months ago). I'm now missing four database updates: made in r20807, r21021 (though marked as '20932' in db_update.php for some unknown reason), r21230, and r21728. Without those, the code in trunk won't work properly. The only way to apply them is to modify db_update.php to call the right update functions (like was necessary before the automatic stuff) and run it by hand.

We have to decouple database updates from SVN revisions.

One way would be using a "DB version number" increased monotonically on every DB update. make_project would store that version number in db_revision instead of the output of svnversion. In the stable-to-trunk situation, the version number would be 2 in stable (regardless of SVN revision), and when switching to trunk, it'd jump to 6, so the script would execute DB updates 3,4,5,6.

Another way could be to have the database update script run 'describe' queries to see what the real current schema is, and figure out what updates are necessary. But this would be quite more complex.

[Nicolas]

Replying to Nicolas:

Another way could be to have the database update script run 'describe' queries to see what the real current schema is, and figure out what updates are necessary. But this would be quite more complex.

Speaking of which...

html/ops/db_update.php

@@ -720 +720 @@
 // If you need to do updates manually,
 // modify the following to call the function you want.
 // Make sure you do all needed functions, in order.
-// (Look at your DB structure using "explain" queries to see
+// (Look at your DB structure using "describe" queries to see
 // which ones you need).
 
 //update_3_17_2010();

'explain' has nothing to do with this.

[davea]

"explain x" shows the columns of table x

[Nicolas]

Wow, learn something new every day. I only knew about the "explain select ..." syntax (which shows SQL optimizer information for that query).

Looks like "EXPLAIN tbl_name" is synonymous (identical results) with "DESCRIBE tbl_name" and "SHOW COLUMNS FROM tbl_name".