[MDEV-6379] Allow ROW based replication to CONNECT engine tables Created: 2014-06-23  Updated: 2018-05-02  Resolved: 2018-05-02

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Fix Version/s: N/A

Type: Task Priority: Minor
Reporter: Richard Bensley Assignee: Olivier Bertrand
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

The CONNECT engine allows for DML statements to be run on remote ODBC tables. But not with ROW based logging. This means, if you have a ROW based infrastructure you must have a relay server set up with binlog_format=STATEMENT and log_slave_updates=1, to replicate to a slave with CONNECT engine tables to an ODBC target.

Here is an example, replicating data to the table called `totals`:

Servers:

  • MariaDB 10 Master
  • MariaDB 10 slave with CONNECT engine tables
  • PostgreSQL 9.1 Server with ODBC driver

MariaDB Master DDL:

CREATE TABLE `totals` (
 
  `a` int(11) DEFAULT NULL,
 
  `b` varchar(64) DEFAULT NULL,
 
  KEY `a` (`a`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

MariaDB Slave DDL:

CREATE TABLE `totals` (
 
  `a` int(11) NOT NULL,
 
  `b` varchar(64) DEFAULT NULL
 
) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='DSN=pg;' `TABLE_TYPE`='ODBC'

PostgreSQL Schema:

reports=> \d+ totals;
 
                        Table "public.totals"
 
 Column |         Type          | Modifiers | Storage  | Description 
--------+-----------------------+-----------+----------+-------------
 
 a      | integer               | not null  | plain    | 
 b      | character varying(64) |           | extended | 
Indexes:
 
    "totals_pkey" PRIMARY KEY, btree (a)
 
Has OIDs: no

This works! The postgresql server is receiving DML from a MariaDB master:
MariaDB Master

mariadb [remote] > insert into totals values (1, "a value");
 
 
 
mariadb [remote] > select * from totals;
 
+------+---------+
 
| a    | b       |
 
+------+---------+
 
|    1 | a value |
 
+------+---------+
 
1 row in set (0.00 sec)

MariaDB Slave (CONNECT table):

mariadb [remote] > select * from totals;
 
+---+---------+
 
| a | b       |
 
+---+---------+
 
| 1 | a value |
 
+---+---------+
 
1 row in set (0.01 sec)

PostgreSQL server:

reports=> select * from totals;
 
 a |    b    
---+---------
 
 1 | a value
 
(1 row)

When setting the master to ROW based, the MariaDB slave fails:

show all slaves status\G
 
...
 
Last_SQL_Errno: 1148
 
 Last_SQL_Error: Error executing row event: 'CONNECT Unsupported command'
 
...

MariaDB slave error log:

Unsupported sql_command=146
 
140623 19:28:35 [ERROR] Master 'master': Slave SQL: Error executing row event: 'CONNECT Unsupported command', Internal MariaDB error code: 1148
 
140623 19:28:35 [Warning] Master 'master': Slave: CONNECT Unsupported command Error_code: 1148
 
140623 19:28:35 [Warning] Master 'master': Slave: Can't lock file (errno: 122 "Internal (unspecified) error in handler") Error_code: 1015
 
140623 19:28:35 [ERROR] Master 'master': Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-log-bin.000006' position 373



 Comments   
Comment by Richard Bensley [ 2014-06-23 ]

Another quick DML demo with STATEMENT based logging to CONNECT engine ODBC tables:

#INSERT
MariaDB master:
mariadb [remote] > insert into totals values (2, "another value");
Query OK, 1 row affected (0.00 sec)

PostgreSQL server:
reports=> select * from totals;
a | b
--+--------------
1 | a value
2 | another value
(2 rows)

  1. DELETE
    MariaDB Master:
    mariadb [remote] > delete from totals where a=1;
    Query OK, 1 row affected (0.00 sec)

PostgreSQL server:
reports=> select * from totals;
a | b
--+--------------
2 | another value
(1 row)

  1. Update
    MariaDB Server:
    mariadb [remote] > update totals set b="Updated value!" where a=2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

MariaDB Slave:
Last_SQL_Errno: 1296
Last_SQL_Error: Error 'Got error 122 'Remote: [unixODBC]ERROR: column "Updated value!" does not exist;
Error while executing the query' from CONNECT' on query. Default database: 'remote'. Query: 'update totals set b="Updated value!" where a=2'

Comment by Richard Bensley [ 2018-05-01 ]

I think maybe this is way out of scope for CONNECT and MED? You may close if you wish.

Comment by Olivier Bertrand [ 2018-05-02 ]

Dropped by reporter.

Generated at Thu Feb 08 07:11:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.