Reply
New Member
Posts: 1
Registered: ‎01-13-2020

MySQL LAST_INSERT_ID() always returns 0 when executed in Stored Procedure

MySQL instance every Stored Procedure now returns 0 for LAST_INSERT_ID() after an INSERT with EXECUTE even though the record is properly created! And if I manually execute the SQL insert, I get the LAST_INSERT_ID() just fine. So this is only happening in Stored Procedures using EXECUTE for custom-built SQL statements. Any ideas what's going on here? Note I'm not using Transactions and all of this code worked perfectly fine before my reboot.

This is the code that's been working for two months, without issue. Note this is in more than 30 Stored Procedures, and all of them are behaving the same way. Replace EXECUTE SQLStatement1 with the actual SQL statement and everything works. Note the EXECUTE does insert the record.

New Member
Posts: 5
Registered: ‎01-14-2020

Re: MySQL LAST_INSERT_ID() always returns 0 when executed in Stored Procedure

While I doubt this is the forum for it, but my last boss literally threatened my life if I made any stored procedures. As you have found out they are finiky, and difficult to troubleshoot. There has been no case in the past four years of working with MySQL where I actually wanted a stored procedure and just did my shenanigans in code.

 

tl;dr don't use stored procedures, do it in code