The explain resuts were from on the mac. I knew about the where clause optimization but that wouldn't explain why the server crashes when I issue the update statement. It is an interesting situation however.
I agree that you're probably correct about the import/CHECK TABLES. Cheers, Tripp --- [EMAIL PROTECTED] wrote: > 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] > > > > > > > > > __________________________________________ > === message truncated === __________________________________________ 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]
