1

Here's what I would like to do: 1. hang a slave off my production master. 2. stop replication 3. adjust some data types and create some indexes on the slave (no data mods) 4. restart replication 5. once the slave has caught up, lock the db & swap slave & master

-the goal, obv. is to have no significant downtime while making some changes that will take time (200G table getting modified) -it should be possible, because the DDL changes I'm making have to do with shrinking data types & adding constraints - nothing that changes the actual values of data. -since GTID replication is row-based, I don't know if replication will choke trying to insert, for example, 11 digit ids into a 5 digit id column, even if the values are in range.

1

You have described a common way to make DDL changes. Yes, it should work. But... please describe the datatype change further.

If you are ALTERing a COLUMN that is current INT SIGNED to be a SMALLINT SIGNED, and if none of the values are bigger than 32767, there should be no problem. If you are changing from SIGNED to UNSIGNED, and none of the numbers are negative, then no problem. Etc. In general, if the old value will fit in the new datatype, then it is OK. If it won't fit, then something will be lost. 1 million into a SMALLINT UNSIGNED will probably become 65535.

As for the application, how does mysql know it is getting an 11-digit id? It sees a number of some number of digits and shoves it into the target size.

Do SHOW WARNINGS; after the DDL.

3
  • Thank you, @rick; That's what I was wondering - specifically, if I have a table thus: color( id int(11) primary key not null autoincrement, color_name varchar(32)) in db1. Then I make db2 from a db1 backup, and make it a slave of db1 now I add (1, 'red') and (2, 'green') to db1, which get replicated into db2 then I stop replication, and in db2 alter table color so that id becomes tinyint, and then insert (3, 'blue') to db1.color, and turn replication back on now db2 would replicate the new row from db1 despite the fact that the id attribute takes less space.
    – Eljuan
    Apr 28 '15 at 5:19
  • If you are using binlog_format = STATEMENT, I am sure it will work. What gets replicated is the statement INSERT ... (1, 'red') or INSERT ... ('red'). For ROW format, I am less certain. Give this a try: create another table on both machines; INT on Master, TINYINT on slave. Then insert something. If it hangs, use sql_slave_skip_counter to un-stick replication.
    – Rick James
    Apr 28 '15 at 16:07
  • Since I'm working on a fabric setup, I need row-based replication, and that's why I was worried about how binary the internal representation of a row-based transaction is, but apparently it works fine. I have done just that - reducing the data type on the target server while leaving it unmolested on the source, and it works on inserts without problem. Once I have a little free time I'll see how it breaks when the target server range is exceeded by the source update...
    – Eljuan
    Apr 29 '15 at 18:04

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.