Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29048

Table create statement on master does not get replicated on replica

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.6.7, 10.7.3, 10.7.4, 10.8.3
    • Fix Version/s: N/A
    • Component/s: Replication
    • Labels:
    • Environment:
      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
      
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            Erland22 Erlandas
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.