BenV's notes

Slackware recently upgraded the MySQL version from 5.0 to 5.1, from the Changelog:

Sun Oct 4 00:17:50 CDT 2009
ap/mysql-5.1.39-x86_64-1.txz: Upgraded.
This bumps the version of the shared libraries to .so.16.0.0.

Yeah, that’s all great, but for me this broke on several of my machines. Not because of the expected recompiles (after all, a new library, so stuff like vpopmail might need a recompile, I’m fine with that), but because MySQL failed to handle my configuration files on those machines. You see, I like my data more than I like MySQL, and prefer their InnoDB tables above MyISAM for that matter (yeah yeah, it’s still MySQL, whatever). Means I have some configuration lines about InnoDB in my /etc/my.cnf, like this:

[mysqld]
skip-networking
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_buffer_pool_size = 64M
innodb_additional_mem_pool_size = 8M

So after the upgrade of the package, mysql came down as fast as it came up:

091005 23:40:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
091005 23:40:14 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
091005 23:40:14 [ERROR] /usr/libexec/mysqld: unknown variable 'innodb_data_file_path=ibdata1:10M:autoextend'
091005 23:40:14 [ERROR] Aborting

Aha… so, I need to run some kind of upgrade tool called mysql_upgrade, good that they told me about that in advance. (Thanks Slackware guys 🙁 )
So we try running the upgrade tool:

root@janeman:~:0>mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) when trying to connect
FATAL ERROR: Upgrade failed

Yeah… mysql didn’t quite come up because of the configuration issues, right. You’d hope an upgrade tool would fix that too, but oh well, guess we’ll have to do that ourselves.
In fact, let’s read what they have to say about it themselves. Upgrading from MySQL 5.0 to 5.1.
Let’s see…. backup, check… read this document… (gee, wonder what we’re doing now)… read about changes… yeah yeah, cute, plugin api, whatever… run the upgrade tool… yeah that failed. If this bla, if that bla… and then the magic:

If problems occur, such as that the new mysqld server does not start or that you cannot connect without a password, verify that you do not have an old my.cnf file from your previous installation.

Guess I do huh. So yeah, obviously MySQL starts fine whenever you empty the config file. But what about my old config settings? Are they still needed?
Apparently you can now choose betwen running InnoDB native or as a plugin. Guess I’ll stick to native.
Now for the fun part. Checking the options to put in the my.cnf file. Huh, the option that it barfed about still exists… how can this be? This document tells us how to set up InnoDB… nothing new in there. Conclusion: Slackware fucked up. (that is, you can probably run it as a plugin, but I don’t want it as a plugin! At least tell me about this in the Changelog guys!)
In case -you- want to run it as a plugin, the page I linked describes it. Basically you have to put this in your /etc/my.cnf:

[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so


Checking the mysql.Slackbuild script from slackware64-current confirms my suspicions:

checking whether to use InnoDB Storage Engine... plugin
checking whether to use InnoDB Storage Engine... plugin

Fine. So I change the mysql.Slackbuild:

--- mysql.SlackBuild 2009-09-28 21:42:00.000000000 +0200
+++ mysql.SlackBuild-BenV 2009-10-07 17:37:54.747337800 +0200
@@ -76,6 +76,7 @@
--with-ssl=/usr \
--enable-largefile \
--with-readline \
+ --with-plugin-innobase="yes" \
--build=$ARCH-slackware-linux
#
# --without-readline

Ironically, running the patched mysql.Slackbuild killed my running mysqld because of the famous OOM killer. Guess 1GB of memory isn’t enough for both compiling AND running MySQL these days :p
Anyway, it gave my a package, and after upgrading to it I finally had InnoDB back the way I wanted. Yeah, my old configuration file works fine again too.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39 Source distribution

mysql> show storage engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)


****UPDATE****
Seems like the guys at slackware fixed it, from their Changelog:

Sat Oct 17 23:56:15 UTC 2009
ap/mysql-5.1.39-x86_64-2.txz: Rebuilt.
Added --with-innodb option. This was omitted from the last package version
since ./configure --help no longer lists --with-innodb or --without-innodb,
but evidently the option is still supported (and needed).
Thanks to Amritpal Bath.