128 posts tagged with "SQL"

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.

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.

ExecuteDeleteAsync sends one DELETE to MySQL, without loading anything, for one row or thousands. It's shorter than loading a row and removing it. But its errors arrive differently, and that catches people out. This guide shows the call, the SQL, and how to handle a foreign key refusal.

SaveChangesAsync loads rows, changes them in memory and writes them back. When you already know the change, that's wasted effort. ExecuteUpdateAsync sends one UPDATE straight to MySQL, for one row or thousands. This guide shows it in use, reads the SQL, and demonstrates the two ways it can surprise you.

To update a row with EF Core, load it, change the property, and call SaveChangesAsync. There's no Update call in that, and EF Core sends an UPDATE with only the columns that changed. If nothing changed, it sends nothing at all. This guide shows that in the real SQL, and compares it with the Update method.

ToQueryString() gives you the SQL a LINQ query would send, as a string, without running it. It's handy for one query you want to look at, with no need to turn on logging for the whole app. This guide shows it with a constant and a variable.

A product belongs to a category, but a query for products doesn't load the category unless you ask. There are two ways to ask: Include and Select. They both produce a join and differ in how much they load. This guide runs each one on MySQL and reads the SQL, including what happens if you ask for neither.