ERROR 126 (HY000): Incorrect key file for table

A very interesting incident happened with me last week, all of a sudden mysql error logs flooded with "ERROR 126 (HY000): Incorrect key file for table" error,  and  in error log files.
I found new heavy queries added in recent release were creating this problem.
Those queries were also failing on mysql shell after that error.

Reason: queries using DISTINCT, COUNT, ORDER BY doing lots of sorting take huge temporary disk space to get final results, whenever temporary disk size is not enough to store temporarily created mysql data start giving these errors or insufficient space in /tmp directory.

Solution : Change the location of /tmp directory to other having more space and sufficient to store temporary tables while executing queries.

Use tmpdir='path' in my.cnf to change tmp directory path.

ERROR 1153 (08S01) at line *: Got a packet bigger than 'max_allowed_packet' bytes

ERROR 1153 (08S01) at line 19747092: Got a packet bigger than 'max_allowed_packet' bytes

increase max_allowed_packet value in my.cnf

mysql>set global max_allowed_packet=100M;

provide max_allowed_packet value at command prompt while restoring database

mysql -u -p database_name --max_allowed_packet=1073741824 < backup.sql


increase --net_buffer_length value for database restore on network.

mysql -u -p database_name --max_allowed_packet=1073741824 --net_buffer_length=100M < backup.sql

The maximum value for --max_allowed_packet is 1G ( 1073741824 )

issue resolved.