[Dbconfig-common-devel] Difficult/partial upgrade

[Sean Finney]

Hi Vincent,

On Wed, 2011-02-16 at 20:49 +0100, Vincent Bernat wrote:
> The problem  is that upstream upgrade  files may be  incorrect. It seems
> that  on some  installation, some  indexes already  exists.  The upgrade
> procedure  consists in  playing  the  SQL file  without  looking at  the
> errors. Something that  dbconfig-common is unable to do.  MySQL does not
> have instructions to ignore errors or instructions like "IF EXISTS".

unable "out of the box", but see below :)

regarding the inconsistant upgrades, it may not be related to this
specific problem but the very latest upload of dbconfig-common fixes a
nasty bug where the upgrade files were in some cases skipped for some
versions.  if you want to be super sure that it isn't a problem you
could put a >= versioned dep on the latest upload.

> I have therefore  move some upgrading into postinst  with something like
> this:
<snip>

okay this is really nasty for a couple reasons:

> 		    MYSQLARGS="-f -u $dbuser -p$dbpass $dbname"
> 		    [ -z "$dbserver" ] || MYSQLARGS="-h $dbserver $MYSQLARGS"
> 		    [ -z "$dbport" ] || MYSQLARGS="-P $dbport $MYSQLARGS"

 * passing the password on the cmdline means someone could
hypothetically get your db user password with while+ps+grep
 * any of those vars having shell metachars would cause Bad Things to
happen.

Have you considered using the "script" functionality instead of the
"file" functionality from dbconfig-common?  


http://people.debian.org/~seanius/policy/dbconfig-common-using.html/ch-develguide.html#s-updates

you could then write a small perl/shell/python/php whatever script to
handle any extra upgrading, on a per-version basis just like the sql
upgrade files.  you can even mix and match the two although i'm not sure
that the order is parallel (i.e. scripts are sorted relative to each
other but may happen before/after files).


> My main problem is how to provide an upgrade path for users that already
> have upgraded to some "buggy" version of my package.
> 
> For a  user which  upgraded from  0.3.1-6 to 0.5.1-1  and for  which the
> upgrade has failed (upgrade file is  0.5-1), I would like to ensure that
> I understand correctly how dbconfig-common works:
>  - a ROLLBACK  has been issued  and therefore, _nothing_ in  the upgrade
>    has been applied

dbconfig-common doesn't do anything like embedding into a transaction,
so the changes are applied with no rollback.  basically the SQL is up to
you, and dbconfig just passes it along to the right invokation of the
cmdline program to execute it.  so if it fails half way though, it's
possible that you might be in an inconsistant state.

the user will be prompted about the problem though, and given the option
of continuing, retrying, or aborting the package upgrade.

>  - if the user later upgrade to 0.5.1+dfsg-2, dbconfig-common will _not_
>    reapply upgrade file for 0.5-1.

this is correct: dbconfig doesn't keep any state, and leaves that to
dpkg.  so if the package is at 0.5-1 it assumes it has successfully
passed all previous upgrades.

> Therefore,   I   was  thinking   about   just   issuing   "mysql  -f   <
> $SQL_UPGRADE_FILE" if  the user is upgrading from  something more recent
> than 0.5-1 and  less recent than the current version.  Is there a better
> way?

i'd recommend one of two things:

 * using the scripting language of the app and writing a small script
reading the app's database config file, which would allow you a bit more
flexibility and expressiveness than a shell script, as well as not
worrying about having to re-generate and handle the configuration.  
 * maybe cheat and call the internal mysql functions of dbconfig
(dbc_mysql_exec_file, for example) and just slap on an "|| true" to the
end of it.  that'd *probably* work, though you'd want to test it :)
consider any function that doesn't start with an "_" to be usable for
that.  if roundcube supports multiple db types, there's something like
$dbc_sqlfile_cmd or similar too.

the more i think of if, the more option 2 might be the best route here
if you're not super fond of writing little php scripts :)

anyway, hope that's a bit helpful at least!
	sean
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://lists.alioth.debian.org/pipermail/dbconfig-common-devel/attachments/20110216/26e446d1/attachment.pgp>