MySQL tempfolder Full

Question

I have a mysql server hosted in an Azure VM. This is a slave where the data is being replicated from an on premise Master. Configurations are mentioned below. users are frequently getting an error The table 'F:\MySQL\temp\#sql262c_9ffa_20' is full . The same error also occurs during replication and replication stops. It gets fixed when I stop and start replication. F drive is the where the Data is hosted and there is more than 1 TB of space left.

VM Configurations :

OS version Windows Server 2019 Datcenter
RAM size 256 GB
number of cores 32
Disk : Premium SSD LRS

MySQL is hosted on Azure VM OS version Windows Server 2019 Datcenter RAM size 256 GB number of cores 32 any SSD Premium SSD LRS

I have a mysql server hosted in an Azure VM. This is a slave where the data is being replicatied from an on premise Master. Configurations are mentioned below. users are frequently getting an error The table 'F:\MySQL\temp\#sql262c_9ffa_20' is full while quering the database . The same error also occurs during replication and replication stops. It gets fixed when I stop and start replication. F drive is the where the Data is hosted and there is more than 1 TB of space left.

Another error I see in the error log is :

[ERROR] [MY-000035] [Server] Disk is full writing 'F:\MySQL\Data\mysql-relay-bin.019409' (OS errno 28 - No space left on device)

VM Configurations :

OS version Windows Server 2019 Datacenter
RAM size 256 GB
number of cores 32
Disk : Premium SSD LRS

Things tried : Looked for space and found there is enough space. Looked for disk quota to see if the user has any quota on the data drive.

• Wilson Hauck: Additional DB information request, please. OS, Version? server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; H) Last 100 lines of your error log. for server workload tuning analysis to provide suggestions.
• Rick James: What query triggered the "full" problem?

Answer 1

Error Message "The table 'F:\MySQL\temp#sql262c_9ffa_20' is full"

If the temp table is InnoDB,The table 'F:\MySQL\temp\#sql262c_9ffa_20' is full message you are seeing has nothing to do with free diskspace. When an InnoDB table keeps a copy of data for the purpose of rollback and transaction isolation, there can come a point when the undo logs and rollback segments become logically full. When this occurs, InnoDB can no longer let the transaction continue.

I have addressed this many times over the years

• May 31, 2013 : `ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend
• Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
• Jun 16, 2014 : MySQL Index creation failing on table is full
• Apr 07, 2015 : MySQL query INSERT INTO ... failed - The Table "x" is full

[ERROR] [MY-000035] [Server] Disk is full writing 'F:\MySQL\Data\mysql-relay-bin.019409' (OS errno 28 - No space left on device)

This message is an entirely different problem

If a slave collects too many relay logs, this could indicate one of the following scenarios:

Replication is Broken as seen in SHOW SLAVE STATUS\G

• Slave_IO_Running: Yes
• Slave_SQL_Running: No
• Seconds_Behind_Master: NULL

Replication Lag is Way Behind as seen in SHOW SLAVE STATUS\G

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: High number and increasing

Maybe Diskspace Really Ran Out in the Folder Where Relay Logs as Stored

When a temp table is populated, if diskspace runs out may freeze (See my old post from 2015 : How to fix a MySQL server with a full hard drive?).

EPILOGUE

I hope these things help you clarify what has been happening