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: | |
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
[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.