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

datetime with connect overflows

    XMLWordPrintable

    Details

      Description

      When I create a CONNECT table to a table with a datetime column,
      I cannot insert to this CONNECT table due to a bug with translation.
      I can cope with it by changing the column to CHAR(19) but this bug should be fixed for user's convenience.

      FROM SERVER 1
      I created a MyISAM table.

      [root@kc0022 test]# mysql -u root
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 678
      Server version: 10.0.13-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> use test;
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Database changed
      MariaDB [test]> show create table table_delivery;
      +----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table          | Create Table                                                                                                                                                                                                                                                   |
      +----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | table_delivery | CREATE TABLE `table_delivery` (
        `order_no` int(11) NOT NULL DEFAULT '0',
        `delivery_charge` double DEFAULT NULL,
        `charge` double DEFAULT NULL,
        `discount` double DEFAULT NULL,
        `ship_date` datetime DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
      +----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      FROM SERVER 2
      I created a MyISAM table.
      I also created a CONNECT table linking to SERVER1's table.

      [root@kc1060 my.cnf.d]# mysql -u root
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 3
      Server version: 10.0.15-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> use test;
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Database changed
      MariaDB [test]> SHOW CREATE TABLE table_delivery;
      +----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table          | Create Table                                                                                                                                                                                                                                                   |
      +----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | table_delivery | CREATE TABLE `table_delivery` (
        `order_no` int(11) NOT NULL DEFAULT '0',
        `delivery_charge` double DEFAULT NULL,
        `charge` double DEFAULT NULL,
        `discount` double DEFAULT NULL,
        `ship_date` datetime DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
      +----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.01 sec)
       
      MariaDB [test]> CREATE TABLE connect_table_delivery ENGINE=CONNECT DEFAULT CHARSET=utf8 table_type=MYSQL dbname=test tabname=table_delivery DATA_CHARSET=cp932 option_list='user=root,host=10.0.1.36';
      Query OK, 0 rows affected (0.08 sec)
       
      MariaDB [test]> INSERT INTO connect_table_delivery SELECT * FROM table_delivery;
       
      ERROR 2013 (HY000): Lost connection to MySQL server during query

      The below is recorded in
      /var/lib/mysql/kc1060.err

      150109 19:25:17 [Note] CONNECT: Version 1.03.0005 Nov 22 2014 03:49:03
      150109 19:25:17 [Note] connect_init: hton=0x7f498727b388
      DTVAL Shift=-32400
      150109 19:25:17 [Note] Server socket created on IP: '::'.
      150109 19:25:17 [Note] Event Scheduler: Loaded 0 events
      150109 19:25:17 [Note] /usr/sbin/mysqld: ready for connections.
      Version: '10.0.15-MariaDB-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
      ...skipping...
      Done:  in=%4d-%2d-%2d %2d:%2d:%2d out=%Y-%m-%d %H:%M:%S
      MakeDateFormat: dfmt=YYYY-MM-DD hh:mm:ss
      Done:  in=%4d-%2d-%2d %2d:%2d:%2d out=%Y-%m-%d %H:%M:%S
      PlugSubAlloc: Not enough memory in Work area for request of 80 (used=67108856 fr
      ee=8)
      150109 19:25:17 mysqld_safe Number of processes running now: 0
      150109 19:25:17 mysqld_safe mysqld restarted

      The error is related to the datetime row, so I changed the row to CHAR(19).
      It works, but I don't want to do this everytime.

      MariaDB [test]> ALTER TABLE connect_table_delivery MODIFY ship_date CHAR(19);
      Query OK, 0 rows affected (0.00 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> INSERT INTO connect_table_delivery SELECT * FROM table_delivery;
      Query OK, 500000 rows affected (3 min 6.25 sec)
      Records: 500000  Duplicates: 0  Warnings: 0

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            takuya Takuya Aoki
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration