287 posts tagged with "MySQL"

EF Core warned me that a migration "may result in the loss of data", and on MySQL that warning turned into an error: "Data too long for column 'Name' at row 2". Worse, the migration had already changed one column before it failed. This post shows how to find the values that don't fit, see exactly what the failed migration did, fix the data, and apply the migration again.

ERROR 1146 (42S02): Table '…' doesn't exist usually means exactly that. But sometimes the table is right there, just with different capital letters, and MySQL on Linux treats Products and products as two different names. This post shows the failing query, the setting that causes it, and how to choose table names that work the same on Linux, Windows and macOS.

ERROR 1264 (22003): Out of range value for column means the number you're saving doesn't fit the column's type. For a DECIMAL column, the limit isn't the number of decimals, which MySQL quietly rounds, but the number of digits before the decimal point. This post finds a column's exact limit, shows the difference between a value that's rounded and one that's refused, and fixes it from both sides: the column and the input.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails means you inserted or updated a row that points at a parent which doesn't exist. I got it from my own mistake: I guessed that a new category had Id 3, because it was the third one I'd created. It wasn't. This post shows why the guess was wrong, how to find the right Id, how to insert without guessing, and how to answer 400 Bad Request from a .NET C# API.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails means the row you're deleting is still in use: another table points at it through a foreign key, and that foreign key forbids the delete. The message even tells you which table and column. This post reads it piece by piece, finds the rows that block the delete, shows two ways to clear them, and turns the error into a 409 Conflict in a .NET C# API.

ERROR 1062 (23000): Duplicate entry … for key … means a unique index refused a value that already exists. Usually that's the index doing its job. The surprise in my case was which value counted as a duplicate: MySQL refused adapters because Adapters was already there. This post explains why, how to choose the behaviour you want, and how to handle the error cleanly in MySQL and in a .NET C# API.

MySQL enforces CHECK constraints, and it tells you when a row breaks one: ERROR 3819 (HY000): Check constraint '…' is violated. I met it twice in one afternoon: first when an EF Core migration tried to add constraints to a table that already held a bad row, then when a new row broke the rule. Both have the same cause and the same kind of fix, and in a .NET C# API the error can become a clear 400 Bad Request instead of a crash.

An EF Core migration script made with dotnet ef migrations script --idempotent failed in MySQL 9.7 at line 8 with ERROR 1064 (42000). Oracle's EF Core provider for MySQL writes the script's "skip what's already applied" checks as SQL Server-style IF NOT EXISTS(…) BEGIN … END blocks, and MySQL accepts that kind of block only inside stored procedures and other stored programs. The fix is a plain script that starts at the database's last applied migration.

A Web API needs somewhere to keep its data, and MySQL is one of the most popular places to keep it. This part connects an ASP.NET Core minimal API, written in .NET 10 and C#, to MySQL 9.7 through Entity Framework Core. It keeps the password out of the code, encrypts the connection, proves the connection works with a health check, and then looks at that same connection from inside MySQL so you can see exactly what happened.

An encrypted connection keeps your password and your data private on the way to MySQL. MySQL 9.7 sets up encryption by itself and uses it whenever it can, but the mysql client in Docker can show SSL: Not in use, and that is fine. This guide shows how to check any connection, why a Unix socket is different, and how to insist on encryption.