| Bug #11868 | update with subquery fails | ||
|---|---|---|---|
| Submitted: | 11 Jul 2005 18:33 | Modified: | 17 Jul 2005 0:33 |
| Reporter: | Dave Rogers | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.7/5.0.10 | OS: | Linux (Fedora Core 4/windows) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[11 Jul 2005 19:41]
MySQL Verification Team
mysql> update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton
-> and t1.startscandate = t2.startscandate and t2.severity = 4 and t2.falsepos = 1
-> group by ipaton);
ERROR 2013 (HY000): Lost connection to MySQL server during query
210711 16:10:38 InnoDB: Started; log sequence number 0 43675
210711 16:10:38 [Note] libexec/mysqld: ready for connections.
Version: '5.0.10-beta-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
mysqld got signal 11;
[11 Jul 2005 19:46]
MySQL Verification Team
Below backtrace on Windows:
> mysqld-debug.exe!add_cond_and_fix(Item * * e1=0x0000000c, Item * e2=0x00ffab78) Line 5110 + 0x3 C++
mysqld-debug.exe!add_not_null_conds(JOIN * join=0x00ff9cd8) Line 5185 + 0x10 C++
mysqld-debug.exe!make_join_select(JOIN * join=0x00ff9cd8, SQL_SELECT * select=0x030cd460, Item * cond=0x030cc6e0) Line 5333 + 0x9 C++
mysqld-debug.exe!JOIN::optimize() Line 736 + 0x1d C++
mysqld-debug.exe!subselect_single_select_engine::exec() Line 1434 + 0xb C++
mysqld-debug.exe!Item_subselect::exec() Line 198 + 0x13 C++
mysqld-debug.exe!Item_singlerow_subselect::val_int() Line 463 + 0xe C++
mysqld-debug.exe!Item::save_in_field(Field * field=0x030b2338, int no_conversions=0) Line 3520 + 0x11 C++
mysqld-debug.exe!fill_record(THD * thd=0x030ae640, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0) Line 3808 + 0x11 C++
mysqld-debug.exe!fill_record_n_invoke_before_triggers(THD * thd=0x030ae640, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0, Table_triggers_list * triggers=0x00000000, trg_event_type event=TRG_EVENT_UPDATE) Line 3849 + 0x15 C++
mysqld-debug.exe!mysql_update(THD * thd=0x030ae640, st_table_list * table_list=0x030cb730, List<Item> & fields={...}, List<Item> & values={...}, Item * conds=0x00000000, unsigned int order_num=0, st_order * order=0x00000000, unsigned long limit=4294967295, enum_duplicates handle_duplicates=DUP_ERROR, int ignore=0) Line 399 + 0x1c C++
mysqld-debug.exe!mysql_execute_command(THD * thd=0x030ae640) Line 3193 + 0x5a C++
mysqld-debug.exe!mysql_parse(THD * thd=0x030ae640, char * inBuf=0x030cb630, unsigned int length=175) Line 5382 + 0x9 C++
mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x030ae640, char * packet=0x030c3571, unsigned int packet_length=176) Line 1674 + 0x1d C++
mysqld-debug.exe!do_command(THD * thd=0x030ae640) Line 1477 + 0x31 C++
mysqld-debug.exe!handle_one_connection(void * arg=0x030ae640) Line 1126 + 0x9 C++
mysqld-debug.exe!pthread_start(void * param=0x030b2df0) Line 63 + 0x7 C
mysqld-debug.exe!_threadstart(void * ptd=0x030afc60) Line 173 + 0xd C
kernel32.dll!7c80b50b()
kernel32.dll!7c8399f3()
[11 Jul 2005 22:39]
MySQL Verification Team
Backtrace on Linux:
210711 19:33:45 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.10-beta-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
[New Thread 1132444592 (LWP 9773)]
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1132444592 (LWP 9773)]
0x08239308 in add_cond_and_fix (e1=0xc, e2=0x8dfe950) at sql_select.cc:5110
5110 if (*e1)
(gdb) backtrace full
#0 0x08239308 in add_cond_and_fix (e1=0xc, e2=0x8dfe950) at sql_select.cc:5110
No locals.
#1 0x08224a74 in add_not_null_conds (join=0x8e2dd00) at sql_select.cc:5185
item = (class Item *) 0x8dfd480
not_null_item = (class Item_field *) 0x8dfd480
referred_tab = (JOIN_TAB *) 0x0
notnull = (class Item_func_isnotnull *) 0x8dfe950
keypart = 1
tab = (JOIN_TAB *) 0x8dfe390
i = 0
_db_func_ = 0x85fbbad "make_join_select"
_db_file_ = 0x8224d24 "\203Ä \203}\f"
_db_level_ = 1132438408
_db_framep_ = (char **) 0x87824f8
#2 0x08224d3c in make_join_select (join=0x8e2dd00, select=0x8dfe6c8, cond=0x8dfd9d0) at sql_select.cc:5333
used_tables = 610278397228079676
thd = (class THD *) 0x8df5158
_db_func_ = 0x0
_db_file_ = 0x87824f8 "ø$x\b"
_db_level_ = 148888124
_db_framep_ = (char **) 0x0
#3 0x08218b71 in JOIN::optimize (this=0x8e2dd00) at sql_select.cc:736
_db_func_ = 0xffffffff <Address 0xffffffff out of bounds>
_db_file_ = 0xffffffff <Address 0xffffffff out of bounds>
_db_level_ = 148884992
_db_framep_ = (char **) 0x8dfd974
sel = (SELECT_LEX *) 0x8dfccb8
#4 0x0819e517 in subselect_single_select_engine::exec (this=0x8dfdbf8) at item_subselect.cc:1434
unit = (SELECT_LEX_UNIT *) 0x8dfce00
_db_func_ = 0x8dfd110 ""
_db_file_ = 0x8df5158 "\b©[\bè\235x\bì\235x\b\030©[\bPéß\bxQß\b"
_db_level_ = 136373744
_db_framep_ = (char **) 0x437fa618
save_where = 0x85fa7b9 "field list"
save_select = (SELECT_LEX *) 0x8df5384
#5 0x0819afee in Item_subselect::exec (this=0x8dfdb70) at item_subselect.cc:198
res = 148888296
#6 0x0819bb50 in Item_singlerow_subselect::val_int (this=0x8dfdb70) at item_subselect.cc:463
No locals.
---Type <return> to continue, or q <return> to quit---
#7 0x0814a134 in Item::save_in_field (this=0x8dfdb70, field=0x8e29980, no_conversions=false) at item.cc:3520
nr = 576460765328551887
error = 1132439736
#8 0x08210975 in fill_record (thd=0x8df5158, fields=@0x8df5408, values=@0x8df55c4, ignore_errors=false) at sql_base.cc:3801
rfield = (class Field *) 0x8e29980
table = (TABLE *) 0x8e29038
f = {<base_list_iterator> = {list = 0x8df5408, el = 0x8dfdc48, prev = 0x0, current = 0x0}, <No data fields>}
v = {<base_list_iterator> = {list = 0x8df55c4, el = 0x8dfdc50, prev = 0x0, current = 0x0}, <No data fields>}
value = (class Item *) 0x8dfdb70
field = (class Item_field *) 0x8dfcc30
_db_func_ = 0x437fa894 "HÜß\b"
_db_file_ = 0x1 <Address 0x1 out of bounds>
_db_level_ = 0
_db_framep_ = (char **) 0x0
#9 0x08210a20 in fill_record_n_invoke_before_triggers (thd=0x8df5158, fields=@0x8df5408, values=@0x8df55c4, ignore_errors=false,
triggers=0x0, event=TRG_EVENT_UPDATE) at sql_base.cc:3840
No locals.
#10 0x08246b12 in mysql_update (thd=0x8df5158, table_list=0x8dfcac8, fields=@0x8df5408, values=@0x8df55c4, conds=0x0, order_num=0,
order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false) at sql_update.cc:397
using_limit = false
safe_update = false
used_key_is_modified = false
transactional_table = false
res = 136219145
error = 0
used_index = 64
want_privilege = 0
table_count = 2
query_id = 8
timestamp_query_id = 0
updated = 0
found = 0
old_used_keys = {map = 1}
table = (TABLE *) 0x8e29038
select = (SQL_SELECT *) 0x0
info = {table = 0x8e29038, file = 0x8e29748, forms = 0x437faa40, read_record = 0x828d806 <rr_sequential>, thd = 0x8df5158,
select = 0x0, cache_records = 0, ref_length = 6, struct_length = 0, reclength = 0, rec_cache_size = 0, error_offset = 0, index = 0,
ref_pos = 0x0, record = 0x8e29838 "õ&Ï\020À\001", rec_buf = 0x0, cache = 0x0, cache_pos = 0x0, cache_end = 0x0, read_positions = 0x0,
io_cache = 0x0, print_error = true, ignore_not_found_rows = false}
---Type <return> to continue, or q <return> to quit---
select_lex = (SELECT_LEX *) 0x8df5384
_db_func_ = 0x5 <Address 0x5 out of bounds>
_db_file_ = 0x8dfca5f "group by ipaton)"
_db_level_ = 140995488
_db_framep_ = (char **) 0x0
#11 0x081e458c in mysql_execute_command (thd=0x8df5158) at sql_parse.cc:3185
res = false
result = 0
lex = (LEX *) 0x8df5198
select_lex = (SELECT_LEX *) 0x8df5384
slave_fake_lock = false
fake_prev_lock = (MYSQL_LOCK *) 0x0
first_table = (TABLE_LIST *) 0x8dfcac8
all_tables = (TABLE_LIST *) 0x8dfcac8
unit = (SELECT_LEX_UNIT *) 0x8df51a8
_db_func_ = 0x0
_db_file_ = 0x0
_db_level_ = 0
_db_framep_ = (char **) 0x437fb04c
#12 0x081ea096 in mysql_parse (thd=0x8df5158,
inBuf=0x8dfc9c0 "update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton\nand t1.startscandate = t2.startscandate and t2.severity = 4 and t2.falsepos = 1\ngroup by ipaton)", length=175) at sql_parse.cc:5381
lex = (LEX *) 0x8df5198
_db_func_ = 0x878c000 "\210\021`\b"
_db_file_ = 0x81e0bda "\203Ä \203=\024Äy\b"
_db_level_ = 1132442408
_db_framep_ = (char **) 0x437fbbb0
#13 0x081e0c3a in dispatch_command (command=COM_QUERY, thd=0x8df5158,
packet=0x8e18791 "update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton\nand t1.startscandate = t2.startscandate and t2.severity = 4 and t2.falsepos = 1\ngroup by ipaton)", packet_length=176) at sql_parse.cc:1674
packet_end = 0x8dfca6f ""
net = (NET *) 0x8df58a0
error = false
_db_func_ = 0x6076f8 <Address 0x6076f8 out of bounds>
_db_file_ = 0x0
_db_level_ = 136924480
_db_framep_ = (char **) 0x437fb348
#14 0x081e0521 in do_command (thd=0x8df5158) at sql_parse.cc:1477
packet = 0x8e18790 "\003update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton\nand t1.startscandate = t2.star---Type <return> to continue, or q <return> to quit---
tscandate and t2.severity = 4 and t2.falsepos = 1\ngroup by ipaton)"
old_timeout = 30
packet_length = 176
net = (NET *) 0x8df58a0
command = COM_QUERY
_db_func_ = 0x8df619c "ÿÿÿÿ"
_db_file_ = 0x81ba1e7 "\203Ä\020ÉÃU\211å\203ì\b\203ì\fÿu\bè\217"
_db_level_ = 1132442472
_db_framep_ = (char **) 0x1010
#15 0x081df72f in handle_one_connection (arg=0x8df5158) at sql_parse.cc:1126
error = 0
net = (NET *) 0x8df58a0
thd = (class THD *) 0x8df5158
launch_time = 0
set = {__val = {0 <repeats 32 times>}}
#16 0x4017daa7 in start_thread () from /lib/tls/libpthread.so.0
No symbol table info available.
#17 0x402aec2e in clone () from /lib/tls/libc.so.6
No symbol table info available.
(gdb)
[15 Jul 2005 17:43]
Igor Babaev
This crash can be demonstrated on the latest tree for 4.1 as well.
The problem is due to the new code that adds NOT NULL predicates
It contains the following statements:
JOIN_TAB *referred_tab= not_null_item->field->table->reginfo.join_tab;
...
add_cond_and_fix(&referred_tab->select_cond, notnull);
The join_tab field is equal to 0 for the t1 table in the reported update statement.
That's why dereferencing of referred_tab causes a crash for the update.
The code adding NOT NULL predicates had another problem fixed by the patch for
bug #11482. Both problems are related, but formally they are caused by different
bugs.
[15 Jul 2005 17:57]
Evgeny Potemkin
Add_not_null_conds() were tried to optimize query by adding NOT NULL constraint to t1.ipaton. For doing this it adds Item_not_null to not_null_item->field->table->reginfo.join_tab->select_cond. But for UPDATE that join_tab is null and try to get acces by it caused the crash.
[15 Jul 2005 20:33]
Bugs System
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/internals/27194
[15 Jul 2005 22:01]
Evgeny Potemkin
Fixed in 4.1.14, cset 1.2359.2.1
[17 Jul 2005 0:33]
Paul DuBois
Noted in 4.1.14 changelog.
Description: The following query fails with the error: ERROR 2013 (HY000): Lost connection to MySQL server during query update t1 set t1.ncount = (select count(*) from t2 where t1.ipaton = t2.ipaton and t1.startscandate = t2.startscandate and t2.severity = 4 and t2.falsepos = 1 group by ipaton); This query with the same data works on MySQL 4.1.9. How to repeat: CREATE TABLE `t1` ( `ipaton` int(4) unsigned NOT NULL default '0', `pcount` int(4) default NULL, `ncount` int(4) default NULL, `startscandate` datetime default NULL, KEY `aaa` (`ipaton`,`startscandate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `t1` VALUES (03222327078,1,NULL,'2005-03-31 02:30:25'), (3231378172,1,NULL,'2005-03-31 06:40:23'), (3222327085,1,NULL,'2005-05-02 03:38:57'), (3222449185,6,NULL,'2005-04-29 06:30:43'), (3236012060,36,NULL,'2005-01-11 09:03:02'), (3231378050,1,NULL,'2005-04-30 08:46:25'); CREATE TABLE `t2` ( `ip` varchar(20) default NULL, `falsepos` int(2) default NULL, `severity` int(2) default NULL, `pluginid` int(4) default NULL, `detailsid` int(4) default NULL, `startscandate` datetime NOT NULL default '0000-00-00 00:00:00', `service` varchar(20) default NULL, `risk` varchar(20) NOT NULL default '', `port` int(3) unsigned default NULL, `ipaton` int(4) unsigned default NULL, `daemon` char(1) NOT NULL default '', `scansourceid` int(1) NOT NULL default '1', KEY `i_dateid` (`startscandate`,`detailsid`,`pluginid`), KEY `i_ipfs` (`ipaton`,`port`,`falsepos`,`severity`), KEY `i_ipr` (`ipaton`,`port`,`risk`), KEY `i_issfp` (`ipaton`,`startscandate`,`port`,`severity`,`falsepos`), KEY `i_ipfst` (`ipaton`,`port`,`falsepos`,`severity`,`risk`), KEY `i_ipsfst` (`ipaton`,`port`,`startscandate`,`falsepos`,`severity`,`risk`), KEY `aaa` (`ipaton`,`startscandate`,`severity`,`falsepos`,`risk`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `t2` VALUES ('192.16.207.38',1,0,11936,113866,'2005-03-31 02:30:25','','none',0,3222327078,'',1), ('192.154.234.252',1,0,10150,203152,'2005-04-30 08:46:25','137/udp','medium',137,3231378172,'',1), ('192.16.207.45',1,0,11936,113866,'2005-03-31 06:40:23','','none',0,3222327085,'',1), ('192.18.172.33',1,0,11936,125649,'2005-05-02 03:38:57','','none',0,3222449185,'',1), ('192.225.160.28',1,0,99999,1188524,'2005-04-29 06:30:43','','none',0,3236012060,'',1), ('192.154.234.130',1,0,10150,1236531,'2005-01-11 03:03:02','137/udp','medium',137,3231378050,'',1), ('192.16.207.38',1,4,11835,604730,'2005-03-31 02:30:25','135/tcp','high',135,3473985318,'',1), ('192.16.207.45',1,4,11835,604730,'2005-03-31 06:40:23','135/tcp','high',135,3473985325,'',1), ('192.18.172.33',0,4,11791,301,'2005-05-02 03:38:57','161/udp','high',161,3474107425,'',1);