sql
Finding duplicate rows in MySQL
I saw this question online reading “How do I identify duplicate rows in MySQL” and it’s been gnawing at me so here’s my approach: Make a duplicate of your subject matter CREATE TABLE a2 LIKE a1; INSERT INTO a2 SELECT * FROM a1; Add a ‘checksum’ column to the new table ALTER TABLE a2 ADD […]
MySQL EXPLAIN Deception and Fraud
Okay, let’s get this straight. I wrote the headline to get some attention. Now that you’re hooked, I have to admit for full disclosure, I’m a MySQL fanboy. This post is simply to draw some attention to the fact that EXPLAIN is just one (very important) tool in a developer’s arsenal for diagnosing performance problems. […]
Comparing MySQL table structures across hosts
Have you ever had to quickly scan table structures to see if they were the same on two separate hosts? Eyeballing this can be a total pain. I wrote a quickie Bash script to automate this. Please note, this is NOT a thorough test. Just a glimpse or cursory check. A deeper look will reveal […]
Understanding binary and relay log disk space management for MySQL
D R A F T I’m writing this as an attempt to help non-database-administrators (nDBAs) understand how to triage disk space crises on MySQL hosts. Here’s the scenario: – One of your MySQL hosts is consuming disk space rapidly. You perform investigation at the operating system level and discover that the logs, not the data […]
Row Deletion MySQL Stored Procedure
Scenario: You’ve got a huge history table that’s getting too big to deal with. It’s grown out of your control and the application has an SLA of 100% uptime. What now? My solution: The answer is to archive rows off the system, then delete rows by primary key. DROP PROCEDURE IF EXISTS proc_delete_id_range_safe; DELIMITER $$ […]
Identifying InnoDB Foreign Key Constraints
When using InnoDB tables in MySQL, the option exists to apply foreign key constraints. Data modelers do this for various reasons, which I’m not going to explore here. What’s important is that these constraints are religion for DBAs (and client software) and have to be respected to maintain data integrity. Knowing the hierarchy of data […]
Using MySQL Stored Procedures for User Authentication
Scenario: You want to authenticate users into a private web site by accepting an e-mail address and a password. You don’t want to do a bunch of security on the front end however you do want to use a token on the client for various purposes. Also, you want the tokens to expire automatically and […]
Testing MySQL data migrations Part 1
On occasion as database administrators we have to migrate data for various reasons. No matter the reason or the origin, we have to test the migration to see if the result is what we expect. The question is: How? In Part 1 of this series, I’m going to explore a simple “smoke test” of a […]
MySQL Best Practice: Forget ALTER, use RENAME instead
MySQL is notorious for a few bummer scenarios. One of them is when you have a table with a couple hundred thousand rows or more and an indexed column or two, and you’ve been asked to add a column to meet a data model requirement. You know this will cause an outage or at least […]
