dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

[Requnix]

I'm encountering some VERY strange behavior when trying to debug and run stored procedures manually. When I EXECUTE a dynamically built SQL statement in a stored procedure, LAST_INSERT_ID() returns 0, but if I execute that exact same statement myself in the Query window, it returns the ID. Even stranger, when the LAST_INSERT_ID() returns 0, the record was still created!

Once again, the record is properly created! So even though the record was inserted into the table, LAST_INSERT_ID() returns 0.

I can set a breakpoint on the below DEALLOCATE line, go to a query window and execute the EXACT same statement that's being EXECUTEd and it works fine, returning the LAST_INSERT_ID().

I've spent hours trying to figure out this issue, and the only conclusion I can come up with is that dbForge is somehow engaging in different pool usage on the back-end of MySQL and it's causing LAST_INSERT_ID() to get out of sync when EXECUTE is called. Or perhaps a deeper bug with MySQL?

This is causing a LOT of problems when debugging. This is reproducible 100% of the time and ONLY happens with the below code:

Code: Select all

SET @mysql := var_sql_insert;
PREPARE SQLStatement1 FROM @mysql;
EXECUTE SQLStatement1;
DEALLOCATE PREPARE SQLStatement1;
        
SET var_index = LAST_INSERT_ID();

Note this happens with ANY INSERT that I try to execute; even valid ones that cause no error. Below is a simplistic example:

Code: Select all

INSERT INTO sourcing_item_data (detail_text) VALUES ('hello!')

Also note this issue is happening regardless of transaction state (commit, etc) and autocommit is ON by default.

[alexa]

We will investigate this issue and will answer you as soon as possible.

At the moment, you may be interested in the following https://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html

[Requnix]

Just a quick update; I finalized all of the proper error handling in my stored procedure structures today - there were some warnings and NOT FOUND errors taking place; and I had the UTF8 warning left and right. It's all fixed and resolved.

So I've confirmed with 100% reproduction that an EXECUTION of the stored procedure (e.g. CALL) works perfectly fine - the LAST_INSERT_ID always returns the proper value.

However, when I try to run that exact same SP with the debugger and the same IN parameters, LAST_INSERT_ID is always 0. So this appears to be some sort of connection/pool problem with dbForge using Debug mode.

[Requnix]

Just encountered this issue again; ran every test under the sun and figured out it's DEFINITELY a dbForge error/issue. The only way I was able to get around this was to somehow force the debugger to rebuild symbols (if it even uses them)/reset. The exact same code that worked on Friday (with no changes to the code or data of any kind) failed to work today - after a system reboot... Once again, LAST_INSERT_ID() continually returned 0 when it was putting the record in just fine. Note there is no connection pool change or any other documented events taking place that could result in this value.

This problem MUST be addressed and resolved. I simply do not know what the source is but it has wasted hours upon hours of time, and often renders the debugger completely useless.

This is a red flag bug. There's something very wrong with the underlying DevArt dbForge code that really 'breaks' the debugger and causes these issues.

[Requnix]

So to confirm; dbForge is breaking MySQL. As stated above, I'm pretty sure it's the debugger. My code will fail with LAST_INSERT_ID(). A restart of dbForge doesn't change anything, but if I restart MySQL, the problem disappears with the exact same code. Once I go in and start using the debugger, LAST_INSERT_ID() always returns 0, but if I execute the code without the debugger, it works fine. It's very frustrating.

Can somebody from DevArt please comment on this problem? This truly is a show stopper since it appears to break the entire debugging/SQL execution system.

[alexa]

We will fix this issue and will notify you once the fix is available for downloading.

[Requnix]

Hey @alexa, you mentioned a new version of dbForge is coming next month (April). Is it still on track for release, and will that new version include a fix for this issue?

Also, is there any current known workaround for this problem until the new release?

[Requnix]

By the way, I have confirmed that having a WARNING or NOT FOUND CONTINUE error handler seems to destroy the debugger and ensure variable watching and other features do not work. I hope the team knows this and April's release fixes this core red-flag issue.

[alexa]

The fix will be included in the next version 9.0 that should be released this month.

[victorharry588]

is there any current known workaround for this problem until the new release?

[Requnix]

While dbForge has forced me to write my own debugging system (which is just sad, because this is a red flag issue that should have been patched months ago since it's a 'show stopper' and it literally breaks MySQL) from what I can tell, omitting a CONTINUE handler seems to work around a number of the problems, but I haven't verified it addresses the LAST_INSERT_ID() issue.

[alexa]

We would like to let you know that we released dbForge Studio for MySQL, v9.0 Enterprise Trial Beta where the issue you reported is fixed https://www.devart.com/dbforge/mysql/studio/download.html

Thank you for your help in improving dbForge Studio for MySQL.

[Requnix]

Thanks! When is the official release planned?

[alexa]

It depends on how many bugs will be found and will need to be fixed in the beta version.

We suppose it should be released within a month's time.