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
COUNT(*)
when only one row can be returned? The advantage is that there is always a result set returned. And honestly, this must not be any problem for a database server in year 2018. – Peter VARGA Apr 2 '18 at 20:10