Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26777

Federated SE cannot read Connect table from remote server

    XMLWordPrintable

    Details

      Description

      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
      

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            anel Anel Husakovic
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration