C API hangs in mysql_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
gcc7.3 using-std=c++17and almost every warning option. - using
glibc2.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
mysql myisam mysql-5.7
asked Apr 2 '18 at 16:07
Al BundyAl Bundy
1851110
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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
gcc7.3 using-std=c++17and almost every warning option. - using
glibc2.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
mysql myisam mysql-5.7
asked Apr 2 '18 at 16:07
Al BundyAl Bundy
1851110
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
No idea, but I wouldn't "select count(*)" where only one row can be returned, I wouldn't pass a string when selecting a datetime, but I would mention what version of MySQL we're dealing with, on which OS, and which client is issuing the query, and how exactly.
– Gerard H. Pille
Apr 2 '18 at 18:09
@GerardH.Pille I updated the question. Why wouldn't you useCOUNT(*)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.
– Al Bundy
Apr 2 '18 at 20:10
1
Sounds like lock contention to me.
– mustaccio
Apr 3 '18 at 13:14
@mustaccio You see the command. How can be here a lock? It is an unbelievable simple command on a MyISAM table.
– Al Bundy
Apr 3 '18 at 13:19
I know, I've read your edit already.
– Gerard H. Pille
Jun 4 '18 at 10:55
add a comment |
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
gcc7.3 using-std=c++17and almost every warning option. - using
glibc2.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
mysql myisam mysql-5.7
asked Apr 2 '18 at 16:07
Al BundyAl Bundy
1851110
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
gcc7.3 using-std=c++17and almost every warning option. - using
glibc2.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
mysql myisam mysql-5.7
mysql myisam mysql-5.7
asked Apr 2 '18 at 16:07
Al BundyAl Bundy
1851110
asked Apr 2 '18 at 16:07
Al BundyAl Bundy
1851110
edited Apr 3 '18 at 15:44
Al Bundy
asked Apr 2 '18 at 16:07
Al BundyAl Bundy
1851110
asked Apr 2 '18 at 16:07
Al BundyAl Bundy
1851110
asked Apr 2 '18 at 16:07
Al BundyAl Bundy
1851110
1851110
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
No idea, but I wouldn't "select count(*)" where only one row can be returned, I wouldn't pass a string when selecting a datetime, but I would mention what version of MySQL we're dealing with, on which OS, and which client is issuing the query, and how exactly.
– Gerard H. Pille
Apr 2 '18 at 18:09
@GerardH.Pille I updated the question. Why wouldn't you useCOUNT(*)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.
– Al Bundy
Apr 2 '18 at 20:10
1
Sounds like lock contention to me.
– mustaccio
Apr 3 '18 at 13:14
@mustaccio You see the command. How can be here a lock? It is an unbelievable simple command on a MyISAM table.
– Al Bundy
Apr 3 '18 at 13:19
I know, I've read your edit already.
– Gerard H. Pille
Jun 4 '18 at 10:55
add a comment |
No idea, but I wouldn't "select count(*)" where only one row can be returned, I wouldn't pass a string when selecting a datetime, but I would mention what version of MySQL we're dealing with, on which OS, and which client is issuing the query, and how exactly.
– Gerard H. Pille
Apr 2 '18 at 18:09
@GerardH.Pille I updated the question. Why wouldn't you useCOUNT(*)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.
– Al Bundy
Apr 2 '18 at 20:10
1
Sounds like lock contention to me.
– mustaccio
Apr 3 '18 at 13:14
@mustaccio You see the command. How can be here a lock? It is an unbelievable simple command on a MyISAM table.
– Al Bundy
Apr 3 '18 at 13:19
I know, I've read your edit already.
– Gerard H. Pille
Jun 4 '18 at 10:55
No idea, but I wouldn't "select count(*)" where only one row can be returned, I wouldn't pass a string when selecting a datetime, but I would mention what version of MySQL we're dealing with, on which OS, and which client is issuing the query, and how exactly.
– Gerard H. Pille
Apr 2 '18 at 18:09
No idea, but I wouldn't "select count(*)" where only one row can be returned, I wouldn't pass a string when selecting a datetime, but I would mention what version of MySQL we're dealing with, on which OS, and which client is issuing the query, and how exactly.
– Gerard H. Pille
Apr 2 '18 at 18:09
@GerardH.Pille I updated the question. Why wouldn't you use
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.– Al Bundy
Apr 2 '18 at 20:10
@GerardH.Pille I updated the question. Why wouldn't you use
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.– Al Bundy
Apr 2 '18 at 20:10
1
1
Sounds like lock contention to me.
– mustaccio
Apr 3 '18 at 13:14
Sounds like lock contention to me.
– mustaccio
Apr 3 '18 at 13:14
@mustaccio You see the command. How can be here a lock? It is an unbelievable simple command on a MyISAM table.
– Al Bundy
Apr 3 '18 at 13:19
@mustaccio You see the command. How can be here a lock? It is an unbelievable simple command on a MyISAM table.
– Al Bundy
Apr 3 '18 at 13:19
I know, I've read your edit already.
– Gerard H. Pille
Jun 4 '18 at 10:55
I know, I've read your edit already.
– Gerard H. Pille
Jun 4 '18 at 10:55
add a comment |
1 Answer
1
active
oldest
votes
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;.
answered Apr 23 '18 at 5:10
Rick JamesRick James
43k22259
No, I am not doing any of the mentioned commands. Here is the more detailed version of the issue. It seems the command hangs but the truth is, the command is never submitted to the MySQL server. Just check the link.
– Al Bundy
Apr 23 '18 at 8:40
For the moment I cannot reproduce this issue. I changed the engine - as you suggested - to InnoDB - it worked for 300k iterations. Then, I changed it back to MyISAM and it also works.
– Al Bundy
Apr 25 '18 at 19:42
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f202849%2fc-api-hangs-in-mysql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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;.
answered Apr 23 '18 at 5:10
Rick JamesRick James
43k22259
No, I am not doing any of the mentioned commands. Here is the more detailed version of the issue. It seems the command hangs but the truth is, the command is never submitted to the MySQL server. Just check the link.
– Al Bundy
Apr 23 '18 at 8:40
For the moment I cannot reproduce this issue. I changed the engine - as you suggested - to InnoDB - it worked for 300k iterations. Then, I changed it back to MyISAM and it also works.
– Al Bundy
Apr 25 '18 at 19:42
add a comment |
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;.
answered Apr 23 '18 at 5:10
Rick JamesRick James
43k22259
No, I am not doing any of the mentioned commands. Here is the more detailed version of the issue. It seems the command hangs but the truth is, the command is never submitted to the MySQL server. Just check the link.
– Al Bundy
Apr 23 '18 at 8:40
For the moment I cannot reproduce this issue. I changed the engine - as you suggested - to InnoDB - it worked for 300k iterations. Then, I changed it back to MyISAM and it also works.
– Al Bundy
Apr 25 '18 at 19:42
add a comment |
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;.
answered Apr 23 '18 at 5:10
Rick JamesRick James
43k22259
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;.
answered Apr 23 '18 at 5:10
Rick JamesRick James
43k22259
answered Apr 23 '18 at 5:10
Rick JamesRick James
43k22259
answered Apr 23 '18 at 5:10
Rick JamesRick James
43k22259
answered Apr 23 '18 at 5:10
Rick JamesRick James
43k22259
43k22259
No, I am not doing any of the mentioned commands. Here is the more detailed version of the issue. It seems the command hangs but the truth is, the command is never submitted to the MySQL server. Just check the link.
– Al Bundy
Apr 23 '18 at 8:40
For the moment I cannot reproduce this issue. I changed the engine - as you suggested - to InnoDB - it worked for 300k iterations. Then, I changed it back to MyISAM and it also works.
– Al Bundy
Apr 25 '18 at 19:42
add a comment |
No, I am not doing any of the mentioned commands. Here is the more detailed version of the issue. It seems the command hangs but the truth is, the command is never submitted to the MySQL server. Just check the link.
– Al Bundy
Apr 23 '18 at 8:40
For the moment I cannot reproduce this issue. I changed the engine - as you suggested - to InnoDB - it worked for 300k iterations. Then, I changed it back to MyISAM and it also works.
– Al Bundy
Apr 25 '18 at 19:42
No, I am not doing any of the mentioned commands. Here is the more detailed version of the issue. It seems the command hangs but the truth is, the command is never submitted to the MySQL server. Just check the link.
– Al Bundy
Apr 23 '18 at 8:40
No, I am not doing any of the mentioned commands. Here is the more detailed version of the issue. It seems the command hangs but the truth is, the command is never submitted to the MySQL server. Just check the link.
– Al Bundy
Apr 23 '18 at 8:40
For the moment I cannot reproduce this issue. I changed the engine - as you suggested - to InnoDB - it worked for 300k iterations. Then, I changed it back to MyISAM and it also works.
– Al Bundy
Apr 25 '18 at 19:42
For the moment I cannot reproduce this issue. I changed the engine - as you suggested - to InnoDB - it worked for 300k iterations. Then, I changed it back to MyISAM and it also works.
– Al Bundy
Apr 25 '18 at 19:42
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f202849%2fc-api-hangs-in-mysql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
No idea, but I wouldn't "select count(*)" where only one row can be returned, I wouldn't pass a string when selecting a datetime, but I would mention what version of MySQL we're dealing with, on which OS, and which client is issuing the query, and how exactly.
– Gerard H. Pille
Apr 2 '18 at 18:09
@GerardH.Pille I updated the question. Why wouldn't you use
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.– Al Bundy
Apr 2 '18 at 20:10
1
Sounds like lock contention to me.
– mustaccio
Apr 3 '18 at 13:14
@mustaccio You see the command. How can be here a lock? It is an unbelievable simple command on a MyISAM table.
– Al Bundy
Apr 3 '18 at 13:19
I know, I've read your edit already.
– Gerard H. Pille
Jun 4 '18 at 10:55