Not the answer you need?
Register and ask your own question!

[ERROR] InnoDB: Error number 28 means 'No space left on device' when creating index??

jbiancotjbiancot ContributorCurrent User Role Supporter
Hi there,

I am running a standalone Percona MySQL Server 5.7.25-28, then I am just trying to add an index on a table, well, the table is kind of big: it has over one billion records, around 92 GB of disk space, here is the definition:

CREATE TABLE `UserLesson` (
`UserID` int(11) NOT NULL DEFAULT '0',
`LessonID` int(11) NOT NULL DEFAULT '0',
`CoCourseID` int(11) NOT NULL DEFAULT '0',
`LanguageTypeID` int(11) NOT NULL DEFAULT '0',
`DueDate` varchar(50) DEFAULT NULL,
`Status` varchar(20) DEFAULT NULL,
`AddUserID` int(11) DEFAULT NULL,
`AddDate` datetime DEFAULT NULL,
`DeleteUserID` int(10) DEFAULT NULL,
`DeleteDate` datetime DEFAULT NULL,
`LastChangeUserID` varchar(20) DEFAULT NULL,
`LastChangeDate` datetime DEFAULT NULL,
PRIMARY KEY (`UserID`,`CoCourseID`,`LessonID`,`LanguageTypeID`),
KEY `byUserID` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Disk space:

12K UserLesson.frm
92G UserLesson.ibd

The entire database is 139GB, and on that partition I have 437G of disk space available, some one I am getting this error:

--
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-28-log Percona Server (GPL), Release '28', Revision 'c335905'

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE INDEX UserLessonDeleteUserID ON UserLesson (DeleteUserID) USING BTREE;

then, eventually I got the message:

ERROR 1878 (HY000): Temporary file write failure.
--

The full error.log file about this matter is:

2019-12-23T19:40:11.388814Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-12-23T19:40:11.388850Z 0 [Note] IPv6 is available.
2019-12-23T19:40:11.388859Z 0 [Note] - '::' resolves to '::';
2019-12-23T19:40:11.388876Z 0 [Note] Server socket created on IP: '::'.
2019-12-23T19:40:11.515746Z 0 [ERROR] Incorrect definition of table performance_schema.global_variables: expected column 'VARIABLE_VALUE' at position 1 to have type varchar(1024), found type varchar(2048).
2019-12-23T19:40:11.517285Z 0 [Note] Event Scheduler: Loaded 0 events
2019-12-23T19:40:11.517573Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.25-28-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release '28', Revision 'c335905'
2019-12-23T19:40:12.423429Z 0 [Note] InnoDB: Buffer pool(s) load completed at 191223 14:40:12
2019-12-23T19:40:36.681846Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 15600ms. The settings might not be optimal. (flushed=0, during the time.)



2019-12-23T19:57:42.840792Z 2 [Warning] InnoDB: 1048576 bytes should have been written. Only 98304 bytes written. Retrying for the remaining bytes.
2019-12-23T19:57:42.842660Z 2 [Warning] InnoDB: 1048576 bytes should have been written. Only 102400 bytes written. Retrying for the remaining bytes.
2019-12-23T19:57:42.843413Z 2 [Warning] InnoDB: 1048576 bytes should have been written. Only 106496 bytes written. Retrying for the remaining bytes.
2019-12-23T19:57:42.844463Z 2 [Warning] InnoDB: 1048576 bytes should have been written. Only 110592 bytes written. Retrying for the remaining bytes.
2019-12-23T19:57:42.845481Z 2 [Warning] InnoDB: 1048576 bytes should have been written. Only 114688 bytes written. Retrying for the remaining bytes.
2019-12-23T19:57:42.846491Z 2 [Warning] InnoDB: 1048576 bytes should have been written. Only 118784 bytes written. Retrying for the remaining bytes.
2019-12-23T19:57:42.847568Z 2 [Warning] InnoDB: Retry attempts for writing partial data failed.
2019-12-23T19:57:42.847596Z 2 [ERROR] InnoDB: Write to file (merge)failed at offset 15516827648, 1048576 bytes should have been written, only 118784 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2019-12-23T19:57:42.847623Z 2 [ERROR] InnoDB: Error number 28 means 'No space left on device'
2019-12-23T19:57:42.847631Z 2 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html


Then, If I have enough space for MySQL, which it might want to re-write the entire table, why I am running out of space?

Comments

  • jbiancotjbiancot Contributor Current User Role Supporter
    Hello,

    Just an update, I found the issue, it is using the tmpdir setup to: /tmp/ which it doesn't have enough disk space to create the index, copy data to the "partition where is" /var/lib/mysql/, bla-bla-bla.

    Then, I just edit the /etc/mysql/percona-server.conf.d/mysqld.cnf and point the tmpdir to another place with plenty of disk space and creation of the index worked. It will use 45GB of disk space or so.

    I hope, this will go down on your permanent record :-)

    Cheers,
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.