How to not replicate index creation on ignored tables in MySQL/MariaDB

Question

I have a setup with two MariaDB masters, each being the slave of the other. Some tables are ignored, with the configuration option :

replicate-ignore-table = moodle.mdl_temp_%

My problem is when a statistic batch is run against one of the server : a CREATE TABLE followed by a CREATE INDEX is issued, and the last statement is replicated (looking at the binlogs the CREATE TABLE is effectively not replicated), which broke the replication on the other server.

Who to avoid the CREATE INDEX being replicated ?

[EDIT] Here is the error on the second server when the statistic batch is ran on the first server.

Error 'Table 'moodle.mdl_temp_stats_daily' doesn't exist' on query. Default database: 'moodle'. Query: 'CREATE INDEX mdl_tempstatdail_cou_ix ON mdl_temp_stats_daily (courseid)

And when I take a look at the binlogs, there's no "CREATE TABLE" but there is still the CREATE INDEX :

# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000101 | grep -i create
CREATE INDEX mdl_tempstatdail_cou_ix ON mdl_temp_stats_daily (courseid)
CREATE INDEX mdl_tempstatdail_tim_ix ON mdl_temp_stats_daily (timeend)
CREATE INDEX mdl_tempstatdail_rol_ix ON mdl_temp_stats_daily (roleid)
[...]

Answer 1

Plan A: Include the index(es) in the CREATE TABLE statement.

Plan B: Use this to add an index:

ALTER TABLE moodle.mdl_temp_stats_daily ADD INDEX(courseid);

(I have not tested either.)

Answer 2

Actually the problem was solved without modifying the way the CMS handles the database.

The real problem :

The issue was with the MariaDB server configuration, where the Replicate_Ignore_Table option did not worked :

(root@server2) [moodle]> SHOW SLAVE STATUS;
[...]
Replicate_Ignore_Table: moodle.mdl_temp_%,moodle.mdl_backup_ids_temp,moodle.mdl_backup_files_temp
[...]
(root@server1) [moodle]> CREATE TABLE mdl_temp_test1 (id INT);
Query OK, 0 rows affected (0.03 sec)
(root@server2) [moodle]> SHOW TABLES LIKE "mdl_temp%";
+------------------------------+
| Tables_in_moodle (mdl_temp%) |
+------------------------------+
| mdl_temp_test1               |
+------------------------------+
1 rows in set (0.00 sec)

The solution :

It took two modifications to the server configuration to make it work :

• I used replicate-wild-ignore-table instead of replicate-ignore-table
• I had to use one table name by line instead of a comma separated list

I don't fully understand why, but here is the working configuration from my /etc/mysql/my.cnf file :

replicate-wild-ignore-table = moodle.mdl_temp_%
replicate-wild-ignore-table = moodle.mdl_backup_ids_temp
replicate-wild-ignore-table = moodle.mdl_backup_files_temp

And now neither the table nor the index are replicated :

(root@server2) [moodle]> SHOW SLAVE STATUS;
[...]
Replicate_Wild_Ignore_Table: moodle.mdl_temp_%,moodle.mdl_backup_ids_temp,moodle.mdl_backup_files_temp
Last_SQL_Errno: 0
Last_SQL_Error:
[...]
(root@server1) [moodle]> CREATE TABLE mdl_temp_test1 (id INT);
Query OK, 0 rows affected (0.03 sec)
(root@server1) [moodle]> CREATE INDEX mdl_test_ix ON mdl_temp_test1 (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@server2) [moodle]> SHOW TABLES LIKE "mdl_temp%";
Empty set (0.00 sec)

(root@server2) [moodle]> SHOW SLAVE STATUS;
[...]
Last_SQL_Errno: 0
Last_SQL_Error: