MySQL binlogs - Don't forget to do your homework!

Now that I'm back doing just database stuff, I've come to realize I've gotten a little sloppy about doing my homework.  Homework's never been my favorite thing in the world, but it often reduces stress when your under the gun during an outage or upgrade...

We had a MySQL database server that's been slow on DML changes, and based on the slowest statements being 'COMMIT', we had a good mind it was an issue with the binary logs, or the disk subsystem that MySQL was writing the binary logs to.

Investigation with iostat (iostat -xtc 10), we were able to narrow it down to a disk subsystem being fully utilized and queuing.  

Easy fix right?  Just change the location of the binlogs via the startup option in my.cnf and restart.

If I had done my homework, I would have known that restarting MySQL with a new location for binlogs would reset the binary log to mysql-bin.000001, and thus break replication to the slave... Oops.

No need to panic - just check the master's log position and run the following on the slave:

 mysql> change master to master_log_file=mysql-bin.000001, master_log_pos=123

Perfect - the slave caught up and was good to go - except that I neglected to recall the fact we had enabled the application and started writing to the master before I checked the log position....  Oops. (2)

Luckily, replication was up to date when we restarted, so I was able to use the handy dandy mysqldump utility to load all the transactions from the new binary log up to the point in which I checked the master:

mysqlbinlog --stop-position=123 mysql-bin.000001 | mysql   

Moral of the story: Doing your homework is usually a good thing! 

Firewalls... what can you say?

Every time I've had to setup something 'special' with a database server and a firewall for the first time there always seems to be way to many issues.

With MySQL it's been mostly straight forward; opening an additional port or to for supporting the enterprise monitor or something, but nothing crazy.

Oracle, however, gets me every time. :(

We were setting up a new Oracle 11g instance, and decided we needed to enable shared server mode ( used to be MTS ); Nothing fancy, no connection pooling, no RAC or anything like that.

So, having done this in the past, we setup the dispatcher to use a dedicated port (rather than the dynamic ports, which firewalls love). Started testing things and realized we kept getting forced disconnects.

somewhat cryptic errors:

org.hibernate.util.JDBCExceptionReporter: SQL Error: 17002, SQLState: 08006
org.hibernate.util.JDBCExceptionReporter: IO Error: Size Data Unit (SDU) mismatch

Then we spend a couple days working with devs, trying different dispatcher/server process scenarios, open a support ticket, etc. Nada.

So we try the next obvious thing and take the firewall out of the equation, and sure enough, everything works fine. So we go back to playing with more configurations, turning off sql inspection on the firewall, etc, etc. Then we start doing network packet traces and analyzing those (another couple days go by).

Then our network engineer notices one of the packets has a tcp option he hasn't seen before 'URG'. Hmm... Sure enough, those packets are generated on the database side, but never make it through the firewall.

A bit more reading/googleing and it becomes clear:

Oracle has a sqlnet option called disable_oob that is off by default, and this allows Oracle to send packets marked 'URGENT' for things like client disconnects, and obviously for some things around dispatchers & shared processes.

Anyway, let those packets pass through the firewall, and it starts working like a charm. One week down, more packet sniffing than I care to do again the rest of the year, all so I can get it setup and forget about it for 5 years till I run into the issue again... :)

More info: Oracle Docs on SQLNet

Lost and Found ?

Sometimes you just have to laugh at the crazy things that can kill a good evening.

I had this brilliant idea to change our replication setup on one of our Master-Master replication server setups this week. I got sick of having to restart MySQL every time we wanted to add a new database and have it included in the list of replicated databases - we were using replicate-do-db in our configs.

So it seems very straight forward to change to ignore-db or ignore-table (because of cross database updates).

After a few weeks in QA and a few weeks in staging - no problems, no issues, no complaints... let's go for it!


Yea, as soon as we deploy and restart MySQL to pickup the configs, replication fails and stops!

And of course to make it lots of fun, replication on a couple other servers failing at the same time for unrelated reasons, and then a migration of another application that night having issues...)

So look around a while, check the MySQL Monitor ... and, wala - lost+found - table does not exist errors!

Yea, ext3 rears it's ugly head again. I'm sure it's probably my responsibility to make sure that lost+found directories are clean up, etc, etc, but it sure made for a headache this week.

The fix (knock on wood) was straight forward - we just added ignore-table=lost%.%

Seemed to do the trick. Maybe we should check out ReiserFS or xfs or zfs.

Either way, if there's a way to break replication, I'm sure I'll find it... ;)


On that note - if you love the file system (linux based) that you are using for your MySQL servers, I'd love to hear your comments (good, bad, or ugly)