3

Have just got a surprise and would appreciate some suggestions. The simple query below updates a table. I have used it severally without qualms but now it does not work as usual causing the MySQL server to go away. What could be responsible. Thanks.

UPDATE rmc_raw_data 
    INNER
    JOIN handover_main
        ON handover_main.handover_time = rmc_raw_data.rmc_time
        AND handover_main.handover_date = rmc_raw_data.rmc_date
        SET rmc_raw_data.handovers = 'Handover'
ibiangalex
  • 375
  • 1
  • 4
  • 16

2 Answers2

1

It would be advisable to go through the list here - they have quite useful checklist/instructions on what to check.

It explains what are the most common and some of the less common reasons for the quoted message, how to test for them and where to look.

Normally, in a way it should not have anything to do with your SQL but with settings.

Apart from the first and most obvious reason (timeout) I would also try to establish if the behaviour is the same when query is run through command line client/script, compared to for example running it through php (and/or whatever your environment is), JDBC/ODBC perhaps as well.

If behaviour always the same I would concentrate on the server settings (using the mysql docs).

Unreason
  • 12,556
  • 2
  • 34
  • 50
  • Thanks guys for your useful suggestions. I did not make an attempt to change any settings even after reading from the links you provided. I made one last minutes gamble by removing the unique indexes from the table and adding normal indexes. Then ran the query and it worked. Due to the unique indexes, the script handling import could not execute and the one handling the update never reached. The server was confused. Meanwhile the update query need indexes to get going due to the large data it is updating. Thank to you all. – ibiangalex Nov 19 '10 at 19:30
0

Increase the timeout of the session. It's most likely set to 5 minutes or so which is too short for long running queries.

Here are the details from the mysql documentation: http://dev.mysql.com/doc/refman/5.1/en/gone-away.html

Here are the details for changing the timeout options: http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html

Falcon
  • 3,150
  • 2
  • 24
  • 35