Mysql Last_insert_id()

[Raz316]

I don't spose anyone here has any experience with the LAST_INSERT_ID() function losing it's value? I have a form that inputs data over multiple relational tables using the said function and every now and then a user will report it not working properly.

I'm at a loss

Cheers

[TheAnimus]

As much as I'd love to slate my second most hated DB product (first place goes to a certain sybase), odds are its a race hazard.

could you have two people try too add a row at once, and it go like?
person A calls Last_insert_id, gets a value, but just before commiting the row, person B calls last_insert_id, and adds their row, thus changing last_insert_id, but person A's call hasn't had the changes reflected.

I've had issues where a value i didn't expect was returned (a value for a row that dosen't exist... even thou no one has delete permissions on the table... niiice, so i simply reverted to using a limit and an order by to select it.)

[Raz316]

Cheers Animus,

I've tried to replicate the error but I'm having no luck and the rate in which data is input by our users would suggest that it's basically not a clash of attempts (I work at a recovery centre, and this form is filled when a new patient enters, usually at most once or twice a day).

I'll keep the limit/order by technique as a backup ; )

[TheAnimus]

ah ic, well there is another bug (you see in a recent post in the last 48 hours where i slated MySQL, and as people pointed out its clear i know absolutely nothing about which i'm talking) in my second most hated DB.

If you insert a row, and the transaction fails, then you get an increment. So you end up with 'gaps' in your auto generated ID. PATHETIC.

Just to check, your not trying to do this in a trigger are you? I mean nobody actually uses triggers in MySQL because they're too broken to be considered of any use..... but lets just check that?

I suppose the major question is why do you need to know this? Is it just for mainting your foriegn key relationship?

[Raz316]

Nah not using triggers.

I'm asking because a majority of the code was done by someone else and ideally I'd rather not change it if possible. Yeah it's for relationship reasons.

[Raz316]

Also, (just read your other posts re: mysql) what database would you go for? There aren't actually that many users here (40 maybe?) so is it even worth considering an alternative?

[TheAnimus]

well this is one of the reasons i can't stand MySQL, they held the belief that relationships where bad. And their code base shows this, because its bug riddled and un-tested.

All i could possibly suggest would be to have a logging table. Where you insert a row for each step that gets done, if you're only getting two a day, this won't get too big too soon, and then have a weekly job that trims anythign over a fortnight. After you've completed each object, insert a row, with the job guid , and the object id. This way you can see which table is screwing up, and your not getting an ID for the object made.

Is this whole thing been done as a transaction? Because that would make sense from a design point of view, its just transactions and mysql don't play well, because i think it was in 2005, they decided that transactions where bad. (seriously, i'm not making this up, they went on the record).

edit: SQLs i like, Postgre is free (under a BSD style license, so no nasty shocks like you get with mysql). My favourate atm is MS SQL thou, its just so damn powerful, free for upto 4GIG databases, but this isn't that hot if your not using .Net, and the xpath is kinda broken).

So as moving from MySQL to a propper database goes Postgre SQL is probably the better choice. But that would involve re-coding, you'll just have to decide if its needed.... The irony is, if your previous developer had ignored any good practice with SQL, odds are none of the problems would of arrison, this is why i get so pissed off with mysql because every now and again someone advocates its use, generally these are only the sort of people who think SQL means single table stored for me...........