MySQL Mixed replication failed to replicate create table with auto_increment in composite key

Question

When we try to create a table on a master-master MySQL MIXED replication, with a composite key containing an AUTO_INCREMENT column, it creates the table on the master but failed to do so on the slave.

Here is the error we got on slave side:

Error 'Incorrect table definition; there can be only one auto column and it must be defined as a key' on query. Default database: 'total_chamilo'. Query: 'CREATE TABLE `c_attendance_result` (
                 c_id INT NOT NULL,
            id int NOT NULL auto_increment,
            user_id int NOT NULL,
            attendance_id int NOT NULL,
            score int NOT NULL DEFAULT 0,
            PRIMARY KEY  (c_id, id)
        ) DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci'

Database is MyISAM.

MySQL version is 5.5.40-0+wheezy1-log

Surprisingly, we have table matching the same schema working on same servers but created on an other replication mode (statement) and/or on a previous MySQL version.

Does anyone know a way to fix this, if possible without changing original query since it is part of a large dump, full of this kind of statement...

thanks,

A.

Answer 1

That looks very much like this slave is not properly configured and trying to use InnoDB instead of MyISAM. An InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column. See the MySQL 5.5 reference manual. In your case the auto-increment column is the second column.