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 [url]http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html[/url]
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?
