Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
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
|