How to fix MySql Replication Error 1236

We have some websites that use two database servers in master-master replication. Recently one of the servers died and had to be resurrected. (They are cloud based and SoftLayer doesn't seem to have their cloud-based offering thing nailed down, yet. Every week one of them will go down and be unresponsive until tech support does some magic.)

After one of the servers was brought back up the other server would not connect. It "Slave_IO_Running: No" and "Seconds_Behind_Master: null" which means it was not playing nicely.

First, I went to the MySql log files, which for this server is found at /var/log/mysql.log and looked at the last few messages here by running "tail /var/log/mysql.log" from the command prompt. This revealed the error number (server_errno=1236). It also had the following info:

Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log

Just before this entry in the mysql.log it indicates the file and position that it is trying to read. So, with this data in hand, I headed over to the master server and took a look at the bin logs. They are located in /var/lib/mysql/. Here I took a look at the file in question using the mysqlbinlog utility. I used the following command to check out the bin log. Obviously, you'll have to replace the position and file name with the position and file indicated in your mysql.log.

mysqlbinlog --offset=128694 mysql-bin.000013

And, this is what I saw here, among other things:

Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

Well, that explains things! When the server crashed the bin log was not closed properly. This is easy to fix. Going back to the slave server, I stopped the slave, reset the bin log position and started the server.

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_POS = 0;
CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000014';
START SLAVE;

I simply pointed the slave to the start of the next bin log. It started right up with no problem.

Update 5/21/13 - An anonymous commentor made a good point about potentially losing data using the above technique. If you are in a situation like I am (master-master replication with 100's of gigs of non-critical data) this is really the only way of getting back up without significant down time. But if you are in a master-slave configuration with a manageable data set or its critical you slave doesn't miss any data, you should probably dump the master database and re-create the slave database to make sure you didn't miss anything.

SimpleXMLElement and EntityRef XML parser

I am using the PHP class SimpleXMLElement to take care of parsing some XML data that I am sourcing from 3rd parties. It had been working well for a while, but I just discovered an error that was popping up frequently. This error was "XML parser error : EntityRef: expecting ';'".

This error comes about as a result of XML input data being improperly encoded. Two data sources I was using had encoded things like "&", "<" and ">" by leaving off the semi-colon. In other words, the ampersand had been encoded as "&" instead of "&". SimpleXMLElement doesn't like this and throws a warning fest.

To fix the problem, I added a line before calling SimpleXMLElement:

$xmldata = preg_replace('/&(amp|lt|gt)([^;])/', '&$1;$2', $xmldata);
$obj_xml = new SimpleXMLElement($xmldata);

The preg_replace fixes the encoding problem and adds the ampersand for you. Just a note, this will only fix the encoding for the three characters I specified above ("&", "<" and ">"). If there are others that are causing problems, you'll have to add them to the first argument of preg_replace().

Here is another blog/article that helped me discover the underlying issue.

Microsoft Wireless Keyboard 700 v2.0 - Keystroke Problem

I thought I'd post this real quick in case someone else is experiencing the frustrating problem I was having. I have a Microsoft Wireless keyboard and mouse 700 and it has been driving me up the wall lately. The keyboard would miss keystrokes, would occasionally respond with a repeated keystroke and would otherwise be annoying. I tried replacing the batteries, re-syncing and restarting and nothing helped.

Then, I tried moving the receiver device that plugs into my USB port further away and voilà: Here I am typing up a storm without getting stressed. It is nice to have a keyboard that works again. Apparently the USB receiver cannot be within a certain distance from my keyboard... maybe I should have read the instruction manual.

Update (6/23/10): The manual states that the mouse & keyboard should be between 8 inches and 4 feet from the receiver.

Update 2 (8/10/10): I've officially given up on this mouse & keyboard. The mouse drains a fresh pair of AA batteries in about two weeks and the keyboard constantly has connectivity problems. I went back to old-fasioned wired replacements, for now.