Bug #87642 During Edit mode, Commit behaves unexpectedly after revert/refresh from DB
Submitted: 1 Sep 2017 17:15 Modified: 23 May 2019 15:06
Reporter: J Nielson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S2 (Serious)
Version:1.3.7 OS:Windows (Windows 7 Enterprise)
Assigned to: CPU Architecture:Any
Tags: commit, Edit, refresh

[1 Sep 2017 17:15] J Nielson
Description:
After connecting, selecting schema and table, and choosing to edit table data, I made changes to existing data and then successfully committed the changes via MySQL for Excel dialog.  Again, using the dialog, I refreshed data from the database.  Then, I made additional changes to existing data and again clicked Commit button in dialog.  During multiple tests, either (1) commit seemed to hang (hourglass displayed indefinitely when hovering over dialog; MySQL server log file showed no query processed), or (2) changes were successfully committed (MySQL server log file showed query processed), but MySQL for Excel continued to display changes with blue background and, after additional changes and a second commit, MySQL reported update had failed because original record could not be found.  In the latter case, based on error details, it seemed MySQL for Excel successfully committed the second change but was also trying to recommit the first change, which produced an error because the first change had already been made so no match could be found for the original record.  Note that committing a new record after refresh also produced errors (duplicate primary key) consistent with an attempt to repeat the commit.

Other relevant information:  Excel 2013; default options in MySQL for Excel Options dialog.

How to repeat:
Create a database with a single, small table.  Mine had 5 varchar columns of varying lengths and used one field as the primary key.  Populate the table with some small number of rows.  I used 15.

Open Excel and MySQL for Excel.  Create a connection to the above database.  Open the connection, select the database and table, and choose to edit the data.  Make a few changes and then commit them via Commit button on the MySQL for Excel dialog.  Then, click Revert Data button and Refresh Data from DB button on the subsequent dialog.  Afterwards, make a few more changes and attempt another commit.

Suggested fix:
Modify MySQL for Excel so that commits are correctly processed, even after data has been refreshed from the database.
[4 Sep 2017 12:18] Chiranjeevi Battula
Hello Nielson,

Thank you for the bug report.
I tried to reproduce the issue at my end using MS Excel 2013,  MySQL for Excel 1.3.7 but not seeing any issues in edit and commit data.
Could you please provide repeatable test case (create table, table data, screenshot, etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[5 Sep 2017 17:20] J Nielson
I've uploaded 4 files:  2 that should allow you to recreate my test database, one containing the screen shots you requested, and the last a mysqld.log excerpt of the edit session.  (Note that mysqld.log shows commit of first change--line 68--but shows no record of second commit.)

Note that test database demonstrates the first "bad" result in the original problem description, the commit that hangs; it does not demonstrate the second, in which MySQL for Excel seems to try to recommit already committed changes.  I saw both results with my original database, but it contains sensitive data so I cannot upload it.  If we cannot resolve the problem with the current test database, I'll try to create another that reproduces the second result.

Finally, you said in your response that, when trying to reproduce the problem, you saw no issues in editing and committing data.  Please note that the problem occurs only after editing, committing, and then refreshing the data from the database.  Subsequent edits and commit demonstrate the problem.

Additional, possibly relevant information:  MySQL version 5.1.73 in 64-bit Red Hat Linux Server release 6.7.  Also, I installed prerequisite Visual Studio 2010 Tools for Office Runtime, but subsequently installed both Visual Studio 2010 and 2012.  Could either have affected MySQL for Excel's use of original prerequisite?
[6 Sep 2017 10:07] Chiranjeevi Battula
Hello Nielson,

Thank you for your feedback and test case.
I could not repeat the issue using MS Excel 2013 and MySQL for Excel 1.3.7.
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thanks,
Chiranjeevi.
[7 Sep 2017 10:29] Chiranjeevi Battula
Hello Nielson,

Thank you for the feedback.
I could not repeat this issue at my end with the provided information. 
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.
[7 Sep 2017 16:37] J Nielson
I'm sorry you have so little interest in resolving this issue.  Given your responses, there seems no point in trying to continue discussion of it.  We will look elsewhere for a tool to meet our needs.
[10 Jan 2018 1:08] Javier Treviño
I can verify the issue described.
When committing data and then refreshing data from the database, any subsequent generated queries incorrectly include the queries for the already committed data.
[13 Dec 2018 15:43] joel allen
I can also reproduce. Windows 10 Enterprise, excel 2016.
[4 Apr 2019 17:42] Javier Treviño
Posted by developer:
 
Fixed a bug where committed data was not being recognized by the Edit Data dialog for subsequent commits.
[23 May 2019 15:06] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.8 release, and here's the changelog entry:

After editing, committing, and then refreshing the data from the database,
subsequent commits were not recognized by the Edit Data operation.

Thank you for the bug report.