Spectrum Mysql tmp files run the disk out of space on a secondary SS on startup

Products

• DX NetOps
• CA Spectrum

Issue/Introduction

When processd is starting mysql on a fault tolerant secondary server it may run the disk out of disk space. 

The mysql.out will show something similar:

2023-08-16T17:54:09.603380Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2023-08-16T17:54:09.603839Z 0 [System] [MY-010931] [Server] /opt/CA/Spectrum/mysql/bin//mysqld: ready for connections. Version: '8.0.31-commercial'  socket: '/opt/CA/Spectrum/mysql/tmp/mysql.sock'  port: 3306  MySQL Enterprise Server - Commercial.
2023-09-10T06:01:23.835316Z 269 [Warning] [MY-012637] [InnoDB] 290816 bytes should have been written. Only 16384 bytes written. Retrying for the remaining bytes.
2023-09-10T06:01:24.547266Z 269 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2023-09-10T06:01:24.547308Z 269 [ERROR] [MY-012639] [InnoDB] Write to file (ddl) failed at offset 88633344, 290816 bytes should have been written, only 16384 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2023-09-10T06:01:25.141538Z 269 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'

2023-09-27T17:54:03.209252Z 476 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_6.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T17:54:03.209252Z 481 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_2.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T17:54:03.209251Z 479 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_7.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T17:2023-09-27T19:27:56.577201Z 487 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_4.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T19:27:56.616274Z 499 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_20.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T19:28:18.999471Z 487 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_4.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T19:28:19.001050Z 487 [Warning] [MY-012637] [InnoDB] 1048576 bytes should have been written. Only 102400 bytes written. Retrying for the remaining bytes.
2023-09-27T19:28:19.001104Z 487 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.

Environment

Spectrum and Mysql 8 

Cause

Bug in the processing of Spectrums mysql

Resolution

Save the DDMdb on the primary and transfer it to the secondary.  Load the primary DDMdb into the secondary DDM.

This is resolved in 23.3.6 with a change to mysql indexing.  A manual change to create the index is needed:

 

• _Symptom:

   _{Large dataset queries on the primary Archive Manager cause MySQL to consume the entire disk space.}

   

  _Resolution:

   _{With this fix, Spectrum incorporates the required code changes to fine-tune the large dataset queries so that only the necessary disk space is consumed. As part of this fix, users need to manually create an index (}

  _{event_ie3}

  _{). For more information, see MySQL Consuming Entire Disk Space. (DE580556, 33564388, 33591892, 33567337, 23.3.6)}

 

_{As part of this fix, users need to manually create the index} 

_{event_ie3}

 _{in the primary SpectroSERVER on the upgraded/older loaded database. After creating the index, restart the Archive Manager to resolve the issue.}

_{Follow these steps to create the index:}

• _{Log in to the MySQL command prompt.}
• _use ddmdb;
• _{create unique index event_ie3 on event (utime, counter, model_h, clk_seq, version, node_id);}

_{Creation of the new index takes considerable amount of time depending on the DDMDB size.}