1 Discovery of problems

The MySQL database, which has been good all the time, suddenly reported an error today. It was discovered by users when they queried data (web application). Take a quick look at the application log:

Caused by: java.sql.SQLException: Got error 28 from storage engine
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2190)
    at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2046)
    at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3543)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:491)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3245)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2413)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2836)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2323)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2712)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2709)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2709)
    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:132)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)
    ... 139 more

2 Reasons Analysis

2.1 Analytical Source Code

According to the stack information of the error log, it can be seen that MysqlIO.checkErrorPacket is the error of MySQL when verifying the response package returned by the operating system. Open the source code tracking to see:

private void checkErrorPacket(Buffer resultPacket) throws SQLException {

    int statusCode = resultPacket.readByte();

    // Error handling
    if (statusCode == (byte) 0xff) {
        String serverErrorMessage;
        int errno = 2000;

        if (this.protocolVersion > 9) {
            errno = resultPacket.readInt();

            String xOpen = null;

            serverErrorMessage = resultPacket.readString(this.connection.getErrorMessageEncoding(), getExceptionInterceptor());

            if (serverErrorMessage.charAt(0) == '#') {

                // we have an SQLState
                if (serverErrorMessage.length() > 6) {
                    xOpen = serverErrorMessage.substring(1, 6);
                    serverErrorMessage = serverErrorMessage.substring(6);

                    if (xOpen.equals("HY000")) {
                        xOpen = SQLError.mysqlToSqlState(errno, this.connection.getUseSqlStateCodes());
                    }
                } else {
                    xOpen = SQLError.mysqlToSqlState(errno, this.connection.getUseSqlStateCodes());
                }
            } else {
                xOpen = SQLError.mysqlToSqlState(errno, this.connection.getUseSqlStateCodes());
            }

            clearInputStream();

            StringBuilder errorBuf = new StringBuilder();

            String xOpenErrorMessage = SQLError.get(xOpen);

            if (!this.connection.getUseOnlyServerErrorMessages()) {
                if (xOpenErrorMessage != null) {
                    errorBuf.append(xOpenErrorMessage);
                    errorBuf.append(Messages.getString("MysqlIO.68"));
                }
            }

            errorBuf.append(serverErrorMessage);

            if (!this.connection.getUseOnlyServerErrorMessages()) {
                if (xOpenErrorMessage != null) {
                    errorBuf.append("\"");
                }
            }

            appendDeadlockStatusInformation(xOpen, errorBuf);

            if (xOpen != null && xOpen.startsWith("22")) {
                throw new MysqlDataTruncation(errorBuf.toString(), 0, true, false, 0, 0, errno);
            }
            throw SQLError.createSQLException(errorBuf.toString(), xOpen, errno, false, getExceptionInterceptor(), this.connection);
        }

        serverErrorMessage = resultPacket.readString(this.connection.getErrorMessageEncoding(), getExceptionInterceptor());
        clearInputStream();

        if (serverErrorMessage.indexOf(Messages.getString("MysqlIO.70")) != -1) {
            throw SQLError.createSQLException(SQLError.get(SQLError.SQL_STATE_COLUMN_NOT_FOUND) + ", " + serverErrorMessage,
                    SQLError.SQL_STATE_COLUMN_NOT_FOUND, -1, false, getExceptionInterceptor(), this.connection);
        }

        StringBuilder errorBuf = new StringBuilder(Messages.getString("MysqlIO.72"));
        errorBuf.append(serverErrorMessage);
        errorBuf.append("\"");

        throw SQLError.createSQLException(SQLError.get(SQLError.SQL_STATE_GENERAL_ERROR) + ", " + errorBuf.toString(), SQLError.SQL_STATE_GENERAL_ERROR, -1,
                false, getExceptionInterceptor(), this.connection);
    }
}

These codes are all for escaping the error code information returned by the operating system and converting it into MySQL's own error information. So SQLError must be MySQL's error information dictionary! Open it and see, as it is:

mysqlToSqlState = new Hashtable<Integer, String>();

mysqlToSqlState.put(MysqlErrorNumbers.ER_SELECT_REDUCED, SQL_STATE_WARNING);
mysqlToSqlState.put(MysqlErrorNumbers.ER_WARN_TOO_FEW_RECORDS, SQL_STATE_WARNING);
...
mysqlToSqlState.put(MysqlErrorNumbers.ER_LOCK_DEADLOCK, SQL_STATE_ROLLBACK_SERIALIZATION_FAILURE);

MysqlErrorNumbers is the error code constant class defined by MySQL, where you can find the origin of the definition of Got error 28 from storage engine:

public final static int ER_GET_ERRNO = 1030; //SQLSTATE: HY000 Message: Got error %d from storage engine...

Some people will find that this ER_GET_ERRNO has not been invoked by other code at all! Personal guess is due to the inconsistency between MySQL's jar source code and the actual jar version.

2.2 Mysterious "28"

So what does "28" mean?

There's one like this on MySQL.com Question Answer What if you can't create or write data to a file?

If the following error occurs when executing an SQL query, it means that MySQL cannot create temporary files on the temporary directory (temporary files are needed for the result set of data).

Can't create/write to file '\\sqla3fe_0.ism'.

Such prompts often appear in windows, and Unix's prompts are similar to those in windows.

The fix is to re-specify a permission folder using the mysqld method, like this:

[mysqld]
tmpdir=C:/temp

C:/temp must exist and have sufficient disk space for MySQL.

Not having permissions can also cause this problem, so you must ensure that MySQL has permission to write tmpdir folders.

You can also use the perror command to determine the meaning of the error code. Another reason is that the disk space is full:

shell> perror 28
OS error code  28:  No space left on device

See, that's what 28 really means!

Check the meaning of 28 in the error code of linux, which is also No space left on device!

Now it's clear that O()O~

3. Solving problems

In linux, query disk space usage first:

myServer# df -h

There must be a filesystem of 100%, probably like this:

Filesystem    Size    Used   Avail Capacity  Mounted on
/dev/vdisk     13G     13G     46M   100%    /
devfs         1.0k    1.0k      0B   100%    /dev

The rest is simple, deleting useless files, moving large files to other file systems, and even re-specifying the path of temporary file directories in MySQL. The purpose of these methods is to free up disk space O()O~