[MCOL-593] support columnstore tables as slaves to innodb master tables Created: 2017-02-27  Updated: 2020-03-11  Resolved: 2020-03-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.7
Fix Version/s: 1.2.5

Type: New Feature Priority: Major
Reporter: David Thompson (Inactive) Assignee: Unassigned
Resolution: Done Votes: 4
Labels: documentation, relnote

Attachments: File fies1hal08.tellabs.fi.log,zip    
Issue Links:
Duplicate
duplicates MCOL-1050 when columnstore can be as a replicat... Closed
Sprint: 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.



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2019-04-15 ]

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.

Comment by Daniel Lee (Inactive) [ 2019-04-22 ]

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)
Comment by YURII KANTONISTOV [ 2020-01-20 ]

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.

Generated at Thu Feb 08 02:22:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.