MySQL Bugs: #34086: Falcon crashes from large insert select statement

Bug #34086	Falcon crashes from large insert select statement
Submitted:	27 Jan 2008 3:59	Modified:	16 Mar 2008 11:21
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.3-alpha/6,0.5BK	OS:	Any
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	crash, falcon, insert, large, SELECT

Description:
An 'insert ... select' statement that inserts 3,500,000 rows into a Falcon table crashes the server.

There is nothing in the db error log. This is the only thing in the stack trace.
#0  0x083eb4b6 in SQLError::SQLError ()

How to repeat:
my.cnf:
innodb_log_files_in_group=3
innodb_log_buffer_size=10M
innodb_log_file_size=500M
innodb_buffer_pool_size=1G
falcon_disable_fsync=1
falcon_record_memory_max=200M
falcon_page_cache_size=800M

SQL:
create table ml(d double primary key) engine=myisam;
-- put 3,500,000 rows into ml

create table fl(d double primary key) engine=myisam;

-- this causes the crash 
insert into fl select * from ml;

Loading the same data into the falcon table by 'load data infile' works fine.

6.0.4-alpha fails with:
ERROR 1296 (HY000): Got error 305 'record memory is exhausted' from Falcon

The same my.cnf values are used:
falcon_record_memory_max=200M
falcon_page_cache_size=800M

Mark,

one question:
  
   create table fl(d double primary key) engine=myisam;

should be

   create table fl(d double primary key) engine=falcon;

right?

Yes, that is a typo. I was comparing MyISAM, InnoDB and Falcon.

With current source server I got the below result:

[miguel@mira 6.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.5-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> insert into fl select * from ml;
ERROR 1296 (HY000): Got error 305 'record memory is exhausted' from Falcon
mysql>

I was able to repeat using the my.cnf provided:

[miguel@mira 6.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> insert into fl select * from ml;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Forgot to add C client application to insert the records:

#include <my_global.h>
#include <m_string.h>
#include "mysql.h"

#define DB_HOST			"192.168.0.175" //"localhost"
#define DB_USER			"miguel" //"root"
#define DB_PASSW		"miguel"
#define DB_NAME			"test"
#define DB_PORT			3306
#define DB_UNIX_SOCKET	"/tmp/mysql.sock" //NULL

void main( void )
{
  MYSQL mysql;
  int x, y;
	char my_sub[255],my_query[255];
 
  mysql_init(&mysql);
  
   
  if (!mysql_real_connect(&mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME,
						   DB_PORT,DB_UNIX_SOCKET,0))
  {
    printf("Error: %s\n",mysql_error(&mysql));
    return;
  }
  else
	printf("Connected to the server: %s\n",mysql_get_server_info(&mysql));

  for (x = 1; x <= 3500000; x++)
  {
	  int10_to_str((int) x,strmov(my_sub, "Insert Into ml values("), 10);
	  strxnmov(my_query,sizeof(my_query)-1,my_sub,")",NullS);
	 	     
	 if (mysql_query( &mysql, my_query) )
	 {
     printf("Error (query): %s\n", mysql_error( &mysql ));
     mysql_close( &mysql );
      return;
  }
 }
  mysql_close(&mysql);
}

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41413

ChangeSet@1.2785, 2008-01-30 12:01:38+01:00, vvaintroub@wva. +4 -0
  Bug#34086 :
   
  *If falcon_page_cache_size is too big, this can lead to 
  crashes. 
  In worst case, if master database is created as a result of the first 
  CREATE TABLE ...ENGINE=FALCON leaves invalid master.fts with 0 bytes 
  This prevents subsequent CREATE TABLE ... ENGINE=FALCON, even after 
  page cache size is reset to a sane value.
  This has been fix with the change.
  
  *Another issue that has been fixed is dereferencing null pointers after
  failing page cache allocation.
   
  *Also, we do not try to initialize Falcon after an already failed 
  initialization.

Chris Powers reviewed the patch and comments;
--------------------
I think the fix is reasonably safe because it mitigates the effect of an already bad situation, that is, it won't make it any worse.

As far as wide impact goes, if Wlad is satisified with the testing of the fix, and that the code path through the exception handling, etc. has been exercised, then I say ship it, only because it will reduce the confusion suffered by users who specifiy too large of a page cache, which is bound to happen.
--------------------
Vlad, You can push this to falcon-team for 6.0.4

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41520

ChangeSet@1.2785, 2008-01-31 19:25:51+01:00, vvaintroub@wva. +6 -0
  Bug#34086 :
   
  *If falcon_page_cache_size is too big, this can lead to 
  crashes.  All existing falcon data will be erased because 
  StorageConnection::initialize()  handles any exception from openDatabase()
  with an attempt to create a new database. 
  
  This has been fix with the change.
  
  *Another issue that has been fixed is dereferencing null pointers after
  failing page cache allocation.
   
  *Also, we do not try to initialize Falcon after an already failed 
  initialization.

Patch is in mysql-6.0-falcon-team and mysql-6.0-release version 6.0.4

Pushed into 6.0.4-alpha

A note has been added to the 6.0.4 changelog: 

When inserting very large data sets into table using INSERT INTO ... SELECT FROM on a Falcon table when the value of falcon_page_cache_size is higher than the available memory. Instead of returning an error, mysqld would crash in this instance.