Bug #73911 Some tables can not be edited correctly
Submitted: 13 Sep 2014 11:03 Modified: 13 Oct 2014 18:38
Reporter: Pascal Leinfelder Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S1 (Critical)
Version:all Versions OS:Windows
Assigned to: CPU Architecture:Any

[13 Sep 2014 11:03] Pascal Leinfelder
Description:
The Description can Be found here in the Forum: http://forums.mysql.com/read.php?172,619915,619915#msg-619915

Short:
A Table which was editable with MySQL for Excel can now not be edited correctly any more. 
I'm able to send about 20 cells correctly to the Table but when i edit more then 20 at the same time the cells stay blue (version 1.2.1 and higher) and will not be edited in the database. 

How to repeat:
The change came up suddenly. I can't tell how to repeat the issue. 

Maybe the table is to big?

Suggested fix:
I found no solution.
I tested multiple environments and server + MySQL for Excel versions.
[16 Sep 2014 12:00] MySQL Verification Team
Please describe which table/fields with their values are you editing and check FK issue if autocommit is enabled. Thanks.
[16 Sep 2014 13:48] Pascal Leinfelder
Hi,
what do you mean exactly with FK issue?

The issue affects every cell in the Table with or without auto-commit enabled.

The table should be called s_article_attributes or s_article_test.
[16 Sep 2014 14:07] MySQL Verification Team
I meant when editing and commit data I got the below error:

MySQL Error 1452:
Cannot add or update a child row: a foreign key constraint fails (`jmdb`.`s_articles_test`, CONSTRAINT `s_articles_test_ibfk_2` FOREIGN KEY (`articledetailsID`) REFERENCES `s_articles_details` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION)

Deleted 0, Inserted 0, Updated 0 row(s).
[17 Sep 2014 23:33] Javier Treviño
Pascal, thanks a lot for taking the time to file the bug report and moreover, to supply the SQL script and the detailed video.

The bug is related to the attrXX column containing HTML text.
I could reproduce the problem, so thanks again for the bug report.
[18 Sep 2014 10:09] Pascal Leinfelder
I'm happy that i could help.

Maybe you find a workaround or a quick solution to stark work again on this table.
[18 Sep 2014 14:36] Javier Treviño
Yes, there is a workaround that could work for you while we fix this bug, the workaround will only work if you do not edit the attrXX columns of type TEXT.

You can right-click the Edit Data dialog and from the context-menu disable the use of optimistic updates, the only drawback of the workaround is if you work on a multi-user environment you could overwrite changes done by someone else while you are editing data.

More information on the optimistic updates feature in MySQL for Exce:
https://blogs.oracle.com/MySqlOnWindows/entry/optimistic_updates_for_edit_data
[21 Sep 2014 19:02] Pascal Leinfelder
Hi,
I just tested it to disable the optimistic update function.
For me it makes no difference. If i edit in the Table more then 20 cells it's still broken.  
I disabled over the button option the function "Use optimistic updates on all Edit Data sessions". Was this correct?
[22 Sep 2014 13:07] Javier Treviño
Yes Pascal, I believe that is correct, then no workaround is available for now. We will give a high priority to this bug.

Thanks.
[26 Sep 2014 22:41] Javier Treviño
Posted by developer:
 
Fixed some bugs related to the way SQL queries are assembled durnig an Edit Data session and to text values. One of the problems was related to memory allocation for the query text overflowing when many TEXT columns were present in the table. Another problem was related to text values used in the WHERE clause of UPDATE queries were not being properly escaped, this was only happening when optimistic updates were being used. Optimized the construction of WHERE clauses with optimistic updates and TEXT columns to use user variables to store the old column values referenced in the WHERE clause to save query text space.
[9 Oct 2014 16:01] Javier Treviño
Posted by developer:
 
Fixed a bug in the way where clauses for refreshing a data row are assembled so they use the correct data row version.
[13 Oct 2014 18:38] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.3 release, and here's the changelog entry:

Committing 20+ cell changes and new rows at the same time would fail to
commit.

Thank you for the bug report.