Your import or CHECK TABLE sound like your best bets. Interesting about your SELECT statement conversion though, under the optimization section, it suggests you may still have a problem. Can you run your "EXPLAIN SELECT" on your MAC for comparison?
7.2.4. How MySQL Optimizes WHERE Clauses Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows. Ed -----Original Message----- From: Tripp Bishop [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 11:01 AM To: emierzwa; mysql@lists.mysql.com Subject: RE: Lost Connection executing query The query fails instantly so I don't think it's a timeout issue. The wait_timeout and interactive_timeout variables are set to 28800. The server seems to be crashing and auto restarting because as you suggested the uptime is small when I do a show status right after attempting the query. The schemas are identical and most of the data is the same. When I try to rewrite the update as a select I get an "impossible where clause" when I do an explain on the select. It can't be a max packet issue because the actual query is really small. The query runs fine on the MAC and takes about 1 second to run. I could break this update statement up into 4 seperate update statements but I'd prefer to keep it as one. I did notice that the MySQL manual suggests running CHECK TABLE on the table(s) involved but no other queries that operate against these tables seem to be having trouble so it seems unlikely that table corruption would be a problem. We did recently upgrade the server from 4.0.40 to 5.0.15 and we did not dump the tables and reimport them. On the MAC we did do a dump and reimport. I wonder if that could be the cause of this problem. I had forgetten about that important difference. Cheers, Tripp --- [EMAIL PROTECTED] wrote: > What are your wait_timeout and/or > interactive_timeout values set to? > Does the server crash and auto restart? Check > server's up time. > Do both servers have the exact table schema? Same > column datatypes and > indexes to be specific. > Although your data volumn may be similar, can the > actual data be > problamatic? > Can you rewrite the UPDATE statement as a SELECT > statement to see if you > can target the rows you are expecting to target? > > You can check section "A.2.8. MySQL server has gone > away" in the online > manual, which also covers your message, for list of > things to try. > http://dev.mysql.com/doc/refman/5.0/en/gone-away.html > > Ed > -----Original Message----- > From: Tripp Bishop [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 03, 2006 4:45 PM > To: mysql@lists.mysql.com > Subject: Lost Connection executing query > > Howdy all, > > First off, We're running 5.0.15. > > Theres a particular update statement that we run > that > updates data in several tables. On our mac OSX test > server (also running 5.0.15) the query executes > quickly and without any errors or warnings. > > On our linux box, which is our production box, we > get > the following error as soon as the query is > executed: > > ERROR 2013 (HY000): Lost connection to MySQL server > during query > > The databases have a similar amount of data in them. > > I've googled on the error but mostly get pages > containing questions about the error when generated > by > stored procedures and mostly on 5.0.3. We're not > using > stored procedures. This is just a straight-up query. > > Here's the query: > > UPDATE customer_indicator > INNER JOIN customer_search_pref ON > customer_search_pref.customer_id = > customer_indicator.customer_id AND > customer_search_pref.office_id = > customer_indicator.office_id > LEFT JOIN contact_log ON > contact_log.customer_id = > customer_indicator.customer_id > LEFT JOIN sent ON sent.pref_id = > customer_search_pref.pref_id > SET customer_indicator.office_id = 33, > customer_search_pref.office_id =33, > customer_indicator.agent_id = 105, > sent.office_id = 33, > contact_log.office_id = 33, > customer_indicator.next_message_id = 4403 > WHERE customer_indicator.customer_id = 78437 AND > customer_indicator.office_id = 34; > > The approximate sizes of the tables involved are: > > customer_indicator: 40K records > customer_search_pref: 45K > contact_log: 390K > sent: 20M (million) > > So my question is, what are some possible causes of > this error? Why would trying to execute this query > cause the connection to be lost? Why would the query > work fine on our mac system and fail on the > prodcution > box? > > Thanks, > > Tripp > > > > __________________________________________ > Yahoo! DSL - Something to write home about. > Just $16.99/mo. or less. > dsl.yahoo.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __________________________________________ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
