List:General Discussion« Previous MessageNext Message »
From:Tripp Bishop Date:January 4 2006 8:01pm
Subject:RE: Lost Connection executing query
View as plain text  
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

--- emierzwa@stripped 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:dyne_erg@stripped] 
> Sent: Wednesday, January 04, 2006 11:01 AM
> To: emierzwa; mysql@stripped
> 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
> 
> 
> --- emierzwa@stripped 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:dyne_erg@stripped] 
> > Sent: Tuesday, January 03, 2006 4:45 PM
> > To: mysql@stripped
> > 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/mysql?unsub=1
> > 
> > 
> 
> 
> 
> 		
> __________________________________________ 
> 
=== message truncated ===



		
__________________________________________ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 

Thread
Best case-insensitive search for Character Set utf8 COLLATE utf8_bin Columns.Robert DiFalco28 Dec
  • Upgrading to 5.0.15Tripp Bishop28 Dec
    • Re: Upgrading to 5.0.15Gleb Paharenko28 Dec
      • Re: Upgrading to 5.0.15Gary Richardson28 Dec
        • Re: Upgrading to 5.0.15Christian Meisinger30 Dec
          • Re: Upgrading to 5.0.15Gary Richardson30 Dec
        • Lost Connection executing queryTripp Bishop4 Jan
          • Re: Lost Connection executing queryGleb Paharenko9 Jan
RE: Lost Connection executing queryemierzwa4 Jan
  • RE: Lost Connection executing queryTripp Bishop4 Jan
    • Re: Lost Connection executing queryKyle6 Jan
    • Re: Lost Connection executing queryKyle6 Jan
RE: Lost Connection executing queryemierzwa4 Jan
  • RE: Lost Connection executing queryTripp Bishop4 Jan
RE: Lost Connection executing queryImran Chaudhry5 Jan