How to fix ERROR 1181: Got error during ROLLBACK in MySQL

The error 1181 message format is "Got error %d - '%s' during ROLLBACK", where %d is the actual underlying error code. Check your MySQL error log to see the original error that triggered the rollback failure.

On Linux/Unix:

tail -f /var/log/mysql/error.log
# or check the configured log path:
tail -f /path/to/mysql/data/host_name.err

On Windows:

type "C:\ProgramData\MySQL\MySQL Server 8.0\Data\hostname.err"

Look for messages like "Out of disk space" (error 28), "Disk full" (error 20), or "InnoDB: Error: page 12345 log sequence number" indicating corruption.

Out of disk space is the most common cause of error 1181. Verify available disk space:

On Linux/Unix:

df -h /path/to/mysql/datadir
# Check if below 10% free space

On Windows:

wmic logicaldisk get name,size,freespace

If disk space is critically low (less than 5% free):
1. Delete old binary logs: PURGE BINARY LOGS BEFORE '2024-12-01';
2. Remove old backups and temporary files
3. Increase storage capacity (add disk space or expand filesystem)
4. Consider moving MySQL data to a different volume with more space

Ensure at least 20% free space for safe operations.

InnoDB cannot write undo logs if the filesystem is read-only or permissions are incorrect.

Check filesystem status:

# Linux/Unix: Check mount options
mount | grep mysql

# If filesystem is mounted as read-only, remount it:
sudo mount -o remount,rw /path/to/mysql/datadir

# Check MySQL data directory ownership and permissions:
ls -ld /var/lib/mysql
# Should show mysql:mysql (or equivalent) with 755 or 750 permissions

# Fix permissions if needed:
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 755 /var/lib/mysql

For SELinux systems, verify SELinux is not blocking MySQL:

getenforce
# If returns "Enforcing", consider setting to "Permissive" for troubleshooting

Stop the MySQL server gracefully to allow pending rollbacks to complete:

# Linux/Unix
sudo systemctl stop mysql
# or
sudo service mysql stop

# Windows
net stop MySQL80
# or use MySQL Installer

After stopping, check for table corruption:

# Run myisamchk on MyISAM tables (if using them)
myisamchk -r /var/lib/mysql/database_name/table_name.MYI

# Or use innochecksum for InnoDB:
innochecksum /var/lib/mysql/database_name/table_name.ibd

Start MySQL and let it complete the recovery process:

# Linux/Unix
sudo systemctl start mysql

# Windows
net start MySQL80

Monitor the error log for recovery progress:

tail -f /var/log/mysql/error.log | grep -i "innodb\|rollback\|recovery"

InnoDB will perform crash recovery and complete any pending rollbacks. This may take minutes to hours depending on transaction size. Do not interrupt this process.

Watch for messages like:
- "InnoDB: Starting crash recovery from checkpoint LSN ..."
- "InnoDB: Completed crash recovery"
- "InnoDB: Started 0 rollback threads..."

If you see "Rolling back" without progress for extended periods, check disk space again and consider stopping/restarting.

If error 1181 appears during large transactions, increase the InnoDB undo log size:

Stop MySQL first, then edit my.cnf (Linux/Unix) or my.ini (Windows):

[mysqld]
# Increase undo log retention time (in seconds)
innodb_undo_log_retain_seconds = 3600

# For MySQL 5.7+, you can add undo tablespaces:
innodb_undo_directory = /var/lib/mysql/undo
innodb_undo_tablespaces = 2

Create the undo directory:

sudo mkdir -p /var/lib/mysql/undo
sudo chown mysql:mysql /var/lib/mysql/undo
sudo chmod 750 /var/lib/mysql/undo

Restart MySQL:

sudo systemctl start mysql

This provides more space for rollback operations and reduces the chance of hitting space limits during transaction undo.

Once MySQL recovers successfully, identify which transactions failed. Review your application logs to see which queries caused the error.

Best practices to prevent error 1181:

1. Keep transactions short – Minimize the time between the first data change and COMMIT
2. Monitor disk space – Set alerts when free space drops below 20%
3. Use consistent table engines – Avoid mixing InnoDB and MyISAM in the same transaction
4. Implement retry logic – Catch SQLSTATE HY000 and retry the transaction
5. Test rollback scenarios – Intentionally trigger rollbacks in staging to catch issues early
6. Enable binary logs – Helps with recovery if needed: log-bin = mysql-bin

Example retry pattern in Python:

import mysql.connector
from mysql.connector import errorcode

for attempt in range(3):
    try:
        connection = mysql.connector.connect(...)
        cursor = connection.cursor()
        cursor.execute("START TRANSACTION")
        cursor.execute("INSERT INTO table VALUES (...)")
        connection.commit()
        break
    except mysql.connector.Error as err:
        if err.errno == 1181:  # Error during rollback
            connection.rollback()
            time.sleep(2 ** attempt)  # Exponential backoff
            continue
        else:
            raise
    finally:
        cursor.close()
        connection.close()