MySQL Bugs: #89144: MySQL Shell incorrectly displaying DateTime values

Bug #89144	MySQL Shell incorrectly displaying DateTime values
Submitted:	8 Jan 2018 22:10	Modified:	10 Jan 2018 6:39
Reporter:	Josh Erickson	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Shell General / Core Client	Severity:	S3 (Non-critical)
Version:	1.0.11	OS:	MacOS (10.13.2)
Assigned to:		CPU Architecture:	Any

Description:
MySQL is failing to insert some (but not all) DateTime values. Issue appears to be related to having 8's or 9's in the DateTime string.

See StackOverflow thread for more details: https://stackoverflow.com/questions/48149288/insert-timestamp-or-datetime-fails-when-month...

How to repeat:
1. Create a table with a DateTime field.
2. INSERT INTO test(date) VALUES("2018-01-08 10:47:47");
3. Resulting value is 2018-01-00 8:00:00

Thank you for the bug report.

miguel@tikal:~ $ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE f;
Query OK, 1 row affected (0.04 sec)

mysql> USE f
Database changed
mysql> CREATE TABLE test (date datetime);
Query OK, 0 rows affected (0.65 sec)

mysql> INSERT INTO test(date) VALUES("2018-01-08 10:47:47");
Query OK, 1 row affected (0.10 sec)

mysql> SELECT * FROM test;
+---------------------+
| date                |
+---------------------+
| 2018-01-08 10:47:47 |
+---------------------+
1 row in set (0.00 sec)

mysql>

Hi, here is my output using the exact same commands as you. Perhaps this is an issue with MySQL Shell.

The original poster on StackOverflow added this comment: "The fail is only in the Mysql shell, I thought it was due to the version, because this last time, I did the query in the MySQL workbench. It is a bug of mysql shell visualization"

I have personally encountered the issue using MySQL Shell (Ver 1.0.11) from Mac OS, and via SQLAlchemy (Version: 1.2.0) library for Python 3.6. 

===== OUTPUT BELOW =====

Your MySQL connection id is 68
Server version: 5.7.20 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL Shell 1.0.11

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.

Currently in SQL mode. Use \js or \py to switch the shell to a scripting language.
mysql-sql> CREATE DATABASE f;
Query OK, 1 row affected (0.00 sec)
mysql-sql> USE f;
Query OK, 0 rows affected (0.00 sec)
mysql-sql> CREATE TABLE test (date datetime);
Query OK, 0 rows affected (0.02 sec)
mysql-sql> INSERT INTO test(date) VALUES("2018-01-08 10:47:47");
Query OK, 1 row affected (0.01 sec)
mysql-sql> SELECT * FROM test;
+--------------------+
| date               |
+--------------------+
| 2018-01-00 8:00:00 |
+--------------------+
1 row in set (0.00 sec)
mysql-sql> 

===== END OF OUTPUT =====

Hi, after some further testing this appears to be an issue with displaying the values in MySQL Shell. The value seems to be stored correctly in the database, as the value is correct when read via SQLAlchemy. It's just not displayed correctly when doing a SELECT via MySQL Shell.

I couldn't repeat using the mysql command client either on Linux CentOS 7 or on Windows 10 Pro, so assume the issue is your environment.

Microsoft Windows [Version 10.0.16299.192]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\>cd dbs

C:\dbs>cd 5.7

C:\dbs\5.7>bin\mysql -uroot -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE f;
Query OK, 1 row affected (0.01 sec)

mysql> USE f
Database changed
mysql> CREATE TABLE test (date datetime);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO test(date) VALUES("2018-01-08 10:47:47");
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;
+---------------------+
| date                |
+---------------------+
| 2018-01-08 10:47:47 |
+---------------------+
1 row in set (0.01 sec)

mysql>

Ok thanks Godofredo. Updating this ticket with my version details. Since it seems like the actual values are fine, this is more of an annoyance than an issue for me at this point.

Josh-MB:~ josh$ mysqlsh --version
mysqlsh   Ver 1.0.11 for Darwin on x86_64 - for MySQL 5.7.20 (MySQL Community Server (GPL))
Josh-MB:~ josh$ sw_vers
ProductName:	Mac OS X
ProductVersion:	10.13.2
BuildVersion:	17C88

I couldn't repeat on Windows:

F:\ade\mysql_archives\shell\1.0.11\mysql-shell-1.0.11-windows-x86-64bit\bin>mysqlsh -uroot
Creating a Session to 'root@localhost'
Enter password:
Your MySQL connection id is 4 (X protocol)
Server version: 5.7.20 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL Shell 1.0.11

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> CREATE DATABASE f;
SyntaxError: Unexpected identifier
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> CREATE DATABASE f;
Query OK, 1 row affected (0.00 sec)
mysql-sql> USE f;
Query OK, 0 rows affected (0.00 sec)
mysql-sql> CREATE TABLE test (date datetime);
Query OK, 0 rows affected (0.12 sec)
mysql-sql> INSERT INTO test(date) VALUES("2018-01-08 10:47:47");
Query OK, 1 row affected (0.02 sec)
mysql-sql> SELECT * FROM test;
+---------------------+
| date                |
+---------------------+
| 2018-01-08 10:47:47 |
+---------------------+
1 row in set (0.00 sec)
mysql-sql> \q
Bye!

F:\ade\mysql_archives\shell\1.0.11\mysql-shell-1.0.11-windows-x86-64bit\bin>mysqlsh.exe --version
mysqlsh.exe   Ver 1.0.11 for Windows on AMD64 - for MySQL 5.7.20 (MySQL Community Server (GPL))

mysqlsh on mac os x

Attachment: mysqlsh-on-mac.png (image/png, text), 400.64 KiB.

Thank you for the feedback. Verified on Mac OS X 10.13.2.

Linux (CentOS 7) also affected:

miguel@tikal:~ $ mysqlsh -uroot 
Creating a Session to 'root@localhost'
Enter password: 
Your MySQL connection id is 5
Server version: 5.7.20 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL Shell 1.0.11

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> CREATE DATABASE f;
Query OK, 1 row affected (0.00 sec)
mysql-sql> USE f;
Query OK, 0 rows affected (0.00 sec)
mysql-sql> CREATE TABLE test (date datetime);
Query OK, 0 rows affected (0.32 sec)
mysql-sql> INSERT INTO test(date) VALUES("2018-01-08 10:47:47");
Query OK, 1 row affected (0.05 sec)
mysql-sql> SELECT * FROM test;
+--------------------+
| date               |
+--------------------+
| 2018-01-00 8:00:00 |
+--------------------+
1 row in set (0.00 sec)
mysql-sql>