0

My replication suddenly went offline. SHOW SLAVE STATUS shows me that I/O Thread is running, while SQL thread is not.

I have error message:

Fatal error: Found table map event mapping table id 0 which was already mapped but with different settings.

I haven't been able to find a solution for this using Google.

Stop/Start slave doesn't help much.

Ladadadada
  • 26,337
  • 7
  • 59
  • 90
Vlad Chaotic
  • 1
  • 1
  • 1
  • 1
  • Are you using MySQL Server 5.1? Please mention the specific server version, major.minor.release for the slave and the master as well. – Michael - sqlbot Oct 04 '13 at 21:15

2 Answers2

2

If your master server is a version older than MySQL 5.1.53, you've hit a bug.

http://bugs.mysql.com/bug.php?id=56226

Changes in MySQL 5.1.53 (2010-11-03) —

Replication: An ALTER TABLE statement that altered a column of a MyISAM table without setting the column's size caused the binary log to become corrupted when the table map was unexpectedly set to 0 by updates (including deletes) on multiple tables, leading to replication failure when more than one table received the same table map ID. (Bug #56226, Bug #11763509)

— http://dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-53.html

The question is now a few days old, so you may have already rebuilt replication by now. If your server version is old, you should upgrade because the problem is likely to return.

If not, you might be able to SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; and skip over the invalid table map entry... although I'm not certain this can be used in this case. Even if it does appear to work, you are likely to then have a slave that diverges from the master, so you will need to verify that the data is identical on the two servers using 3rd party tools.

If your server is not old, this suggests a corrupt binary log, which should not happen, but if it does, may mean other problems on the master or slave are looming on the horizon.

Community
  • 1
Michael - sqlbot
  • 22,658
  • 2
  • 63
  • 86
0

If your replication fails, you better can start over again, instead of troubleshooting the problem.

1) On the Primary MySQL server FLUSH TABLES WITH READ LOCK;

2) Dump all databases mysqldump -uusername --all-databases -p | gzip -2 > dump.sql.gz

3) On Primary MySQL server UNLOCK TABLES; // Not necessarily to be CAPS.

4) scp your file to the Secondary MySQL server.

5) slave stop; on the secondary MySQL server.

6) On Secondary MySQL server import dump.sql.gz as follo:

gunzip < dump.sql.gz | mysql -uusername -password

7) If you've set your MASTER configuration correctly, you can start slave as

start slave;

8) Show the status of slave SHOW SLAVE STATUS\G;

Check for a specific error code and update your answer to provide more details. if possible

Valentin Bajrami
  • 4,045
  • 1
  • 18
  • 26