Error
Error Code:
1114
MariaDB Error 1114: Table Full Error
Description
Error 1114, 'The table '%s' is full,' indicates that MariaDB cannot write new data to a specified table because it has reached its maximum storage capacity or the underlying file system is full. This typically occurs during INSERT, UPDATE, or CREATE TABLE operations when there's no more space to allocate for new records.
Error Message
The table '%s' is full
Known Causes
4 known causesInsufficient Disk Space
The physical disk where MariaDB stores its data files has run out of free space, preventing new data from being written.
Table Size Limit Reached
The specific MariaDB table or partition has reached its defined maximum size limit, even if overall disk space is available.
Temporary File Overflow
Large temporary tables or files created during complex queries have consumed all available disk space, leading to the error.
File System Inode Limit
The underlying file system has run out of available inodes, which are necessary for creating new files or extending existing ones.
Solutions
4 solutions available1. Increase Disk Space easy
Free up disk space or add more storage to the server where MariaDB is hosted.
1
Identify the disk partition where MariaDB data files are located. Typically, this is `/var/lib/mysql` on Linux systems. You can use `df -h` to check disk usage.
df -h2
If the partition is full, delete unnecessary files from that partition. This could include old logs, temporary files, or other applications' data that are not critical.
rm /path/to/unnecessary/file3
If deleting files is not feasible, consider adding more storage to the server. This might involve attaching a new disk, expanding an existing one, or migrating to a larger instance if using a cloud provider.
N/A (depends on infrastructure)4
After freeing up space, restart MariaDB to allow it to recognize the available space.
sudo systemctl restart mariadb2. Optimize and Clean Up Table Data medium
Reduce the size of the full table by removing redundant or old data and optimizing its structure.
1
Connect to your MariaDB server using the MariaDB client.
mysql -u your_user -p2
Identify large or redundant data within the problematic table. This might involve looking for duplicate records, old audit logs, or temporary data that can be purged.
SELECT COUNT(*) FROM your_table;
-- Analyze data patterns to identify candidates for deletion.3
Delete unnecessary rows. Be extremely careful and ensure you have backups before performing delete operations on production data.
DELETE FROM your_table WHERE some_condition_for_deletion;
-- Example: DELETE FROM audit_logs WHERE timestamp < '2023-01-01';
-- Example: DELETE FROM temporary_data WHERE created_at < NOW() - INTERVAL 7 DAY;4
After deleting data, optimize the table to reclaim disk space and improve performance. For InnoDB tables, this usually involves `OPTIMIZE TABLE` which rebuilds the table.
OPTIMIZE TABLE your_table;5
Check if the table is still full after optimization. If the issue persists, consider the 'Increase Disk Space' solution.
N/A3. Adjust InnoDB File Per Table Setting advanced
If `innodb_file_per_table` is OFF, all InnoDB data is in a shared tablespace. Turning it ON can help manage space better for individual tables.
1
Check the current value of `innodb_file_per_table`.
SHOW VARIABLES LIKE 'innodb_file_per_table';2
If `innodb_file_per_table` is OFF, create a backup of your database. This is a critical step as it involves significant data restructuring.
mysqldump -u your_user -p --all-databases > full_backup.sql3
Stop the MariaDB server.
sudo systemctl stop mariadb4
Edit the MariaDB configuration file (e.g., `/etc/my.cnf` or `/etc/mysql/my.cnf` or files in `/etc/mysql/conf.d/`). Add or modify the following line under the `[mysqld]` section:
[mysqld]
innodb_file_per_table = 15
Start the MariaDB server.
sudo systemctl start mariadb6
For existing tables, you will need to run `ALTER TABLE ... ENGINE=InnoDB;` for each table to move them to their own `.ibd` files. This can be a time-consuming process and requires careful planning.
ALTER TABLE your_table ENGINE=InnoDB;7
After converting tables, restart MariaDB again.
sudo systemctl restart mariadb8
If you encounter issues, restore from your backup.
mysql -u your_user -p < full_backup.sql4. Increase Table Size Limits (MyISAM) medium
For MyISAM tables, increase the maximum size of a table if it's a hard limit being hit.
1
Check the table type. This error is more common with MyISAM due to its fixed-size file limitations.
SHOW CREATE TABLE your_table;2
If the table is MyISAM, you might need to increase `max_heap_table_size` and `tmp_table_size` in your MariaDB configuration file (`my.cnf` or similar). These settings affect temporary tables and in-memory tables.
[mysqld]
max_heap_table_size = 256M
tmp_table_size = 256M3
Restart MariaDB after modifying the configuration file.
sudo systemctl restart mariadb4
Consider converting MyISAM tables to InnoDB, which generally handles disk space more dynamically and is the recommended storage engine for most use cases.
ALTER TABLE your_table ENGINE=InnoDB;