#MySQL
Build APM on Windows, Part 4: Compile MariaDB 11.8 LTS
Part 4 of the Build APM on Windows series walks through compiling MariaDB 11.8.9 from source with CMake and Ninja, from preparing the tools and running the script to testing on port 3399 and explaining why collation must be pinned to utf8mb4_unicode_ci
No kWh Meter? Estimate Daily Electricity Costs from Current
Notes on converting CT current readings into energy usage and daily time-of-use electricity costs, from the three-phase power formula and the sampling interval built into it, through skipping outages and separating peak from off-peak with epoch arithmetic, to a daily summary table that made the web page seven times faster.
Reads from a Replica Must Show How Many Minutes Old the Data Is
replication can report a lag of 0 seconds while the data is stuck at yesterday, because these two signals measure different things — how to set up a monitor that checks both Seconds_Behind_Master and business data freshness, catches anyone writing directly to the slave, and has another check watching the monitor itself
Moving JSON Time Series to Columns Without Changing the Dashboard
I stored entire device frames as JSON arrays at one row per second. After two weeks, the tables had grown to 183 MB at the endpoint and 625 MB on the server, with JSON_EXTRACT queries that could not use an index — here is how I moved the data into int columns and had the API rebuild a sparse array without changing a single line of the dashboard.
Auditing prod with real customer data without leaving a single row
The system owner asked me to test the entire system on the live server holding customer data without leaving even a single junk row behind. My approach was to bootstrap Laravel myself and call controllers directly inside a transaction that I rolled back, with a COUNT of every table before and after as evidence.
Moving Computation from PHP to the Browser: 12 Seconds to 12 ms
A single page ran tens of thousands of subqueries and took 12 seconds to load. I fixed it by precomputing a summary table, sending a single 107 KB dataset, and moving the formulas to the browser, bringing computation down to 12 milliseconds, with a parity test that requires JS results to match PHP on every row.
MariaDB on defaults — a 128M buffer pool on a 55 GB RAM server
A 16 core server with 55 GB RAM and an SSD, but MariaDB still had the default 128 MB buffer pool. The result was 50 million disk reads in 5 days. The full before-and-after settings, service name and config path traps on DirectAdmin/Debian, rollback steps, and another 500 MyISAM tables waiting for conversion.
Charset Mismatch Silently Kills JOIN Indexes — an utf8mb4 Mirror
The old database uses tis620 and the new one uses utf8mb4. JOIN across databases, and every index immediately becomes decorative without a single error — how to prove it with EXPLAIN and build a mirror that converts tis620 to utf8mb4 during the dump, plus the sed trap that silently breaks tables.
One Query Hung for 54 Minutes, Causing Sitewide 504s via MyISAM Locks
A single 30 MB table caused 504s on every page across the site, even with 9.5 GB of RAM still free — I traced information_schema.PROCESSLIST to a single SELECT that had been stuck for 54 minutes on a MyISAM table, cascading into metadata locks across the server, with an immediate fix and ways to prevent it from happening again.
Migrating vBulletin 4.2.5 to Flarum: Lessons from 200,000 Posts
Migrating an old vBulletin 4.2.5 forum with over seven thousand members, over twenty thousand threads, and over two hundred thousand posts to Flarum — notes on pitfalls that cost real time, from guest userid 0 breaking FK constraints across an entire batch, nested md5 hashes for old passwords, and converting BBCode to s9e XML, to attachments stored on disk instead of in the DB and why I had to scp scripts instead of using heredoc
Recover MariaDB Redo Log Corruption with force_recovery and Reinit
MariaDB would not start after an unclean shutdown — a corrupted InnoDB redo log took the mysql.global_priv system table down with it. This is the recovery sequence I actually used, starting with innodb_force_recovery to make the data readable, then dumping each db before reinitializing the whole system, with one rule never to raise force_recovery higher than necessary.
Thai Data Migration Traps: VARCHAR, Idempotency, and OCR Vowels
Lessons from importing and migrating hundreds of thousands of Thai data rows — undersized VARCHAR columns silently truncating synthetic keys into collisions, idempotency requiring DELETE before INSERT to stop totals reaching 200%, collation treating su001 and SU001 as equal while storing both, and Thai vowels corrupted by OCR that need a normalization map before insert
Legacy Apps Break After Migration — MariaDB Strict Mode Errors
After moving a website from an old server to a new one, the registration form breaks even though the code has not changed — the cause is STRICT_TRANS_TABLES in the sql_mode of newer MariaDB versions, with fixes at 3 levels, from conf.d to per-app SET SESSION