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: 23
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: 2856
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: 23
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.

Post Reply