[debian-mysql] Fwd: Re: Bug#674122: otrs2: fails to upgrade from squeeze: Can't create table 'otrs2.#sql-1712_71

[Patrick Matthäi]

Hi Sean, Debian-MySQL team and the rest of the world

first thanks to the MySQL team and sean for doing a good job with mysql
and dbconfig-common

But now we have got a critical issue with upgrades from Squeeze to
Wheezy, where I need some help..

The situation is:
a) otrs2, like many other packages in Squeeze, are using MyISAM as
storage engine as default
b) With mysql-5.5 the default storage engine has been changed from
MyISAM to InnoDB, which is IMHO (with my administrator hat on) a realy
good decision
c) .. but applications like otrs2 using MyISAM may fail with it :(

If we would install otrs2 with the current sid development all tables
are created with the InnoDB database engine, it would be successful but
the ticket fulltextsearch would fail

There are no errors and side effects if a user upgrades his otrs2
installation from Squeeze to current wheezy/testing (with mysql-5.1),
since CREATE TABLE statements will create MyISAM tables (upstream didn't
"hardcoded" the engine)

Upgrading from a Squeeze installation to current sid with mysql-5.5 will
create a few new tables, with the InnoDB format and after that setting
some attributes will fail, as reported in #674122 ..


So I want to ask if there is some nice and quick workaround for such
issues, I don't think that otrs2 will be the only DBMS application with
5.1 -> 5.5 upgrade issues.
Patching every upgrade and install script of dbconfig-common to hardcode
MyISAM (in this case) is IMHO a big bunch of work, maybe you have got
better ideas or there is already an implementation (which I do not find)
to solve such problems

Thanks!


-------- Original-Nachricht --------
Betreff: Re: Bug#674122: otrs2: fails to upgrade from squeeze: Can't
create table 'otrs2.#sql-1712_71
Datum: Wed, 23 May 2012 20:18:45 +0200
Von: Patrick Matthäi <pmatthaei at debian.org>
Antwort an: pmatthaei at debian.org
Organisation: www.Debian.org
An: Thomas Mueller <thomas at chaschperli.ch>,
674122-submitter at bugs.debian.org

Am 23.05.2012 10:39, schrieb Thomas Mueller:

>>    Installing new version of config file /etc/init.d/otrs2 ...
>>    dbconfig-common: writing config to /etc/dbconfig-common/otrs2.conf
>>    Replacing config file /etc/otrs/database.pm with new version
>>    creating database backup in
>> /var/cache/dbconfig-common/backups/otrs2_2.4.9+dfsg1-3+squeeze1.mysql.
>>    applying upgrade sql for 2.4.9+dfsg1-3+squeeze1 ->  3.0.
>>    error encountered processing
>> /usr/share/dbconfig-common/data/otrs2/upgrade/mysql/3.0:
>>    mysql said: ERROR 1005 (HY000) at line 322: Can't create table
>> 'otrs2.#sql-1712_71' (errno: 150)
>>    dbconfig-common: otrs2 configure: aborted.
>>    dbconfig-common: flushing administrative password
>>    dpkg: error processing otrs2 (--configure):
>>     subprocess installed post-installation script returned error exit
>> status 1
>>
> Can't reproduce this error.


I tried to reproduce this error, please notice that I also upgraded to
mysql-server-5.5.
After I had installed otrs2 on squeeze and upgraded to sid I get:

otrs2 (3.1.5+dfsg1-1) wird eingerichtet ...
Neue Version der Konfigurationsdatei /etc/init.d/otrs2 wird installiert ...
dbconfig-common: writing config to /etc/dbconfig-common/otrs2.conf
Replacing config file /etc/otrs/database.pm with new version
creating database backup in
/var/cache/dbconfig-common/backups/otrs2_2.4.9+dfsg1-3+squeeze1.mysql.
applying upgrade sql for 2.4.9+dfsg1-3+squeeze1 -> 3.0.
error encountered processing
/usr/share/dbconfig-common/data/otrs2/upgrade/mysql/3.0:
mysql said: ERROR 1005 (HY000) at line 322: Can't create table
'otrs2.#sql-2ddc_72' (errno: 150)
dbconfig-common: otrs2 configure: aborted.



Then I simply tried out following:


mysql> drop database otrs2; create database otrs2;

root at squeeze:~/usr/share/dbconfig-common/data/otrs2/install# mysql
-ptest otrs2 < mysql
root at squeeze:~/usr/share/dbconfig-common/data/otrs2/install# (this was
the squeeze database, I habe unpackaged it to ~/)

root at squeeze:~/usr/share/dbconfig-common/data/otrs2/install# mysql
-ptest otrs2 < /usr/share/dbconfig-common/data/otrs2/upgrade/mysql/3.0
ERROR 1025 (HY000) at line 46: Error on rename of './otrs2/article_flag'
to './otrs2/#sql2-2ddc-78' (errno: 152)
root at squeeze:~/usr/share/dbconfig-common/data/otrs2/install#

And this was the 3.0 upgrade database from the 3.1.5 package, so I can
confirm it..


-- 
/*
Mit freundlichem Gruß / With kind regards,
 Patrick Matthäi
 GNU/Linux Debian Developer

E-Mail: pmatthaei at debian.org
        patrick at linux-dev.org
*/



-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 836 bytes
Desc: OpenPGP digital signature
URL: <http://lists.alioth.debian.org/pipermail/pkg-mysql-maint/attachments/20120525/5fa9d3cd/attachment.pgp>