Google Cloud MySQL Row-based replication datatypes error

0
votes

I'm using Google Cloud SQL as a MASTER (version 5.5.37) and replicating to EC2 slaves (also 5.5.37) using ROW based replication and on a SINGLE database (using replicate-do-db in my.cnf)

There are NO problems whatsoever if I mysqldump my master with all tables defined and THEN start replication.

The problem is if I perform

CREATE TABLE site_users(id INT NOT NULL AUTO_INCREMENT, nick VARCHAR(20), PRIMARY KEY (id));
INSERT INTO site_users(nick) VALUES("Bob");

Everything works fine on the master, but on the slave I get error 1677

Column 1 of table '(db_name).site_users' cannot be converted from type 'varchar(60)' to type 'varchar(20)'

When I query the slave 

DESCRIBE site_users;

the response shows a table exactly defined as it is on the master. When I experiment with different VARCHAR lengths, the size the master is sending is always 3x the size the table expects - eg if nick is varchar(100), the error says "cannot convert from type varchar(300)..."

Edit: If both the database containing this table AS WELL as the "mysql" database are dumped when instantiating the SLAVE, everything works correctly, except that a lot of other settings from the MASTER are copied as well... is this how things are supposed to be done? This is my first time setting up replication on a SINGLE database with replicate-do-db.

1
mysqlsqlsql-servergoogle-cloud-sql
What does the SHOW CREATE TABLE for that table on the master and slave? Are there any differences? - Razvan Musaloiu-E.
site_users | CREATE TABLE site_users ( nick varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Note that I removed the id column that I had in the original question because even a single column table is giving the error. If I copy both the 'mysql' database as well as the database that contains this table in the mysqpdump that I use to instantiate the slave, then after I create table, inserts on the master are properly replicated on the slave. It seems bizarre to me that I would need to do this because obviously a lot of other settings are copied. - user3571068
Note that the default character set for Cloud SQL is UTF8. Can you make sure the SHOW CREATE TABLE show the exact thing on both master and slave? - Razvan Musaloiu-E.
Yes you are correct - there is a difference in the charset on the MASTER and SLAVE. CloudSQL is using UTF8 while EC2 slave is Latin1 for that table. Let me change my.cnf on the slave and reinstantiate. I have a feeling this is going to work. WIll follow up shortly... - user3571068
Yes sir, that was the problem. Thank you very much for your help. the mysqldump seems to comment out the default character set. I'm guessing there's a way to convert those comments into commands via flags, but am not sure how to do that. I just changed the default settings for the SLAVE in my.cnf. Please submit your solution as answer. - user3571068

1 Answers

0
votes

The table on the slave needs to have the same character set as the table on the master. A way to insure that is to do a SHOW CREATE TABLE on the master and use that command to create the table on the slave.

Note that the default character set for Cloud SQL is currently utf8.