MySQL Bugs: #41581: Alter table add index fails with Falcon

Bug #41581	Alter table add index fails with Falcon
Submitted:	18 Dec 2008 7:35	Modified:	18 Jan 2009 9:14
Reporter:	Allan Packer	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0.8	OS:	Any (Solaris Nevada)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	INDEX

Description:
An attempt to create multiple additional indexes on an existing Falcon table does not result in any indexes being created.  Here's an example:

load data infile "/home1/tpce_flat_files/Trade.srt" into table trade  fields terminated by '|'
--------------
Query OK, 86400000 rows affected (2 hours 14 min 39.29 sec)
Records: 86400000  Deleted: 0  Skipped: 0  Warnings: 0
--------------
commit
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
ALTER TABLE trade
  ADD INDEX idx_t_ca_id_dts ( t_ca_id, t_dts),
  ADD INDEX idx_t_s_symb_dts ( t_s_symb, t_dts),
  ADD INDEX fk_t_st_id (t_st_id),
  ADD INDEX fk_t_tt_id (t_tt_id)
--------------
Query OK, 0 rows affected (1 hour 59 min 11.34 sec)
Records: 0  Duplicates: 0  Warnings: 0
--------------

This was repeated with a number of different tables.

It then proved impossible to shutdown mysqld.   After killing mysqld, Falcon failed to reboot, and reported the following error:

081205  5:35:20 [Note] /home1/dists/falc6.0.8gcc/libexec/mysqld: Normal shutdown
081205  5:35:20 [Note] Event Scheduler: Purging the queue. 0 events
18883: Falcon shutdown
Stalled threads
 Thread 50b357720 (42) sleep=0, grant=0, locks=1, who 0, parent=0
   Pending SerialLog::shutdown state 0 (1) syncObject 1ba9c6e0
Stalled synchronization objects:
 SyncObject 1ba9c6e0: state 1, readers 0, monitor 0, waiters 1
   Waiting thread b357720 (42), type 1; SerialLog::shutdown
------------------------------------
Serial log write took 2 seconds
081207 20:36:28 mysqld_safe Starting mysqld daemon with databases from /home1/dists/falc6.0.8gcc/var
081207 20:36:28 [Note] Plugin 'InnoDB' disabled by command line option
New table space FALCON_USER, id 1, type 0, filename falcon_user.fts
New table space FALCON_TEMPORARY, id 2, type 0, filename falcon_temporary.fts
Recovering database /home1/dists/falc6.0.8gcc/var/falcon_master.fts ...
first recovery block is 86201
last recovery block is 92583
recovery read block is 91107
Bugcheck: assertion ((page->level == level - 1) || (pageNumber == nextPage)) failed at line 319 in file IndexRootPage.cpp
[Falcon] Error: assertion ((page->level == level - 1) || (pageNumber == nextPage)) failed at line 319 in file IndexRootPage.cpp

How to repeat:
A test system can be made available.

Allan,

creating that many indexes on such a big table will take some time - quite some I would say.

Please try to wait until mysqld becomes idle again.

Hakan,

I didn't attempt to shutdown mysqld until several hours after the load had finished.  Having said that, I can't say with certainty what the state of Falcon was at the time, so it's possible it was still doing something.

(Note that the problem with shutting down and restarting mysqld is only a secondary issue for this bug report.)

Regards,
Allan

Note that a successful workaround is to create the indexes before loading the data.

Thank you for the report.

Please provide ouptu of SHOW CREATE TABLE trade and file 'home1/tpce_flat_files/Trade.srt' so we can reproduce it.

Recovery failures are a serious problem in real life (as opposed
to testing and development life).

The post-load work is building a single index from a bunch of 
sorted index segments which will be much faster if we merge 
segments in parallel rather than one at a time.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".