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

[Clint Byrum]

Excerpts from Patrick Matthäi's message of 2012-05-25 13:13:05 -0700:
> 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
> 

Hi Patrick.

This is a bug in otrs2. Any time one uses a storage engine specific
feature, like fulltext indexes, one should be explicit when creating
tables.

There's nothing reasonable to do in the mysql package. otrs2 just needs
to evolve and start being explicit about its storage engine for the tables
which need FULLTEXT search. 

It would also be a good idea to consider using something better, like
sphinx, as FULLTEXT really does not perform well, and can only operate
on the non-transactional non-crash-safe MyISAM.

> 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.

You're right, but there's not much we can or even should do in the MySQL
package. I'd rather ship as pure-to-upstream as possible a package,
and that means default storage engine is InnoDB.

> 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

Whatever code creates fulltext indexes needs to also ensure that the
table is MyISAM. 5.5 has been out for a long time, and the change is
well documented in the release notes. People just need to fix their
applications, even though I understand this is a lot of work.