MySQL Survival Guide

As I continue working with MySQL, I’ve come across a few quirks and issues that I wish I had known earlier. This blog post highlights some of the key lessons learned, and I will update it as I discover more interesting findings.

The Upsert Problem: Auto-Increment ID Gaps

When performing an upsert (a combination of an insert and an update), MySQL can create gaps in auto-incremented IDs. This happens because failed insert attempts still consume an auto-increment ID. You can check this stackoverflow issue.

This behaviour is not applied to natural keys

I wondered if the same issue exists in PostgreSQL and I created this little test and behold, there is no garbage IDs.

Possible Solutions:

• Use PostgreSQL

• Use BIGINT

• https://www.percona.com/blog/avoiding-auto-increment-holes-on-innodb-with-insert-ignore

AWS RDS MySQL: Non-Strict SQL Mode by Default

AWS RDS comes with non-strict SQL Mode causing errors such as overflow to raise warnings instead of errors. This issue can be fixed with settings SQL Mode after connecting to the RDS.

I did a bit of googling and I found this issue someone else also encountered this issue and asked why this behaviour isn't implemented.

You can check sql mode of the MySQL server by running the following query. DBeaver sets STRICT_TRANS_TABLES flag to the connection by default so its best to run this with mysql CLI.

SELECT @@sql_mode;

ENUM Fields: Beware of Reordering

Using ENUM in MySQL has its own set of caveats. If you ever need to reorder ENUM values, it triggers a full table rebuild.