search

duckdb / duckdb_mysql

https://duckdb.org/docs/extensions/mysql
MIT License
47 stars 11 forks source link

Fail to create table with time column #16

Closed Cricle closed 9 months ago

Cricle commented 9 months ago

What happens?

Throw IO Error.

Error: IO Error: Failed to run query "INSERT INTO `test-2`.`juhe6`  VALUES (500001, '2000-01-01 09:00:00', '2000-01-01 .....

 Incorrect datetime value: '2000-01-01 09:00:00+08' for column 'ja1' at row 1

To Reproduce

> ATTACH 'host=192.168.1.101 user=root port=3306 database=test-2 password=Syc123456.' AS mysqlscanner (TYPE mysql_scanner);
> USE mysqlscanner;
> show tables;
┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ juhe    │
│ juhe1   │
│ juhe2   │
│ juhe3   │
│ juhe4   │
│ juhe5   │
└─────────┘
> CREATE TABLE juhe6 AS SELECT * FROM juhe5;
Error: IO Error: Failed to run query "INSERT INTO `test-2`.`juhe6`  VALUES (500001, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 904, '899.29', 'Sakamoto Nanami'), (500002, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 558, '61.69', 'Michelle Foster'), (500003, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 214, '397.17', 'Ishii Daisuke'), (500004, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 432, '931.06', 'Steven Baker'), (500005, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 335, '110.19', 'Christopher Murphy'), (500006, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 316, '101.38', 'Amber Mitchell'), (500007, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 683, '160.96', 'Tamura Mio'), (500008, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 205, '30.60', 'Mary Cox'), (500009, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 578, '523.25', 'Kim Jimenez'), (500010, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 987, '158.43', 'Saito Daisuke'), (500011, '2000-01-01 09:00:00', '2000-01-01 09:00:00+08', 943, '733.77', 'Judy Sanchez'.....

In mysql

SELECT count(*) FROM juhe5; ---500000

OS:

win x64

MySQL Version:

8.1.0

DuckDB Version:

0.9.2

DuckDB Client:

cli

Full Name:

HCricle

Affiliation:

No

Have you tried this on the latest main branch?

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

Mytherin commented 9 months ago

Thanks for the report! Could you provide a reproducible example?

Cricle commented 9 months ago

Executing the sql script in mysql. juhe5.zip

Use duckdb memory mode, after attack the library, and call command

CREATE TABLE ttt2 AS SELECT * FROM juhe5;

Will be throw exception.

My time zone is UTC+8

Mytherin commented 9 months ago

I've pushed a fix in #17