Mysql - very weird behavior: query works locally, but not on a remote server for some people

Question

I have a very weird thing that we cannot understand or fix.

The short version: Some very simple SELECT-queries in MySQL hangs the whole application/program who made the query. However, its not consistent. If I have the EXACT same MySQL version locally, with the exact same table structure and the EXACT same data, the query works fine. but when executing the exact same query on the remote server (through for example HeidiSQL) - it hangs.

We are writing code in C# and we are seing the exact same behavior there too.

The long version: We have two remote server, REMOTE1 and REMOTE2.

REMOTE1 has MySQL 5.1.51-community.

REMOTE2 has MySQL 5.0.27-community-nt

Locally I run MySQL 5.1.36-community.

Sometimes, when executing a specific query (listed below) on REMOTE1, the app that is executing the query hangs.

This happens, for example, when I execute the query from my computer via HeidiSQL. When my work-partner does the EXACT same thing, using HeidiSQL, it works (we are on the same network). But, if my work-partner executes the same query not from HeidiSQL but from our own C#-program - then it also hangs.

Our own programs are written in C# and we can clearly see that when sending the query (via the MySQL-connector) it never returns from that method, thus hangs.

Now, when executing the exact same query on REMOTE2 it works without any problems at all, both from HeidiSQL and C#-code. Neither me or my work-partner has any problems when executing the same query on REMOTE2.

When testing locally it never hangs (neither through C# or HeidiSQL).

Also note:

• we upgraded the MySQL-server on REMOTE1 to 5.1.51 since the problem existed on the previous version too
• my work-partner has two computers, one of which has HeidiSQL version 5.0.0.3272 and the other version 5.1.0.3316. On both of these computers he can manually execute the query and it works on REMOTE1, but not through C#-code.
• In C#-code, we have tried with 3 different connectors, MySQL Connector 5.x.x, 6.1.5 and 6.3.5; nothing seems to work.
• I updated to HeidiSQL 5.1.0.3569 and the same error shows.

ANY IDEAS? =)

THe table structure is listed below as. When excluding the column "message", then it always works, so it has something to do with that column.

CREATE TABLE `sammessages` (
 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `externalMessageId` BIGINT(10) UNSIGNED NULL DEFAULT NULL,
 `timeStamp` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
 `message` TEXT NULL COLLATE 'latin1_general_ci',
 `direction` SET('INCOMING','OUTGOING','EXCEPTION','LOG') NULL DEFAULT NULL,
 `central` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
 `isCleaned` TINYINT(1) UNSIGNED NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 INDEX `NewIndex` (`timeStamp`),
 INDEX `central_timestamp` (`central`, `timeStamp`),
 INDEX `direction` (`direction`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=65352

And the query:

SELECT message FROM sammessages WHERE central='9' AND direction='INCOMING' AND timestamp >= '2010-10-24 04:00:00' AND timestamp <= '2010-10-24 23:00:00' AND message LIKE '%700%' AND message LIKE '%+%'

Answer 1

in mysql have you tried

grant all privileges on mydbname.* to mydbuser@'%' identified by 'mydbpasswd'

or

grant all privileges on mydbname.* to mydbuser@'138.239.201.234' identified by 'mydbpasswd' with grant option; 

Answer 2

Is there a possibility that some other part of your application is locking the table during an update? Your query might be bumping up against that lock, but since it only happens sometimes, the symptom appears to be the system the query is coming from. Just a stab in the dark, really, but something to look at.