Home > bugs, MySQL > Federated tables bug

Federated tables bug

Scenario
Recently came across this bug when trying out the federated storage engine for the first time in MySQL 5.1. Had a security table with user information on a remote server & database that needed to be joined to a local table housing site-specific permissions but only containing user IDs. I definitely wanted to use the “create server” method for the new table in case we later decided to link to a different table in the same remote database. A terrific little feature of the MySQL federated storage engine, to be sure.

Problem
The local server was the master in a replication pair. After executing the create server statement on the master, I proceeded to create the new federated table pointed to the new remote server. That’s when my mysql replication monitoring script alerted me that the replication thread had stopped on the slave server. The error log complained about:

100806 10:33:32 [Warning] Slave: Can't create/write to file 'server name: 'svr123' doesn't exist!' (Errcode: 1186271216) Error_code: 1

I could verify the create server statement was successful on the slave:

>select * from servers\G
*************************** 1. row ***************************
Server_name: svr123
Host: svr123
Db: defaultdb
Username: blah
Password: blah
Port: 0
Socket:
Wrapper: mysql
Owner:

Workaround
The slave obviously doesn’t register the new remote server for some weird reason, even though it makes the entry in the servers table on the slave server. To fix the slave (& replication), I just deleted the row from the mysql.server table on the slave and ran the create server SQL (also on the slave). A simple but very annoying workaround, as I don’t like to run any sql on a slave server if I can help it. After starting the slave, it ran & created the new federated table with no other problems.

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: