287 posts tagged with "MySQL"

I wanted to see which transactions were open, and MySQL said ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation. The account has full rights on its own database, and that's not enough. Here's why, and the right way to fix it without making your application's account more powerful.

An UPDATE hung for a while, then failed with ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. Raising the timeout is the tempting fix, and the wrong one, because the real problem is another transaction holding the row. Here's how to find it, and a surprise about what the error leaves behind.

An ordinary UPDATE failed with ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction, and MySQL threw away everything the transaction had done so far. Two transactions had taken the same rows in opposite orders. Here's how to see that for yourself, and the small change that stops it.

Part 2 created the tables, and they're still empty. In this part the API starts using them: you'll insert, read, change and delete rows through real endpoints, and read the SQL that EF Core sends to MySQL for every one of them. You'll also turn MySQL's refusals, such as a duplicate name, into clear HTTP answers, and stop two people from silently overwriting each other's changes.

When an UPDATE sets a column to the value it already has, did it affect the row? MySQL can answer either way, and a connection string option decides which you get. With Oracle's connector, the default is "yes", and that matters more than it looks. Turn the option on, and EF Core starts throwing an exception on some ordinary saves.

Add a CancellationToken parameter to a minimal API endpoint, pass it to EF Core, and the work can stop when the client goes away. That's the idea, so I measured what it does on MySQL with Oracle's connector. The token fires, and the next step stops. A statement that's already running keeps going until it ends, so the token isn't a substitute for a command timeout.

When a unique index refuses a value, EF Core throws an exception, and your API answers 500. But nothing broke: the client asked for something that already exists, and 409 Conflict is the right answer. This guide reads MySQL's error number from the exception, and catches only that error, with a small helper you can reuse.

When two people edit the same row, the second save overwrites the first, and nobody finds out. A concurrency token fixes this: a column that changes with every update, and that EF Core adds to the WHERE of each UPDATE. If it changed since you read the row, the update finds nothing, and you can tell the client. This guide adds one on MySQL.

A list endpoint usually has optional filters: a category, a search term, a checkbox. Since an EF Core query doesn't run until the end, you can add each Where only when its filter is present, and get SQL with exactly those conditions. This guide builds that, and shows the SQL for different requests.

EF Core gives you several ways to search text, and on MySQL with Oracle's provider they behave differently in ways that matter: some ignore case, some don't, and one treats % as a wildcard. This guide runs each one on the same data, and reads the SQL, so you can choose.