MariaDB & MySQL: Error 28

Introduction

Any time you run a query on a database, your database management system pulls on system resources to make the query happen. For example, all queries generate temporary files which are then stored in a folder. These files are necessary for the query to be completed but also require space. If your database is unable to create these temp files for whatever reason, this can prevent your query from running. In the case of MySQL or its fork MariaDB, an error will be generated to let you know that the temp files were unable to be created due to a lack of space. This error is known as “Error 28”.

Example of the error here:

In some cases, the error may present itself like so:

“/tmp/MYfsHC0p' Errcode: 28 "No space left on device".

In the error shown above, /tmp refers to the location in which MariaDB or MySQL stores all of your temp files. The subdirectory located inside of your temp folder, in this case the MYfsHC0p folder, is where a specific query tried to generate temporary files but failed.

What Causes Error 28?

It is important to remember that this error will present itself when there is a lack of available space for our query to create and store its temporary files. Even if it appears as though there is plenty of space available on our system, if the query needs more than what is available we will see the error. Additionally, for Linux users, there are other things to take into consideration such as linked files that will be discussed later in this article. 

Relevance with Ignition

Ignition works in conjunction with the user’s database type of choice via a Java DataBase Connectivity(JDBC) driver. 

A JDBC, is essentially a bridge between Java applications and an SQL Database server. It allows for queries written on the Java application to be translated and executed in an environment that the Database server can understand.

This also means that any syntax requirements, errors, and other vendor specific properties will also appear in Ignition.

Case Study

A user reported having issues with his query in MariaDB. The customer was using Ignition to run a query that returned a total of 2401364 rows and took a total of 7.2 seconds to run. When using phpMyAdmin, the query ran without issue, but when writing the same query as a Named Query in Ignition, he received the following error:
“Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Error writing file '/tmp/MYfsHC0p' (Errcode: 28 "No space left on device")”
He received this error despite his installation of MariaDB running on a 3.5 TB storage device. 

Solutions

Solution 1: Move your temp folder to a location with more space

In order to direct our temp files to a larger storage device, you first need to locate the config file that tells MySQL and MariaDB where to store files.

To do this, follow the following steps:

For MariaDB
1. Run Command Prompt, or Shell for Linux, in C:\Program Files\MariaDB x.x\bin folder

For MySQL:
C:\Program Files\MySQL x.xx\bin folder.

2. Use the  “mysqld —-help —-verbose” command line.

3. This will return several lines, but if you scroll to the top of the lines that are returned, you will see a line saying: 

“Default options are read from the following files in the given order:

C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL X.X\my.ini C:\Program Files\MySQL X.X\my.cnf C:\Program Files\MySQL X.X\data\my.ini C:\Program Files\MySQL X.X\data\my.cnf”
Example with MySQL on Windows:

These files are listed in order of what is searched for first. However, the last file that is found is what will be read. In the example given above, C:WINDOWS\ is the directory that will be searched first for a config file, however, if a file is found in C:\Program Files\MySQL X.X\data\, that will be the config file that is used.

Follow the path where the my.cnf or my.ini file is located and open that file in your text editor of choice. Search for “[mysqld]“ and add your desired file path in place of the existing one.

For example:
“tmpdir = C:/mysql/temp”

  When selecting a new path for your tmp folder, be sure the file path you choose is located on a storage device with enough space. 

Solution 2: Adding Indexes to your Table 

Non-Indexed Queries:
If you believe you have enough space, one thing to note is that selections on columns without indexes quickly use up large amounts of temporary disk space. 

Indexes for Queries serve the same purpose as an Index for a book. Imagine, you are given a 100 page math textbook and are asked to find the section on multiplication. Without an index to show you where it is, you would have to search through each page of the textbook until you found that section. 

Similarly, when you run a query without an index, your DBMS searches through your entire table to find the data that you are searching for. For tables that contain large amounts of data, this creates unnecessary amounts of load on the system and fills the temp folder quickly with temporary files.

Adding Indexes:
Here is an example of a SELECT * query on my sample table titled group_table. The query was completed in approximately 0.142 seconds.

Let’s index a value now. Use the “valveDischarge” key. To create an Index for the valveDischarge key, run the following query:

CREATE Index “Index nameIX_group_table_valveDischarge ON group_table (valveDischarge ASC)

MySQL Workbench also lets you create a new Index by right clicking the Indexes folder under the table. You have an option for Clustered, NonClustered, Spatial, Primary XML. Unique vs. Non-Unique.

Check MySQL workbench after executing the query. For this example there is an index titled “IX_group_table_valveDischarge”.

After running the query in Ignition’s Database Query Browser again, the query only takes .058 seconds.

Providing an index for your query reduces the amount of resources required to run the query and, in turn, makes it more efficient. Now, with larger queries, indexes reduce the amount of temp files that need to be stored and will help to avoid quickly filling up your temp folder. 

Solution 3 (Linux Only): Using lsof+L1 to locate linked files

When mysqld(the true name for your MySQL/MariaDB program) is terminated, temporary files are automatically removed. However, for Unix users, this process is a little different. Unix does this by “unlinking” the file after it is opened which means that the file name no longer shows in the directory listings. Sometimes, temp files can still remain open after being unlinked. A perfect example of this is if a process is still using the unlinked file. A case such as this results in an unlinked file that is still occupying space.

For Linux users, the “lsof+L1” command can be used to help identify any unlinked files that are still occupying space on your system. The “lsof” portion of the command lists all open files while the  “+L1” portion of the command requests to show all files that have less than 1 link (0 links). 

Here is an example of the lsof+L1 command being used:

Additional Note for Linux users:

“low disk space” and inode exhaustion: 

It is possible for linux users to receive “low disk space” or “insufficient disk space” errors despite seemingly having plenty of available disk space. This is commonly due to the storage device running out of available index nodes, commonly referred to as inodes.

Every file on your storage device has an inode assigned to it. These inodes serve as an index for the file that also contain information such as the file’s creation date, the number of links it has, ownership, etc. A file system that has several small files can result in inode exhaustion. This is what occurs when your system runs out of available inodes. This will result in the “low disk space” errors mentioned earlier.

Inode exhaustion is especially common when dealing with cloud environments that have smaller storage space.

To find out if your system is experiencing inode exhaustion, simply open your terminal and use the “df -i” command to check inode usage.