dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
Requnix
Posts: 29
Joined: Thu 12 Dec 2019 19:41

dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Post by Requnix » Tue 14 Jan 2020 00:07

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
Devart Team
Posts: 2890
Joined: Fri 24 Jun 2011 14:17

Re: dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Post by alexa » Tue 14 Jan 2020 12:20

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
Posts: 29
Joined: Thu 12 Dec 2019 19:41

Re: dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Post by Requnix » Wed 15 Jan 2020 23:39

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
Posts: 29
Joined: Thu 12 Dec 2019 19:41

Re: dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Post by Requnix » Mon 23 Mar 2020 22:01

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
Posts: 29
Joined: Thu 12 Dec 2019 19:41

Re: dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Post by Requnix » Wed 25 Mar 2020 19:47

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
Devart Team
Posts: 2890
Joined: Fri 24 Jun 2011 14:17

Re: dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Post by alexa » Tue 31 Mar 2020 08:27

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

Requnix
Posts: 29
Joined: Thu 12 Dec 2019 19:41

Re: dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Post by Requnix » Tue 31 Mar 2020 15:57

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
Posts: 29
Joined: Thu 12 Dec 2019 19:41

Re: dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Post by Requnix » Tue 07 Apr 2020 21:39

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
Devart Team
Posts: 2890
Joined: Fri 24 Jun 2011 14:17

Re: dbForge Studio 2019 for MySQL interfering with LAST_INSERT_ID()?

Post by alexa » Wed 08 Apr 2020 14:42

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

Post Reply