3 ms·

You can't really corrupt a database that easily, can you? That's half the point of using a database, so you have transactions, etc.

by TillE 12y ago

btmorex 12y ago
not corrupt != everything is peachy At the very least, you need to be prepared for a possibly very long replay of logs. Also, a huge number of people run databases in a configuration that doesn't make those guarantees. For example, many people will run mysql (esp. less performant slaves) with innodb_flush_log_at_trx_commit = 0 for performance with the understanding that a failure might require manual fixes.
- seunosewa 12y ago
  A failure with that parameter will only cause transactions committed in the last second to be lost. The DB won't require manual fixes.
  - spudlyo 12y ago
    The DB won't require manual fixes. Losing a transaction that was committed upstream can lead to a bunch of manual fixes in replicated database clusters. What happens if rows inserted in that lost transaction are eventually updated? Replication breaks, you get paged, and now you're faced with either manually fixing the DB consistency error or by rebuilding the whole slave. Woe be unto you if this host is a replication hub with a bunch of slaves hanging off it.
servowire 12y ago
I don't remember the exact details, but I once had a DB admin flabbergasted because there was a non-numeric character in a field/row that was numeric. The DB engine crashed upon starting and could not clean it up. I think it was Oracle. If I remember correctly we had to get the vendor involved This happened after an unclean shutdown (power failure). The disk were RAID, but the cache battery was dead so there was some corrupt data written to the disk on block level. THis could happen too on a kill -9.
maaaats 12y ago
I've never corrupted the DB, but I have ended up with inconsistencies because I needed to do several related queries in a row. Because I didn't send them as a single transaction from my application some of them were run, some not. Edit: Especially when you have multiple systems that should be consistent. Deleting a user from the DB, but being killed before it can be removed from our auth system, for instance.
- spullara 12y ago
  The typical way of ensuring consistency in cases where the systems are unable to participate in a transaction would be to use a durable queue between the systems that can participate. So, in your Edit example, you would delete the user from the DB and add it the user to a queue in the same database to be deleted. Another process would then pull from that queue and attempt to delete from the auth system and only acknowledges the message once it is successful. You will still temporarily be in an inconsistent state but ultimately the operation will complete. This design only works for idempotent work.

You can't really corrupt a database that easily, can you? That's half the point of using a database, so you have transactions, etc.

by TillE 12y ago

You can't really corrupt a database that easily, can you? That's half the point of using a database, so you have transactions, etc.

btmorex 12y ago

not corrupt != everything is peachy At the very least, you need to be prepared for a possibly very long replay of logs. Also, a huge number of people run databases in a configuration that doesn't make those guarantees. For example, many people will run mysql (esp. less performant slaves) with innodb_flush_log_at_trx_commit = 0 for performance with the understanding that a failure might require manual fixes.

seunosewa 12y ago
A failure with that parameter will only cause transactions committed in the last second to be lost. The DB won't require manual fixes.
- spudlyo 12y ago
  The DB won't require manual fixes. Losing a transaction that was committed upstream can lead to a bunch of manual fixes in replicated database clusters. What happens if rows inserted in that lost transaction are eventually updated? Replication breaks, you get paged, and now you're faced with either manually fixing the DB consistency error or by rebuilding the whole slave. Woe be unto you if this host is a replication hub with a bunch of slaves hanging off it.

servowire 12y ago

I don't remember the exact details, but I once had a DB admin flabbergasted because there was a non-numeric character in a field/row that was numeric. The DB engine crashed upon starting and could not clean it up. I think it was Oracle. If I remember correctly we had to get the vendor involved This happened after an unclean shutdown (power failure). The disk were RAID, but the cache battery was dead so there was some corrupt data written to the disk on block level. THis could happen too on a kill -9.

maaaats 12y ago

I've never corrupted the DB, but I have ended up with inconsistencies because I needed to do several related queries in a row. Because I didn't send them as a single transaction from my application some of them were run, some not. Edit: Especially when you have multiple systems that should be consistent. Deleting a user from the DB, but being killed before it can be removed from our auth system, for instance.

spullara 12y ago
The typical way of ensuring consistency in cases where the systems are unable to participate in a transaction would be to use a durable queue between the systems that can participate. So, in your Edit example, you would delete the user from the DB and add it the user to a queue in the same database to be deleted. Another process would then pull from that queue and attempt to delete from the auth system and only acknowledges the message once it is successful. You will still temporarily be in an inconsistent state but ultimately the operation will complete. This design only works for idempotent work.