MySQL Bugs: #83042: MySQL Error 1064 when appending values formatted as hh:mm:ss

Bug #83042	MySQL Error 1064 when appending values formatted as hh:mm:ss
Submitted:	19 Sep 2016 13:58	Modified:	20 Sep 2016 11:57
Reporter:	Stacey Greensall	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S2 (Serious)
Version:	1.3.6	OS:	Windows (Win10 Pro)
Assigned to:		CPU Architecture:	Any

Description:
I had requested for help with this issue in the support forum and was advised to create a bug report so that the issue could be replicated.

I have a table in excel that I want to use to append a MySQL table, using MySQLforExcel 1.3.6 
Some of the columns contains a custom format of time (hh:mm:ss), which outputs the following error... 

Inserted Excel data in MySQL Table `results` with the following statements:

1: INSERT INTO `worstat`.`results`
(`eventcode`,`champround`,`racecode`,`classcode`,`motonumber`,`ridercode`,`finishpos`,`laps`,`finishtime`,`pblap`,`pbmph`,`pbpercent`,`avglap`,`avgmph`,`avgpercent`,`stdev`,`statsfactor`,`slowlap`,`slowmph`,`slowpercent`,`overallpercent`)
VALUES
(106,1,1,16,1,146,5,11,02:11:10,00:11:08,30.1796407185629,88.9221556886228,00:11:19.8000000,29.6470588235294,89.3712054347535,00:00:16.4240000,3.981,00:12:25.1820000,27.0604026845638,88.6510322105922,88.9814644446562)

Error while appending rows...

MySQL Error 1064:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':11:10,00:11:08,30.1796407185629,88.9221556886228,00:11:19.8000000,29.6470588235' at line 4

I know it is the time values causing my issue, because when I blank out the time values everything is appended as expected.

I am using...
Windows 10 Pro
Excel 2013 
MySQL for Excel 1.3.6
MySQL Version: 5.6.32

How to repeat:
Here is the info required to create my table...
CREATE TABLE `results` (
	`key` INT(11) NOT NULL AUTO_INCREMENT,
	`eventcode` INT(11) NOT NULL,
	`champround` INT(3) NULL DEFAULT NULL,
	`racecode` INT(11) NOT NULL,
	`classcode` INT(11) NOT NULL,
	`motonumber` INT(11) NULL DEFAULT NULL,
	`ridercode` INT(11) NOT NULL,
	`finishpos` INT(11) NULL DEFAULT NULL,
	`laps` INT(11) NOT NULL,
	`finishtime` TIME NOT NULL,
	`pblap` TIME NULL DEFAULT NULL,
	`pbmph` DECIMAL(5,2) NULL DEFAULT NULL,
	`pbpercent` DECIMAL(5,2) NULL DEFAULT NULL,
	`avglap` TIME NULL DEFAULT NULL,
	`avgmph` DECIMAL(5,2) NULL DEFAULT NULL,
	`avgpercent` DECIMAL(5,2) NULL DEFAULT NULL,
	`stdev` TIME NULL DEFAULT NULL,
	`statsfactor` DECIMAL(5,3) NULL DEFAULT NULL,
	`slowlap` TIME NULL DEFAULT NULL,
	`slowmph` DECIMAL(5,2) NULL DEFAULT NULL,
	`slowpercent` DECIMAL(5,2) NULL DEFAULT NULL,
	`overallpercent` DECIMAL(5,2) NULL DEFAULT NULL,
	PRIMARY KEY (`key`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=100007031
;

Won't you need to change TIME types to TIME(6) - or not use strict_all_tables SQL-mode - as you - as far as I can see - use microseconds precision in a table column not specified with such?

Refer:
http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html.
http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict.

-- Peter
-- not a MySQL/Oracle person

Thanks for the two links
for a min, I thought microseconds could of been the cause. 
I experienced a similar quirk with appending decimal values.  
Excels increase/decrease decimal placement only hides trailing digits and MySQLforExcel still picks them up causing append errors, so I had to restrict the decimal placement inside the formula.

I am not intentioanlly using microseconds, but the values are the result of other formula.
If that was the cause, I could restrict the formula as I did before
so I simplified my table and did some more testing,...

-------------------------------------------------------------------
CREATE TABLE `time` (
	`time` TIME(6) NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;
-------------------------------------------------------------------

Inserted Excel data in MySQL Table `time` with the following statements:

1: INSERT INTO `testDB`.`time`
(`time`)
VALUES
(02:11:10)

Error while appending rows...

MySQL Error 1064:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':11:10)' at line 4
-------------------------------------------------------------------

If  my server configuration was causing the rejection surely I should not be able to do this from a thrid party query tool
1: INSERT INTO `testDB`.`time`
(`time`)
VALUES
(021110)

database result...
02:11:10.000000

I also tried without microseconds
-------------------------------------------------------------------
CREATE TABLE `time` (
	`time` TIME NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;
-------------------------------------------------------------------

but I get the same error when appending a single cell containing a time value...
-------------------------------------------------------------------
Inserted Excel data in MySQL Table `time` with the following statements:

1: INSERT INTO `testDB`.`time`
(`time`)
VALUES
(02:11:10)

Error while appending rows...

MySQL Error 1064:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':11:10)' at line 4
-------------------------------------------------------------------

There is definitely some quirk I am missing, because for the past 5years I have been uploading these tables to my server using SQLConverterExcel.
I recently upgrading to office 2013 which is not supported by my old tool, and I thought MySQLForExcel looked like a nicely packaged solution .

Hello Stacey Greensall,

Thank you for the bug report.
This is most likely duplicate of Bug #81970, please see Bug #81970.

Thanks,
Chiranjeevi.