Hacker News Reader

Comment by matwood | Hacker News Reader

I was just complaining about mysql yesterday. Data truncation and silent failures should not be warnings! They should be errors.

For example, 'str1' + 'str2' results in '0' and a warning in the default mysql config. A real rdbms should fail and tell you + is not for string concatenation in that system instead of running your query and perhaps overwriting your data and then warning you about it after the fact ('That query you just ran is broken, but we overwrote your data anyways - hey, here's a warning.').

Broken, horrible design.

View on HN

One that has bitten my colleagues lately:

Auto-increment primary keys get reused when, say, you create a record, then erase it, then pull the power plug. Mysql does not save the highest number used, meaning that you turn on the machine again, add a new record, and it has the same ID as the previous one.

My very subjective impression is that every time I have anything to do with Mysql, sooner or later, some weird thing like that jumps out and bites someone in the ass.

This effects InnoDB, but not MyISAM

Ok, but ... MyISAM is like racing down a hill on a bicycle with no brakes.

That's actually better than MyISAM, because at least experiencing it is thrilling...

If you are using MyISAM you might as well use a raw file system and assume you will lose your data at some point.

I dislike MySQL as much as the next guy, but I'm not convinced what you've posted there is true because you can query the auto-increment number - in fact you can even define it in SQL when creating the the table. So MySQL does store the increment number (and it's not even some volatile runtime value that might get lost during power cycles as I've bounced enough dev machines to notice if that was the case).

The autoincrement number is stored just fine unless you erase the last record and then stop Mysql, in which case that last one is lost and reused when you restart Mysql. This can cause problems if, for instance, you did something with that record between the time it was created and deleted and used that ID...

http://stackoverflow.com/questions/3718229/stop-mysql-reusin...

http://dba.stackexchange.com/questions/16602/prevent-reset-o...

I've done exactly what you've described and never ran into that problem. Quite the opposite in fact, on some dev servers I've have to manually fudge the auto increment number because it didn't automatically reset to zero after I'd flattened the table.

Like I said before, you can even create an empty table and define the auto increment to be 10000 if you wanted. So there is a persistent value that's stored. However I suspect the issue here is that I'm using a different MySQL engine to yourself.

Yup. I encountered MySQL's silent truncation behavior back in 2000, and it blew my mind that a tool whose primary job is to safely store my data would ever delete some of it without my explicit command. Been a Postgres person ever since.

Reply on news.ycombinator.com