28 posts tagged with "SQL"

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.

The character set decides which characters MySQL can store, and the collation decides how it compares and sorts them. Get them wrong and emoji fail to save, or café and cafe turn out to be "equal" when you didn't expect it. This guide checks both at the server, database and connection level, and proves what the default utf8mb4_0900_ai_ci really does.

Before you follow a guide, report a bug or plan an upgrade, you need to know exactly which MySQL you're talking to. MySQL tells you in several places, and one of them works without signing in at all. This guide shows five ways, with real output from MySQL 9.7.4 in Docker, and the one place where the version you see can mislead you.

The official MySQL Docker image already contains the mysql command-line client, so you don't need to install anything on your computer to run SQL. One docker compose exec command opens the client inside the running container. This guide signs in, runs the first few queries every new database deserves, and shows the small things that confuse beginners.

I ran SHOW PROCESSLIST in MySQL 9.7 to see who was connected, and the result ended with 1 warning. SHOW WARNINGS named it: Warning 1287, the old implementation behind SHOW PROCESSLIST is deprecated. Querying the Performance Schema's processlist table directly gives the same list, without the warning.

Entity Framework Core maps each database table to an entity class and gives you a DbContext to query them. Most of the time you read from a single DbSet, but sooner or later you need data that lives across two tables — exactly what a SQL JOIN gives you. In EF Core, the lambda-based Join() method does the same job, and it translates into real SQL that runs on the database server.

Everything below targets .NET 10 and EF Core 10, the current Long Term Support releases.