MySQL MaxID returning stale value after new insert

For me I read this as “I need it for a particular reason” that without seeing a design of the intended operations I just can’t be sure of anything as you think you are now.

Fine, maybe you’re not sure, but you also shouldn’t assume his reasons are valid, especially for such a basic yet critical part of database design such as this. The very fact that he’s using this design demands an explanation as to why, and any competent db designer/developer would usually provide said reason with this type of question.

So without first establishing the validity of his design goal(s), you could just be enabling him to continue getting by with a flawed design rather than fixing it properly.

1 Like

I assume nothing about unknown things.

Well I don’t claim to be any sort of database guru. Just a network type who sometimes develops software and happens to have spent a couple years in the 1990s working for a client / server software house. The company in question specialised in migrating DB/2 green screen solutions to MSSQL and native Windows client’s by leveraging three tier principles. The vast majority of the business logic (data processing) would be encapsulated within the DBMS as views, stored procedures and triggers with remarkably few lines of SQL embedded in the client source code. Of course we now have Nth tier and micro-services and even fewer lines of SQL sent from the client.

I’m also just about old enough to have started my career in ‘physical’ electronics production and remember having to call the stores manager to issue the next available part# from his central register. From that perspective a stored procedure would seem to fit as a virtual analogue for calling the stores manager, and not be limited to integer values.

My former employer’s answer to a client needing MaxID would be to read it from a roll-up table and update the roll-up within the insert transaction or from a trigger on the target table. Logically the last part# and next part# are not the same thing.

Please, try to question the OP needs, understand his needs, and try to help him.
Seems like he has enough info to find a solution for his problem.
I’m not contracting anyone and not interested in reading resumes, and I’m also not interested in options for some particular unknown design your prior company has chosen.