Fix: Tmp Memory full with MAD and MAI Large Files

When you run some command like ALTER/CREATE on InnoDB table, temporary table is created and which is stored in /tmp directory by default on cPanel server. It is because default value is set to /tmp directory in my.cnf .

Your tmp memory full with files like /tmp/#sql-temptable-511****.MAI or /tmp/#sql_2345****.MAD. You will see many such files and thus your tmp memory get full.

Once your tmp memory get full, you will see error like below->

Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1021 Disk full (/tmp/#sql_2f799f_21.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")

ERROR: PDOException: SQLSTATE[HY000]: General error: 1021 Disk full (/tmp/#sql_2f799f_13.MAI); waiting for someone to free some space... (errno: 28 "No space left on device") in /home/

You can fix this by manually flushing the content of /tmp directory but this can be irritating if it get full continuously.

It is recommended to move the tmp directory of mysql error log files to different drive with more space.

If you are running MariaDB as MYSQL server in your cPanel server, follow the below steps.

1. Create a new file -> /etc/systemd/system/mariadb.service.d/override.conf
2. Add below content in override.conf created.

   [Service]
   ProtectHome=false

3. Run Below commands

   systemctl daemon-reload
   /scripts/restartsrv_mysql

4. Now create new folder/directory on which there is enough space.

   mkdir /home/mysqltmp
   chown mysql:mysql /home/mysqltmp

5. Now edit the content of /etc/my.cnf and add below content.

   tmpdir=/home/mysqltmp

6. Finally restart the MYSQL by below command.

   /scripts/restartsrv_mysql
   
   

Now whenever MAD and MAI files is created, it will be stored in /home.mysqltmp directory and thus your /tmp memory will not be full.