Bug #104249 A field with type integer(12) unsigned is replicated as a bigint
Submitted: 8 Jul 6:31 Modified: 9 Jul 18:02
Reporter: Andrey Lebedev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:8.0.16 OS:CentOS (8)
Assigned to: MySQL Verification Team CPU Architecture:x86

[8 Jul 6:31] Andrey Lebedev
Description:
We have setup slave server from a dump with follow options:
 --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A

Master my.cnf:
[client]
port		= 3306
socket		= /opt/mysql/run/mysqld/mysqld.sock
[mysqld_safe]
socket		= /opt/mysql/run/mysqld/mysqld.sock
nice		= 0
basedir		= /opt/mysql
datadir		= /opt/mysql/var/lib/mysql
pid-file        = /opt/mysql/run/mysqld/mysqld.pid
user		= mysql8
defaults-file   = /opt/mysql/etc/my.cnf

[mysqld]
user		= mysql8
pid-file	= /opt/mysql/run/mysqld/mysqld.pid
socket		= /opt/mysql/run/mysqld/mysqld.sock
port		= 3306
basedir		= /opt/mysql
datadir		= /opt/mysql/var/lib/mysql
tmpdir		= /opt/mysql/tmp
lc-messages-dir	= /opt/mysql/share
skip-external-locking
mysqlx          = 0 
sql-mode        = "ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
explicit_defaults_for_timestamp = 0
bind-address		= 0.0.0.0
max_allowed_packet	= 32M
thread_stack		= 192K
thread_cache_size       = 60
table_open_cache	= 6000
max_connections        = 1000
wait_timeout		= 3600
sort_buffer_size = 1M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 1M
log_error = /opt/mysql/var/log/mysql/error.log
slow_query_log_file = /opt/mysql/var/log/mysql/mysql-slow.log
slow_query_log      = 1
long_query_time = 3
server-id = 1
binlog-format = mixed
innodb_flush_log_at_trx_commit = 1
sync_binlog=1
innodb_buffer_pool_size = 35G
innodb_log_file_size = 4G
innodb_buffer_pool_instances = 8
innodb_stats_on_metadata = OFF
innodb_file_per_table = 1
innodb_log_buffer_size = 1G
innodb_lock_wait_timeout = 120

secure-file-priv="/opt/mysql/tmp/"

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[isamchk]
key_buffer		= 16M

Slave my.cnf:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

skip-external-locking
mysqlx          = 0
sql-mode        = "ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
explicit_defaults_for_timestamp = 0
log_timestamps = SYSTEM

bind-address            = 0.0.0.0
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 60
table_open_cache        = 6000
max_connections        = 1000
wait_timeout            = 3600
sort_buffer_size = 1M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 1M

innodb_buffer_pool_size = 35G
innodb_log_file_size = 4G
innodb_buffer_pool_instances = 8
innodb_stats_on_metadata = OFF
innodb_file_per_table = 1
innodb_log_buffer_size = 1G
innodb_lock_wait_timeout = 120

server-id  = 2
binlog-format = mixed
relay-log = binrelay
log-slave-updates = 1
slave_type_conversions = ALL_NON_LOSSY
read-only = 1

skip-grant-tables

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
default-character-set=utf8

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
default-character-set=utf8

[isamchk]
key_buffer              = 16M

Master table definition:

show create table inbound_orders \G;
*************************** 1. row ***************************
       Table: inbound_orders
Create Table: CREATE TABLE `inbound_orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `inbound_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `begin_at` int(10) unsigned NOT NULL,
  `end_at` int(10) unsigned NOT NULL,
  `cost` float unsigned DEFAULT NULL,
  `sms_price` float unsigned NOT NULL DEFAULT '0',
  `created_at` int(10) unsigned NOT NULL,
  `canceled_at` int(10) unsigned DEFAULT NULL,
  `is_canceled_by_user` tinyint(1) NOT NULL DEFAULT '0',
  `currency` char(3) NOT NULL DEFAULT 'EUR',
  `endpoint` text,
  `endpoint_version` tinyint(4) NOT NULL DEFAULT '2',
  `processing_by` tinyint(4) DEFAULT NULL,
  `fail_extend_email_at` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `indx_unique_inbound_orders_inbound_id_canceled_at` (`inbound_id`,(ifnull(`canceled_at`,0))),
  UNIQUE KEY `inbound_orders_inbound_month_year` (`inbound_id`,(year(from_unixtime(`begin_at`))),(month(from_unixtime(`begin_at`)))),
  CONSTRAINT `inbound_orders_ibfk_1` FOREIGN KEY (`inbound_id`) REFERENCES `inbound` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

ERROR: No query specified

Slave table definition:

mysql> show create table inbound_orders \G
*************************** 1. row ***************************
       Table: inbound_orders
Create Table: CREATE TABLE `inbound_orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `inbound_id` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `begin_at` int(10) unsigned NOT NULL,
  `end_at` int(10) unsigned NOT NULL,
  `cost` float unsigned DEFAULT NULL,
  `sms_price` float unsigned NOT NULL DEFAULT '0',
  `created_at` int(10) unsigned NOT NULL,
  `canceled_at` int(10) unsigned DEFAULT NULL,
  `is_canceled_by_user` tinyint(1) NOT NULL DEFAULT '0',
  `currency` char(3) NOT NULL DEFAULT 'EUR',
  `endpoint` text,
  `endpoint_version` tinyint(4) NOT NULL DEFAULT '2',
  `processing_by` tinyint(4) DEFAULT NULL,
  `fail_extend_email_at` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `indx_unique_inbound_orders_inbound_id_canceled_at` (`inbound_id`,(ifnull(`canceled_at`,0))),
  UNIQUE KEY `inbound_orders_inbound_month_year` (`inbound_id`,(year(from_unixtime(`begin_at`))),(month(from_unixtime(`begin_at`)))),
  CONSTRAINT `inbound_orders_ibfk_1` FOREIGN KEY (`inbound_id`) REFERENCES `inbound` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

We started replication and it worked about half a day and after failed by an error:

2021-07-07T22:00:01.676048Z 10 [ERROR] [MY-013146] [Repl] Slave SQL for channel '': Column 17 of table 'acp.inbound_orders' cannot be converted from type 'bigint' to type 'int(2)', Error_code: MY-013146
2021-07-07T22:00:01.676097Z 10 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000850' position 808685867

Binlog from 'binlog.000850' position 808685867:

BINLOG '
6h3lYA8BAAAAeAAAAHwAAAAAAAQAOC4wLjE2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgEKmaL/
'/*!*/;
# at 808685867

After skip that event on slave server and start replication we compared table dump an found difference in one row:

- master

mysql> select end_at from inbound_orders where id=142;
+------------+
| end_at     |
+------------+
| 1628373600 |
+------------+
1 row in set (0.00 sec)

- slave

mysql> select end_at from inbound_orders where id=142;
+------------+
| end_at     |
+------------+
| 1625695200 |
+------------+
1 row in set (0.00 sec)

In other words field with type integer(12) unsigned is replicated as a bigint. 
Please help to fix it.

How to repeat:
Master database is highload and other records with fields integer(12) unsigned  (but in other tables) replicates successfully.

So i don't know how to repeat that case.
[9 Jul 7:02] Andrey Lebedev
After some investigation we found that if create the same table (but with different name) replication is working fine.

Some years ago we upgraded master server from 5 to 8 version by copying data file (not from backup). 

Could it be a reason?
[9 Jul 18:02] MySQL Verification Team
Hi,

I'm not reproducing this.
Have you tried reproducing this on the fresh installation?

> Some years ago we upgraded master server from 5 to 8 version by copying data file (not from backup). 

It is possible you did not properly upgrade. You can backup and try mysql_upgrade.

https://dev.mysql.com/doc/refman/8.0/en/mysql-upgrade.html

all best
Bogdan