mysql 5.5.15 changes w/ more automated upgrading

[scott]

So for everyone thats been through a 5.0->5.1 or a 5.5 upgrade in the past there has been an external step performed to run the mysql_upgrade event. This has always been a support burden on this side, and a bigger problem in a larger combined update.

To that end I'm playing with automating the mysql_upgrade step in the RPM itself and now would be a good time for outside vetting before this becomes permanent. The following is the proposed design:

The %post install event (this is a script action that occurs after the upgrade happens) will:

1) stop mysql

2) manually restart mysql with "skip-grant tables". This disables password authentication.

3)run mysql_upgrade (no passwords required)

4) dump any tables that cannot be repaired in this step to /tmp/mysql_optimize.sql (this is required for innodb tables)

5) restart, again with skip-grant tables

6) import the mysql_optimize.sql

7) stop mysql

restart mysql the normal way

That is a lot of moving parts there, and a lot of places where this can go wrong. Also I can see that some people would never want to go through this on a live server (the dump and import could take a very long time) so that end, I'm proposing a standard environmental variable that will allow you to skip actions like this by setting:
export ATOMIC_SKIP=1
before you run the upgrade (rpm -U or yum upgrade)


Some caveats to this approach, the %post routine is triggered on Update events only for obvious reasons. However there is no way to say "only run this if its mysql 5.0 -> 5.5" Its always going to be there, and would run from 5.5.15 to 5.5.16 as long as ATOMIC_SKIP is not set. Also you can not, in any way shape or form, make an rpm interactive. It cant prompt you, and it shouldnt even echo anything back to the user like other packagers do (ie: plesk), hence the use of a silent environmental variable for controlling this.

A test implementation of this is available now for el5 environments in the atomic-testing channel in version 5.5.15-2.

[Kalimari]

...(MySQL) 5.0->5.1 or a 5.5 upgrade (required) external step performed to run the mysql_upgrade event. This has always been a support burden on this side... The following is the proposed design:

The %post install event (this is a script action that occurs after the upgrade happens) will:

1) stop mysql
2) manually restart mysql with "skip-grant tables". This disables password authentication.
3) run mysql_upgrade (no passwords required)
4) dump any tables that cannot be repaired in this step to /tmp/mysql_optimize.sql (this is required for innodb tables)
5) restart, again with skip-grant tables
6) import the mysql_optimize.sql
7) stop mysql
restart mysql the normal way

Understand why you are considering it, but think this type of post "upgrade" action should be seen as an essential step and undertaken with proper measures in place by the server admin themselves (^and you have just written the guide^ ). If it HAS to be added, then the automated/default should be OFF (defeats the point for admin's who want to click install and do nothing further; which will always be a support burden ).

Maybe a yum script which can be run stand-alone and and can added to the upgrade instructions would be another option:

Code: Select all

yum upgrade mysql
yum install atomic-mysql-upgrade
# atomic-mysql-upgrade

Then it needn't monkey with the server set-up - as you mentioned "That is a lot of moving parts there"...

[scott]

Where I see this cause problems now are when you run a yum upgrade that gets both mysql and something that depends on it (plesk or ASL Web for example). If you go from 5.0 to say 5.5, and you have a mysql using package upgrading at the same time, the downstream packages will fail (catastrophically in plesks case).

Heres what the actual routine looks like at the moment:

datadir="/var/lib/mysql"
socketfile="$datadir/mysql.sock"
mypidfile=/var/run/mysqld/mysqld.pid
errorlogfile=/var/log/mysqld.log

start_nonsecure() {
/etc/init.d/mysqld stop
/usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile" \
--log-error="$errlogfile" --pid-file="$mypidfile" \
--user=mysql --skip-grant-tables >/dev/null 2>&1 &

STARTTIMEOUT=120
while [ $STARTTIMEOUT -gt 0 ]; do
if /usr/bin/mysqladmin ping 2>/dev/null; then break; fi
sleep 1
STARTTIMEOUT=`expr $STARTTIMEOUT - 1`
done
}

run_upgrade() {
start_nonsecure
no_repair="The storage engine for the table doesn't support repair"
/usr/bin/mysql_upgrade | grep -B1 "$no_repair" | grep -v "$no_repair" | sed -e "s/^\(.*\)$/OPTIMIZE TABLE \\1;/" 2>&1 >/tmp/mysql_optimize.sql

# black magic 2: after mysql_upgrade, mysqld --skip-grant table
# suddenly wants to authenticate us again..

start_nonsecure
/usr/bin/mysql < /tmp/mysql_optimize.sql
/etc/init.d/mysqld stop
/etc/init.d/mysqld start

}

run_upgrade

[faris]

I'm confused by this code. I'm not seeing a dump/restore mechanism for the tables that can't be optimized. I suppose I'm missing something fundamental as usual, aren't I?

Faris.

[scott]

This dumps those tables:

Code: Select all

/usr/bin/mysql_upgrade | grep -B1 "$no_repair" | grep -v "$no_repair" | sed -e "s/^\(.*\)$/OPTIMIZE TABLE \\1;/" 2>&1 >/tmp/mysql_optimize.sql

and this restores them:

Code: Select all

/usr/bin/mysql < /tmp/mysql_optimize.sql

[hostingguy]

Im not a fan of this being automated - if your planning on doing a big update like 5.0 to 5.5 then you should be doing it manually and off hours. If your doing a 5.5.15 to 5.5.16 you should be able to do that anytime since the impact should be minimal, and possibly could be automated or ran by non sys admins (like lower level IT people, NOC staff, etc) who you do not want messing with this kind of thing and could be very unexpected for them.

[scott]

It gets back to combo updates, ie plesk & mysql. If you do both at the same time then you will absolutely break plesk and the whole thing will blow up on you.

As another note, this is actually already implemented in the parallels distributed mysql rpms. The major difference here is I added in the option to bypass it.

Also if there are legacy fields in my.cnf that mysql 5.5 does not support, you will also end up with this failed upgrade condition. So potentially the upgrade may have to retire the existing my.cnf with a new default one.

So here are the options:
1) There is the bypass variable: ATOMIC_SKIP <- if this is defined none of this will happen
2) Move the upgrade event into a plesk only trigger & still offer the above variable to bypass it. I would likely replace the original my.cnf here too
3) Remove all this completely, and be harassed by emails & forum posts by people who refuse to read the wiki, use google, or hit the search button**
4) Go all apple on people and force them to do it my way and not explain myself.***

** These people annoy me more than you do.
*** Already invalidated because of this thread.

[hostingguy]

why not during asl -f -s detect their mysql version and auto add the config value to the asl.config to skip when its not needed, and then have your post event script update the same var after the ugprade occurs so that it only happens when its needed.

[scott]

Oh yeah I could totally do that there. Its the non-ASL users that will still bug me.

[mikeshinn]

And its a LOT of bugging btw, many hours are spent answering questions for this exact situation over and over again. In the many years we have supported mysql people keep asking the same questions about the same manual steps they didn't follow, over and over and over...

So, what we are trying to do is reduce the impact on our guys, so they can focus on producing cool software and not answering the same questions, year after year after year with an incomplete upgrade of mysql. Keep in mind, some people run yum upgrade and thats about it. When they see 200 packages to upgrade, they trust the packager and most of the time nothing happens. But then when their db dies (or something else), they really dont realize what caused it or even know where to look for the "process" to upgrade. They think "yum upgrade" well upgrades.

So when we push a major update that requires manual effort, we get hit with a deluge of requests for desperate help from people that just "totalled" their boxes. We'd like to minimize that as much as possible, for our sanity and theirs.

[hostingguy]

are there still that many people migrating to/from a version that requires teh update script be ran?

[scott]

You have no idea I dont even respond to the mysql emails any more.

[Highland]

If you do both at the same time then you will absolutely break plesk and the whole thing will blow up on you.

As another note, this is actually already implemented in the parallels distributed mysql rpms.

I had to snip that and do the uncouth thing of quoting for truth. Plesk blowing up is a well documented event and the way this is written, it looks like a feature

** These people annoy me more than you do.

I'm sorry we annoy you

The only major config I've seen that will break in the 5.0 - 5.5 realm is the BERKLEY engine disable. It was common to turn it off in 5.0 (especially if you used mysqltuner) but it's gone in 5.1+ so mysql breaks. I documented that in the wiki for that reason. I've had a painless move from 5.1 to 5.5 on the servers I've done it on. You might be able to get away with just commenting that out of my.cnf. Either that or force users to install mysqltuner and force run it afterwards.

[hostingguy]

Picture says it all

[mikeshinn]

** These people annoy me more than you do.

I'm sorry we annoy you

Scotts a very grouchy turtle when he doesn't get his daily intake of wine. He's had to actually become a wine maker, and make it hundreds of gallons. But, doctors orders.

Kindly ignore his old man grumblings. Its just crazy talk, feed him wine and he cranks out rpms. Don't feed him wine, well he gets cranky.

In fact, if anyone wants to PAY him in wine, we'd be ever so appreciative. Some sort of "paywine" as opposed to paypal service. His wine budget is insane, which is another reason why he had to start making it and we had to create an entire company just for "medicine".

Won't you help this grouchy turtle? For less than a dollar a day you can provide this turtle with funding that goes to needy grapes that only want to be a fine red wine (or a chocolate infused desert wine, which apparently exists in industry terms to "pick up chicks", so idea what that means, its all greek to me I prefer Scotch) to keep this turtle happy and productive.