[Maria-developers] New to the list & question about CONNECT
Hello everyone, My name is Martijn Tonies and I’m a Windows tools developer, we have a tool called “Database Workbench”, a developer tool for several database engines. For it’s next major version, we’re planning to officially support MariaDB in the MySQL module. I’ve investigated several of the features, implemented support for Roles, Virtual Columns etc, but I’m having trouble with the CONNECT engine. Here’s what I did, using MariaDB 10.0.10 1) registered a server with CREATE SERVER: CREATE SERVER mysql55_at_local FOREIGN DATA WRAPPER mysql OPTIONS(HOST 'MT-XP-VM-MYSQL', PORT 3355, DATABASE 'test', USER 'root', PASSWORD ‘secret’); 2) created a table in the MySQL database: CREATE TABLE sfdata ( mark1 Integer(11), mark2 Integer(11), mark3 Integer(11), mark4 Integer(11), `name` VarChar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; 3) created a table in the MariaDB database: CREATE TABLE `sfdata_proxy3` ( `mark1` int(11) DEFAULT NULL, `mark2` int(11) DEFAULT NULL, `mark3` int(11) DEFAULT NULL, `mark4` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='mysql55_at_local/sfdata' I then expected that table “sfdata_proxy3” has the rows as “sfdata” has, but I got nothing returned. Any clue where to look? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird!
Hi all, Follow up to myself. Depending on the exact combination of clauses in CREATE TABLE, a registered server works or doesn’t work. Here’s a few examples, the server “mysql55_at_local” points to database “test” with table “t1” in it. This fails: CREATE TABLE `t10` ( `icol` int(11) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED CONNECTION='mysql55_at_local' `table_type`=MYSQL `dbname`=test `tabname`=t1 Error on SELECT * FROM t10: #HY000Got error 174 '(1146) Table 'test.t10' doesn't exist [SELECT `icol` FROM `t10`]' from CONNECT t10? Why doesn’t “tabname=t1” work? This fails: CREATE TABLE `t9` ( `icol` int(11) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED CONNECTION='mysql55_at_local' `table_type`=MYSQL `tabname`=t1 Error on SELECT * FROM t9: #HY000Got error 174 '(1146) Table 'test.t9' doesn't exist [SELECT `icol` FROM `t9`]' from CONNECT t9? Why doesn’t “tabname=t1” work? This, fails: CREATE TABLE `t3` ( `icol` int(11) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED CONNECTION='mysql55_at_local' `table_type`=MYSQL `dbname`=test `tabname`=t1 #HY000Got error 174 '(1054) Unknown column 'icol' in 'field list' [SELECT `icol` FROM `t3`]' from CONNECT Huh? Uknown column? This works: CREATE TABLE `t11` ( `icol` int(11) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED CONNECTION='mysql55_at_local' `tabname`=t1 Removed table_type option. This fails: CREATE TABLE `t8` ( `icol` int(11) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED CONNECTION='mysql55_at_local/test/t1' No result. This fails: CREATE TABLE `t7` ( `icol` int(11) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED CONNECTION='mysql55_at_local/t1' No result. This works: CREATE TABLE `t4` ( `icol` int(11) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED CONNECTION='mysql://root:pass@localhost:3355' `table_type`=MYSQL `dbname`=test `tabname`=t1 To be honest, the pattern is unclear to me. With regards, Martijn Tonies Upscene Productions http://www.upscene.com == I’ve investigated several of the features, implemented support for Roles, Virtual Columns etc, but I’m having trouble with the CONNECT engine. Here’s what I did, using MariaDB 10.0.10 1) registered a server with CREATE SERVER: CREATE SERVER mysql55_at_local FOREIGN DATA WRAPPER mysql OPTIONS(HOST 'MT-XP-VM-MYSQL', PORT 3355, DATABASE 'test', USER 'root', PASSWORD ‘secret’); 2) created a table in the MySQL database: CREATE TABLE sfdata ( mark1 Integer(11), mark2 Integer(11), mark3 Integer(11), mark4 Integer(11), `name` VarChar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; 3) created a table in the MariaDB database: CREATE TABLE `sfdata_proxy3` ( `mark1` int(11) DEFAULT NULL, `mark2` int(11) DEFAULT NULL, `mark3` int(11) DEFAULT NULL, `mark4` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=utf8 CONNECTION='mysql55_at_local/sfdata' I then expected that table “sfdata_proxy3” has the rows as “sfdata” has, but I got nothing returned. Any clue where to look? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
participants (1)
-
Martijn Tonies (Upscene Productions)