C API hangs in mysql_query()

Question

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

Answer 1

Are you doing a backup every 12 hours? MyISAM is blocked when dump, ALTER, OPTIMIZE, and several other operations are done.

Switch to InnoDB to eliminate most of such locks.

You can probably see the offending SQL by getting a full SHOW PROCESSLIST;.