master [localhost] {msandbox} (test) > create table t (id int unsigned not null auto_increment primary key, a mediumtext, b mediumblob)engine=innodb;
Query OK, 0 rows affected (0.02 sec)
master [localhost] {msandbox} (test) > insert into t (a, b) values (repeat('a', 2097152*2), cast(repeat('a', 2097152*2) as binary));
Query OK, 1 row affected (0.26 sec)
master [localhost] {msandbox} (test) > select length(a), length(b) from t;
+-----------+-----------+
| length(a) | length(b) |
+-----------+-----------+
| 4194304 | 4194304 |
+-----------+-----------+
1 row in set (0.00 sec)
-- When checksumming with PTDEBUG=1, the following chunk checksumming is produced:
How to repeat:
master [localhost] {msandbox} (test) > create table t (id int unsigned not null auto_increment primary key, a mediumtext, b mediumblob) engine= innodb;
Query OK, 0 rows affected (0.02 sec)
master [localhost] {msandbox} (test) > insert into t (a, b) values (repeat('a', 2097152*2), cast(repeat('a', 2097152*2) as binary));
Query OK, 1 row affected (0.26 sec)
master [localhost] {msandbox} (test) > select length(a), length(b) from t; -----+- ------- ---+ -----+- ------- ---+ -----+- ------- ---+
+------
| length(a) | length(b) |
+------
| 4194304 | 4194304 |
+------
1 row in set (0.00 sec)
-- When checksumming with PTDEBUG=1, the following chunk checksumming is produced:
# RowChecksum:5792 17587 Row checksum: CRC32(CONCAT_ WS('#', `id`, `a`, `b`, CONCAT(ISNULL(`a`), ISNULL(`b`)))) LOWER(CONV( BIT_XOR( CAST(CRC32( CONCAT_ WS('#', `id`, `a`, `b`, CONCAT(ISNULL(`a`), ISNULL(`b`)))) AS UNSIGNED)), 10, 16)), 0) AS crc
# RowChecksum:5831 17587 Chunk checksum: COUNT(*) AS cnt, COALESCE(
-- On the slave, the CRC is NULL
slave1 [localhost] {msandbox} (test) > select * from percona.checksum \G ******* ******* ****** 1. row ******* ******* ******* ******
*******
db: test
tbl: t
chunk: 1
chunk_time: NULL
chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
this_crc: 0
this_cnt: 1
master_crc: NULL
master_cnt: NULL
ts: 2016-06-16 19:19:30
1 row in set (0.00 sec)
-- Testing with the actual CRC query on the table, we get NULL:
master [localhost] {msandbox} (test) > select CRC32(CONCAT_ WS('#', `id`, `a`, `b`, CONCAT(ISNULL(`a`), ISNULL(`b`)))) as crc from t;
+------+
| crc |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)
-- This is because of max_allowed_packet:
master [localhost] {msandbox} (test) > show warnings; ---+--- ---+--- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- + ---+--- ---+--- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- + ---+--- ---+--- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- +
+------
| Level | Code | Message |
+------
| Warning | 1301 | Result of concat_ws() was larger than max_allowed_packet (4194304) - truncated |
+------
1 row in set (0.00 sec)
-- Adding CRC32 to the BLOB/TEXT columns solves the problem
master [localhost] {msandbox} (test) > select CRC32(CONCAT_ WS('#', `id`, CRC32(`a`), CRC32(`b`), CONCAT(ISNULL(`a`), ISNULL(`b`)))) as crc from t;
+------------+
| crc |
+------------+
| 2498444559 |
+------------+
1 row in set (0.01 sec)