Databases, Data Modeling, and Data Access
View All Tags
Changing a MySQL setting failed with ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation. The word GLOBAL in the statement is the cause. Most of the time you don't want the whole server changed anyway, and one word fixes it.
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.
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.
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.
A list endpoint shouldn't return every row. Skip and Take give you one page at a time, which EF Core turns into MySQL's LIMIT and OFFSET. Getting it right takes three more things: a sort order rows can't escape from, the total count, and a limit on the page size. This guide builds all of it.
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.
