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.
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.