Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-593

support columnstore tables as slaves to innodb master tables

Details

    • 2019-04

    Description

      Setting up a columnstore table in a slave and replicating to it currently does not work and silently fails. I believe this is because we use master / slave replication in a multi um setup to ensure synchronization of non columnstore lookup tables and columnstore table definitions where we obviously don't want the slave to process any dml since the data is already available.

      Another challenge is that columnstore does not have a mode to ignore non supported constructs and so most innodb ddl will fail. This could be worked around by precreating the schema.

      Attachments

        Issue Links

          Activity

            Patch adds a MariaDB replication option which can be enabled using the following in the SystemConfig section of Columnstore.xml:

            <ReplicationEnabled>Y</ReplicationEnabled>

            For QA:
            1. Create a MariaDB server with an InnoDB table
            2. Create a ColumnStore 1UM setup with a ColumnStore table of the same/similar schema
            3. Configure slave-ids and enable binlog on the MariaDB server
            4. Use CHANGE MASTER TO on the ColumnStore UM to enable replication
            5. Do a few DML statements.

            There may be issues with multi-UM setups for now. Improvements for this will come later, this is why it is a hidden option for now.

            LinuxJedi Andrew Hutchings (Inactive) added a comment - Patch adds a MariaDB replication option which can be enabled using the following in the SystemConfig section of Columnstore.xml: <ReplicationEnabled>Y</ReplicationEnabled> For QA: 1. Create a MariaDB server with an InnoDB table 2. Create a ColumnStore 1UM setup with a ColumnStore table of the same/similar schema 3. Configure slave-ids and enable binlog on the MariaDB server 4. Use CHANGE MASTER TO on the ColumnStore UM to enable replication 5. Do a few DML statements. There may be issues with multi-UM setups for now. Improvements for this will come later, this is why it is a hidden option for now.

            Build verified: 1.2.2-1 nightly

            [root@localhost centos7]# cat gitversionInfo.txt
            server commit:
            137b9a8
            engine commit:
            b3a7559

            1. installed MariaDB Server 10.3.14, set it as replication master
            2. created InnoDB table repdb.orders
            3. installed ColumnStore 1.2.4-1 (nightly build), set it as replication Slave
            4. created ColumnStore table repdb.orders
            5. created InnoDB tables in MariaDB server and verified tables were replicated to ColumnStore
            6. inserted rows into repdb.orders in MariaDB Server
            7. verified inserted row was not replicated (Expected, Columnstore.xml not yet setup)
            8. added <ReplicationEnabled>Y</ReplicationEnabled> and restarted ColumnStore
            9. inserted rows into repdb.orders in MariaDB Server
            10. verified inserted row was replicated

            After replication has been setup, tables can be setup as the following:

            1. stop replication slave in ColumnStore (stop slave)
            2. reset replication slave in ColumnStore (reset slave)
            3. create InnoDB table in MariaDB Server
            4. create ColumnStore table in ColumnStore
            5. "CHANGE MASTER TO" in ColumnStore
            6. start slave in ColumnStore (start slave)
            7. insert rows in master
            8. verify inserted rows in slave

            Notes:

            On Master

            MariaDB [repdb]> CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret';
            Query OK, 0 rows affected (0.000 sec)

            MariaDB [repdb]> GRANT REPLICATION SLAVE ON . TO 'replication_user'@'%';
            Query OK, 0 rows affected (0.001 sec)

            MariaDB [repdb]> show master status;
            --------------------------------------------------------+

            File Position Binlog_Do_DB Binlog_Ignore_DB

            --------------------------------------------------------+

            master1-bin.000001 2452    

            --------------------------------------------------------+
            1 row in set (0.000 sec)

            On Slave:

            update /etc/hosts file

            10.0.0.15 repmaster

            MariaDB [repdb]> CHANGE MASTER TO
            -> MASTER_HOST='repmaster',
            -> MASTER_USER='replication_user',
            -> MASTER_PASSWORD='bigs3cret',
            -> MASTER_PORT=3306,
            -> MASTER_LOG_FILE='master1-bin.000001',
            -> MASTER_LOG_POS=2452,
            -> MASTER_CONNECT_RETRY=10;
            Query OK, 0 rows affected (0.023 sec)

            MariaDB [repdb]> start slave;
            Query OK, 0 rows affected (0.002 sec)

            MariaDB [repdb]> show slave status\G;

                                                                • 1. row ***************************
                                                                  Slave_IO_State: Waiting for master to send event
                                                                  Master_Host: repmaster
                                                                  Master_User: replication_user
                                                                  Master_Port: 3306
                                                                  Connect_Retry: 10
                                                                  Master_Log_File: master1-bin.000001
                                                                  Read_Master_Log_Pos: 2452
                                                                  Relay_Log_File: relay-bin.000002
                                                                  Relay_Log_Pos: 557
                                                                  Relay_Master_Log_File: master1-bin.000001
                                                                  Slave_IO_Running: Yes
                                                                  Slave_SQL_Running: Yes
                                                                  Replicate_Do_DB:
                                                                  Replicate_Ignore_DB:
                                                                  Replicate_Do_Table:
                                                                  Replicate_Ignore_Table:
                                                                  Replicate_Wild_Do_Table:
                                                                  Replicate_Wild_Ignore_Table:
                                                                  Last_Errno: 0
                                                                  Last_Error:
                                                                  Skip_Counter: 0
                                                                  Exec_Master_Log_Pos: 2452
                                                                  Relay_Log_Space: 860
                                                                  Until_Condition: None
                                                                  Until_Log_File:
                                                                  Until_Log_Pos: 0
                                                                  Master_SSL_Allowed: No
                                                                  Master_SSL_CA_File:
                                                                  Master_SSL_CA_Path:
                                                                  Master_SSL_Cert:
                                                                  Master_SSL_Cipher:
                                                                  Master_SSL_Key:
                                                                  Seconds_Behind_Master: 0
                                                                  Master_SSL_Verify_Server_Cert: No
                                                                  Last_IO_Errno: 0
                                                                  Last_IO_Error:
                                                                  Last_SQL_Errno: 0
                                                                  Last_SQL_Error:
                                                                  Replicate_Ignore_Server_Ids:
                                                                  Master_Server_Id: 1
                                                                  Master_SSL_Crl:
                                                                  Master_SSL_Crlpath:
                                                                  Using_Gtid: No
                                                                  Gtid_IO_Pos:
                                                                  Replicate_Do_Domain_Ids:
                                                                  Replicate_Ignore_Domain_Ids:
                                                                  Parallel_Mode: conservative
                                                                  SQL_Delay: 0
                                                                  SQL_Remaining_Delay: NULL
                                                                  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
                                                                  Slave_DDL_Groups: 1
                                                                  Slave_Non_Transactional_Groups: 0
                                                                  Slave_Transactional_Groups: 1
                                                                  1 row in set (0.001 sec)
            dleeyh Daniel Lee (Inactive) added a comment - Build verified: 1.2.2-1 nightly [root@localhost centos7] # cat gitversionInfo.txt server commit: 137b9a8 engine commit: b3a7559 1. installed MariaDB Server 10.3.14, set it as replication master 2. created InnoDB table repdb.orders 3. installed ColumnStore 1.2.4-1 (nightly build), set it as replication Slave 4. created ColumnStore table repdb.orders 5. created InnoDB tables in MariaDB server and verified tables were replicated to ColumnStore 6. inserted rows into repdb.orders in MariaDB Server 7. verified inserted row was not replicated (Expected, Columnstore.xml not yet setup) 8. added <ReplicationEnabled>Y</ReplicationEnabled> and restarted ColumnStore 9. inserted rows into repdb.orders in MariaDB Server 10. verified inserted row was replicated After replication has been setup, tables can be setup as the following: 1. stop replication slave in ColumnStore (stop slave) 2. reset replication slave in ColumnStore (reset slave) 3. create InnoDB table in MariaDB Server 4. create ColumnStore table in ColumnStore 5. "CHANGE MASTER TO" in ColumnStore 6. start slave in ColumnStore (start slave) 7. insert rows in master 8. verify inserted rows in slave Notes: On Master MariaDB [repdb] > CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret'; Query OK, 0 rows affected (0.000 sec) MariaDB [repdb] > GRANT REPLICATION SLAVE ON . TO 'replication_user'@'%'; Query OK, 0 rows affected (0.001 sec) MariaDB [repdb] > show master status; ------------------- -------- ------------ -----------------+ File Position Binlog_Do_DB Binlog_Ignore_DB ------------------- -------- ------------ -----------------+ master1-bin.000001 2452     ------------------- -------- ------------ -----------------+ 1 row in set (0.000 sec) On Slave: update /etc/hosts file 10.0.0.15 repmaster MariaDB [repdb] > CHANGE MASTER TO -> MASTER_HOST='repmaster', -> MASTER_USER='replication_user', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master1-bin.000001', -> MASTER_LOG_POS=2452, -> MASTER_CONNECT_RETRY=10; Query OK, 0 rows affected (0.023 sec) MariaDB [repdb] > start slave; Query OK, 0 rows affected (0.002 sec) MariaDB [repdb] > show slave status\G; 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: repmaster Master_User: replication_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: master1-bin.000001 Read_Master_Log_Pos: 2452 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 557 Relay_Master_Log_File: master1-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2452 Relay_Log_Space: 860 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 1 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 1 1 row in set (0.001 sec)

            Tried this feature with MariaDB 10.3.21 innodb as a master and Columnstore 1.2.5(MariaDB 10.3.16) as a slave, simplest 1xUM+1xPM configuration.

            Replication can be established - both master and slave has pre-created table:

            master:
            create table xxx (id int) ENGINE=InnoDB;

            slave:
            create table xxx (id int) ENGINE=ColumnStore;

            "CHANGE MASTER TO/start slave/show slave status" commands work smoothly, but when doing very first "insert into xxx values (1);" on the master -
            slave's mysqld process goes into crash-restart loop (see attached fies1hal08.tellabs.fi.log,zip):

            ...
            2020-01-20 13:40:21 10 [Note] Slave I/O thread: Start asynchronous replication to master 'mycolumnrep@172.19.132.52:2048' in log '1.000002' at position 539
            2020-01-20 13:40:21 11 [Note] Slave SQL thread initialized, starting replication in log '1.000002' at position 334, relay log '/usr/local/mariadb/columnstore/mysql/db/relay-bin.000002' position: 547
            2020-01-20 13:40:21 0 [Note] /usr/local/mariadb/columnstore/mysql//bin/mysqld: ready for connections.
            Version: '10.3.16-MariaDB-log' socket: '/usr/local/mariadb/columnstore/mysql/lib/mysql/mysql.sock' port: 3306 Columnstore 1.2.5-1
            2020-01-20 13:40:21 10 [Note] Slave I/O thread: connected to master 'mycolumnrep@172.19.132.52:2048',replication started in log '1.000002' at position 539
            terminate called after throwing an instance of 'std::logic_error'
            what(): basic_string::_S_construct null not valid
            200120 13:40:21 [ERROR] mysqld got signal 6 ;
            ...

            Will appreciate any hint how to fix it or at least how to investigate it further.

            ykantoni YURII KANTONISTOV added a comment - Tried this feature with MariaDB 10.3.21 innodb as a master and Columnstore 1.2.5(MariaDB 10.3.16) as a slave, simplest 1xUM+1xPM configuration. Replication can be established - both master and slave has pre-created table: master: create table xxx (id int) ENGINE=InnoDB; slave: create table xxx (id int) ENGINE=ColumnStore; "CHANGE MASTER TO/start slave/show slave status" commands work smoothly, but when doing very first "insert into xxx values (1);" on the master - slave's mysqld process goes into crash-restart loop (see attached fies1hal08.tellabs.fi.log,zip): ... 2020-01-20 13:40:21 10 [Note] Slave I/O thread: Start asynchronous replication to master 'mycolumnrep@172.19.132.52:2048' in log '1.000002' at position 539 2020-01-20 13:40:21 11 [Note] Slave SQL thread initialized, starting replication in log '1.000002' at position 334, relay log '/usr/local/mariadb/columnstore/mysql/db/relay-bin.000002' position: 547 2020-01-20 13:40:21 0 [Note] /usr/local/mariadb/columnstore/mysql//bin/mysqld: ready for connections. Version: '10.3.16-MariaDB-log' socket: '/usr/local/mariadb/columnstore/mysql/lib/mysql/mysql.sock' port: 3306 Columnstore 1.2.5-1 2020-01-20 13:40:21 10 [Note] Slave I/O thread: connected to master 'mycolumnrep@172.19.132.52:2048',replication started in log '1.000002' at position 539 terminate called after throwing an instance of 'std::logic_error' what(): basic_string::_S_construct null not valid 200120 13:40:21 [ERROR] mysqld got signal 6 ; ... Will appreciate any hint how to fix it or at least how to investigate it further.

            People

              Unassigned Unassigned
              dthompson David Thompson (Inactive)
              Votes:
              4 Vote for this issue
              Watchers:
              8 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.