November 24, 2014

MySQL Installation and upgrade scripts.

I generally find MySQL Sever sufficiently tested, meaning at least minor version upgrades rarely cause the problems. Of course it is not perfect and I remember number of big issues when some releases could not be used due to behavior changes in them and when something had to be rolled back in the next release.

But generally I think MySQL is doing much better than a lot of other projects in this respect.

Major version upgrades become more painful recently. In MySQL 3.22->3.23 upgrades or 3.23->4.0 upgrades you really could just swap binary and try out new version and if you need to get back you just swap binaries to the old version. I think it was great.
In MySQL 4.1 and later more caution is required because you could screw your data, for example by specifying utf8 as default character set together not to mention infamous timestamp format change which forced to change a lot of applications.
MySQL 5.0 added more issues to the pile with a lot of data type and sort order related changes, this is why long needed CHECK FOR UPGRADE was added in the newer versions and why number of people established slow by safe upgrade procedures – dump your data and reload.

Little tip to mention – upgrading to the new major version make sure you upgrade to latest current version at first. Ie upgrade to recent MySQL 5.0 before upgrading to MySQL 5.1 – this makes sure you will be at somewhat tested upgrade/downgrade path and your chances of successful downgrade if having problems with new major release will increase significantly. Making sure you can afford mysqldump and reload in worse case scenario is also good idea.

Anyway I was going to write not about MySQL Server upgrade process but rather about my experience with RPM scripts used during install and upgrade. These are obviously not part of automated mysql-test process and I’m wondering if they are tested at all.

For example if you use RHEL4 in default configuration (which is having SELinux enabled) upgrading RedHat RPM to MySQL “RedHat” RPM will not allow MySQL to start because SELinux will not let it. In some cases you will not even get server logs created because SELinux restricts console output as MySQL changes name to “mysql”.

Come On, as I remember MySQL And Redhat should be partners or is this partnership happening in area of marketing and taking money from the customers and have limited technical side ?

Today I spent another quite a while trying to Upgrade RedHat RPM binaries to MySQL Community RPM. Of course you may argue MySQL is not responsible for upgrade process from some “third party” binaries but I would argue they should as it is most common process – you get RedHat or Fedora which comes with MySQL binary and if you’re standard MySQL user you would just use that RPM and only upgrade it to something else if you need it for any reason. And this path does not work that great.

I logged some of my experiences as a bug but here are some more details about the problems

Upgrade Script fails to shut down MySQL Server. Obviously nothing can work without this one.

Upgrade Script Removes /var/lib/mysql symlink I’m not sure what is the problem about having this a a symlink – I like this approach a lot because it allows you to have database logically in same standard location while move it around as you seem suits. But even if there is the reason removing symlink on upgrade is just bad idea – it would be much better to tell me it is not supported any more so I go and change my environment instead of just finding it broken by upgrade. Not to mention it may be scary to find /var/lib/mysql gone.

If I’m not mistaken in one other instance I had new /var/lib/mysql created (probably when I retried second time) – this is even more scary to get running MySQL but find the database is gone now.

mysql user removed I’m not sure if this one is done by RedHat uninstall script or MySQL install script but simply I find no mysql user after upgrade.

MySQL Does not start after upgrade This is simply result of upgrade. Obviously with symlink removed

I can’t call myself a best expert in RPM out where but this was pure “rpm -Uvh MySQL*” in the directory with few downloaded MySQL binaries so it should be pretty straightforward.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Ivo Teel says:

    We have had simular issues with RPM, however, the missing MySQL user is related to the RPM itself. It removes the mysql user on deinstallation and for some reason doesn’t readd it. This only occurs for upgrades, if you remove it and then reinstall (the newer version) it works just fine.

    Nowadays we just remove the old version first and immediate install the newer version and move back the configuration.

  2. peter says:

    Thank Ivo,

    I would have guessed there is a workaround but again I would expect this is something MySQL and RedHat could have worked among themselves – if RPM is tuned not to remove mysql user or if it is readded with same id I do not care much. I would just prefer it to work, keeping my user hat on in this case :)

  3. Dmitri Mikhailov says:

    Heh, here are my 2 cents: I tried to upgrade from 4.1.19 to:

    5.0.24a – fast, “check for upgrade” did not trigger table repairs (bad, varchar string comparison with trailing spaces might return different from expected results), no problems with replication, no problems with merge tables, the release just does not have the latest features/fixes.

    5.0.27 – slow, with “check for upgrade” repairs, problems with auto_increment values in the replication binlogs (bug #24432)

    5.0.36sp1 – slow, with “check for upgrade” repairs, no problems with large merge tables (Unable to open underlying table which is differently defined or of non-MyISAM type), no problems with replication

    5.0.37 – slow, with “check for upgrade” repairs, problems with merge tables, performance problems while table is updated by PK = empty string (actially is was an application side problem, but at least this should not cause perf problems at the db side)

    5.0.41 – the release is just too fresh to blindly migrate to.

    Oh, yes, SELinux had to be disabled.

    At the present time I decided to upgrade to 5.0.36sp1 (MySQL Enperprise) it seems to be the most stable release, it’s still in the tests, who knows what bug we are going to hit next :)

  4. Toan says:

    Hi,
    I’m struggling search over the Internet how to upgrade minor version of both Percona & Mysql server.
    Currently I have to stop the server, do a mysqldump, uninstall it & install a newer version since my databse is quite small.
    Thanks

  5. Toan says:

    Sorry for missing the question in the above comment.
    Is there a better way to upgrade minor version of Percona, I’ve just moved from Mysql 5.6 to Percona 5.6.
    One more thing, is there a way to convert my current InnoDB table to XtraDB? Make a dump -> export to an SQL file, replace ENGINE from INNODB -> XTRADB, that’s it ???
    I’m very grateful if sb could save me.
    Best regards,

  6. Hi Toan, I’m Percona’s community manager. Thanks for the comment, however, this post is from 2007 and is not the right place for this sort of question. Our discussion forums, however, are the perfect place to ask it. I invite you to ask it there under the Percona Server 5.6. board here: http://www.percona.com/forums/questions-discussions/mysql-and-percona-server/percona-server-5-6

  7. Toan says:

    I’ve created a new thread in the forum already.
    Thanks for your information.

Speak Your Mind

*