Description:
This can currently be demonstrated in mysql-6.1-glob tree,
but WL#1213 will also be in 5.6 (Celosia) soon:
In mysql-6.1-glob I get the following:
Replication between New master (5.6) -> Old Slave (5.1) fails as :
100127 16:35:49 [Note] Slave I/O thread: connected to master 'rep_user@127.0.0.1:3307',replication started in log 'mysql-bin.000001' at position 365
100127 16:35:49 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 365, relay log './nidhi-laptop-relay-bin.000001' position: 4
100127 16:37:41 [ERROR] Slave SQL: Error 'Character set '#45' is not a compiled character set and is not specified in the '/home/nidhi/mysql-builds/mysql-5.1-slave/share/mysql/charsets/Index.xml' file' on query. Default database: 'nidhi1'. Query: 'create database nidhi1', Error_code: 22
100127 16:37:41 [Warning] Slave: Character set '#45' is not a compiled character set and is not specified in the '/home/nidhi/mysql-builds/mysql-5.1-slave/share/mysql/charsets/Index.xml' file Error_code: 22
100127 16:37: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.000001' position 536
How to repeat:
Start master with 6.1 server
bin/mysql_install_db --no-defaults --datadir=./data/
nidhi@nidhi-laptop:~/mysql-builds/mysql-6.1-glob$ bin/mysqld_safe --defaults-file=my.cnf --basedir=. --datadir=./data/ --socket=/tmp/master.sock --port=3307 &
[1] 5355
nidhi@nidhi-laptop:~/mysql-builds/mysql-6.1-glob$ bin/mysql --user=root --socket=/tmp/master.sock
mysql> CREATE USER 'rep_user'@'127.0.0.1' IDENTIFIED BY 'somepass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'127.0.0.1' IDENTIFIED BY 'somepass';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 365 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
Start slave with 5.1 server
---------------------------------
bin/mysql_install_db --no-defaults --datadir=./data/
nidhi@nidhi-laptop:~/mysql-builds/mysql-5.1-slave$ bin/mysqld_safe --defaults-file=my.cnf --basedir=. --datadir=./data/ --socket=/tmp/slave.sock --port=3308 &
[1] 8306
nidhi@nidhi-laptop:~/mysql-builds/mysql-5.1-slave$ bin/mysql --user=root --socket=/tmp/slave.sock
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='rep_user', MASTER_PORT=3307, MASTER_PASSWORD='somepass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=365;
Query OK, 0 rows affected (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS;
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | 127.0.0.1 | rep_user | 3307 | 60 | mysql-bin.000001 | 365 | nidhi-laptop-relay-bin.000002 | 252 | mysql-bin.000001 | Yes | Yes | | | | | | | 0 | | 0 | 365 | 414 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
1 row in set (0.00 sec)
On master run:
---------------
mysql> create database nidhi;
Query OK, 1 row affected (0.01 sec)
mysql> use nidhi;
Database changed
mysql> create table t1(i int);
Query OK, 0 rows affected (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> create database nidhi1;
Query OK, 1 row affected (0.00 sec)
On slave things look fine till you set 'utf8'. After that any statement fails as below.
---------------------------------------------------
100127 16:35:49 [Note] Slave I/O thread: connected to master 'rep_user@127.0.0.1:3307',replication started in log 'mysql-bin.000001' at position 365
100127 16:35:49 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 365, relay log './nidhi-laptop-relay-bin.000001' position: 4
100127 16:37:41 [ERROR] Slave SQL: Error 'Character set '#45' is not a compiled character set and is not specified in the '/home/nidhi/mysql-builds/mysql-5.1-slave/share/mysql/charsets/Index.xml' file' on query. Default database: 'nidhi1'. Query: 'create database nidhi1', Error_code: 22
100127 16:37:41 [Warning] Slave: Character set '#45' is not a compiled character set and is not specified in the '/home/nidhi/mysql-builds/mysql-5.1-slave/share/mysql/charsets/Index.xml' file Error_code: 22
100127 16:37: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.000001' position 536
