Bug #93501 Error copying table data / value is not suitable to be converted to a DateTime
Submitted: 6 Dec 2018 7:53 Modified: 23 May 2019 19:05
Reporter: Amit Unknown Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S1 (Critical)
Version:1.3.7 OS:Windows (10)
Assigned to: CPU Architecture:x86 (64 bit)

[6 Dec 2018 7:53] Amit Unknown
Description:
Its been 2 years and its still not fixed even though status says closed (https://bugs.mysql.com/bug.php?id=80139)

The DB I'm trying to open in XL via the MySQL add-in is in production, fully working, opens fine in workbench and so on. There is nothing wrong with the DB.

Almost all tables of this DB gives the same error when trying to open inside XL: value is not suitable to be converted to a DateTime data type

Any random field inside the mysql table gives this error. In one table, it reports this for the ID field, hence the error: Value '1' is not suitable to be converted to a DateTime data type.

In another table it reports this for a different field, hence a similar error: Value 'blahblah' is not suitable to be converted to a DateTime data type.

In another table, it reports this for a text field. And on and on.

In none of these cases are these fields of type date/time.

How to repeat:
Open a DB via the Excel add-in, then try to edit any table data. The messagebox error comes up right there.
[6 Dec 2018 10:07] MySQL Verification Team
Thank you for the bug report. Please provide the create table statement and insert data which presents the issue reported when imported.
[7 Dec 2018 18:45] Amit Unknown
Hello, your question is not making sense to me. Like I mentioned, this is a problem with MySQL for Excel, when I'm trying to edit data from an existing database. Neither am I trying to create a table, nor am I trying to insert data.
[7 Dec 2018 21:23] MySQL Verification Team
You say: "I'm trying to edit data from an existing database." So what I asked you is to provide a table create statement with some data insert for me to create a database with that table and then I will try to edit that table, just wanted to have a similar table like you are using.
[7 Dec 2018 21:49] MySQL Verification Team
Error message

Attachment: 93501.png (image/png, text), 128.64 KiB.

[7 Dec 2018 21:50] MySQL Verification Team
Thank you for the bug report. Please disregard prior request, verified using sakila database.
[12 Dec 2018 18:58] Javier Treviño
The way it was internally reproduced is not throwing the exception described by Amit, that's a different problem.

Amit, can you please supply a dump of the database schema for the tables showing this problem?  With the databases we have the error is not present.
[13 Dec 2018 0:22] Javier Treviño
I created bug https://bugs.mysql.com/bug.php?id=93581 for the error Miguel Solorzano reproduced. Which is not related to the error Amit is reporting, which happens on an Edit Data operation, not an Import Data one.
[13 Dec 2018 1:05] Javier Treviño
Amit, the error you describe does NOT happen with any table.
The steps to duplicate you added to the bug are misleading, hence the error has not been able to be reproduced.

In order to pinpoint what's going on and fix a bug, if any, please supply a clear set of steps (you are saying OPEN any database table, you need to indicate what kind of operation you are performing, IMPORT or EDIT, OPEN is not an operation). And you need to specify your Excel version, your database version and give some sample data to reproduce the problem.

I will continue trying to reproduce the error, but if possible please update the bug report with any more information about your data.

Thank you.
[14 Dec 2018 16:48] Amit Unknown
Hello Javier, I was quite precise in what I had said in the steps: "Open a DB via the Excel add-in, then try to edit any table data. The messagebox error comes up right there."

I did not say "OPEN any database table". I said "open a DB via Excel add-in".

Then I mentioned that I'm trying to EDIT any table data. Surely these steps are not "VERY misleading" :-)

Excel version: Office 365, Excel version 1811
Database version: I'm not sure what that is or where to retrieve it from
MySQL server version: 8.0.12, MySQL Community Server

Sample data to reproduce the problem: this is a bit of a problem for two reasons. #1 the data is proprietary and #2 the problem is intermittent. I'm unable to figure out when it happens and why. For the same database and tables and data, sometimes it will throw an error and sometimes it wont. I was working on some tables in this DB via Excel (i.e. edit and append) some days ago and it was fine. Right now I closed Excel, then restarted the server, then reconnected via Excel and tried to edit -> worked fine.

So may I suggest that you keep this issue under observation, and the next time I hit the issue, I will gather more data and also try to send you a sample file.
[14 Dec 2018 17:54] Javier Treviño
Hi Amit,
Thanks for your quick response.
Now it's clear the problem happens during an Edit Data operation, and also that the problem is random.  This certainly helps.  I have been trying to reproduce and I could only see the error 1 time out of about 50 that I tried to edit a table's data, and I still have no idea how to consistently reproduce the bug.

I switched the status to open and will continue trying to reproduce, I do not think it's related to specific data in the table, I believe the problem is more related with what operations have been done on the Excel workbook.

If you stumble with the same bug again please try to check what the Excel workbook is holding at the moment and what operations were done on it since it was opened or created. It also certainly helps to know if the workbook is an new unsaved workbook or an existing saved one.

Thanks for your help pinpointing the issue.
[15 Dec 2018 1:45] Amit Unknown
> I believe the problem is more related with what operations have been done on the Excel workbook.

That may not be true. My workflow is the same everytime - 

1. export DB from AWS
2. import via workbench
3. open a *new instance of Excel and Excel workbook*
4. open the DB via the Excel add-in
5. edit whatever records I need to (this is where the issue intermittantly happens)
6. commit
7. repeat #5 and #6 multiple times
8. close excel workbook without saving
[15 Dec 2018 1:56] Amit Unknown
I have to add that if the edit step (#5 in my previous comment) on *any* table proceeds the first time, then the issue does not show up for that entire session, irrespective of which or how many tables I edit / append data to. Workbench and Excel would be open for days together, and everything is fine.

However, after a fresh start, say from step #3, if #5 fails the first time, it will give the same error for just about every table and I will not be able to proceed. I can close Excel, workbench, restart them, whatever - it will keep throwing the same error at #5 step. Then I would give up.

Another day I would proceed again from #3, and it would work just perfect. Its the most infuriating issue :-D
[18 Dec 2018 5:09] Javier Treviño
Thanks for the information Amit.
I haven't been able to get the error again, I got it a single time out of many times that I have been starting an Edit Data session. Have been trying with different tables with no luck.

Can you attach a log file to the bug report when you stumble into the errors again? The logs are found at "%APPDATA%\Oracle\MySQL for Excel"

Thanks.
[8 Jan 2019 12:36] Sven Wargenau
I have the same Error when starting the Import / Edit dialogue from the db in Excel.

I could also reproduce the issue on multiple installations (Office 2013/Office Professional Plus 2016 / german and english/icelandic locales). 

I specifically encountered this problem after implementing new stored procedures. (side-note: i cannot get stored procedures to work with the excel plugin)

Sometimes rebooting the db instance solved the issue but not reliably. A rollback to an earlier snapshot of the db ALWAYS fixed the issue for me.

My DB instance is hosted by AWS and runs the latest MYSQL 8 version.
I also uploaded a Model of my database (reverse engineered in workbench) and a create and insert statement for one of the tables.
[8 Jan 2019 22:20] Amit Unknown
Screenshot 1

Attachment: 2019-01-09 (1).png (image/png, text), 151.42 KiB.

[8 Jan 2019 22:20] Amit Unknown
Screenshot 2

Attachment: 2019-01-09 (2).png (image/png, text), 149.03 KiB.

[8 Jan 2019 22:26] Amit Unknown
After quite some days today I needed to update the DB, and hit the problem again. This was after importing the latest DB from AWS.

After hitting the problem, I exited Excel, and restarted the mysql server and then I was able to proceed on the same DB.

Logs are following, I found all the earlier logs as well.
[10 Jan 2019 11:22] Sven Wargenau
While having the issue, I checked the Import behavior with a different Excel Add-Inn (Devart Excel Add-In 2.0 for MySQL) and it worked just fine. 

(Also it would be nice to get any feedback... is someone working on this or has this been abandoned ?)
[10 Jan 2019 15:39] Javier Treviño
Posted by developer:
 
Thanks Sven and Amit for the extra information you attached to the bug report.
The bug is being worked on, all the information you provided is very useful, but since the bug is random I still have not found the state the table needs to be before starting an Edit Data session on it.  Rest assured this is in progress.
[19 Jan 2019 19:31] Amit Unknown
Field circled in red which is throwing error on "edit"

Attachment: mysql edit error.PNG (image/png, text), 59.94 KiB.

[19 Jan 2019 19:31] Amit Unknown
Field circled in red which is throwing error on "edit"

Attachment: mysql edit error.PNG (image/png, text), 59.94 KiB.

[19 Jan 2019 19:32] Amit Unknown
I just hit it again right now:

Exception message:
Value 'Independent' is not suitable to be converted to a DateTime data type.

Exception thrown by:
MySqlDataTable.CopyTableData

Stack trace:
   at MySQL.ForExcel.Classes.MySqlDataType.GetValueAsDateTime(Object rawValue)
   at MySQL.ForExcel.Classes.MySqlDataColumn.GetInsertingValueForType(Object rawValue, Boolean escapeStringForTextTypes)
   at MySQL.ForExcel.Classes.MySqlDataTable.PrepareCopyingItemArray(Object[]& itemArray, Boolean escapeFormulaTexts)
   at MySQL.ForExcel.Classes.MySqlDataTable.CopyTableData(DataTable fromSourceTable, Boolean preserveChanges)
[25 Mar 2019 9:10] TEST TEST
from bug #94719 : this may not actually be related to DateTime but rather to an out-of-order column type conversion. Try using the sample database below and import the table into Excel - it will yield the same error.

' Copy-paste the below script and try to import the table "foo" into Excel
' using the Import MySQL data button
'
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' WARNING : WILL DELETE TABLE "FOO" IN DATABASE "TEST" IF SUCH
' A DATABASE ALREADY EXISTS
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CREATE DATABASE  IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;
USE `test`;
-- MySQL dump 10.13  Distrib 8.0.15, for Win64 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	8.0.15

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 SET NAMES utf8 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `foo`
--

DROP TABLE IF EXISTS `foo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `foo` (
  `b` int(11) NOT NULL,
  `a` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `foo`
--

LOCK TABLES `foo` WRITE;
/*!40000 ALTER TABLE `foo` DISABLE KEYS */;
INSERT INTO `foo` VALUES (1,'somestring');
/*!40000 ALTER TABLE `foo` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-03-20 14:17:30
[1 Apr 2019 23:29] Kent Bjorklund
Same issue here, except with appending.  My theory is that auto-mapping is corrupt and I cannot get to the options to turn it off because MySQL for Excel simply quits after I say OK to the error. Where are user settings stored such as mapping? Maybe I can delete them or disable auto mapping on startup.  Very frustrating dead end.  I tried to reinstall MySQL for Excel but again user settings must be stored somewhere so the issue persists. I thought maybe they were stored in the database but I don't see them.  Please advice so I can reinstall a fresh version of the tool and meet my deadlines.  Thank you.
[1 Apr 2019 23:49] Kent Bjorklund
Exception message:
Value 'ARTE 302' is not suitable to be converted to a DateTime data type.

Exception thrown by:
MySqlDataTable.CopyTableData

Stack trace:
   at MySQL.ForExcel.Classes.MySqlDataType.GetValueAsDateTime(Object rawValue)
   at MySQL.ForExcel.Classes.MySqlDataTable.PrepareCopyingItemArray(Object[]& itemArray, Boolean escapeFormulaTexts)
   at MySQL.ForExcel.Classes.MySqlDataTable.CopyTableData(DataTable fromSourceTable, Boolean preserveChanges)
[4 Apr 2019 17:22] Javier Treviño
Updated the MySql.Data (from MySQL Connector/NET) library, with a pre-release
version that workarounds a Server bug (introduced along in 8.0) where fetching schema information for columns returnis the rows in alphabetical order instead of ordinal order.
[7 May 2019 16:53] Amit Unknown
Hi guys, any chance this can be solved in this decade? :-) On and on we go.

Exception message:
Value '1335' is not suitable to be converted to a DateTime data type.

Exception thrown by:
MySqlDataTable.CopyTableData

Stack trace:
   at MySQL.ForExcel.Classes.MySqlDataType.GetValueAsDateTime(Object rawValue)
   at MySQL.ForExcel.Classes.MySqlDataColumn.GetInsertingValueForType(Object rawValue, Boolean escapeStringForTextTypes)
   at MySQL.ForExcel.Classes.MySqlDataTable.PrepareCopyingItemArray(Object[]& itemArray, Boolean escapeFormulaTexts)
   at MySQL.ForExcel.Classes.MySqlDataTable.CopyTableData(DataTable fromSourceTable, Boolean preserveChanges)
[7 May 2019 17:07] Amit Unknown
Please note the following steps:

1. Previous dump from AWS worked fine, had been using that for a while.
2. Today, got the latest dump from AWS, started MySQL server, imported the dump, tried to "Edit MySQL data" inside MS Excel -> didnt work.
3. Restarted SQL server, tried Excel again -> didnt work
2. Re-imported the latest dump, tried Excel again - > now its working.
[23 May 2019 19:05] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.8 release, and here's the changelog entry:

The Edit Data operation returned an error message intermittently (value
not suitable to be converted to a DateTime data type), even for tables
without a DATETIME column. This fix updates the library used for internal
connections to MySQL 8.0 server instances and the caching_sha2_password
plugin. In addition, the updated library resolves an error in which
fetched schema information for columns returns the rows in alphabetical
order, instead of ordinal order.

Thank you for the bug report.