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

Fri May 25 21:59:05 UTC 2012

block 671115 by 674122
thanks

Patrick,
	Some ideas. Not sure how the application works so not sure which is 
most liekly to be deoable.

* There is a  --default-storage-engine=type start up option. If you let 
the standard mysql scripts manage the mysqld process that one might not 
work.

* It should be possible to convert that option into  an option entry in 
the my.cnf file. You should also be able to own  your own my.cnf 
fragment in say /etc/mysql/conf.d/otrs2 .

* If you only need to enforce the default engine in upgrade scripts (or 
the creation of tables is confined in some other way) then this SQL 
would do it:

set default_storage_engine = 'MyISAM';

However that will only persist as long as that connection.

* Again you can use this SQL:

set global default_storage_engine = 'MyISAM';

However that only takes effect on the next (and all subsequent 
connections) and requires super user  privileges and will not survive a 
bounce.

On 25/05/12 21:13, Patrick Matthäi wrote:
> 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..
>
>
>
>
>
> _______________________________________________
> pkg-mysql-maint mailing list
> pkg-mysql-maint at lists.alioth.debian.org
> http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-mysql-maint