Bug #29441 Locking error
Submitted: 29 Jun 2007 10:31 Modified: 10 Jul 2007 13:31
Reporter: Sany Das Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.1.16 OS:Linux (Locking Error on contineous insertions)
Assigned to: CPU Architecture:Any

[29 Jun 2007 10:31] Sany Das
Description:
We have 2 datanodes 2 api nodes and 1 mgmd node

when we try to insert values contineously through SQL procedures into a table that is in ndbd cluster then ,it abort insertion in between showing error

Error:1205 Lock wait timeout exceeded try restarting transaction.

The configuration files are

" my.cnf  "  in data nodes
=========================
[client]				 
port	= 3306				 
socket	= /var/lib/mysql/mysql.sock	 
[mysqld]				 
port	= 3306				 
socket	= /var/lib/mysql/mysql.sock	 
datadir = /var/lib/mysql		 
back-log=75
skip-innodb
max_connections=500
skip-locking				 
key_buffer = 384M			
myisam_sort_buffer_size=64m
join_buffer_size=1M
max_allowed_packet = 64M			 
table_cache = 1800
thread_cache_size=384
wait_timeout=7200
connect_timeout=10
tmp_table_size=64M
max_heap_table_size=64M
max_connect_errors=1000
read_rnd_buffer_size=524288
bulk_insert_buffer_size=8M
sort_buffer_size = 2M			 
net_buffer_length = 8K			 
read_buffer_size = 1M			 
log-bin=mysql-bin			 
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
query_prealloc_size=65536
querry_alloc_block-size=131072

[mysqld_safe]
nice=-5
open_files_limit=8192

[mysqldump]				 
quick					 
max_allowed_packet = 16M		 
[mysql]					 
no-auto-rehash				 

[myisamchk]				 
key_buffer = 64M			 
sort_buffer_size = 64M			 
read_buffer = 16M 			 
write_buffer = 16M			 
  			 
[mysqlhotcopy]				 
interactive-timeout  			 
[mysqld]				 
ndbcluster 				 
ndb-connectstring=192.168.3.97 
[mysql_cluster] 			 
ndb-connectstring=192.168.3.97  

and "config.ini" in mgmd node is
================================

[NDBD DEFAULT]				
NoOfReplicas=2				
DataMemory=600M				
IndexMemory=200M	
RedoBuffer=300M

		
						
# TCP/IP options:				
[TCP DEFAULT]				
portnumber=2202				
						
# Management process options:		
[NDB_MGMD] 				
						
hostname=192.168.3.97  	# Hostname or IP address of MGM node		
datadir=/var/lib/mysql-cluster	# Directory for MGM node logfiles		

						
						
							
# Options for data node 1 :			
[NDBD]						
		# (one [NDBD] section per data node)	

RedoBuffer=500M
DataMemory=256M
IndexMemory=256M							
hostname=192.168.3.99	# Hostname or IPs 	
datadir=/var/lib/mysql	# path for data node's datafiles 
MaxNoOfAttributes=15000	
TimeBetweenLocalCheckPoints=6
TimeBetweenGlobalCheckPoints=10			
MaxNoOfTables=1000				
MaxNoOfOrderedIndexes=10000			
MaxNoOfUniqueHashIndexes=10000
MaxNoOfConcurrentTransactions=5000			
MaxNoOfConcurrentOperations=250000            	
TransactionBufferMemory=10M
NoOfFragmentLogFiles=300
TimeBetweenWatchDogCheck=600000
MaxNoOfLocalOperations=275000			
							
							
# Options for data node 2 :			
[NDBD]						
		# (one [NDBD] section per data node)	

RedoBuffer=500M
DataMemory=256M	
IndexMemory=256M					
hostname=192.168.3.100	# Hostname or IPs 	
datadir=/var/lib/mysql	# path for data node's datafiles 
MaxNoOfAttributes=15000				
TimeBetweenLocalCheckPoints=6
TimeBetweenGlobalCheckPoints=10
MaxNoOfTables=1000				
MaxNoOfOrderedIndexes=10000			
MaxNoOfUniqueHashIndexes=10000	
MaxNoOfConcurrentTransactions=5000		
MaxNoOfConcurrentOperations=250000
TransactionBufferMemory=10M
NoOfFragmentLogFiles=300
TimeBetweenWatchDogCheck=600000            	
MaxNoOfLocalOperations=275000			
							
	# SQL node options:				
[mysqld]					 
hostname=192.168.3.96		# SQL Node IP
[mysqld]
hostname=192.168.3.95		# SQL/Offline IP	 	 

How to repeat:
set cluster with atleast two data nodes

and try to insert contineously into ndbd tables

observe the result after some time
[6 Jul 2007 22:27] Adam Dixon
Can you provide the stored procedures, schema and approximate concurrency you were dealing with so we can try to reproduce this locally?
[10 Jul 2007 13:31] Hartmut Holzgraefe
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.