We have a slave server that has stopped replication due to the following error:

Slave SQL: Query caused different errors on master and slave.

What could be the cause of this error? And what would be a way to fix it?

Version of both master and slave is MySQL 5.5.30

130726 23:55:45 [Note] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: Shutdown complete

130726 23:58:39 [Note] Plugin 'FEDERATED' is disabled.
130726 23:58:39 [Warning] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ignoring option '--innodb-file-per-table' due to invalid value 'ON'
130726 23:58:39 [Note] Plugin 'InnoDB' is disabled.
130726 23:58:39 [Note] Server hostname (bind-address): ''; port: 3306
130726 23:58:39 [Note]   - '' resolves to '';
130726 23:58:39 [Note] Server socket created on IP: ''.
130726 23:58:39 [Note] Slave SQL thread initialized, starting replication 
    in log 'mysql-bin.000234' at position 1065421256, 
    relay log '.\slave-relay-bin.000917' position: 1065421402
130726 23:58:39 [Note] Slave I/O thread: connected to master 'replication@',
    replication started in log 'mysql-bin.000235' at position 166680598
130726 23:58:39 [Note] Event Scheduler: Loaded 0 events
130726 23:58:39 [Note] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: 
    ready for connections.
Version: '5.5.30-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
130726 23:59:04 [ERROR] Slave SQL: Query caused different errors on master and slave.
    Error on master: message (format)='Incorrect key file for table '%-.200s'; 
    try to repair it' error code=1034 ; 
    Error on slave: actual message='no error', error code=0. 
    Default database: 'shared'. 
    Query: 'CREATE TEMPORARY TABLE tmp_grades (
                        vehsysid INT(11),
                        grade INT(1),
                        dt TIMESTAMP,
                    ) SELECT vehsysid, Grade, MAX(dt) AS dt
                        FROM shared.tbl_valuations 
                        GROUP BY vehsysid, grade', Error_code: 0
130726 23:59:04 [ERROR] Error running query, slave SQL thread aborted. Fix the problem,
   and restart the slave SQL thread with "SLAVE START". 
   We stopped at log 'mysql-bin.000234' position 1065421256

What else I can't figure out is how a temporary table would lead to such an error (in master):
'Incorrect key file for table '%-.200s'; try to repair it' error code=1034

The last lines from master error log:

130725 23:15:57 [Warning] Warning: Enabling keys got errno 120 on shared.tmp_grades, retrying
130726 23:15:58 [Warning] Warning: Enabling keys got errno 137 on shared.tmp_grades, retrying

Additional info:

  • both Master and Slave run on Windows (I don't know if that's relevant.)
  • the disks at both have plenty of space.
  • replication format is MIXED
  • innodb is skipped in all instances, master and slaves. MyISAM is the default.
  • @JamesLupolt No, the disk has more than 1TB free space. – ypercubeᵀᴹ Jul 27 '13 at 0:14
  • Ah, OK. So there's no way the MySQL tmpdir is pointing to a smaller filesystem such as a /tmp mountpoint? In that case, I don't know why the 'incorrect key file' error occurred. – James Lupolt Jul 27 '13 at 0:17
  • Deleted previous comment due to some misinformation. – James Lupolt Jul 27 '13 at 0:51

I suspect that some of this is stating the obvious. I apologize for that but wanted to include it in the interest of thoroughness.

The slave stopping in this condition is the expected behavior.

Every query that gets written to the binlog has metadata that includes the error code that the query returned on the server that generated the binlog event. The error code is normally 0 for "no error." You can see this error code and the other metadata if you run the log through mysqlbinlog.

#130727 10:52:46 server id 10 end_log_pos 166 Query thread_id=673471 exec_time=1 error_code=0

If an error occurs after the query reaches a certain point in execution, the query still gets written to the binlog along with the error code.

The slave expects to encounter the same error that the master encountered (usually but not always 0) and stops when the values don't match to help avoid data inconsistencies that could otherwise result if replication was allowed to continue after such a condition was encountered.

The binlog data structures don't appear to have a place for the error message or the values that were sprintf()'ed into the message -- just the code -- so the slave has to translate that code into something human-readable. Lacking the values that the master would have plugged in to the message, it just displays the raw message format, including the placeholders.

# from source file include/mysqld_ername.h
{ "ER_NOT_KEYFILE", 1034, "Incorrect key file for table \'%-.200s\'; try to repair it" },

So what you're seeing as the last replication error on the slave is normal behavior when an error occurs on the master but the same query doesn't generate an error on the slave.

You can skip over the error with SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; as you have probably already done by now, but recovery beyond that depends on what you did with the results from that temporary table. If the temporary table was created and populated by a single statement-based event, and wasn't used to do any kind of further table updates, you might be fine, because replication typically ignores errors that occur when trying to DROP TEMPORARY TABLE and the temporary table doesn't exist on the slave, since the slave thread has no reliable way of knowing whether the CREATE TEMPORARY TABLE statement actually appeared previously in the binlog or whether the slave might have stopped and started between the CREATE and DROP events on the temporary table (which would have destroyed any temporary tables that had been created by the slave SQL thread before the stop/start). On the other hand, if the temporary table was used to update other tables, you still might be fine because those updates might have been written as row-based events since you're in MIXED binlog format... but I would verify consistency among any tables you updated from the temporary table results, if there are any.

As for the errors that occurred on the master:

130725 23:15:57 [Warning] Warning: Enabling keys got errno 120 on shared.tmp_grades, retrying
130726 23:15:58 [Warning] Warning: Enabling keys got errno 137 on shared.tmp_grades, retrying

There is something I missed about these at first -- I thought they were 1 second apart, but these errors actually appear to have been on two different days, the 25th and 26th, so 24 hours and 1 second apart... 2 different warnings on the same temporary table name but in fact two different temporary tables generated a day apart.

MySQL error code 120: Didn't find key on read or update (HA_ERR_KEY_NOT_FOUND)
MySQL error code 137: No more records (read after end of file) (HA_ERR_END_OF_FILE)

At first I thought it was strange that you had no "Incorrect key file" messages in the master's log, but those wouldn't be in the log -- those would be errors returned to the client that issued the query.

I remember having seen errors like that in my servers' error logs, but it turns out, from what I can tell, those "Incorrect key file" errors only go to the server error log if they are encountered by the slave_sql thread or by a query executed by the event scheduler, since those are the only times there's not a client connection to receive the error... so it could be that whatever is running the query that creates the temporary table has generated a log of errors that might be of interest.

We can't technically assume that it was the 'tmp_grades' table that encountered the "Incorrect key file" error if the SELECT portion of the query created its own implicit temporary table... it could have been that table instead, causing an error that affected the query.

Looking into the internals, a bit, it looks like when you create a MyISAM temporary table with indexes in a CREATE TEMPORARY TABLE ... SELECT statement, the table is created with the indexes disabled and then the server builds and enables the indexes after the data is written rather than having to update indexes as it inserts data... and it appears that the warnings you're seeing may have been at the point when it builds the indexes ("Enabling keys...")... and assuming that's correct, it suggests only a handful of possibilities, listed in no particular order, but all of which revolve around the idea that your temporary table is getting corrupted:

  • You have another job running at the same time that also creates a massive temporary table either explicitly or implicity (Using temporary) so you have a transient low disk space condition that disappears immediately after the other job finishes
  • The SELECT part of the query that's populating the temporary table is building another implicit temporary table that's causing a transient low disk space condition and competing with the explicit table for space
  • You have a problem with your system memory, so the temporary table is being corrupted in the OS cache
  • You have a problem with your disk and the temporary table is too large to remain in the OS cache, the temporary table is being corrupted when it's flushed to disk.

Of course, the biggest problem here is that none of these explanations seems particularly likely. After verifying that the data between the two machines is currently consistent, the unfortunate next step may be to wait for it to happen again though I would be inclined to verify the system memory and the integrity of the temporary disk if it were me.

| improve this answer | |
  • Thank you very much for the answer. I'll do some checking along your possible explanations and will update. – ypercubeᵀᴹ Jul 28 '13 at 8:00
  • Might also be worth considering the possibility that something is interfering with your tmpdir (most likely C:\windows\temp on Windows). Perhaps either a scheduled task cleaning the tmpdir or an antivirus scan. – James Lupolt Jul 28 '13 at 8:40
  • @JamesLupolt I guess you are referring to the Master's antivirus and tempdir? (or to Slave's? or both?) – ypercubeᵀᴹ Jul 28 '13 at 16:00
  • @ypercube Yes, the master's, since that's where the problem started. – James Lupolt Jul 28 '13 at 16:01
  • @JamesLupolt thank you. I'm not sure about your assertion, though, since DDL is not transactional in InnoDB so "simply be rolled back" seems unlikely. The binlog_format does play a role, however. If it is row then temporary tables and their DDL are never replicated regardless of storage engine; if statement or mixed then it seems like the storage engine would not be able to change the replication behavior on error. – Michael - sqlbot Jul 28 '13 at 16:07

as Michael said "You have a problem with your system memory, so the temporary table is being corrupted in the OS cache" this the most commonly status , try to increase tmp_table_size and max_heap_table_size, as the statement has "create temporary table" and "group by" clauses this use temporary table which need these buffers my suggestion is : increase these buffers on master and slave you can find more details in this link http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.