| Bug #906 | union subquery problem | ||
|---|---|---|---|
| Submitted: | 24 Jul 2003 4:05 | Modified: | 11 Aug 2003 3:09 |
| Reporter: | Oleksandr Byelkin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | Any (any) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[24 Jul 2003 13:38]
Heikki Tuuri
Sanja,
the bug is that MySQL first calls ::external_lock() on the table handle like it should, but then it releases the lock and AFTER THAT continues to fetch from the table.
Releasing the lock reduces the 'lock count' to 0, which makes InnoDB to commit the current transaction. That is why there is no 'read view' and we get a seg fault.
Fix: in MySQL those external locks should only be released after we have completed the execution of the statement. This is a general rule which holds also for MyISAM.
Regards,
Heikki
[Switching to Thread 11276 (LWP 27653)]
Breakpoint 1, ha_innobase::external_lock (this=0x8949210, thd=0x8954de8,
lock_type=0) at ha_innodb.cc:4504
4504 row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt;
(gdb) c
Continuing.
Breakpoint 2, ha_innobase::index_read (this=0x8949210, buf=0x8949300 "",
key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY) at ha_innodb.cc:2685
2685 row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_pre
built;
(gdb) bt
#0 ha_innobase::index_read (this=0x8949210, buf=0x8949300 "", key_ptr=0x0,
key_len=0, find_flag=HA_READ_AFTER_KEY) at ha_innodb.cc:2685
#1 0x081793ba in ha_innobase::index_first (this=0x8949210, buf=0x8949300 "")
at ha_innodb.cc:2988
#2 0x081794ca in ha_innobase::rnd_next (this=0x8949210, buf=0x8949300 "")
at ha_innodb.cc:3094
#3 0x081650f9 in rr_sequential (info=0x8963f00) at records.cc:186
#4 0x0813f02d in join_init_read_record (tab=0x8963ed8) at sql_select.cc:5608
#5 0x081380f7 in sub_select (join=0x896b3f8, join_tab=0x8963ed8,
end_of_records=false) at sql_select.cc:5201
#6 0x08137eb5 in do_select (join=0x896b3f8, fields=0x894f160, table=0x0,
procedure=0x0) at sql_select.cc:5110
#7 0x0812fc64 in JOIN::exec (this=0x896b3f8) at sql_select.cc:1320
#8 0x08130285 in mysql_select (thd=0x8954de8, rref_pointer_array=0x894f10c,
tables=0x894f370, wild_num=0, fields=@0x894f160, conds=0x895e328,
og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2164823040, result=0x8963870, unit=0x894f1d8,
select_lex=0x894f0b8, tables_and_fields_initied=true) at sql_select.cc:1423
#9 0x0819e75c in mysql_derived (thd=0x8954de8, lex=0x8955060, unit=0x894f1d8,
org_table_list=0x895ea78) at sql_derived.cc:180
#10 0x081103de in mysql_execute_command (thd=0x8954de8) at sql_parse.cc:1693
#11 0x08114d81 in mysql_parse (thd=0x8954de8,
inBuf=0x894ea70 "select * from\n(\nselect * from user where user_id in\n(\ns
elect object_id from group_right inner join user_group on group_right.group_id\n
= user_group.group_id\ninner join right_type on group_right.right_i"...,
length=1479) at sql_parse.cc:3629
#12 0x0810f55e in dispatch_command (command=COM_QUERY, thd=0x8954de8,
packet=0x89560a1 "select * from\n(\nselect * from user where user_id in\n(\n
select object_id from group_right inner join user_group on group_right.group_id\
n= user_group.group_id\ninner join right_type on group_right.right_i"...,
packet_length=1480) at sql_parse.cc:1328
#13 0x0811635d in do_command (thd=0x8954de8) at sql_parse.cc:1099
#14 0x0810e497 in handle_one_connection (arg=0x8954de8) at sql_parse.cc:876
#15 0x40157e67 in pthread_start_thread () from /lib/libpthread.so.0
#16 0x40157eb5 in pthread_start_thread_event () from /lib/libpthread.so.0
(gdb) c
Continuing.
Breakpoint 1, ha_innobase::external_lock (this=0x8949210, thd=0x8954de8,
lock_type=2) at ha_innodb.cc:4504
4504 row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt;
(gdb) bt
#0 ha_innobase::external_lock (this=0x8949210, thd=0x8954de8, lock_type=2)
at ha_innodb.cc:4504
#1 0x080ffde4 in unlock_external (thd=0x8954de8, table=0x896aad4, count=7)
at lock.cc:379
#2 0x080ffcb5 in mysql_unlock_read_tables (thd=0x8954de8, sql_lock=0x896aaa8)
at lock.cc:272
#3 0x081346fa in join_free (join=0x89705a0, full=false) at sql_select.cc:3510
#4 0x08137ef5 in do_select (join=0x89705a0, fields=0x894f5b8, table=0x0,
procedure=0x0) at sql_select.cc:5126
#5 0x0812fc64 in JOIN::exec (this=0x89705a0) at sql_select.cc:1320
#6 0x08130285 in mysql_select (thd=0x8954de8, rref_pointer_array=0x894f59c,
tables=0x894f5c8, wild_num=0, fields=@0x894f5b8, conds=0x0, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=17339392,
result=0x895e390, unit=0x894f548, select_lex=0x896eee8,
tables_and_fields_initied=false) at sql_select.cc:1423
#7 0x0819df0b in st_select_lex_unit::exec (this=0x894f548) at sql_union.cc:354
#8 0x080ec547 in subselect_union_engine::exec (this=0x895ea30)
at item_subselect.cc:867
#9 0x080ea112 in Item_in_subselect::val_int (this=0x895e328)
at item_subselect.cc:131
#10 0x083ce459 in Item::val_int_result (this=0x895e328) at item.h:146
#11 0x080d84a0 in Item_in_optimizer::val_int (this=0x895e3a8)
at item_cmpfunc.cc:409
#12 0x081381a5 in sub_select (join=0x896b3f8, join_tab=0x8963ed8,
end_of_records=false) at sql_select.cc:5223
#13 0x08137eb5 in do_select (join=0x896b3f8, fields=0x894f160, table=0x0,
procedure=0x0) at sql_select.cc:5110
#14 0x0812fc64 in JOIN::exec (this=0x896b3f8) at sql_select.cc:1320
#15 0x08130285 in mysql_select (thd=0x8954de8, rref_pointer_array=0x894f10c,
tables=0x894f370, wild_num=0, fields=@0x894f160, conds=0x895e328,
og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2164823040, result=0x8963870, unit=0x894f1d8,
select_lex=0x894f0b8, tables_and_fields_initied=true) at sql_select.cc:1423
#16 0x0819e75c in mysql_derived (thd=0x8954de8, lex=0x8955060, unit=0x894f1d8,
org_table_list=0x895ea78) at sql_derived.cc:180
#17 0x081103de in mysql_execute_command (thd=0x8954de8) at sql_parse.cc:1693
#18 0x08114d81 in mysql_parse (thd=0x8954de8,
inBuf=0x894ea70 "select * from\n(\nselect * from user where user_id in\n(\ns
elect object_id from group_right inner join user_group on group_right.group_id\n
---Type <return> to continue, or q <return> to quit---
= user_group.group_id\ninner join right_type on group_right.right_i"...,
length=1479) at sql_parse.cc:3629
#19 0x0810f55e in dispatch_command (command=COM_QUERY, thd=0x8954de8,
packet=0x89560a1 "select * from\n(\nselect * from user where user_id in\n(\n
select object_id from group_right inner join user_group on group_right.group_id\
n= user_group.group_id\ninner join right_type on group_right.right_i"...,
packet_length=1480) at sql_parse.cc:1328
#20 0x0811635d in do_command (thd=0x8954de8) at sql_parse.cc:1099
#21 0x0810e497 in handle_one_connection (arg=0x8954de8) at sql_parse.cc:876
#22 0x40157e67 in pthread_start_thread () from /lib/libpthread.so.0
#23 0x40157eb5 in pthread_start_thread_event () from /lib/libpthread.so.0
(gdb) next
4503 {
(gdb)
461 trx = check_trx_exists(thd);
(gdb)
4504 row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt;
(gdb)
461 trx = check_trx_exists(thd);
(gdb)
463 if (prebuilt->trx != trx) {
(gdb)
468 user_thd = thd;
(gdb)
4513 trx = prebuilt->trx;
(gdb)
4515 prebuilt->sql_stat_start = TRUE;
(gdb)
4516 prebuilt->hint_no_need_to_fetch_extra_cols = TRUE;
(gdb)
4518 prebuilt->read_just_key = 0;
(gdb)
4520 if (lock_type == F_WRLCK) {
(gdb)
4527 if (lock_type != F_UNLCK) {
(gdb)
4565 auto_inc_counter_for_this_stat = 0;
(gdb)
4563 trx->n_mysql_tables_in_use--;
(gdb)
4564 prebuilt->mysql_has_locked = FALSE;
(gdb)
4565 auto_inc_counter_for_this_stat = 0;
(gdb)
4570 if (trx->n_mysql_tables_in_use == 0) {
(gdb)
4572 trx->mysql_n_tables_locked = 0;
(gdb)
4573 prebuilt->used_in_HANDLER = FALSE;
(gdb)
185 if (trx->has_search_latch) {
(gdb)
189 if (trx->declared_to_be_inside_innodb) {
(gdb)
192 srv_conc_force_exit_innodb(trx);
(gdb)
4582 & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN
))) {
(gdb)
4583 if (thd->transaction.all.innodb_active_trans !=
0) {
(gdb)
4584 innobase_commit(thd, trx);
(gdb)
4598 DBUG_RETURN(error);
(gdb) c
Continuing.
Breakpoint 3, ha_innobase::general_fetch (this=0x8949210,
buf=0x8949300 "\001", direction=1, match_mode=0) at ha_innodb.cc:2888
2888 {
(gdb) bt
#0 ha_innobase::general_fetch (this=0x8949210, buf=0x8949300 "\001",
direction=1, match_mode=0) at ha_innodb.cc:2888
#1 0x081794eb in ha_innobase::rnd_next (this=0x8949210, buf=0x8949300 "\001")
at ha_innodb.cc:3100
#2 0x081650f9 in rr_sequential (info=0x8963f00) at records.cc:186
#3 0x08138219 in sub_select (join=0x896b3f8, join_tab=0x8963ed8,
end_of_records=false) at sql_select.cc:5238
#4 0x08137eb5 in do_select (join=0x896b3f8, fields=0x894f160, table=0x0,
procedure=0x0) at sql_select.cc:5110
#5 0x0812fc64 in JOIN::exec (this=0x896b3f8) at sql_select.cc:1320
#6 0x08130285 in mysql_select (thd=0x8954de8, rref_pointer_array=0x894f10c,
tables=0x894f370, wild_num=0, fields=@0x894f160, conds=0x895e328,
og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2164823040, result=0x8963870, unit=0x894f1d8,
select_lex=0x894f0b8, tables_and_fields_initied=true) at sql_select.cc:1423
#7 0x0819e75c in mysql_derived (thd=0x8954de8, lex=0x8955060, unit=0x894f1d8,
org_table_list=0x895ea78) at sql_derived.cc:180
#8 0x081103de in mysql_execute_command (thd=0x8954de8) at sql_parse.cc:1693
#9 0x08114d81 in mysql_parse (thd=0x8954de8,
inBuf=0x894ea70 "select * from\n(\nselect * from user where user_id in\n(\ns
elect object_id from group_right inner join user_group on group_right.group_id\n
= user_group.group_id\ninner join right_type on group_right.right_i"...,
length=1479) at sql_parse.cc:3629
#10 0x0810f55e in dispatch_command (command=COM_QUERY, thd=0x8954de8,
packet=0x89560a1 "select * from\n(\nselect * from user where user_id in\n(\n
select object_id from group_right inner join user_group on group_right.group_id\
n= user_group.group_id\ninner join right_type on group_right.right_i"...,
packet_length=1480) at sql_parse.cc:1328
#11 0x0811635d in do_command (thd=0x8954de8) at sql_parse.cc:1099
#12 0x0810e497 in handle_one_connection (arg=0x8954de8) at sql_parse.cc:876
#13 0x40157e67 in pthread_start_thread () from /lib/libpthread.so.0
#14 0x40157eb5 in pthread_start_thread_event () from /lib/libpthread.so.0
(gdb)
[9 Aug 2003 6:56]
Oleksandr Byelkin
ChangeSet 1.1593 03/08/09 14:39:54 bell@sanja.is.com.ua +3 -0 fixed union unlocking problem (BUG#906)
[11 Aug 2003 3:09]
Oleksandr Byelkin
Thank you for bug report. Fix for this bug included in internal source repository and will be present in next release of 4.1
Description: crash in: (gdb) where #0 0x0826462b in read_view_sees_trx_id (view=0x0, trx_id={high = 0, low = 3196054796}) at ../../innobase/include/read0read.ic:53 #1 0x0828d4c4 in lock_clust_rec_cons_read_sees (rec=0x0, index=0x0, view=0x0) at lock0lock.c:537 #2 0x08236d57 in row_search_for_mysql (buf=0x8965d08 "\001", mode=1, prebuilt=0x40273c68, match_mode=0, direction=0) at row0sel.c:3129 #3 0x081c7097 in ha_innobase::general_fetch(char*, unsigned, unsigned) (this=0x8965c20, buf=0x0, direction=0, match_mode=0) at ha_innodb.cc:2675 #4 0x081c743a in ha_innobase::rnd_next(char*) (this=0x8965c20, buf=0x8965d08 "\001") at ha_innodb.cc:2875 #5 0x081b9547 in rr_sequential (info=0x8982408) at records.cc:186 #6 0x0817e1b5 in sub_select (join=0x897a688, join_tab=0x89823e0, end_of_records=3) at sql_select.cc:5238 #7 0x0817ddd1 in do_select (join=0x897a688, fields=0x89823e0, table=0x0, procedure=0x0) at sql_select.cc:5110 #8 0x0817453b in JOIN::exec() (this=0x897a688) at sql_select.cc:1319 #9 0x0817499a in mysql_select(THD*, Item***, st_table_list*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, select_result*, st_select_lex_unit*, st_select_lex*, bool) (thd=0x8961898, rref_pointer_array=0x0, tables=0x0, wild_num=0, fields=@0x0, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2164823040, result=0x897a5d8, unit=0x8962ee0, select_lex=0x8962dd8, tables_and_fields_initied=true) at sql_select.cc:1423 #10 0x081fff5d in mysql_derived(THD*, st_lex*, st_select_lex_unit*, st_table_list*) (thd=0x8961898, lex=0x8961b10, unit=0x8962ee0, org_table_list=0x8971970) at sql_derived.cc:169 #11 0x0814e709 in mysql_execute_command(THD*) (thd=0x8961898) at sql_parse.cc:1691 #12 0x08153a57 in mysql_parse(THD*, char*, unsigned) (thd=0x8961898, inBuf=0x8962798 "select * from\n(\nselect * from user where user_id in\n(\nselect object_id from group_right inner join user_group on group_right.group_id\n= user_group.group_id\ninner join right_type on group_right.right_i"..., length=144055056) at sql_parse.cc:3629 #13 0x0814d690 in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x8961898, packet=0x896d191 "select * from\n(\nselect * from user where user_id in\n(\nselect object_id from group_right inner join user_group on group_right.group_id\n= user_group.group_id\ninner join right_type on group_right.right_i"..., packet_length=1480) at sql_parse.cc:1304 #14 0x0814ceed in do_command(THD*) (thd=0x8961898) at sql_parse.cc:1099 #15 0x0814c3ec in handle_one_connection (arg=0x0) at sql_parse.cc:876 #16 0x400281e0 in pthread_start_thread () from /lib/libpthread.so.0 #17 0x4002825f in pthread_start_thread_event () from /lib/libpthread.so.0 How to repeat: http://georg.fooassociates.com/crash4-1.tar.gz