[MDEV-2802] LP:706884 - INSERT ... SELECT consumes all memory and causes crash Created: 2011-01-24  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Sergei Petrunia Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug706884.xml    

 Description   

Run the following queries (this is not necessary the minimum testcase):

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (
kp1 int, kp2 int,
filler char(100),
col int,
key(kp1, kp2)
);

set myisam_sort_buffer_size=32*1000*1000;
insert into t1
select
1000 + A.a + 10*B.a + 100*C.a + 1000*D.a + 10000 * F.a,
1,
'filler-data filler-data filler-data filler-data filler-data',
1
from
t0 A, t0 B, t0 C, t0 D, t0 E, t0 F, t0 G, t0 H

You'll see that mysqld will consume more and more memory, and on 32-bit machine will eventually be killed when it has consumed 2G and asks for more.

 Comments   
Comment by Sergei Petrunia [ 2011-01-24 ]

Re: INSERT ... SELECT consumes all memory and causes crash
while INSERT ... SELECT is running, mysqld is making repeated alloc_root() calls:

Breakpoint 3, alloc_root (mem_root=0xa8cf618, length=8) at my_alloc.c:180

Breakpoint 3, alloc_root (mem_root=0xa8cf618, length=8) at my_alloc.c:180

Breakpoint 3, alloc_root (mem_root=0xa8cf618, length=8) at my_alloc.c:180

Breakpoint 3, alloc_root (mem_root=0xa8cf618, length=8) at my_alloc.c:180

Breakpoint 3, alloc_root (mem_root=0xa8cf618, length=8) at my_alloc.c:180
...

The stack trace seems to be always the same:
(gdb) wher
#0 alloc_root (mem_root=0xa8cf618, length=8) at my_alloc.c:180
#1 0x0823d3fb in sql_alloc (Size=8) at thr_malloc.cc:70
#2 0x081b671e in Sql_alloc::operator new (size=8) at sql_list.h:30
#3 0x081b681d in base_list::push_back (this=0x901d3da8, info=0xa8e8788) at sql_list.h:193
#4 0x0831df04 in List<st_table>::push_back (this=0x901d3da8, a=0xa8e8788) at sql_list.h:456
#5 0x0830b383 in fill_record (thd=0xa8cd668, ptr=0xa90fba4, values=@0xa8cf094, ignore_errors=true, use_value=false) at sql_base.cc:8507
#6 0x0830b528 in fill_record_n_invoke_before_triggers (thd=0xa8cd668, ptr=0xa90fb98, values=@0xa8cf094, ignore_errors=true, triggers=0x0, event=TRG_EVENT_INSERT) at sql_base.cc:8577
#7 0x08364f13 in select_insert::store_values (this=0xa9075d8, values=@0xa8cf094) at sql_insert.cc:3274
#8 0x083677a2 in select_insert::send_data (this=0xa9075d8, values=@0xa8cf094) at sql_insert.cc:3210
#9 0x08330eb2 in end_send (join=0xa92f550, join_tab=0xa9552a8, end_of_records=false) at sql_select.cc:14507

Comment by Sergei Petrunia [ 2011-01-24 ]

Re: INSERT ... SELECT consumes all memory and causes crash
in sql/sql_base.cc there is this code:

bool
fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors,
bool use_value)
{
List_iterator_fast<Item> v(values);
List<TABLE> tbl_list;
...
tbl_list.empty();
...
while ((field = *ptr++) && ! thd->is_error())

{ ... tbl_list.push_back(table); }

...

List::push_back() will call alloc_root(). One may not call List::push_back() from a function that's run per each record combination.

Comment by Sergei Petrunia [ 2011-01-24 ]

Re: INSERT ... SELECT consumes all memory and causes crash
mainline MySQL does not have this code, it was added as part of Virtual Columns patch.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 706884

Generated at Thu Feb 08 06:44:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.