maria-developers team mailing list archive

["Martijn Tonies (Upscene Productions)" <m.tonies@xxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp