How to fix ERROR 1180: Got error during COMMIT in MySQL

The error 1180 message contains only a placeholder. The actual underlying error is logged separately.

On Unix/Linux systems:

tail -n 100 /var/log/mysql/error.log | grep -i "error"

On Windows:

Get-Content "C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\COMPUTERNAME.err" -Tail 100 | Select-String -Pattern "error"

Look for error codes like:
- Error 28: Disk full—requires freeing space immediately
- Error 5: I/O error—indicates hardware or file system failure
- Error 131: Transaction size exceeded—break transactions into smaller units
- Error 1114: Too many open files—increase system file descriptors

Many commit failures are caused by a full disk. Check available space:

# On Linux/Unix
df -h /var/lib/mysql

# Check inode usage too
df -i /var/lib/mysql

# On Windows (check the drive where MySQL data resides)
Get-Volume C:

If disk usage is above 90%, you must free space before proceeding:
- Remove old binary logs: PURGE BINARY LOGS BEFORE '2025-01-01 00:00:00';
- Archive or delete old backups
- Expand the partition or mount additional storage
- Restart MySQL after freeing space

Run this query in MySQL to identify transaction lock conflicts and waits:

SHOW ENGINE INNODB STATUS\G

Look for sections labeled:
- TRANSACTIONS: Lists active transactions and their state
- LOCK WAITS: Shows blocked transactions waiting for locks

If you find a blocking transaction, terminate it carefully:

-- First, identify the blocking process ID
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- Kill the blocking transaction (replace PROCESS_ID)
KILL PROCESS_ID;

Note: Use KILL QUERY for just the query, or KILL CONNECTION to close the entire session.

When importing large dump files, mysqldump uses SET autocommit=0 to wrap all INSERTs in a single transaction. If the table is very large, this can exceed transaction size limits or hit other constraints.

Solution: Strip the autocommit settings before import

# For gzipped dumps
zcat dump.sql.gz | sed '/set autocommit=0;/d' | sed '/commit;/d' | mysql -u root -p mydb

# For uncompressed dumps
sed '/set autocommit=0;/d' ; sed '/commit;/d' dump.sql | mysql -u root -p mydb

This allows MySQL to auto-commit each INSERT statement individually, reducing transaction size.

Alternatively, export the dump without autocommit=0:

mysqldump --extended-insert --quick --single-transaction mydb > dump.sql

If the error is not disk or lock related, adjust InnoDB settings in my.cnf or my.ini:

[mysqld]
# Increase buffer pool (50-75% of system RAM on dedicated servers)
innodb_buffer_pool_size=4G

# Increase log file size for large transactions
innodb_log_file_size=1G

# Increase number of log files
innodb_log_files_in_group=3

# For Galera Cluster, increase transaction size limit
wsrep_max_ws_size=512M

After editing, restart MySQL:

sudo systemctl restart mysql

If your application performs massive multi-statement transactions, reduce their scope:

Before (fails with error 1180 on large datasets):

START TRANSACTION;
INSERT INTO table1 VALUES (...); -- 100,000 rows
INSERT INTO table2 VALUES (...); -- 100,000 rows
UPDATE table3 SET ... WHERE ...; -- 50,000 rows
COMMIT;

After (commit in batches):

-- Batch 1
START TRANSACTION;
INSERT INTO table1 VALUES (...); -- 10,000 rows
COMMIT;

-- Batch 2
START TRANSACTION;
INSERT INTO table2 VALUES (...); -- 10,000 rows
COMMIT;

-- And so on...

Smaller transactions reduce the risk of exhausting buffer pool space or transaction size limits.

If MySQL becomes read-only after a failed commit and normal restart does not help, use InnoDB recovery mode:

[mysqld]
innodb_force_recovery=1

Valid values: 1-6 (higher values = more aggressive, less safe)
- 1: Skip corrupted pages
- 2: Prevent rollback of uncommitted transactions
- 3: Don't run undo logs
- 4: Prevent crash recovery
- 5: Treat incomplete log as correct
- 6: Do not access undo logs

After restart with recovery mode:
1. Export all data to a backup
2. Stop MySQL
3. Remove recovery setting
4. Rebuild the instance with exported data