ERROR 1114 (HY000): Spectrum mysql temp table is full

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

When trying to run a query within a Spectrum mysql database, you may receive an error similar to the following:

ERROR 1114 (HY000): The table '/ca/spectrum/mysql/tmp/#sql_1b99_0' is full

Environment

Release : 10.3.x 10.4.x, 22.2.x

Component : Spectrum Applications

Cause

This error is caused by either a mysql table limit within the configuration or the system is out of disk space.

Resolution

If you have verified the system is not out of disk space then adding the following parameters to the <SPECROOT>/mysql/my-spectrum.cnf file and then restarting mysql will resolve the issue.

innodb_temp_data_file_path = ibtmp1:12M:autoextend
innodb_data_file_path = ibdata1:10M:autoextend

Additional Information

To restart mysql:

Windows:

- Log into the server as the user that owns the Spectrum installation

- Launch the Windows Services

- Stop the Spectrum mysql

- Start the Spectrum mysql

Linux:

- Log into the system as the user that owns the Spectrum installation

- Find the processd of mysql (Enter ps -ef | grep -i mysql)

- cd to the $SPECROOT/bin directory

- Enter the following command where <PID> is the process id of mysql to shutdown mysql

./cmdC localhost 2 <PID>

- Verify mysql is not running

- Enter the following command to start mysql

./launchinstdbapp localhost MYSQL y MYSQL.OUT

- Verify mysql is running\