MySQL and replication, it has always been a touchy subject. Overall, things work properly. Non the less, on a certain project, we experienced regular replication failures:
Error 'Can't create table 'db.temp_table' (errno: -1)' on query. Default database: 'db'. Query: 'CREATE TEMPORARY TABLE temp_table ...
Basically the errors clearly points out that the temporary MySQL table could not be created. Similar errors kept on coming, all saying the same thing: there where temporary tables that could not be created.
The MySQL documentation did not make me any wiser, nor did Google this time. I have found a lot of information on replication problems with temporary tables, but nothing regarding the creation of them.
Temporary tables are not really necessary to be created in a replication. This because they normally only exist in single session and get destroyed once the session terminates. So in this case, ignoring the temporary tables solved our problem. Of course, if data from the temporary tables is used in combination with normal table data, this would be a bad idea, since those queries would start to fail as well.
To ignore temporary tables, just open up your my.cnf file and add the temporary tables you would like to dismiss from the replication (replicate-wild-ignore-table allows you to use wildcard on table names, while with replicate-ignore-table you need to specify the whole name ).
replicate-wild-ignore-table=database_name.temp_%
You may add multiple lines to ignore multiple tables.
replicate-wild-ignore-table=database_name.temp_% replicate-wild-ignore-table=database_name.temp2_% replicate-wild-ignore-table=database2_name.temp_%
If anyone would have a clue why the CREATE TEMPORARY TABLE queries fail on replication, feel free to leave a comment. This would be much appreciated 😉
