Conversion to utf8mb4 and replication

  • Filter
  • Time
  • Show
Clear All
new posts

  • Conversion to utf8mb4 and replication

    Hi all,

    I've got a very strange problem here.

    So, there is a database which contains a single table called "questions":
    | Field                 | Type          | Null | Key | Default | Extra |
    | id                    | bigint(11)    | NO   | PRI | NULL    |       |
    | question_text         | varchar(300)  | NO   |     | NULL    |       |
    I am trying to convert it from standard utf8 to utf8mb4. In order to reduce downtime, I am trying to do it in the following way:
    1. Create replica using innobackupex
    2. Stop replication
    3. Launch pt-online-schema-change on a slave:
      pt-online-schema-change --nodrop-old-table --nodrop-new-table --execute --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" "D=questions,t=questions"
    4. Start replication and wait till it catches the master
    5. Set master as read-only
    6. Stop replication and promote slave to be a new master
    Everything works perfectly till the 4th step. If I run "start slave" after pt-online-schema-change, replication fails with the following error:
    140717 15:24:41 [ERROR] Slave SQL: Column 1 of table 'questions.questions' cannot be converted from type 'varchar(900)' to type 'varchar(300)', Error_code: 1677
    140717 15:24:41 [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.004315' position 28070522
    Percona MySQL Server version: 5.5.16

    Any ideas why is it failing after conversion to utf8mb4?