Thursday, December 25, 2014

MySQL server - Lost connection to server during query

There are many reasons for such an error, thats why MySQL wrote a dedicated subsection at their reference manual (
It happened to me just yesterday while trying to query a large table. 
The way I debugged it was by setting the following: 
1. Log level to 2:  --log-warnings=2 
2. Turn on the slow queries log: slow-query-log=1
Running the query again, revealed the following error in MySQL log file: 
"...Got timeout writing communication packets"

Make sure that your timeout parameters were not set in your MySQL configuration file - their default is: 480 hours which is enough:
Set the following parameters to 28800 seconds - the default is: 60 seconds which is very low for such a query: 

Sunday, October 12, 2014

MySQL Cluster - Migrating a large InnoDB table to NDB Cluster

If this is the first time you try to migrate a large InnoDB table into a NDB cluster, most probably you will run into some issues.
By nature, both storage engines (InnoDB and NDBCluster) have many differences - its highly recommended to read the following:
It really depends on your MySQL cluster architecture, setup and the amount of data you are trying to migrate. But in general, you will probably run into common issues:
1. The table 'table_1' is full
2. Lock wait timeout exceeded; try restarting transaction

These errors can be related to many different configuration issues, but usually you need to consider setting the following important config.ini parameters:
1. DataMemory and IndexMemory - 
The DataMemory value is in bytes and defines the available space to store database records. Note that this will allocate the entire amount specified in your config file, so make sure that you have the required actual space. 
IndexMemory value is in bytes as well and in case you got index, you will need to define this parameter. It controls the amount of storage used to hash the MySQL cluster indexes - depends on your cluster configuration. Lets say you got 4 Nodes (fragments), 2 replicas and there are 1M rows, you can calculate the size using the following formula (see MySQL documentation):

size  = ( (fragments * 32K) + (rows * 18) ) * replicas

2. Lock wait timeout error can be caused by many different configuration issues, but usually its related to the TransactionDeadlockDetectionTimeout parameter. 
Setting the timeout will require you to set the MaxBufferedEpochs parameter as well.
NOTE: while setting your TransactionDeadlockDetectionTimeout, its highly recommended to make sure that your mysqld innodb_lock_wait_timeout set to the correct timeout (

I suggest to read the following blog post as well: 

Finally, don't forget to delete the ndbd_mgm config bin (cache file located in the config location) each time you make changes to your config.ini.

Good luck