When developing monitoring plugins like Nagios checks for MySQL database health, we often face the challenge of testing edge cases - particularly how our system handles corrupted tables. Since corruption rarely occurs naturally in development environments, we need controlled methods to simulate this condition.

The most reliable way to corrupt a table is by directly modifying its files:

# First, identify the table files
SHOW TABLE STATUS LIKE 'your_table_name';

# Then stop MySQL service
sudo service mysql stop

# Navigate to the data directory (typically /var/lib/mysql)
cd /var/lib/mysql/your_database

# Corrupt the .MYD (data) or .MYI (index) file
dd if=/dev/urandom of=your_table_name.MYD bs=1 count=1000 conv=notrunc

# Restart MySQL
sudo service mysql start

This overwrites part of the table data file with random bytes, ensuring detectable corruption while keeping the file structure intact enough for MySQL to attempt reading it.

If you prefer methods that don't require service interruption:

-- Force a crash during operation
SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL innodb_force_recovery = 6;
-- Then abruptly kill the MySQL process

Or for MyISAM tables:

-- Create a test table
CREATE TABLE test_corruption (id INT PRIMARY KEY, data VARCHAR(255)) ENGINE=MyISAM;

-- Force corruption by manipulating internal pointers
UPDATE test_corruption SET id = id WHERE 1=0;
-- Then kill the MySQL process mid-query

After inducing corruption, verify it with:

mysqlcheck -c your_database your_table_name

Or within MySQL:

CHECK TABLE your_table_name FAST QUICK;

Always work on a development server and have backups. Document your testing procedure clearly to avoid accidental execution in production. Consider using Docker containers for isolated testing environments.

For CI/CD pipelines, you might create a dedicated test case:

#!/bin/bash
# Test corruption detection script
setup_test_table() {
    mysql -e "CREATE DATABASE IF NOT EXISTS test_corruption_db"
    mysql test_corruption_db -e "CREATE TABLE test_table (id INT)"
}

induce_corruption() {
    sudo service mysql stop
    sudo dd if=/dev/urandom of=/var/lib/mysql/test_corruption_db/test_table.MYD bs=1 count=500
    sudo service mysql start
}

run_monitoring_check() {
    ./check_mysql_corruption # Your Nagios plugin
    return $?
}

setup_test_table
induce_corruption
run_monitoring_check
# Cleanup afterward...

---

When developing monitoring solutions like Nagios plugins for database integrity, we face a unique challenge: how to properly test corruption detection when all tables are healthy. This article demonstrates safe methods to simulate table corruption in a controlled development environment.

MySQL tables can become corrupted through several mechanisms:

• Hardware failures or sudden power loss during writes
• Filesystem errors
• Storage engine bugs
• Manual manipulation of data files

For MySQL 5.0 on Ubuntu (and most versions), these approaches work:

1. Directly Manipulating Storage Files

# Stop MySQL first
sudo /etc/init.d/mysql stop

# For MyISAM tables (common in MySQL 5.0)
sudo dd if=/dev/urandom of=/var/lib/mysql/dbname/tablename.MYI bs=1 count=1000 seek=500

# For InnoDB (more dangerous - not recommended for testing)
# sudo dd if=/dev/urandom of=/var/lib/mysql/ibdata1 bs=1 count=100000 seek=1000000

# Restart MySQL
sudo /etc/init.d/mysql start

2. Using MySQL Debug Commands

MySQL includes debug commands that can simulate corruption:

-- For MyISAM tables
REPAIR TABLE test_table QUICK;

-- Then force corruption with (use with caution):
SET GLOBAL debug='d,simulate_myisam_corruption';

3. Manual Index Corruption

-- Create a test table
CREATE TABLE test_corruption (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE=MyISAM;

-- Insert sample data
INSERT INTO test_corruption VALUES (1, 'test'), (2, 'data');

-- Force index corruption
FLUSH TABLES test_corruption WITH READ LOCK;
\! sudo perl -i -pe 's/test/TEST/g' /var/lib/mysql/testdb/test_corruption.MYI
UNLOCK TABLES;

After creating controlled corruption, verify your plugin:

#!/bin/bash
# Sample plugin test
result=$(mysqlcheck -c testdb -u root -p)
if [[ $result == *"corrupt"* ]]; then
    echo "WARNING: Database corruption detected"
    exit 1
else
    echo "OK: No corruption found"
    exit 0
fi

Always repair test tables after validation:

REPAIR TABLE test_corruption;
or
mysqlcheck -r testdb test_corruption

For non-destructive testing consider:

• Mocking mysqlcheck output in test scripts
• Using MySQL's test suite corruption patterns
• Testing against pre-corrupted database dumps

Remember these techniques should only be used in development environments with proper backups.