Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.6.7, 10.7.3, 10.7.4, 10.8.3
-
Ubuntu 20.04
Description
Simple CREATE TABLE (or a column in the table) statement used to create a new table in the existing database does not get replicated on replica. Sometimes it works from some MySQL DBA clients but usually it is an issue from CLI, DataGrip and other DBA.
Replica then issues:
Last_Errno: 1146
Last_Error: Error 'Table 'jobs2.queued_tasks_slots' doesn't exist' on query. Default database: 'jobs2'. Query: 'INSERT INTO jobs2.queued_tasks_slots (task_name, slots_used, updated_at) VALUES ('Insert_jobs_batch_into_indexes', DEFAULT, DEFAULT)'
Slave_IO_Running: Yes
Slave_SQL_Running: No
I have been having this issue since 10.6.7 when I started using Master-> Replica setup
In the past I was getting 1054 error which is unknown column name (when I was adding column to the existing table but I see the same problem is with new table addition too), now with 10.8.3 I am getting 1146 error but the cause is always the same - create a table with columns or a new column on master and replication then breaks on replica when master gets writes into newly created column/table.
The only solution then is to import the whole database again on replica from master dump and start replication again.
Replica config as follows:
[server]
|
|
|
# this is only for the mysqld standalone daemon
|
[mysqld]
|
|
|
#
|
# * Basic Settings
|
#
|
|
|
user = mysql
|
pid-file = /run/mysqld/mysqld.pid
|
basedir = /usr
|
datadir = /var/lib/mysql
|
lc-messages-dir = /usr/share/mysql
|
lc-messages = en_US
|
skip-external-locking
|
|
|
#Replication Settings
|
read_only=1
|
server_id=14
|
expire_logs_days=2
|
gtid_strict_mode=1
|
#Use the two lines below to get over duplicate primary key entries errors and deletion of non existent keys or when some table did not get ALTER replicated
|
##slave-skip-errors=1062,1032
|
##slave-skip-errors=1054,1091
|
##skip-slave-start
|
slave_transaction_retry_interval=2
|
slave_transaction_retries=30
|
replicate_do_db=cms
|
replicate_do_db=lgeonames
|
replicate_do_db=jobs2
|
replicate_do_db=osticket
|
replicate_do_db=phpbt
|
replicate_do_db=phpcrwl
|
replicate_do_db=schools2
|
slave_domain_parallel_threads=32
|
slave_parallel_threads=100
|
slave_parallel_max_queued=524288
|
email_db.slave_parallel_mode=conservative
|
web1_db.slave_parallel_mode=conservative
|
web2_db.slave_parallel_mode=conservative
|
|
|
performance-schema=0
|
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
|
open_files_limit = 240000
|
log-error=/var/lib/mysql/errors.log
|
tmpdir = /var/lib/mysql/tmp
|
innodb_file_per_table=1
|
innodb_defragment=1
|
default-storage-engine=InnoDB
|
innodb_flush_method=O_DIRECT
|
innodb_buffer_pool_size=180G
|
innodb_change_buffering=all
|
innodb_io_capacity = 200
|
innodb_read_io_threads = 64
|
innodb_write_io_threads = 64
|
innodb_log_file_size=128M
|
innodb_log_buffer_size =64M
|
max_connections=1600
|
key_buffer_size = 8M
|
max_allowed_packet=512M
|
#slow_query_log = 1
|
#slow_query_log_file = /var/lib/mysql/slow.log
|
#long_query_time = 1
|
#log_queries_not_using_indexes=1
|
wait_timeout=120
|
innodb_lock_wait_timeout=60
|
lock_wait_timeout = 50
|
interactive_timeout=120
|
read_buffer_size = 128K
|
table_definition_cache = 6400
|
table_open_cache = 12000
|
max_heap_table_size = 128M
|
tmp_table_size = 128M
|
join_buffer_size = 256K
|
sort_buffer_size = 256K
|
skip-name-resolve=1
|
query_cache_type = 0
|
query_cache_size = 0
|
innodb_buffer_pool_dump_pct=75
|
innodb_buffer_pool_dump_at_shutdown=ON
|
innodb_buffer_pool_load_at_startup=ON
|
innodb_flush_log_at_trx_commit =1
|
|