MySQL not returning successful results on statement – query looks like it’s hanging but is successful

javaMySQL

So we have a script(s) that runs in succession.
Randomly this task would halt/freeze and error out simply because it did not see a returned response from the MySQL server saying the statement ran fine breaking the entire procedure.
However when manually checking against the DB, the statements all ran fine it's just the script (using JBDC) aborted since it didn't see a response.

Nothing in the error logs, etc.

Is there a setting or reason why MySQL would be doing this?
This is version 5.1 .

I thought of network issues causing this but this seems to happen in 2 different environments, both through a WAN and local LAN .

any ideas?

Best Answer

You can try setting up a shell script that runs the SQL scripts or write a program in perl/whatever language which tries to log the errors and return codes from MySQL. Otherwise it is very difficult to troubleshoot without knowing the errors.

You could also add SHOW ERRORS; and SHOW WARNINGS; to your SQL scripts, but I suspect the problem is not with your queries.

Related Solutions

MySQL Error Handling – log_warnings Not Working for Aborted Connections in MySQL 5.1

This seems to be an elusive bug nobody wants to fix

http://bugs.mysql.com/bug.php?id=24761 (2006-12-01 : Status Triaged)
http://bugs.mysql.com/bug.php?id=41602 (2008-12-18 : Status Closed)
https://serverfault.com/questions/235369/how-can-i-get-mysql-5-5-to-log-warnings-to-one-of-the-log-files (2011-02-14)

According to http://bugs.mysql.com/bug.php?id=24761, the aborted connect is recorded in the general log

[29 Sep 2008 8:03] Konstantin Osipov
OK, it was fixed differently:
      /*
        Log the command before authentication checks, so that the user can
        check the log for the tried login tried and also to detect
        break-in attempts.
      */
      general_log_print(thd, command,
                        (thd->main_security_ctx.priv_user ==
                         thd->main_security_ctx.user ?
                         (char*) "%s@%s on %s" :
                         (char*) "%s@%s as anonymous on %s"),
                        thd->main_security_ctx.user,
                        thd->main_security_ctx.host_or_ip,
                        db ? db : (char*) "");

So, this is logged in the general log at least.

I was reviewing a patch that added more logging,
but I can't remember the worklog task number.

We just have to wait on MySQL (I mean Oracle) to fix it. (That was sarcasm)

Mysql – creating table syntax error 1064

I see a few issues with your existing code to create the waiters table.

First, the index creation code is incorrect. The column to be indexed should be inside the parentheses -- INDEX (manager_id).

Second, you are trying to add an index on a column that is not listed in your waiters table. You need to include the manager_id as a column.

Third, the syntax for the FOREIGN KEY constraint is incorrect. The syntax is from the MySQL docs is:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

So your code should be:

CREATE  TABLE `waiters` (
  `waiter_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `manager_id` INT NOT NULL,
  PRIMARY KEY (`waiter_id`) ,
  INDEX  (`manager_id`) ,
  CONSTRAINT FK_Manager
    FOREIGN KEY (`manager_id`)
    REFERENCES `managers` (manager_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

See SQL Fiddle with Demo

Best Answer

Related Solutions

MySQL Error Handling – log_warnings Not Working for Aborted Connections in MySQL 5.1

Mysql – creating table syntax error 1064

Related Question