Mysql – C API hangs in thesql_query()

I have this very strange issue and I cannot reproduce it – it occurs once in ~12 hours only for this table:

CREATE TABLE `ReferenceWaves` (
  `ReferenceWave` datetime NOT NULL COMMENT 'DateTime of the Reference Wave',
  `InstrumentId` int(11) NOT NULL,
  `Triggered` datetime DEFAULT NULL COMMENT 'When it was triggered'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Assures FAFWE doesn''t use this reference wave when restarted';

ALTER TABLE `ReferenceWaves`
  ADD UNIQUE KEY `ReferenceWave` (`ReferenceWave`,`InstrumentId`);

Then running this command

SELECT COUNT(*) FROM MyISAM_ElliottWavesCore.ReferenceWaves 
      WHERE ReferenceWave = '2018-04-02 17:06:00' AND InstrumentId = 73

does not return from the mysql_query() C function. This occurs more or less once in 12 hours – in the meantime around 10'000 queries like this have been executed without any problem.

Running SELECT * FROM information_schema.PROCESSLIST WHERE ID=6981; – where ID is the thread id for the query – returns this:

ID      USER    HOST                                      DB    COMMAND TIME STATE  INFO
6981    FAEWE   aaeb-app206ly.aaeb-holding.local:52492  (null)  Sleep   1836        (null)

After around 5 hours this thread apparently is killed automatically by MySQL and not visible anymore in the information_schema.PROCESSLIST.

The table MyISAM_ElliottWavesCore.ReferenceWaves has ~4'800 rows and the total size – including the index – is <150KB. I have tables which have million of rows and have size > 1GB but there is never this issue – it is only in this table and very unpredictable. You can imagine how frustrating it is.

Any idea what is going wrong?

Technical data:

• SLES 12.3 running on XenServer 7.4 as VM
• compiled with gcc 7.3 using -std=c++17 and almost every warning option.
• using glibc 2.27 which is separately linked into the project because SLES 12.3 uses 2.22
• MySql Community Server 5.7.21
• C API client 5.7.19