MariaDB

INSERT ... SELECT consumes all memory and causes crash

Bug #706884 reported by Sergey Petrunia on 2011-01-24

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	MariaDB	Fix Released	Medium	Oleksandr "Sanja" Byelkin	MariaDB 5.2

Bug 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.

Revision history for this message

Sergey Petrunia (sergefp) wrote on 2011-01-24:

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
...

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

Revision history for this message

Sergey Petrunia (sergefp) wrote on 2011-01-24:

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.

Revision history for this message

Sergey Petrunia (sergefp) wrote on 2011-01-24:

mainline MySQL does not have this code, it was added as part of Virtual Columns patch.

Oleksandr "Sanja" Byelkin (sanja-byelkin) on 2011-01-24

Changed in maria:
assignee:	nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)
status:	New → In Progress

Oleksandr "Sanja" Byelkin (sanja-byelkin) on 2011-01-24

Changed in maria:
milestone:	none → 5.2

Sergey Petrunia (sergefp) on 2011-01-24

Changed in maria:
status:	In Progress → Fix Committed

Oleksandr "Sanja" Byelkin (sanja-byelkin) on 2011-01-24

Changed in maria:
importance:	Undecided → Medium

Daniel Bartholomew (dbart) on 2011-12-13

Changed in maria:
status:	Fix Committed → Fix Released

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.