Page 2 - MySQL

Since MySQL 8, tree structures and other hierarchical data can be resolved directly in SQL through WITH RECURSIVE, without needing recursive application logic or a fixed number of self joins. The basic syntax follows the SQL standard, but MySQL brings a few implementation details of its own, from the internal handling as a UNION loop to the configurable infinite loop protection through cte_max_recursion_depth. This article covers how recursive CTEs work internally in MySQL, where the typical pitfalls sit, and how a Magento category tree can be resolved with one in practice, performantly.

A single transaction left open can make the InnoDB undo log grow without bound, hold locks for minutes, and delay replication, without a single error message appearing anywhere. This article explains how long-running transactions arise in MySQL, how to reliably track them down with information_schema.innodb_trx, and which timeout and batch strategies prevent them in application code from the start.

Running E2E suites against an unknown database state produces flaky tests instead of reliable results. This article shows how to put Magento stores into an exactly known state before every test run, through API calls, CLI commands, or direct SQL fixtures, and how to reset that state quickly between runs without seeding everything from scratch every time.

Database integration directly in Nuxt Server Routes saves a separate backend project, but comes with its own pitfalls: connection pooling in serverless environments, migration workflows, and the choice between Drizzle and Prisma decide the maintainability and performance of the whole application.

Several gigabytes of swap usage while free -h simultaneously reports plenty of free memory looks paradoxical, but is usually proactive, intentional kernel behavior rather than a fault. This guide shows how to use Pressure Stall Information, vmstat, and smaps to distinguish harmless from actually problematic swapping, what role swappiness really plays, and when MySQL buffer pool configuration is the actual cause.

An oversized INT, a VARCHAR field that should really be TEXT, or FLOAT instead of DECIMAL for money add up in large tables to significant, often overlooked storage cost and performance loss. Choosing the right data type is one of the cheapest optimizations in the entire database design. This article explains the most important data type decisions with concrete sizing rules.

Anyone who rolls back an entire transaction just because a single step inside a larger batch operation failed is throwing away work already done. Savepoints let you return to a marked point inside a transaction with precision, without losing the changes that came before it. This article shows the complete SAVEPOINT syntax in MySQL, explains the behavior inside InnoDB in detail, and provides practical examples for batch imports and a PHP implementation with PDO.

Unencrypted connections between an application server and a database are an underestimated risk in production Magento environments: anyone with access to the network segment between the web server and the database server can read customer data, sessions, and credentials in plain text. This article shows how to consistently secure MySQL connections with TLS on both the server and client side, manage certificates cleanly, and avoid the typical pitfalls that show up in multi-store setups with several application servers.

A deadlock in InnoDB is not a bug, it is an unavoidable consequence of parallel transactions blocking each other. This article shows how to read the deadlock report from SHOW ENGINE INNODB STATUS, which patterns keep recurring in practice, and how retry logic together with consistent lock order systematically reduces deadlocks.

Trigger-based tools like pt-online-schema-change solve many problems but add synchronous overhead to every single write. gh-ost, built at GitHub, takes a fundamentally different approach: it reads changes asynchronously from the MySQL binary log, exactly like a regular replica, and applies them to a ghost table. On tables with very high, constant write load, that difference is what separates a migration nobody notices from one that visibly slows down production.