Latest Content

2019-03-27 10:56 UTC

Fun MySQL fact of the day: in defence of `auto_increment`

Monday's introduction to auto_increment maybe wasn't as glowing as it should have been, but we really shouldn't let one little ~~bug~~ well-documented issue stop us from using it. You see, auto_increment really is an invaluable tool, and today we'll consider why. ... read more

2019-03-26 11:49 UTC

Fun MySQL fact of the day: the table is a lie

Over the course of the last several weeks, you may have caught me telling partial truths or using "weird" or inconsistent words when talking about MySQL and InnoDB. And now, before we move on to the rest of this week, it's time to clear a few things up. ... read more

2019-03-25 09:23 UTC

Fun MySQL fact of the day: `auto_increment` is mostly monotonic

On Friday, I left you with a cliff-hanger suggesting that foregoing the use of foreign key constraints can result in undetectable account/identity takeovers, data leakage, data loss, corruption, and so on, and so on. And it can. And not only by accident, but also by a really fun MySQL ... read more

2019-03-22 09:36 UTC

Fun MySQL fact of the day: foreign keys are important

A Foreign Key is a table constraint that allows a database index to remain consistent based on the existence (or non-existence) of another row in a another table. For example, MySQL will prevent the removal of a referenced row until all other referencing rows have first been removed. It will ... read more

2019-03-21 10:17 UTC

Fun MySQL fact of the day: necessarily coarse locks

If, like me, you're curious why, in yesterday's example, InnoDB acquires a next-key lock before and a gap lock after the records it is updating with equality in a secondary index when using REPEATABLE READ (what a mouthful!), then today's Fun MySQL fact is for you! I found myself ... read more

2019-03-20 08:33 UTC

Fun MySQL fact of the day: the phantom record menace

Yesterday, we created an index to prevent a full table scan from locking the entire list_of_things table during a single UPDATE. I mentioned it was an improvement to the problem, but not a complete fix. Today, we'll see why. ... read more

2019-03-19 08:32 UTC

Fun MySQL fact of the day: tenant lock-out

A couple weeks ago, we very briefly touched on the subject about MySQL/InnoDB locking only single records. And since we're on the topic of multi-tenant schemas, I think it's a good time to focus on this subject. For now, we'll focus on using MySQL's default isolation, REPEATABLE READ. ... read more

2019-03-18 10:32 UTC

Fun MySQL fact of the day: `REPLACE` your expectations

So, if we agree that we should avoid using INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE, surely we can use REPLACE INTO to upsert a row, right?! And yes, you can, if you want a DoS ~~bug~~ feature or account/identity takeover vulnerability in your multi-tenant system. Yep, ... read more

2019-03-15 11:11 UTC

Fun MySQL fact of the day: `IGNORE` at your own peril

Yesterday, we saw how INSERT ... ON DUPLICATE KEY UPDATE can unknowingly result in not-completely-expected situations. To get around that, you may have thought to use INSERT IGNORE, instead. Often, INSERT IGNORE is used when attempting to insert a row that may already exist. The underlying expectation a ... read more

2019-03-14 11:33 UTC

Fun MySQL fact of the day: `ON DUPLICATE KEY LEAK DATA`

As developers, part of our job is ensuring system stability long after we are finished writing code. It is our responsibility to ensure the next developer in our place doesn't inherit a system littered with landmines and that our customers' data integrity and privacy is always a top priority. Today's ... read more

« Previous 4 5 6 7 8 9 10 Next »