MySQL Bugs: #77472: Failing to Commit Changes

Bug #77472	Failing to Commit Changes
Submitted:	24 Jun 2015 11:24	Modified:	30 Sep 2015 22:27
Reporter:	Lars Butveit	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S3 (Non-critical)
Version:	1.3.4	OS:	Windows (10)
Assigned to:		CPU Architecture:	Any

Description:
I've been using MySQL for Excel for several years on Vista/Excel 2010.

I have now moved to an new compute with Windows 8.1 and Excel 2013 (Office Pro Plus 2013)

Using the MySQL Installer I have installed MySQL for Excel without error messages. During the installation the MySQL Installer told me I had to install Microsoft Visual C++ 2010, which I did, without error messages.

MySQL for windows works as expected until I press "Commit Changes" where the following error message appears immediately:
"There is not enough contiguous memory segment that can hold the SQL text being
Press Back to continue"
And the details box shows:
"Exception of type 'System.OutOfMemoryException' was thrown."

The changes are not committed to the database.

Any help is much appreciated.

 

How to repeat:
I have reinstalled twice, both Office and MySQL for Excel, with the same result.
I have tested the functionality on my old system, and it still works as before.

Are you able to provide a dump file (with some insert data) of a test table that presents such behavior?. Thanks.

I can try, if you give me a "how-to".

Can anybody help with this, please?
-Lars

Please update the bug with the information of the query being sent to the server when you hit "Commit", you can do that by clicking on Options on the panel where you selected "Edit MySQL Data" and selecting the option where you can preview SQL queries before these are sent to the server.

This means that when you hit Commit, you will get another dialog containing the SQL script sent to the server. If the SQL script is generated then the problem happens when the script is sent to the server, if you get this error before the SQL script appears after hitting Commit, then it means the error is thrown while trying to generate the SQL Script.

If the case is the 2nd one, then you will need to provide an exact set of steps to duplicate this and provide a SQL script that lets us regenerate the DB table you are editing along with its data.

Thank you.

Thank you for the update.
I did the test you described and I get the error messages after clicking "Commit". After closing the error message the SQL Preview box appears, but the text box is empty (no SQL statement)
So we have case 2.
I will generate a step-by-step and an SQL of my database.

I have now attached the SQL script for my failing table.

Please note that the MySQL for Excel component seem to work on all my other databases, just not this one.

Step by step of how to replicate the error:
1. Start Excel 2013 (on Windows 8.1)
2. Create Blank Workbook
3. DATA -> MySQL for Excel
(The MySQL "window" opens in the right hand side of Excel)
4. Double click my only Remote Connection
(a couple of seconds later the Schemas are shown)
5. Double click the correct Schema
(a couple of seconds later my 200+ tables appear in the list)
6. Scroll to "tam_joodb_models" and select it.
7. Click on "Edit MySQL Data"
8. Press OK to download all rows
(the table shows as expected after a few seconds and the  "Revert Data/Commit Changes" floating window appears.
9. Edit Cell G2 by adding some text (for example "nn") and press Enter
(cell turns light blue)
10. Press "Commit Changes" button
(immediately the mentioned Error message appears as a pop-up window: "There is not enough contiguous memory segment that can hold the SQL text string being"

I hope this is enough to describe the error.

-Lars

Have you had time to test my Table?

Hello Lars,
Thanks for the detailed steps and for the SQL file to recreate your table.
I have already tested this but could not duplicate the error, in my environment when I hit commit the SQL script is generated correctly without any issues.

So I will need more information:
1. More details about your environment (RAM, is this a physical computer or a VM?).
2. When the error appears, do you get a window that lets you see a full stack trace?? If so please supply the stack trace. If it does not appear in the error window maybe you can get it from the log file found at "%APPDATA%\Oracle\MySQL for Excel".

I think this error is being thrown by MySQL Connector.NET (used internally by MySQL for Excel to communicate with the MySQL Server), but I need to be able to reproduce the error to know for sure. I will keep investigating, but it would really help if you can provide the information requested above.

Thanks a lot for investing the time to report this bug.

Hi, sorry for the late reply (vacation this week...)
I'm on a new laptop computer, Intel-Core i7, 16GB Ram, Windows 8.1, 64 bit.

I have attached the log C:\Users\larbut\AppData\Roaming\Oracle\MySQL for Excel\MySQLForExcelInterop.log after I did another test today (dated 2015-07-19)

-Lars

I forgot: I don't see any windows with error log when the error log appears. Only the attached error message and the "Review SQL script"-window.

Hi Lars,
I am wondering why I can't reproduce, I am using Windows 8.1 and Office 2013, same as you. Recreated your table with data using the script you attached, but no error.

Is there any other specifics you can tell us about your environment??
The error log helped a lot to pinpoint where the code is failing, but we also need to be able to reproduce it in order for this to be fixed.

Thanks a lot.

Hi Javier,
Not sure what specifics you could need?
It's important to notethat the table in question (the one I sent you) is the only one I can't edit. Are there any PHPMyAdmin tricks I could try on the database/table?
Please see the attached video, showing whatI do when it fails, and see it work in another (larger)  table.

-Lars

Ok, the video was too large to upload here, so you can see it at this url:
www.tamiyabase.com/larbut/2015-08-03_20-58-43.mp4

Anything I can do to help solving this problem?
I need to update my database...

I observed one thing last night.
At 00:00 a "DOS" window appeared and the text said something about installing/updating something about MySQL. I did not get a screeshot of it, but it prompted me to say yes or no to an update. I pressed Yes.
As far as I could see the update did not go through, but the MySQL Installer opened showing that the only MySQL product I have installed is "MySQL for Excel" version 1.3.4 for X86 architecture. (which looks to be the latest)

I don't know if this is relevant or not for the error I'm seeing.

Hi Lars,
What you saw is a bug in the MySQL Installer that is fixed in the latest version (MySQL Installer 1.4.9), which you should be able to update to.

Thanks for the video you shot BTW.

I am still digging into this issue and think I found the code that is failing, but I can't repro in my box which has the same RAM and OS as yours.

Is your Office version a 32-bit one??  If so I will try to duplicate in a virtual machine with a 32-bit version of Office.

Yes, my office is 32 bit. (Professional Plus English)

Javier, if you like, I can test your software changes om my environment.

Lars,
I tested this on a VM with a 32-bit version of Windows, and again I could not reproduce the problem. I searched the internet for the exception that you got and that is why I thought this would be easily reproducible on a 32-bit OS.

Are you testing with exactly the same schema and data of the SQL script you attached?

As a workaround, please try disabling Optimistic Updates for your Edit Data session (right-click the black floating dialog and uncheck the option) and see if that fixes the problem.  The reason behind this is that the SQL queries being generated with optimistic updates on are way longer than without.

Hi, I'm "in production" on an online server, hosted at Hostgator, so I'm not in a test environment.

I can give you access to the server, but I will need your IP address for that.
Remember it's a live site...

I tested disabling Optimistic Updates, with no improvement.

Thanks for the quick response Lars.
I think giving me access to the server will make no difference since the problem is not at the server side but at the client (Excel add-in) side, so it is in your environment.

Do you have any way to test this on a different computer with similar environment as yours??  I already tried 3 different environments with no-luck to reproduce the error.

The exception being thrown according to what I could see at Microsoft forums is an odd one, it is not an error that is commonly thrown, but it is related to a memory assignment problem... and I need to pinpoint what triggers it.

Could Remote Desktop to my laptop be a way?

Would not help much since I will see the same you recorded on video. I believe you the bug exists, but I need to reproduce it and in that environment test a solution after finding one.

Maybe cloning your environment so I can play with it could be an option, but also I can see for many reasons that you wouldn't want to do that.

Again, have you tried this on another computer or setting up a Virtual Machine with similar environment?  If you could set up a VM where you can reproduce this then you could lend me the VM so I can then see why the error happens. What I really need is to see in Visual Studio the code that is failing and the environment configuration that causes the failure.

Nobody else has reported in the forums or on a bug report something like what you are seeing.

Hi, sorry for being away.
I don't know how cloning my PC would be done, or how it might affect me. If it's an easy task and relatively risk free task, I will consider it.

MySQL for Excel is running fine on the same database/table on my old laptop, so yes, it works fine on a different computer. (Running Vista if I recall correct) Would the spec of this machine help you in any way?

I'm not into VM, but if I get guidance in what to do, I will help as much as I can.

-Lars

Lars,
The specs of the computer where you don't see any problem do not help.
It is very strange that you get the error on a newer computer.
Good to know you are able to update the table you are interested to using your old laptop, I mean you have a workaround for now.

Cloning your hard drive would mean exposing everything you have in there, that is why I told you I see a ton of reasons why you would not want that.

Creating a VM image is easy using VirtualBox, the problem is the image file created would be very big, and recreating the exact same environment to reproduce the problem could be tricky. Still I would advise you to try this anyway, create a VM using same OS and same Office version, and try to reproduce the problem... if you can repro we can see if you can tell me how you created the VM so I can create it same as yours, or worst case scenario we would work out how I can download the VM image.

Again Lars, thanks for your help... I appreciate the time you have invested on this.  Since we cannot reproduce we could also close the bug, but I want to exhaust all alternatives to reproduce it before closing it.

Ok, I'll have a go with Virtualbox.
I'll get back to you.

Hi Javier,
I'm sorry, but I have given up on creating a VM. I can't figure out how.

-Lars

Still, I think I have fixed the problem.

I used the MySQL for Excel function called "Export Excel data to new table", selected all cells and, to a large extent, accepted the default suggestions to structure. (I had to change a couple of columns misunderstood as dates, actually being fractions) In other words, I sort of copied the database using MySQL for Excel.

Now I can load the newly created database and edit it using MySQL for Excel.

First I tried copying the old table using phpMyAdmin, but that did not help.

Anyway, I seem to be in good shape again :-)
Thank you vey much for your patience and support, which has been excellent.

-Lars

Perfect Lars, it is good to hear the problem you see is solved.
I do not understand how it got solved by doing that, but I am glad.
Will close this bug then.

Thanks.