Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.15
-
None
-
CentOS release 6.5 (X86_64)
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
|