Ygthkb

Rear brake cable temporary fix possible?

What to do if authors don't respond to my serious concerns about their paper?

How to remove trailing forward slash

It took me a lot of time to make this, pls like. (YouTube Comments #1)

Is there hidden data in this .blend file? Trying to minimize the file size

Tikzing a circled star

Why did the villain in the first Men in Black movie care about Earth's Cockroaches?

Is it a fallacy if someone claims they need an explanation for every word of your argument to the point where they don't understand common terms?

A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?

Pendulum Rotation

Program that converts a number to a letter of the alphabet

How is the Incom shipyard still in business?

Compound Interest... with Wizard Money

What is the time complexity of enqueue and dequeue of a queue implemented with a singly linked list?

En Passant For Beginners

Issues with new Macs: Hardware makes them difficult for me to use. What options might be available in the future?

1 0 1 0 1 0 1 0 1 0 1

What do you call a fact that doesn't match the settings?

Explain the objections to these measures against human trafficking

What is the best way to simulate grief?

Why zero tolerance on nudity in space?

Can you earn endless XP using a Flameskull and its self-revival feature?

Why is working on the same position for more than 15 years not a red flag?

What is better: yes / no radio, or simple checkbox?

C API hangs in mysql_query()

mysql_query() is NOT passing the command to the MySQL server using the C APITRUNCATE TABLE statement sometimes hangsMySQL hangs in killed stateMySQL instance stalling “doing SYNC index”MySQL select data over time that matches pattern over several rowsInvision Power Board 3.4.6 - mysql locks during query with pending updatesSQL Timeout when updating specific rows that goes away when other columns are updatedHow to Achieve Concurrent Write Transactions to MySQL Group Replication in Multi-Primary ModePartitioning increases CPU usage in mariadbmysql_query() is NOT passing the command to the MySQL server using the C APIAurora MySQL 5.7 DDL statements hangs intermittently

3

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

mysql myisam mysql-5.7

share|improve this question

edited Apr 3 '18 at 15:44

Al Bundy

asked Apr 2 '18 at 16:07

Al BundyAl Bundy

1851110

bumped to the homepage by Community♦ 21 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 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
  

  
  
  
  

add a comment | 

3

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

mysql myisam mysql-5.7

share|improve this question

edited Apr 3 '18 at 15:44

Al Bundy

asked Apr 2 '18 at 16:07

Al BundyAl Bundy

1851110

bumped to the homepage by Community♦ 21 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 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
  

  
  
  
  

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 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

mysql myisam mysql-5.7

share|improve this question

edited Apr 3 '18 at 15:44

Al Bundy

asked Apr 2 '18 at 16:07

Al BundyAl Bundy

1851110

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`);

SELECT COUNT(*) FROM MyISAM_ElliottWavesCore.ReferenceWaves 

      WHERE ReferenceWave = '2018-04-02 17:06:00' AND InstrumentId = 73

ID      USER    HOST                                      DB    COMMAND TIME STATE  INFO

6981    FAEWE   aaeb-app206ly.aaeb-holding.local:52492  (null)  Sleep   1836        (null)

share|improve this question

edited Apr 3 '18 at 15:44

Al Bundy

asked Apr 2 '18 at 16:07

Al BundyAl Bundy

1851110

share|improve this question

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

1 Answer
1

active

oldest

votes

0

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;.

share|improve this answer

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
});


}
});

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

0

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;.

share|improve this answer

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 | 

0

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;.

share|improve this answer

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;.

share|improve this answer

answered Apr 23 '18 at 5:10

Rick JamesRick James

43k22259

share|improve this answer

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