List:Cluster« Previous MessageNext Message »
From:Jim Hoadley Date:April 21 2005 5:02am
Subject:Re: Workaround for ERROR 1205 (HY000): Lock wait timeout exceeded;
View as plain text  
A follow up on this post. By increasing the TransactionDeadlockDetectionTimeout
to 12000 milliseconds (default is 1200), I avoid the "Lock wait timeout
exceeded" 
error altogether.

-- Jim

--- Jim Hoadley <j_hoadley@stripped> wrote:

> Clarification: 
> 
> After loading, I must shutdown the cluster, then restart all nodes
> or else nodes 2 and 4 hang indefinitely at phases 2 and 5.
> 
> When restarting the entire cluster (nodes 2 and 4 with --initial),
> the management node error log WARNs about failed indexes 
> for 2 and 4, but eventually they come online.
> 
> Again, my question: is this an improper procedure?
> Am I creating or risking corruption?
> 
> Thx.
> 
> -- Jim
> 
> --- Jim Hoadley <j_hoadley@stripped> wrote:
> 
> > I've found that if I stop 2 of 4 nodes before loading a mysqldump
> > file, I completely avoid this error. The nodes must be on different hosts 
> > and, obviously, not in the same node group.
> > 
> > host a                  host b
> > ---------           ----------
> > 
> > NDB1                    NDB2
> > NDB3                    NDB4
> > 
> > ---------           ----------
> > 
> > Stop nodes NDB1 and NDB4, load, restart  NDB1 and NDB4 with --initial.
> > This works, but is it dangerous to the cluster's integrity? Is it
> supported?
> > 
> > Thx.
> > 
> > -- Jim
> > 
> > 
> > --- Jim Hoadley <j_hoadley@stripped> wrote:
> > 
> > > Martin --
> > > 
> > > Thanks. I think that is a good suggestion. Unfortunately, in this case it
> > did
> > > not change the outcome:
> > > 
> > > mysql> LOAD DATA INFILE 'nbn_lead_records.csv' INTO TABLE nbn_leads;
> > > ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
> transaction
> > > 
> > > I've also tried loading the mysqldump file as TYPE=MyISAM (which works)
> and
> > > then changing the table type with ALTER TABLE nbn_leads ENGINE=NDB, same
> > > result.
> > > 
> > > -- Jim
> > > 
> > > 
> > > --- Martin Skold <Martin.Skold@stripped> wrote:
> > > > Try bulk loading instead it is better
> > > > optimized for cluster
> > > > LOAD DATA INFILE <file name>;
> > > > 
> > > > BR
> > > > -- Martin
> > > > 
> > > > 
> > > > Jim Hoadley wrote:
> > > > 
> > > > >FYI I often get this same error when trying to load data. I'll
> restart
> > the
> > > > >transaction, I'll drop the table and reload. Same result. And
> nothing
> in
> > > any
> > > > >error log. Seems like adjusting parameters in config.ini should
> fix,
> but
> > > so
> > > > far
> > > > >I've found no magic bullet.
> > > > >
> > > > >-- Jim
> > > > >
> > > > >
> > > > >  
> > > > >
> > > > >>We're giving Cluster a go on a lab database cluster with some
> sample
> > > > >>datasets before allocating any real hardware to it. We have a
> dump
> from
> > > > >>another mysql server that is about 250 MB in size, and trying
> to 
> > > > >>import it
> > > > >>into our mysqld node in the cluster results in the following
> error 
> > > > >>after
> > > > >>more than an hour of importing (using mysql <
> import.file):
> > > > >>
> > > > >>ERROR 1205 (HY000) at line 559: Lock wait timeout exceeded;
> try 
> > > > >>restarting
> > > > >>transaction
> > > > >>
> > > > >>    
> > > > >>
> > > > >
> > > > >In some cases this error code could occur due to a node failure.
> Check
> 
> > > > >cluster log
> > > > >if any strange things occurred.
> > > > >
> > > > >Rgrds Mikael
> > > > >
> > > > >  
> > > > >
> > > > >>Our cluster config is 3 hosts: 2x servers with 1024MB RAM and
> > > > >>2-PIII/850MHz and SCSI disks, one box 128MB RAM and
> 1-PIII/700MHz and
> 
> > > > >>IDE
> > > > >>disk. The two servers with 1GB RAM are the NDB nodes, the
> other is
> the 
> > > > >>API
> > > > >>and MGM node. Below is our config.ini for the cluster. Could
> this 
> > > > >>error be
> > > > >>the result of low system resources on the SQL node, or are
> there 
> > > > >>settings
> > > > >>that we should adjust in the config? And what would cause the
> import
> to
> > > > >>take over an hour for a 250 MB database?
> > > > >>
> > > > >>[NDBD DEFAULT]
> > > > >>NoOfReplicas=2
> > > > >>DataMemory=640M
> > > > >>IndexMemory=128M
> > > > >>LogLevelStartup=1
> > > > >>LogLevelShutdown=1
> > > > >>LogLevelStatistic=1
> > > > >>LogLevelCheckpoint=1
> > > > >>LogLevelNodeRestart=1
> > > > >>LogLevelConnection=1
> > > > >>LogLevelError=1
> > > > >>LogLevelInfo=1
> > > > >>MaxNoOfConcurrentOperations=250000
> > > > >>
> > > > >>[TCP DEFAULT]
> > > > >>
> > > > >>[NDB_MGMD]
> > > > >>hostname=abajo-dev-db-temp.sento.com
> > > > >>datadir=/var/lib/mysql-cluster
> > > > >>ArbitrationRank=1
> > > > >>
> > > > >>[NDBD]
> > > > >>hostname=abajo-dev-db-1.sento.com
> > > > >>datadir=/var/lib/mysql-cluster
> > > > >>
> > > > >>[NDBD]
> > > > >>hostname=abajo-dev-db-2.sento.com
> > > > >>datadir=/var/lib/mysql-cluster
> > > > >>
> > > > >>[MYSQLD]
> > > > >>hostname=abajo-dev-db-temp.sento.com
> > > > >>ArbitrationRank=0
> > > > >>
> > > > >>TIA
> > > > >>
> > > > >>DS
> > > > >>
> > > > >>-- 
> > > > >>    
> > > > >>
> > > > >
> > > > >
> > > > >
> > > > >		
> > > > >__________________________________ 
> > > > >Do you Yahoo!? 
> > > > >Yahoo! Small Business - Try our new resources site!
> > > > >http://smallbusiness.yahoo.com/resources/
> > > > >
> > > > >  
> > > > >
> > > > 
> > > > 
> > > > -- 
> > > > MySQL Cluster Mailing List
> > > > For list archives: http://lists.mysql.com/cluster
> > > > To unsubscribe:   
> > http://lists.mysql.com/cluster?unsub=1
> > > > 
> > > > 
> > > 
> > > 
> > > 		
> > > __________________________________ 
> > > Do you Yahoo!? 
> > > Yahoo! Small Business - Try our new resources site!
> > > http://smallbusiness.yahoo.com/resources/
> > > 
> > 
> > 
> > 		
> > __________________________________ 
> > Yahoo! Mail Mobile 
> > Take Yahoo! Mail with you! Check email on your mobile phone. 
> > http://mobile.yahoo.com/learn/mail 
> 
=== message truncated ===

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
Thread
Workaround for ERROR 1205 (HY000): Lock wait timeout exceeded;Jim Hoadley14 Apr
  • Re: Workaround for ERROR 1205 (HY000): Lock wait timeout exceeded;Jim Hoadley14 Apr
    • Re: Workaround for ERROR 1205 (HY000): Lock wait timeout exceeded;Jim Hoadley21 Apr