MySQL 5.0.22 -> 5.0.24 upgrade replication issue.

[Andrew Tulloch]

I have a strange replication issue since upgrading from the port from 
5.0.22 to 5.0.24.

This is all running on FreeBSD 6.1-p3 on two amd64 machines (one single
processor, single core 1GB ram, one dual processor, dual core 6GB ram).
Both running either stock GENERIC or SMP kernel configurations.

Each of the MySQL instances is running inside a jail(8) on each of the
two machines, the jails are full basic installs with no attempts to cut
down the size.
Each of the jails has a libmap.conf with the following:
[/usr/local/libexec/mysqld]
libpthread.so.2         libthr.so.2
libpthread.so           libthr.so

I also tried all the following it with those lines commented out so it
was using the default libpthread and the situation was the same.

I installed MySQL 5.0.22 from the port
/usr/ports/databases/mysql50-server with 'make WITH_OPENSSL=yes'

Configured the servers into a ring replication with log-slave-updates
and setting replicate-same-server-id to 0 (I intend to hang a read only
replica off them for backup purposes). I then added the SSL certificates
and used "REQUIRE SSL" with the grant statements for replication users
to force ssl. This ran fine for a few weeks of testing various bits of
work on them.

I noticed the port was upgraded to 5.0.24 so stopped MySQL on both
machines, pkg_delete'd the mysql-server and mysql-client ports and
reinstalled from /usr/ports/databases/mysql50-server again and started
them back up, everything seemed to be running normally.

/usr/local/libexec/mysqld  Ver 5.0.24 for portbld-freebsd6.1 on amd64
(FreeBSD port: mysql-server-5.0.24)

I have a cron job that checks the slave status on the machines so I get
a warning if they aren't replicating. Shortly after starting up the new
I started getting messages that the slave thread was not replicating and
stopped in "Slave_IO_State: Waiting to reconnect after a failed master
event read"

I attempted to stop the slave on that machine.

slave stop;
*hang*

Another mysql command line show processlist indicates the thread is in
state "Killing slave" and doesn't change. The thread also seems to be
unkillable with kill.

Now oddly if I go to the master server for it and issue a 'flush logs;'
the 'slave stop;' completes and the slave thread is stopped.
The amount of time I wait before running the flush logs doesn't seem to
matter e.g. I left it for quite a while to see resulting in:
Query OK, 0 rows affected (10 min 50.16 sec)

I could issue a 'slave start;' normally and things were going again.

Next time my cron job caught a machine in the previous Slave_IO_State I
issued a 'flush logs' on it's master server and it reconnected and 
continued replicating.

The second feature of this issue is when I try to shutdown MySQL it
doesn't stop.
/usr/local/etc/rc.d/mysql-server stop results in a screen full of:
Waiting for PIDS: 2617.....

If I tail the error log I see:
060821 15:11:06 [Note] /usr/local/libexec/mysqld: Normal shutdown

060821 15:11:06 [Note] Slave I/O thread killed while reading event
060821 15:11:06 [Note] Slave I/O thread exiting, read up to log
'mysql-bin.000007', position 98

Again, if I go to the master and issue a 'flush logs'
The rc.d script exits, mysqld is shutdown and in the end of the error
log is:

060821 15:11:53 [Note] Error reading relay log event: slave SQL thread
was killed
060821 15:11:53  InnoDB: Starting shutdown...
060821 15:11:55  InnoDB: Shutdown completed; log sequence number 0 55148
060821 15:11:55 [Note] /usr/local/libexec/mysqld: Shutdown complete

060821 15:11:55  mysqld ended
Which if you put the two together seems to be a normal shutdown except
for the slave io thread being stuck until I flush logs?

It doesn't seem to matter which machine I try to shutdown on first, it
requires me to flush logs (or shutdown) on the master server for it to exit.

There don't seem to be any actual errors output anywhere that I can find.

Has anyone seen anything similar?

Thanks in advance,
Andrew