When starting the SpectroSERVER, a large file is created in the mysql\tmp folder. Why?

Document ID : KB000073135
Last Modified Date : 13/03/2018
Show Technical Document Details

Every time we start the spectroServer, it creates a huge MyISAM database file in the mySQL/tmp directory.
for example:
-rw-r--r-- 1 spectrum None 33271390208 Feb 19 09:44 #sql15b4_2_b.MYD
-rw-r--r-- 1 spectrum None 1024 Feb 19 09:44 #sql15b4_2_b.MYI

It grows to more than 10 times the size of the Archive Manager database and consumes nearly all of the available disk space.
It eventually stops growing, reduces in size and disappears, until the next time, we restart the SpectroSERVER.

Why is this happening and what can we do to stop it?

A recreation of the problem, while examining the running processes in mySQL, show multiple identical large mySQL queries running simultaneously and consuming processes, especially disk space.

./mysql -uroot -proot reporting
Once connected, type: ./select * from landscape\G ddmdb Query 371 Waiting for table level lock SELECT E.model_h, M.model_name, M.mtype_h, MT.mtype_name, U.user_name, E.type, E.severity, E.vardata_string FROM model_type as MT, model as M, event as E, user_def as U WHERE MT.mtype_h =3D M.mtype_h AND M.model_h =3D E.model_h AND E.user_key =3D U.user_key AND E.utime =3D 1519157812 AND E.counter =3D 8130 AND E.clk_seq =3D 922 AND E.version =3D 1 AND E.node_id =3D '\\0PV?Y=89'

This is not a known issue.  So far we have seen just a singular instance occurring but if all the disk space of a mySQL database is used, it will crash and corrupt the database.
With no free space on the server, the options available to repair the very limited and we are forced to initialize the corrupt data. 
Unless there is a recent backup of the ddmDB available, it is possible to lose event data.

On investigation, no problems were found in logs or configuration files.  The problem was resolve this by running the maintenance, optimize and repair scripts, so we were not able to analyse the root cause.
Additional Information:

For more information on database maintenance and optimization scripts.

To run a repair of the Archive Manager database.