Error 1181 occurs when MySQL fails to complete a transaction rollback due to internal errors like disk space issues or corrupted undo logs. Fix by checking disk space, repairing tables, and reviewing error logs.
Error 1181 (SQLSTATE HY000) is a critical MySQL error that indicates the storage engine encountered a failure while undoing a transaction. This error is reported by the server layer after InnoDB or another transactional engine fails to complete the rollback process. When you execute a ROLLBACK command explicitly or when MySQL automatically rolls back a transaction (due to a failed COMMIT, deadlock, or lock timeout), the storage engine logs undo changes to disk. If an error occurs during this undo process—such as running out of disk space, encountering corrupted undo logs, or hitting filesystem permission issues—MySQL raises error 1181. This is a critical error because it leaves the database in an uncertain state. The transaction may be partially committed or partially rolled back, creating the risk of data inconsistency.
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.errOn 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 spaceOn Windows:
wmic logicaldisk get name,size,freespaceIf 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/mysqlFor SELinux systems, verify SELinux is not blocking MySQL:
getenforce
# If returns "Enforcing", consider setting to "Permissive" for troubleshootingStop 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 InstallerAfter 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.ibdStart MySQL and let it complete the recovery process:
# Linux/Unix
sudo systemctl start mysql
# Windows
net start MySQL80Monitor 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 = 2Create the undo directory:
sudo mkdir -p /var/lib/mysql/undo
sudo chown mysql:mysql /var/lib/mysql/undo
sudo chmod 750 /var/lib/mysql/undoRestart MySQL:
sudo systemctl start mysqlThis 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()Error 1181 in Replication: In MySQL replication, error 1181 on the replica can occur during the replay of transactions from the binary log. If this happens, the replica may fall behind or stop replication. Check SLAVE STATUS and the relay log for details.
Savepoint Rollback: ROLLBACK TO SAVEPOINT can also trigger error 1181 if the savepoint context is corrupted. The error message may reference error codes like 153 (savepoint not found) during the rollback attempt.
Galera Cluster: In Percona XtraDB Cluster or Galera, error 1181 with error code 149 (deadlock) may indicate write certification failures. This is typically transient and should be retried by the application.
InnoDB Undo Truncation: Modern MySQL versions (8.0.14+) can now truncate undo logs. If you see excessive undo space, this feature may help. Check innodb_undo_log_truncate setting.
Emergency Recovery: If error 1181 persists and recovery doesn't work, you may need to restore from a backup. Use mysqldump from a hot backup or binary log replay to restore a consistent point-in-time.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL