|
When the Federated table is created from the Connect table from the remote server :
- remote Connect table:
MariaDB [web_users]> show create table webusers\G
|
*************************** 1. row ***************************
|
Table: webusers
|
Create Table: CREATE TABLE `webusers` (
|
`id` int(1) NOT NULL `JPATH`='$.id',
|
`name` char(13) NOT NULL `JPATH`='$.name',
|
`username` char(9) NOT NULL `JPATH`='$.username',
|
`email` char(17) NOT NULL `JPATH`='$.email',
|
`address_street` char(13) NOT NULL `JPATH`='$.address.street',
|
`address_suite` char(9) NOT NULL `JPATH`='$.address.suite',
|
`address_city` char(11) NOT NULL `JPATH`='$.address.city',
|
`address_zipcode` char(10) NOT NULL `JPATH`='$.address.zipcode',
|
`address_geo_lat` char(8) NOT NULL `JPATH`='$.address.geo.lat',
|
`address_geo_lng` char(8) NOT NULL `JPATH`='$.address.geo.lng',
|
`phone` char(21) NOT NULL `JPATH`='$.phone',
|
`website` char(13) NOT NULL `JPATH`='$.website',
|
`company_name` char(15) NOT NULL `JPATH`='$.company.name',
|
`company_catchPhrase` char(38) NOT NULL `JPATH`='$.company.catchPhrase',
|
`company_bs` char(32) NOT NULL `JPATH`='$.company.bs'
|
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 `TABLE_TYPE`='JSON' `FILE_NAME`='web_users.json'
|
1 row in set (0.000 sec)
|
- locally
MariaDB [test]> INSTALL SONAME 'ha_federatedx';
|
Query OK, 0 rows affected (0.001 sec)
|
|
MariaDB [test]> CREATE TABLE test
|
-> ENGINE = federated
|
-> CONNECTION 'link_remote1/webusers';
|
ERROR 1939 (HY000): Engine FEDERATED failed to discover table `test`.`test` with 'CREATE TABLE `webusers` (
|
`id` int(1) NOT NULL `JPATH`='$.id',
|
`name` char(13) NOT NULL `JPATH`='$.name',
|
`username` char(9) NOT NULL `JPATH`='$.username',
|
`email` char(17) NOT NULL `JPATH`='$.email',
|
`address_street` char(13) NOT NULL `JPATH`='$.address.street',
|
`address_suite` char(9) NOT NULL `JPATH`='$.address.suite',
|
`address_city` char(11) NOT NULL `JPATH`='$.address.city',
|
`address_zipcode` char(10) NOT NULL `JPATH`='$.add
|
|
# When using Connect it works:
|
|
MariaDB [test]> CREATE TABLE test
|
-> ENGINE = CONNECT
|
-> TABLE_TYPE=MYSQL
|
-> CONNECTION 'link_remote1/webusers';
|
Query OK, 0 rows affected (0.026 sec)
|
|
When we create the normal/innodb like table from Connect on remote:
MariaDB [web_users]> create table web_users_innodb as select * from webusers;
|
Query OK, 2 rows affected (0.022 sec)
|
MariaDB [web_users]> show create table web_users_innodb\G
|
*************************** 1. row ***************************
|
Table: web_users_innodb
|
Create Table: CREATE TABLE `web_users_innodb` (
|
`id` int(1) NOT NULL,
|
`name` char(13) NOT NULL,
|
`username` char(9) NOT NULL,
|
`email` char(17) NOT NULL,
|
`address_street` char(13) NOT NULL,
|
`address_suite` char(9) NOT NULL,
|
`address_city` char(11) NOT NULL,
|
`address_zipcode` char(10) NOT NULL,
|
`address_geo_lat` char(8) NOT NULL,
|
`address_geo_lng` char(8) NOT NULL,
|
`phone` char(21) NOT NULL,
|
`website` char(13) NOT NULL,
|
`company_name` char(15) NOT NULL,
|
`company_catchPhrase` char(38) NOT NULL,
|
`company_bs` char(32) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
1 row in set (0.000 sec)
|
it will work, we can get the table using federated se:
MariaDB [test]> CREATE TABLE test.web_users_innodb ENGINE = federated CONNECTION 'link_remote1';
|
Query OK, 0 rows affected (0.011 sec)
|
MariaDB [test]> select * from web_users_innodb;
|
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 0: ' from FEDERATED
|
|