Details
-
Type:
Task
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Fix Version/s: None
-
Component/s: None
-
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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)
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)
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'