MySQL Bugs: #86723: MYSQL for Excel won't save TIME back to MYSQL Database

Bug #86723	MYSQL for Excel won't save TIME back to MYSQL Database
Submitted:	16 Jun 2017 4:41	Modified:	23 May 2019 22:23
Reporter:	Rod Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S3 (Non-critical)
Version:	1.3.7	OS:	Windows (Windows 10)
Assigned to:		CPU Architecture:	Any
Tags:	MYSQL for Excel 1.3.7 error saving time to MYSQL, MYSQL for Excel won't save time, MYSQL time error saving in MYSQL to Excel

Description:
I have a time field in MYSQL and I can import it to Excel but when I try to save it back with anything than a NULL/blank value it gives me an error.

The error that I'm getting is: 

MySQL Error 1064: 
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':30:00 WHERE `id`=1497 AND `memberID`='0834' AND (('0834-15-0009' IS NULL AND `a' at line 1 

How to repeat:
The MYSQL Database I'm using is:

I just follow the wizard to connect to the database and import the data. I then try to edit any of the data and save it back, it doesn't have to be in the time field and it just gives me the error. 

This is the database and a standard kind of row. 

-- phpMyAdmin SQL Dump 
-- version 4.6.5.2 
-- https://www.phpmyadmin.net/ 
-- 
-- Host: 127.0.0.1 
-- Generation Time: Jun 13, 2017 at 08:31 AM 
-- Server version: 10.1.21-MariaDB 
-- PHP Version: 7.1.1 

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 
SET time_zone = "+00:00"; 

/*!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 */; 
/*!40101 SET NAMES utf8mb4 */; 

-- 
-- Database: `fafa1424_records` 
-- 

-- -------------------------------------------------------- 

-- 
-- Table structure for table `newrecords` 
-- 

CREATE TABLE `newrecords` ( 
`id` int(11) NOT NULL, 
`memberID` varchar(4) NOT NULL, 
`animalID` varchar(25) DEFAULT NULL, 
`nickName` varchar(50) NOT NULL, 
`animal` varchar(256) NOT NULL, 
`family` varchar(150) DEFAULT NULL, 
`code` varchar(10) DEFAULT NULL, 
`dateOfEncounter` date DEFAULT '1970-01-01', 
`encounterAddress` varchar(256) DEFAULT NULL, 
`postcode` varchar(256) DEFAULT NULL, 
`LGA` varchar(256) DEFAULT NULL, 
`encounterType` varchar(100) DEFAULT NULL, 
`animalCondition` varchar(256) DEFAULT NULL, 
`comment` mediumtext, 
`animalSex` varchar(30) DEFAULT NULL, 
`age` varchar(50) DEFAULT NULL, 
`initalWeight` varchar(20) DEFAULT NULL, 
`pouchCondition` varchar(256) DEFAULT NULL, 
`initialAssessor` varchar(256) DEFAULT NULL, 
`rehabilitatorName` varchar(256) DEFAULT NULL, 
`fateOfAnimal` varchar(256) DEFAULT NULL, 
`memberHolding` varchar(256) DEFAULT NULL, 
`dateOfFate` date DEFAULT '1970-01-01', 
`releaseLocation` varchar(256) DEFAULT NULL, 
`releasePostcode` varchar(256) DEFAULT NULL, 
`releaseGPS` varchar(50) DEFAULT NULL, 
`chipNumber` varchar(256) DEFAULT NULL, 
`rehabilitator1` varchar(256) DEFAULT NULL, 
`rehabilitator2` varchar(256) DEFAULT NULL, 
`rehabilitator3` varchar(256) DEFAULT NULL, 
`rehabilitator4` varchar(256) DEFAULT NULL, 
`date_transfer1` date DEFAULT '0000-00-00', 
`date_transfer2` date DEFAULT '0000-00-00', 
`date_transfer3` date DEFAULT '0000-00-00', 
`date_transfer4` date DEFAULT '0000-00-00', 
`timeOfCall` time DEFAULT NULL, 
`callerDetails` varchar(256) DEFAULT NULL, 
`contactNumber` varchar(20) DEFAULT NULL, 
`incidentDetails` varchar(1024) DEFAULT NULL, 
`memberContacted` varchar(256) DEFAULT NULL, 
`actionTaken` varchar(256) DEFAULT NULL, 
`finalised` varchar(5) DEFAULT NULL, 
`recordFinalised` int(1) DEFAULT NULL 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

-- 
-- Dumping data for table `newrecords` 
-- 

INSERT INTO `newrecords` (`id`, `memberID`, `animalID`, `nickName`, `animal`, `family`, `code`, `dateOfEncounter`, `encounterAddress`, `postcode`, `LGA`, `encounterType`, `animalCondition`, `comment`, `animalSex`, `age`, `initalWeight`, `pouchCondition`, `initialAssessor`, `rehabilitatorName`, `fateOfAnimal`, `memberHolding`, `dateOfFate`, `releaseLocation`, `releasePostcode`, `releaseGPS`, `chipNumber`, `rehabilitator1`, `rehabilitator2`, `rehabilitator3`, `rehabilitator4`, `date_transfer1`, `date_transfer2`, `date_transfer3`, `date_transfer4`, `timeOfCall`, `callerDetails`, `contactNumber`, `incidentDetails`, `memberContacted`, `actionTaken`, `finalised`, `recordFinalised`) VALUES 
(721, '0335', '0335-15-0003', 'Smokey', 'Frogmouth, Tawny', 'BN', '313', '2015-09-16', 'South West Rocks Road, ', '2440 - POLA CREEK', 'Kempsey Shire Council', 'Collision - Motor Vehicle', 'Injury to body', 'Found on the road at night, possibly hit by car.16', 'Unknown', 'Unknown', 'unkown', 'N/A', '0335 - Yvonne', '0335 - Yvonne', '', '0335 - Yvonne', NULL, '', '', '', '', '', '', '', '', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '05:12:12', '', '', '', '', '', '', 0); 

-- 
-- Indexes for dumped tables 
-- 

-- 
-- Indexes for table `newrecords` 
-- 
ALTER TABLE `newrecords` 
ADD PRIMARY KEY (`id`), 
ADD UNIQUE KEY `animalid` (`animalID`); 

-- 
-- AUTO_INCREMENT for dumped tables 
-- 

-- 
-- AUTO_INCREMENT for table `newrecords` 
-- 
ALTER TABLE `newrecords` 
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13709; 
/*!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 */;
--------------------------------------------------------------------------

The SQL statement that is being applied is 

UPDATE `fafa1424_records`.`newrecords` SET `timeOfCall`=03:30:00 WHERE `id`=1497 AND `memberID`='0834' AND (('0834-15-0009' IS NULL AND `animalID` IS NULL) OR `animalID`='0834-15-0009') AND `nickName`='Fideous' AND `animal`='Wallaby, Red-necked' AND (('M' IS NULL AND `family` IS NULL) OR `family`='M') AND (('1261' IS NULL AND `code` IS NULL) OR `code`='1261') AND (('2015-06-14' IS NULL AND `dateOfEncounter` IS NULL) OR `dateOfEncounter`='2015-06-14') AND (('Armidale road, 16' IS NULL AND `encounterAddress` IS NULL) OR `encounterAddress`='Armidale road, 16') AND (('2440 - WILLAWARRIN' IS NULL AND `postcode` IS NULL) OR `postcode`='2440 - WILLAWARRIN') AND (('Kempsey Shire Council' IS NULL AND `LGA` IS NULL) OR `LGA`='Kempsey Shire Council') AND (('Collision - Motor Vehicle' IS NULL AND `encounterType` IS NULL) OR `encounterType`='Collision - Motor Vehicle') AND (('No apparent distress' IS NULL AND `animalCondition` IS NULL) OR `animalCondition`='No apparent distress') AND (('' IS NULL AND `comment` IS NULL) OR `comment`='') AND (('Male' IS NULL AND `animalSex` IS NULL) OR `animalSex`='Male') AND (('Juvenile' IS NULL AND `age` IS NULL) OR `age`='Juvenile') AND (('697g' IS NULL AND `initalWeight` IS NULL) OR `initalWeight`='697g') AND (('N/A' IS NULL AND `pouchCondition` IS NULL) OR `pouchCondition`='N/A') AND (('Dianne Gorham' IS NULL AND `initialAssessor` IS NULL) OR `initialAssessor`='Dianne Gorham') AND (('0834 - Betty Booth' IS NULL AND `rehabilitatorName` IS NULL) OR `rehabilitatorName`='0834 - Betty Booth') AND (('Escaped From Care' IS NULL AND `fateOfAnimal` IS NULL) OR `fateOfAnimal`='Escaped From Care') AND (('' IS NULL AND `memberHolding` IS NULL) OR `memberHolding`='') AND (('2015-11-14' IS NULL AND `dateOfFate` IS NULL) OR `dateOfFate`='2015-11-14') AND (('' IS NULL AND `releaseLocation` IS NULL) OR `releaseLocation`='') AND (('' IS NULL AND `releasePostcode` IS NULL) OR `releasePostcode`='') AND (('' IS NULL AND `releaseGPS` IS NULL) OR `releaseGPS`='') AND (('' IS NULL AND `chipNumber` IS NULL) OR `chipNumber`='') AND (('0108 - Joy Mackay' IS NULL AND `rehabilitator1` IS NULL) OR `rehabilitator1`='0108 - Joy Mackay') AND (('1566 - Noeline Patterson' IS NULL AND `rehabilitator2` IS NULL) OR `rehabilitator2`='1566 - Noeline Patterson') AND (('0901 - Diane Gorham' IS NULL AND `rehabilitator3` IS NULL) OR `rehabilitator3`='0901 - Diane Gorham') AND (('1577 - Narelle Anderson' IS NULL AND `rehabilitator4` IS NULL) OR `rehabilitator4`='1577 - Narelle Anderson') AND (('2015-06-15' IS NULL AND `date_transfer1` IS NULL) OR `date_transfer1`='2015-06-15') AND (('2015-07-16' IS NULL AND `date_transfer2` IS NULL) OR `date_transfer2`='2015-07-16') AND (('2015-10-09' IS NULL AND `date_transfer3` IS NULL) OR `date_transfer3`='2015-10-09') AND (('2015-11-02' IS NULL AND `date_transfer4` IS NULL) OR `date_transfer4`='2015-11-02') AND ((12:20:00 IS NULL AND `timeOfCall` IS NULL) OR `timeOfCall`=12:20:00) AND (('' IS NULL AND `callerDetails` IS NULL) OR `callerDetails`='') AND (('' IS NULL AND `contactNumber` IS NULL) OR `contactNumber`='') AND (('' IS NULL AND `incidentDetails` IS NULL) OR `incidentDetails`='') AND (('' IS NULL AND `memberContacted` IS NULL) OR `memberContacted`='') AND (('' IS NULL AND `actionTaken` IS NULL) OR `actionTaken`='') AND (('' IS NULL AND `finalised` IS NULL) OR `finalised`='') AND ((2 IS NULL AND `recordFinalised` IS NULL) OR `recordFinalised`=2); 

Suggested fix:
Unknown, any help would be great

There seems to be an error when saving back to MYSQL in MYSQL for Excel when you have a TIME column. If you change the TIME column to a VARCHAR it will work but once you try to use any kind of value in 00:00:00 format it won't save back to MYSQL.

Hello Rod Miller,

Thank you for the bug report.
Could you please provide repeatable test case (sample excel data,screenshot, etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

Hello Rod Miller,

Thank you for your feedback.
Verified this behavior on MS Excel 2013, MySQL for Excel 1.3.7.

Thanks,
Chiranjeevi.

Screenshot

Attachment: 86723.JPG (image/jpeg, text), 187.53 KiB.

Sorry, I should have said that my version is Office 2016.

Rod

I also have the same issue with 1.3.7 and Excel 2016. Here is the test value statement.

('SITE','2017-07-03','ABCD',08:00:00,'EFGH',123456,'2017-07-03','TEST');

I noticed that there are no ' before and after the time 08:00:00 so I manually added them and it went thru successfully. Of course, it wouldn't make sense to do this to 2000+ rows.

I have the same issue. Office 2016, MySQL for Excel 1.3.7

Error while inserting 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 ':30:00,20:10:00,160),
(2,18674,'NEUR 4200','Adv Neuro Lab','2018-01-10 00:00:00' at line 4

no one assigned to this? this should be fixed.

Posted by developer:
 
This is being worked on, thanks for your bug report.

Posted by developer:
 
Fixed the SQL query assembled during an Edit Data operation when involving time fields to wrap their value in single quotes.

Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.8 release, and here's the changelog entry:

An error was generated when an Edit Data operation involved changing the
value of a date or time field. Now the value of each date or time field is
wrapped with single quotes and the edits are saved to the database.

Thank you for the bug report.