MySQL Bugs: #76287: Commit Changes error

Bug #76287	Commit Changes error
Submitted:	12 Mar 2015 12:26	Modified:	19 May 2017 21:26
Reporter:	Eric PIERUNEK	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S2 (Serious)
Version:	MySQL for Excel 1.3.4	OS:	Windows (Windows 8, Excel 2013)
Assigned to:		CPU Architecture:	Any
Tags:	Commit Changes, MySQL for Excel

Description:
With MySQL for Excel, I 'Edit MySQL Data' of a 'big' database (80 columns, 6000 rows)
I did some corrections in the cells, copying, moving values from fields to fields, respecting the data type (numerical, text...)
When 'Commit Changes' to the database, some records fails:
MySQL Error 1253:
COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
In some cases, cells that were not modified or are empty or had been emptied, report the same problem.
Deleting the cell and/or (re)typing the value doesn't solve the problem.
How does this happen as I just copy/paste some values, and as some copy/paste reports no errors and some other fails to commit?

With such a 'big' table, it's also difficult to find the error : just one cell is pink colorized.

When doing a second modification turn, the problem occurs on other cells even not modified. It seems to occurs randomly.

How to repeat:
Just use a database, modify some field and try to 'Commit Changes'

Suggested fix:
Fix in order help to identify the cell:
It would be helpfull to colorize the full line.
Or to focus on the 'erroneous' cell.

My actual workaround in order to save the modifications: I 'Cut/Paste' the entire row in another worksheet, for each cell that reports the problem.
Need to repeat the operation as long as the error occurs.
When the remaining modification finally commit, I 'Append Excel Data to Table'. (Fastidious, as each time I have to manually map the fields). Data import without problem.

Can you please supply a SQL script to recreate the DB and table you are working with? Even better if it contains some data.

This may not be a bug, MySQL for Excel is trying to send the text values using UTF8 (that supports international characters) but the table or some of the columns in that table are set to have a LATIN text encoding.

Are some of the text values containing special characters?

Also it would be very helpful if you can turn on the option to show the SQL queries before sending them to the server, and attaching a text file to this bug report containing the text generated by MySQL for Excel for the commit that would cause the error you report.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi,
A colleague had the same problem, however the fix was to change the default configuration of the column name to  latin1 - default configuration in the DB. I could not easily find where to change Excel from Latin1 to UTF8 (if at all possible).  Initially, just changing the table collation did not work as the column collation was not set to Table Default but to utf8 - default Configuration.
Steve

This sounds more like a configuration problem dealing with table and column collations. MySQL for Excel connects to the Server using UTF8 for the encoding of text between the add-in and the server.  This is in order to support characters from all languages.

If a table or server is configured to use latin1 for the character encoding, the server may throw an error.  Depending on the error and the scenario, this could not be necessarily a bug but a configuration issue.

In order to determine if this is a real bug in the add-in we need a SQL script to recreate the table being used, the values of the global variables for charset and collation used in the server, and steps to duplicate the problem.  Without this information we can't proceed to analyze and schedule a fix in case this is really a bug.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I can reproduce this bug.
It seems not to actually be related to character encoding or collation, since some cells/values can be edited and some other can't.

Below is the export of a sample table that has the bug. Use MySQL for Excel, select this table, click "Edit MySQL data".
Changing the second cell on the first line works OK (removing the first white space for instance). Actually, every edit on the first line seems OK.
Changing anything on the second line raises the error.

I use Windows 7 sp1 x84 and Excel 2013 x32. All updates applied.
I connect to a remote MySQL server. All details below :

-- phpMyAdmin SQL Dump
-- version 4.0.10.6
-- http://www.phpmyadmin.net
--
-- Host: mysql1.paris1.alwaysdata.com
-- Generation Time: Nov 16, 2016 at 12:24 AM
-- Server version: 10.1.13-MariaDB
-- PHP Version: 5.6.16

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 utf8 */;

--
-- Database: `mysql4xlbug_1`
--

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

--
-- Table structure for table `DEFBUG`
--

CREATE TABLE IF NOT EXISTS `DEFBUG2` (
  `ID` int(5) NOT NULL,
  `Type` varchar(19) DEFAULT NULL,
  `Blaze` varchar(206) DEFAULT NULL,
  `Creche` varchar(147) DEFAULT NULL,
  `CP` varchar(5) DEFAULT NULL,
  `Dpt` varchar(5) DEFAULT NULL,
  `ID_PP1` varchar(7) DEFAULT NULL,
  `BlazePP1` varchar(39) DEFAULT NULL,
  `PP1_pation` varchar(19) DEFAULT NULL,
  `PP1_Creche` varchar(71) DEFAULT NULL,
  `PP1_CP` varchar(3) DEFAULT NULL,
  `ID_PP2` varchar(7) DEFAULT NULL,
  `BlazePP2` varchar(194) DEFAULT NULL,
  `PP2_Pation` varchar(86) DEFAULT NULL,
  `PP2_Creche` varchar(67) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `DEFBUG`
--

INSERT INTO `DEFBUG2` (`ID`, `Type`, `Blaze`, `Creche`, `CP`, `Dpt`, `ID_PP1`, `BlazePP1`, `PP1_pation`, `PP1_Creche`, `PP1_CP`, `ID_PP2`, `BlazePP2`, `PP2_Pation`, `PP2_Creche`) VALUES
(0, '-', '-', '-', '-', '-', '-', '-', '-', '-', '-', '-', '-', '-', '-'),
(1, 'NatajlW 1', ' ZZZ WXPalaN, nalWT, WTBWVW, VaLWlp Wt ljVjWlW', '28, loW aogoXtW nanWt 97455 Xajnt PjWllW', '97455', '974', 'PP1D001', 'ñW Patljck VaLWlp', '', '', '974', 'PP2D001', '', '', ''),
(14, 'PlañatWol', 'CWlWNjCjña', '3, allàW alpBanXW Fjlljan 44120 VWltao', '44120', '44', 'PP1D013', 'ñ. nWnjañjn NjcajXW', 'PlàXjdWnt', 'LjWo-djt L''angWnaldjèlW 44120 VWltao', '44', 'PP2D013', 'ñ. nWnjañjn NjcajXW', 'PlàXjdWnt d''jnN GWXTjaN Wt ancjWn PlàXjdWnt dW CWlWNjCjña joXqo''ao 14 fàvljWl 2007', '23, njX loW dW la CalW 44120 VWltao');

/*!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 statement that caused the error is the following :

UPDATE `mysql4xlbug_1`.`DEFBUG2` SET `PP1_pation`='PràXjdWnt' WHERE `ID`=14 AND (('PlañatWol' IS NULL AND `Type` IS NULL) OR `Type`='PlañatWol') AND (('CWlWNjCjña' IS NULL AND `Blaze` IS NULL) OR `Blaze`='CWlWNjCjña') AND (('3, allàW alpBanXW Fjlljan 44120 VWltao' IS NULL AND `Creche` IS NULL) OR `Creche`='3, allàW alpBanXW Fjlljan 44120 VWltao') AND (('44120' IS NULL AND `CP` IS NULL) OR `CP`='44120') AND (('44' IS NULL AND `Dpt` IS NULL) OR `Dpt`='44') AND (('PP1D013' IS NULL AND `ID_PP1` IS NULL) OR `ID_PP1`='PP1D013') AND (('ñ. nWnjañjn NjcajXW' IS NULL AND `BlazePP1` IS NULL) OR `BlazePP1`='ñ. nWnjañjn NjcajXW') AND (('PlàXjdWnt' IS NULL AND `PP1_pation` IS NULL) OR `PP1_pation`='PlàXjdWnt') AND (('LjWo-djt L\'angWnaldjèlW 44120 VWltao' IS NULL AND `PP1_Creche` IS NULL) OR `PP1_Creche`='LjWo-djt L\'angWnaldjèlW 44120 VWltao') AND (('44' IS NULL AND `PP1_CP` IS NULL) OR `PP1_CP`='44') AND (('PP2D013' IS NULL AND `ID_PP2` IS NULL) OR `ID_PP2`='PP2D013') AND (('ñ. nWnjañjn NjcajXW' IS NULL AND `BlazePP2` IS NULL) OR `BlazePP2`='ñ. nWnjañjn NjcajXW') AND ((@OldCol14Value IS NULL AND `PP2_Pation` IS NULL) OR `PP2_Pation`=@OldCol14Value) AND (('23, njX loW dW la CalW 44120 VWltao' IS NULL AND `PP2_Creche` IS NULL) OR `PP2_Creche`='23, njX loW dW la CalW 44120 VWltao')

The error is:

MySQL Error 1253:
COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

I really wonder why we have all these 
AND (('<Value>' IS NULL AND `<Column Name>` IS NULL) OR `<Column Name>`='<Value>') ...

When I run this statement as a SQL command, I get no error but a "0 rows affected. (Query took 0.000X sec)".

Now if I run this SQL command:
UPDATE `mysql4xlbug_1`.`DEFBUG2` SET `PP1_pation`='PràXjdWnt' WHERE `ID`=14

It works OK (in phpmyadmin)... If MySQL for Excel used that command, I guess it would have worked OK

Last comment for tonight :
I think it  nailed it down to the use of "@OldColNNValue" in the SQL request, and the reason for the presence of this "@OldColNNValue".

This request works :

UPDATE `mysql4xlbug_1`.`DEFBUG2` SET `Creche`='28, loW aogoXtW nanWt 97455 Xajnt PjWllW' WHERE `ID`=1 AND (('NatajlW 1' IS NULL AND `Type` IS NULL) OR `Type`='NatajlW 1') AND (('ZZZ WXPalaN, nalWT, WTBWVW, VaLWlp Wt ljVjWlW' IS NULL AND `Blaze` IS NULL) OR `Blaze`='ZZZ WXPalaN, nalWT, WTBWVW, VaLWlp Wt ljVjWlW') AND (('28, loW aogoXtW nanWt 97455 Xajnt PjWllW truc' IS NULL AND `Creche` IS NULL) OR `Creche`='28, loW aogoXtW nanWt 97455 Xajnt PjWllW truc') AND (('97455' IS NULL AND `CP` IS NULL) OR `CP`='97455') AND (('974' IS NULL AND `Dpt` IS NULL) OR `Dpt`='974') AND (('PP1D001' IS NULL AND `ID_PP1` IS NULL) OR `ID_PP1`='PP1D001') AND (('ñW Patljcka VaLWlp' IS NULL AND `BlazePP1` IS NULL) OR `BlazePP1`='ñW Patljcka VaLWlp') AND (('blublo' IS NULL AND `PP1_pation` IS NULL) OR `PP1_pation`='blublo') AND (('braaa' IS NULL AND `PP1_Creche` IS NULL) OR `PP1_Creche`='braaa') AND (('974' IS NULL AND `PP1_CP` IS NULL) OR `PP1_CP`='974') AND (('PP2D001' IS NULL AND `ID_PP2` IS NULL) OR `ID_PP2`='PP2D001') AND (('' IS NULL AND `BlazePP2` IS NULL) OR `BlazePP2`='') AND (('' IS NULL AND `PP2_Pation` IS NULL) OR `PP2_Pation`='') AND (('' IS NULL AND `PP2_Creche` IS NULL) OR `PP2_Creche`='')

And this one does not:

UPDATE `mysql4xlbug_1`.`DEFBUG2` SET `Creche`='3, allàW alpBanXW Fjlljan 44120 Vwltao truc' WHERE `ID`=14 AND (('PlañatWol' IS NULL AND `Type` IS NULL) OR `Type`='PlañatWol') AND (('CWlWNjCjña' IS NULL AND `Blaze` IS NULL) OR `Blaze`='CWlWNjCjña') AND (('3, allàW alpBanXW Fjlljan 44120 VWltao' IS NULL AND `Creche` IS NULL) OR `Creche`='3, allàW alpBanXW Fjlljan 44120 VWltao') AND (('44120' IS NULL AND `CP` IS NULL) OR `CP`='44120') AND (('44' IS NULL AND `Dpt` IS NULL) OR `Dpt`='44') AND (('PP1D013' IS NULL AND `ID_PP1` IS NULL) OR `ID_PP1`='PP1D013') AND (('ñ. nWnjañjn NjcajXW' IS NULL AND `BlazePP1` IS NULL) OR `BlazePP1`='ñ. nWnjañjn NjcajXW') AND (('PlàXjdWnt' IS NULL AND `PP1_pation` IS NULL) OR `PP1_pation`='PlàXjdWnt') AND (('LjWo-djt L\'angWnaldjèlW 44120 VWltao' IS NULL AND `PP1_Creche` IS NULL) OR `PP1_Creche`='LjWo-djt L\'angWnaldjèlW 44120 VWltao') AND (('44' IS NULL AND `PP1_CP` IS NULL) OR `PP1_CP`='44') AND (('PP2D013' IS NULL AND `ID_PP2` IS NULL) OR `ID_PP2`='PP2D013') AND (('ñ. nWnjañjn NjcajXW' IS NULL AND `BlazePP2` IS NULL) OR `BlazePP2`='ñ. nWnjañjn NjcajXW') AND ((@OldCol14Value IS NULL AND `PP2_Pation` IS NULL) OR `PP2_Pation`=@OldCol14Value) AND (('23, njX loW dW la CalW 44120 VWltao' IS NULL AND `PP2_Creche` IS NULL) OR `PP2_Creche`='23, njX loW dW la CalW 44120 VWltao')

See the "@OldCol14Value" in the second request? 

I wondered why there was this "@OldColNNValue". I made some tests and it seems that if a value's length is greater than 48 characters, the "@OldColNNValue" is used and the error appears.
Try it. Create a varchar value longer than 48 characters, then try to commit the changes and see if it works. I made further tests and as soon as a value's length is greater than 48 characters, the bug appears.
Seems like a real bug to me...

Thanks so much Yves Gattegno.
All the information you included is VERY detailed and easy to understand.
I can verify the bug with the schema and data you supplied and your suspicions and assessment about the problem are totally accurate.

Posted by developer:
 
Fixed a problem with the assembled where clause for queries generated in Edit Data sessions when having the option to use optimistic concurrency; when empty strings were present in the editing MySQL table, the values where being incorrectly converted to null in the where clause.

Ensured the connection used to push Edit Data changes uses the same client character set the editing table has.

Added a friendly error message in case the editing table has a  Unicode character set that is not UTF-8 compatible since those are NOT supported client character sets in MySQL, so editing such a table is currently not possible.

Posted by developer:
 
Fixed on 1.3.7 build 3

Posted by developer:
 
Edit-data operations in which the SQL query used optimistic updates, and the data contained empty strings, produced errors during the commit to 
MySQL. Enhanced mapping of character sets and clearer error-message text were added to identify the use of client character sets that are 
unsupported in MySQL.