MySQL Bugs: #44403: OPTIMIZE TABLE blocks read/write on NDB tables

Bug #44403	OPTIMIZE TABLE blocks read/write on NDB tables
Submitted:	22 Apr 2009 9:33	Modified:	30 Apr 2009 13:10
Reporter:	Mikiya Okuno	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
While executing OPTIMIZE TABLE command, reads and writes to the table are blocked from the same SQL node where OPTIMIZE TABLE command is issued. The table can be still read and written from other SQL nodes. This is because OPTIMIZE TABLE acquires TL_WRITE lock when OPTIMIZE TABLE command is issued. lock_type should be changed to TL_WRITE_ALLOW_WRITE during OPTIMIZE TABLE.

How to repeat:
- Run more than one SQL nodes.
- Populate some tables with variable length columns.
- Run OPTIMIZE TABLE command.
- See reads/writes are blocked from other sessions on the same SQL node.
- See reads/writes are not blocked on the other SQL nodes.

Suggested fix:
nada

This happens on MySQL Cluster 6.3.24 too.

Is this just a cluster issue? Seems this would be above the ndb handler.

IMHO, what type of lock is acquired depends on the type of storage engine. For example, as OPTIMIZE TABLE is mapped to ALTER TABLE, we can read and cannot write during OPTIMIZE TABLE command. See the stack trace below:

(gdb) bt
#0  mysql_alter_table (thd=0x108fa00, new_db=0x0, new_name=0x0, create_info=0xb0b8d870, table_list=0x10990a8, alter_info=0xb0b8d8f8, order_num=0, order=0x0, ignore=false) at sql_table.cc:6057
#1  0x001fc034 in mysql_recreate_table (thd=0x108fa00, table_list=0x10990a8) at sql_table.cc:7418
#2  0x001fce6a in mysql_admin_table (thd=0x108fa00, tables=0x10990a8, check_opt=0x109105c, operator_name=0x517f6e "optimize", lock_type=TL_WRITE, open_for_modify=false, no_warnings_for_error=false, extra_open_options=0, prepare_func=0, operator_func={__pfn = 0x1e2640 <handler::ha_optimize(THD*, st_ha_check_opt*)>, __delta = 0}, view_operator_func=0) at sql_table.cc:4513
#3  0x001fdf37 in mysql_optimize_table (thd=0x108fa00, tables=0x0, check_opt=0x0) at sql_table.cc:4677
#4  0x000f0534 in mysql_execute_command (thd=0x108fa00) at sql_parse.cc:2985
#5  0x000f3781 in mysql_parse (thd=0x108fa00, inBuf=0x1099010 "optimize table t1", length=17, found_semicolon=0xb0b8ed0c) at sql_parse.cc:5881
#6  0x000f46a9 in dispatch_command (command=COM_QUERY, thd=0x108fa00, packet=<value temporarily unavailable, due to optimizations>, packet_length=17) at sql_parse.cc:1225
#7  0x000f52a6 in do_command (thd=0x108fa00) at sql_parse.cc:858
#8  0x000e1c8a in handle_one_connection (arg=0x108fa00) at sql_connect.cc:1119
#9  0x967f6095 in _pthread_start ()
#10 0x967f5f52 in thread_start ()

I think we can change the lock type when OPTIMIZE TABLE command is issued against an NDB table.

-Version 6.3.23

In version 6.4 we have a Cluster implementation, and in 6.3 it use the default optimize table. Cluster in 6.3 is not the one requiring locks in alter table, that is the a default calling by the basic optimize table function.

Please upgrade, and note that mysql-5.1-telco-7.0 is GA.