tech rant: mysql

[nolly]

I would like to give a serious clue-by-four beating to whoever thought it was a good idea to implement last_insert_id() many versions before adding transactions to MySQL. In a transactional context, it's a possibly useful function, though I prefer specifying what I want the last ID from, a la PG's currval. Outside of one? Dangerously unreliable, a concept one or more of my predecessors completely failed to grasp.

[end rant]

Comments

[allaboutm-e.livejournal.com]

I'd send sympathy and understanding, but I don't actually understand the rant -- too high tech for moi... so sympathy only, it is

[nolly.livejournal.com]

I cou8ld probably exoplain it in person, with hand-waving, but yeah, I didn't expect anyone who wasn't already familiar with the topic to follow in detail.

[gunslnger.livejournal.com]

MySQL isn't transactional? That's sucky.

[nolly.livejournal.com]

I believe current releases are, but not the older version I'm currently working with, which does have last_insert_id().

[dancingguy.livejournal.com]

MyISAM isn't transactional, INODB is.

How are you getting burned by last_insert_id ()? I'm using it in my code, and to the best of my knowledge I'm not getting burned by it.

[nolly.livejournal.com]

Presumably, you're also using a version more recent than 3.23 -- and yes, we're in the process of migrating away from that (migrating to PostgreSQL 8.3), which is why I'm digging through all the crusty old code.

Due to the non-transactional nature of the interactions in the current system, occasionally another insert happens between the insert and the last_insert_id call, and last_insert_id will return an id from another table altogether.

It's not common, but it is possible.

Also, certain people in the past of this codebase wrote overly clever functions which have been annoying to convert to Postgres.

[dancingguy.livejournal.com]

Ah. Yes, that bite you in the ass in certain situations. :-(

Happily, I took a look at my code, and it turns out I'm not using that call after all. :-)