MySQL Bugs: #32964: Falcon crashes when creating indexes

Bug #32964	Falcon crashes when creating indexes
Submitted:	4 Dec 2007 14:53	Modified:	18 Dec 2007 16:42
Reporter:	Robin Schumacher	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S1 (Critical)
Version:	6.0.3	OS:	Linux (Fedora Core 32 bit)
Assigned to:		CPU Architecture:	Any

Description:
When running the Quest Benchmark Factory set of tests against Falcon, I get a crash when the tool goes to create indexes on the underlying tables (repeated twice now). The format of the tables/indexes and the row counts are below as are the config values.  Index creation attempts were primary key on the first column

mysql> show create table SH_Product\G
*************************** 1. row ***************************
       Table: SH_Product
Create Table: CREATE TABLE `SH_Product` (
  `Project_Number` int(11) NOT NULL,
  `Model` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `Product_Name` char(32) NOT NULL,
  `Price` double NOT NULL,
  `Class` int(11) NOT NULL,
  `Evaluations` char(20) NOT NULL,
  `Description` char(50) NOT NULL,
  `EmployeeID` int(11) NOT NULL,
  `Type` int(11) NOT NULL,
  `VendorID` int(11) NOT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Attempted Indexes: UNIQUE KEY `SH_Product_first` (`ProductID`),
KEY `SH_Product_prime` (`Project_Number`,`EmployeeID`,`Class`)

mysql> show create table SH_Part\G
*************************** 1. row ***************************
       Table: SH_Part
Create Table: CREATE TABLE `SH_Part` (
  `Model` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `Serial_Number` char(12) NOT NULL,
  `Sub_Category` char(5) NOT NULL,
  `Version` int(11) NOT NULL,
  `Part_Name` char(24) NOT NULL,
  `Comment1` char(30) NOT NULL,
  `Price` double NOT NULL,
  `VendorID` int(11) NOT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Attempted Index: KEY `SH_Part_prime` (`ProductID`)

mysql> show create table SH_InsertTable\G
*************************** 1. row ***************************
       Table: SH_InsertTable
Create Table: CREATE TABLE `SH_InsertTable` (
  `Project_Number` int(11) NOT NULL,
  `Model` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `Product_Name` char(32) NOT NULL,
  `Price` double NOT NULL,
  `Class` int(11) NOT NULL,
  `Evaluations` char(20) NOT NULL,
  `Description` char(50) NOT NULL,
  `EmployeeID` int(11) NOT NULL,
  `Type` int(11) NOT NULL,
  `VendorID` int(11) NOT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Attempted Index: KEY `insertidx` (`Project_Number`,`ProductID`)

mysql> select count(*) from SH_Product;
+----------+
| count(*) |
+----------+
|   220000 |
+----------+
1 row in set (1.27 sec)

mysql> select count(*) from SH_Part;
+----------+
| count(*) |
+----------+
|   880000 |
+----------+
1 row in set (4.48 sec)

mysql> select count(*) from SH_InsertTable;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> show global variables like '%fal%';
+----------------------------------+-----------------+
| Variable_name                    | Value           |
+----------------------------------+-----------------+
| falcon_checkpoint_schedule       | 7 * * * * *     |
| falcon_debug_mask                | 0               |
| falcon_debug_server              | OFF             |
| falcon_disable_fsync             | OFF             |
| falcon_index_chill_threshold     | 4               |
| falcon_initial_allocation        | 0               |
| falcon_max_transaction_backlog   | 150             |
| falcon_page_cache_size           | 104857600       |
| falcon_page_size                 | 4096            |
| falcon_record_chill_threshold    | 5               |
| falcon_record_memory_max         | 262144000       |
| falcon_record_scavenge_floor     | 50              |
| falcon_record_scavenge_threshold | 67              |
| falcon_scavenge_schedule         | 15,45 * * * * * |
| falcon_serial_log_buffers        | 10              |
| falcon_serial_log_dir            |                 |
+----------------------------------+-----------------+

How to repeat:
Run Quest tool's scaleable benchmark test

Robin, can you add a link to where these tools/tests can be downloaded?

Kevin - 

The tool can be downloaded from http://www.quest.com/benchmark-factory/

I'm trying to figure out where the crash is.  I let the tool create myisam objects instead of falcon (and all ran fine), then I created Falcon clones of the myisam tables manually (Create like...alter table engine=falcon), inserted the data manually, and added the indexes manually and they created OK.  Not sure if the tool is running other code or not.  Last message the tools sends is "Creating indexes" and then the MySQL instance crashes.

Removed the old falcon user tablespace, rebooted both test boxes, and reran benchmark test.  Crashed again.  Last attempted command before crash:

mysql> show full processlist;
+----+------+------------------+------+---------+------+-------------------+--------------------------------------------
-------+
| Id | User | Host             | db   | Command | Time | State             | Info
       |
+----+------+------------------+------+---------+------+-------------------+--------------------------------------------
-------+
|  1 | root | 192.168.0.3:2293 | test | Query   |    0 | NULL              | show full processlist
       |
|  7 | root | 192.168.0.8:1064 | test | Query   |   10 | copy to tmp table | create index SH_Part_prime on SH_Part (Prod
uctID) |
+----+------+------------------+------+---------+------+-------------------+--------------------------------------------
-------+
2 rows in set (0.00 sec)

Robin,

how does the database initialization work?

1. DDL
2. Data load
3. Index creation

If the indexes are created after the the data loading, then most likely this bug is fixed in latest Falcon 6.0.4.

Best regards,

Hakan

New 6.0.4 build fixed index creation; tests now run OK.