Bug #101567 Generated column as primary key breaks replication
Submitted: 11 Nov 2020 19:22 Modified: 13 Nov 2020 11:07
Reporter: Ilya Raudsepp Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7.30/5.7.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: generated columns, replication

[11 Nov 2020 19:22] Ilya Raudsepp
Description:
If you create table like below, and then run REPLACE query, replication will be broken.

CREATE TABLE test.test (
  `id1` bigint(20) unsigned DEFAULT NULL,
  `id2` bigint(20) unsigned DEFAULT NULL,
  `hash` varchar(40) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (sha(concat(`id1`,'/',`id2`))) STORED NOT NULL,
  `somechars` varchar(50),
  PRIMARY KEY (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

How to repeat:
Two test instances config:
```
[client]
port = 3308
socket = /local/instance1/mysql/mysql.sock
[mysqld]
port = 3308
sql_mode = NO_ENGINE_SUBSTITUTION
socket = /localinstance1/mysql/mysql.sock
datadir = /local/instance1/mysql/data
ssl = 0
secure_file_priv=/local/instance1/mysql/tmp
;
skip-external-locking
query_cache_size = 0
query_cache_type = 0
optimizer_search_depth = 0
max_connect_errors = 4294967295
skip-name-resolve
skip-host-cache
character-set-server = utf8
collation-server = utf8_unicode_ci
log-bin = /local/instance1/mysql/bin/bin
relay-log = relay-bin
binlog-format = ROW
binlog_row_image = minimal
sync_binlog = 0
master_info_repository = TABLE
relay_log_info_repository = TABLE
server-id = 1
expire_logs_days = 1
log-slave-updates = 1
slave_compressed_protocol = 1
innodb_thread_concurrency = 0
innodb_file_per_table
;innodb_undo_tablespaces = 16
;innodb_undo_directory = /local/instance1/mysql/innodb
;Deprecated
;innodb_file_format = barracuda
innodb_strict_mode = 1
innodb_table_locks = 1
innodb_data_home_dir = /local/instance1/mysql/innodb
innodb_data_file_path = ibdata1:64M:autoextend
innodb_log_group_home_dir = /local/instance1/mysql/var
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_log_buffer_size = 128M
innodb_lock_wait_timeout = 50
innodb_old_blocks_time = 1000
innodb_stats_on_metadata = 0
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
```
same for instance2 but different directory/server_id/port number

On instance1:
grant replication slave on *.* to replica@'%';
On instance2:
change master to master_host='127.0.0.1', master_port=3307, master_user='replica';

create table listed above.

Fill the table:
insert into test.test set id1=1, id2=2, somechars='test1';
insert into test.test set id1=3, id2=4, somechars='test2';

Master:
mysql> select * from  test.test;
+------+------+------------------------------------------+-----------+
| id1  | id2  | hash                                     | somechars |
+------+------+------------------------------------------+-----------+
|    3 |    4 | cc80df1f23a810ec9e1b7b0863bf8d648ccf9930 | test2     |
|    1 |    2 | ee2caa07b76d9053386202d6a9c27c949c65c58f | test1     |
+------+------+------------------------------------------+-----------+
2 rows in set (0.00 sec)

Slave:
mysql> select * from test.test;
+------+------+------------------------------------------+-----------+
| id1  | id2  | hash                                     | somechars |
+------+------+------------------------------------------+-----------+
|    3 |    4 | cc80df1f23a810ec9e1b7b0863bf8d648ccf9930 | test2     |
|    1 |    2 | ee2caa07b76d9053386202d6a9c27c949c65c58f | test1     |
+------+------+------------------------------------------+-----------+
2 rows in set (0.00 sec)

Then run replace:
mysql> replace into test.test(id1,id2,somechars) values(1,2,'changedtext');
Query OK, 2 rows affected (0.01 sec)

And slave will be broken, with error:
Last_SQL_Error: Could not execute Update_rows event on table test.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log bin.000002, end_log_pos 2147
[13 Nov 2020 11:07] MySQL Verification Team
Thank you for the bug report.