📋

Description

This error indicates that MySQL failed to create a new table or tablespace because the underlying disk storage ran out of space. It commonly occurs when the data directory, temporary directory, or the OS partition hosting MySQL is completely full, preventing new file writes.

💬

Error Message

Create table/tablespace '%s' failed, as disk is full

🔍

Known Causes

3 known causes

⚠️

MySQL Data Directory Full

The dedicated storage location for MySQL database files (e.g., /var/lib/mysql) has exhausted its available disk space.

⚠️

Operating System Partition Full

The disk partition hosting MySQL's data, binaries, temporary files, or log files has no more free space.

⚠️

Temporary Directory Full

MySQL's configured temporary file directory (e.g., tmpdir) has run out of space, preventing new file creation for operations.

🛠️

Solutions

4 solutions available

1. Free Up Disk Space easy

Manually delete unnecessary files from the disk where MySQL data is stored.

1

Identify the disk partition experiencing the 'disk full' error. This is typically where your MySQL data directory (`datadir`) is located.

df -h

2

Navigate to the disk partition and identify large, unnecessary files or directories. Common culprits include old log files, temporary files, or unused backups.

cd /path/to/your/mysql/data/directory
ls -lhS

3

Delete the identified unnecessary files. Be extremely cautious and ensure you are deleting files that are not critical to your system or other applications.

rm /path/to/unnecessary/file.log
rm -rf /path/to/unused/directory

4

After freeing up space, retry the `CREATE TABLE` operation.

2. Optimize and Prune MySQL Data medium

Reduce the size of existing MySQL tables by optimizing them and removing old, unneeded data.

1

Connect to your MySQL server.

2

Identify large tables that may contain fragmented or outdated data.

SHOW TABLE STATUS LIKE 'your_table_name';
SELECT table_name, data_length, index_length FROM information_schema.tables WHERE table_schema = 'your_database_name' ORDER BY (data_length + index_length) DESC;

3

Optimize tables. This can reclaim unused space and improve performance.

OPTIMIZE TABLE your_table_name;

4

If applicable, delete old or unneeded rows from large tables. For example, removing old log entries or historical data.

DELETE FROM your_table_name WHERE some_timestamp_column < 'YYYY-MM-DD HH:MM:SS';
-- Consider using a specific date range or condition relevant to your data.

5

After optimizing and pruning, retry the `CREATE TABLE` operation.

3. Increase Disk Space or Relocate MySQL Data advanced

Allocate more physical disk space or move the MySQL data directory to a larger partition.

1

Determine the current `datadir` for your MySQL instance.

SHOW VARIABLES LIKE 'datadir';

2

If possible, expand the existing disk partition or add a new disk to your server. Consult your system administrator or cloud provider for this.

3

Alternatively, prepare a new, larger disk partition or volume.

4

Stop the MySQL service.

sudo systemctl stop mysql

5

Copy the entire contents of the current `datadir` to the new location. Ensure permissions are preserved.

sudo rsync -av /var/lib/mysql/ /path/to/new/mysql/data/

6

Edit the MySQL configuration file (`my.cnf` or `my.ini`) to update the `datadir` variable to point to the new location.

sudo nano /etc/mysql/my.cnf
# Find and modify the line:
datadir = /path/to/new/mysql/data/

7

Start the MySQL service.

sudo systemctl start mysql

8

Verify that MySQL is running correctly and retry the `CREATE TABLE` operation.

4. Configure InnoDB Tablespace Size (Advanced) advanced

Adjust InnoDB configuration parameters to manage tablespace growth, potentially preventing future disk full issues.

1

Understand InnoDB's tablespace management. For `innodb_file_per_table=ON`, each table has its own `.ibd` file. For `innodb_file_per_table=OFF`, all tables share a single `ibdata` file.

2

If `innodb_file_per_table` is OFF and the `ibdata` file is growing excessively, consider migrating to `innodb_file_per_table=ON`. This is a complex process involving re-creating tables and may require downtime.

3

If using `innodb_file_per_table=ON`, ensure the disk where the `datadir` resides has sufficient space. Individual `.ibd` files can grow large.

4

Consider `innodb_autoextend_increment` and `innodb_data_file_path` (if using shared tablespace) in your `my.cnf` for more fine-grained control over tablespace growth. This is typically for very specific scenarios and requires deep understanding.

5

After making any configuration changes, restart MySQL and retry the `CREATE TABLE` operation.

🔗

Related Errors

5 related errors

MySQL Error 3675: Disk Full During Table Creation