Ошибка mysql got error 28 from storage engine

The error 1030 Got error 28 from storage engine in MySQL is a common issue faced by database administrators. It occurs when the disk storage on the server hosting the database reaches its maximum capacity, leading to insufficient disk space to store the database files. In such cases, the database is unable to write any more data to disk and the server returns the 1030 error message. To resolve this issue, several solutions can be implemented.

Method 1: Increasing Disk Space

To fix the «1030 Got error 28 from storage engine» error in MySQL by increasing disk space, follow these steps:

1. Check the available disk space on your server using the following command:

2. Identify the disk partition that MySQL is using. Usually, it is the /var partition.

3. Free up some disk space on the partition by deleting unnecessary files or moving them to another partition. You can use the following command to identify large files:

   du -h /var | sort -hr | head -n 10

4. If you cannot free up enough space, you can increase the disk space by adding a new disk or resizing the existing disk. Refer to your server documentation for instructions.

5. Once you have freed up enough space or increased the disk space, restart MySQL using the following command:

   sudo service mysql restart

6. Verify that the error is gone by running your MySQL queries.

Example code:

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        20G   19G  1.1G  95% /
tmpfs           3.9G     0  3.9G   0% /dev/shm
/dev/sdb         50G   60M   47G   1% /mnt/data

$ sudo rm /var/log/mysql/error.log
$ sudo service mysql restart

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        20G   17G  3.1G  85% /
tmpfs           3.9G     0  3.9G   0% /dev/shm
/dev/sdb         50G   60M   47G   1% /mnt/data

Method 2: Optimizing Queries and Database Structure

Optimizing Queries and Database Structure to Fix 1030 Got error 28 from storage engine

When you encounter the error «1030 Got error 28 from storage engine» in MySQL, it means that the disk space of your server is full or has reached its limit. This error is commonly caused by large or inefficient queries, which can quickly fill up the available disk space.

To fix this error using optimizing queries and database structure, follow these steps:

1. Identify the problematic queries: Use the MySQL slow_query_log to identify the queries that are taking up the most disk space. You can enable the slow query log in your my.cnf file or by running the following queries:

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
   SET GLOBAL long_query_time = 1;

2. Optimize the queries: Once you have identified the problematic queries, optimize them by using indexes, reducing the number of joins, and avoiding subqueries. Here is an example of optimizing a query by adding an index:

   -- Before optimization
   SELECT * FROM orders WHERE customer_id = 123;
   
   -- After optimization
   ALTER TABLE orders ADD INDEX customer_id_idx (customer_id);
   SELECT * FROM orders WHERE customer_id = 123;

3. Optimize the database structure: Review your database structure and make sure that it is normalized and efficient. This can include removing redundant data, splitting large tables into smaller ones, and using appropriate data types. Here is an example of optimizing a database structure by splitting a large table:

   -- Before optimization
   CREATE TABLE orders (
     id INT PRIMARY KEY,
     customer_name VARCHAR(255),
     order_date DATE,
     order_total DECIMAL(10,2),
     ...
   );
   
   -- After optimization
   CREATE TABLE customers (
     id INT PRIMARY KEY,
     name VARCHAR(255),
     ...
   );
   
   CREATE TABLE orders (
     id INT PRIMARY KEY,
     customer_id INT,
     order_date DATE,
     order_total DECIMAL(10,2),
     ...
     FOREIGN KEY (customer_id) REFERENCES customers(id)
   );

By following these steps, you can optimize your queries and database structure to prevent the «1030 Got error 28 from storage engine» error from occurring in the future.

Method 3: Increasing the Swap Space on the Server

When you encounter the «Mysql: how to fix 1030 Got error 28 from storage engine?» error, one solution is to increase the swap space on the server. Here are the steps to do this:

1. Check the current swap space usage by running the following command:

1. Create a new swap file with the desired size. For example, to create a 2GB swap file, run the following commands:

sudo fallocate -l 2G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile

1. Enable the new swap file by running the following command:

1. Verify that the new swap space is now available by running the following command:

1. To make the swap space permanent, add the following line to the /etc/fstab file:

/swapfile swap swap defaults 0 0

1. Restart the MySQL service by running the following command:

sudo systemctl restart mysql

By following these steps, you should be able to fix the «Mysql: how to fix 1030 Got error 28 from storage engine?» error by increasing the swap space on the server.

Method 4: Modifying the my.cnf Configuration File

To fix the «1030 Got error 28 from storage engine» error in MySQL, you can modify the my.cnf configuration file. Here are the steps to do it:

1. Locate the my.cnf file. It is usually located in the /etc/mysql/ directory.

2. Open the my.cnf file using a text editor.

3. Add the following line to the [mysqld] section of the my.cnf file:

   innodb_data_file_path = ibdata1:10M:autoextend

   This line sets the size of the InnoDB data file to 10MB and allows it to autoextend.

4. Save the my.cnf file and exit the text editor.

5. Restart the MySQL server to apply the changes:

   sudo service mysql restart

6. Verify that the changes have been applied by checking the MySQL error log:

   sudo tail -f /var/log/mysql/error.log

   If the error log does not show any errors related to the storage engine, then the issue has been resolved.

Here is an example of what the [mysqld] section of the my.cnf file should look like after adding the innodb_data_file_path line:

[mysqld]
innodb_data_file_path = ibdata1:10M:autoextend

Note that you can adjust the size of the InnoDB data file as needed. The important thing is to make sure that it is large enough to accommodate your database and that it is set to autoextend so that it can grow as needed.