MySQL Bugs: #39307: debug assertion when accessing a view and available memory is too low

Bug #39307	debug assertion when accessing a view and available memory is too low
Submitted:	8 Sep 2008 10:23	Modified:	18 Jan 2013 17:29
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
080904  2:20:50 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
mysqld: sql_view.cc:1459: bool mysql_make_view(THD*, File_parser*, TABLE_LIST*, uint): Assertion `thd->lex == table->view' failed.

080904  2:20:50 - mysqld got signal 6 ;

0x81b7b74 handle_segfault + 836
0xe88402 (?)
0xcbc811 (?)
0xcb452b (?)
0x831cd9d mysql_make_view + 3613
0x8204311 open_unireg_entry + 897
0x8209d29 open_table + 2473
0x820a98a open_tables + 858
0x8250894 mysql_update + 276
0x81d6b8d mysql_execute_command
0x81da29f mysql_parse + 639
0x81dadcd dispatch_command + 1405
0x81dd011 handle_one_connection + 2961

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8b1b578 = update ignore `view_table0` set <cut>
thd->thread_id=9

How to repeat:
Either have a real out of memory scenario, or compile mysqld --with-debug=full and run it with --safemalloc-mem-limit=12485760 and then update a view in some threads.  After a few out of memory errors, debug assert will happen.  Will upload testcase later.

happens on select, delete, and update.

This not only happens on Windows.

When you have View that creates a temp table and you don't have enough space for this table then you will run into this problem.

Verified with ubuntu and MySQL 5.1.

It's in 5.5 also. Seeing it in Percona Server, but assume it would be the same in latest MySQL 5.5. Cannot see any memory or disk issues in this case. Would suggest re-triage.

mysqld: /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_view.cc:1650: bool mysql_make_view(THD*, File_parser*, TABLE_LIST*, uint): Assertion `thd->lex == table->view' failed.
00:36:17 UTC - mysqld got signal 6 ;
[...]
Query (7f2df401faf0): select * from v
Connection ID (thread ID): 142

(gdb) bt
#0  0x0000003133e0c60c in pthread_kill () from /lib64/libpthread.so.0
#1  0x00000000007dba48 in my_write_core (sig=6) at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/mysys/stacktrace.c:433
#2  0x00000000006b17b8 in handle_fatal_signal (sig=6)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/signal_handler.cc:249
#3  <signal handler called>
#4  0x0000003133a35925 in raise () from /lib64/libc.so.6
#5  0x0000003133a370d8 in abort () from /lib64/libc.so.6
#6  0x0000003133a2e6a2 in __assert_fail_base () from /lib64/libc.so.6
#7  0x0000003133a2e752 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000061d7f9 in mysql_make_view (thd=0x674d970, parser=<optimized out>, table=0x7f2df401fcb8, flags=0)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_view.cc:1650
#9  0x000000000055f274 in open_new_frm (thd=thd@entry=0x674d970, share=share@entry=0x7f2df4016cc0,
    alias=<optimized out>, db_stat=db_stat@entry=39, prgflag=prgflag@entry=44, ha_open_flags=<optimized out>,
    outparam=outparam@entry=0x0, table_desc=table_desc@entry=0x7f2df401fcb8, mem_root=mem_root@entry=0x7f2ea02a9300)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_base.cc:9108
#10 0x000000000055ff17 in open_table (thd=thd@entry=0x674d970, table_list=table_list@entry=0x7f2df401fcb8,
    mem_root=mem_root@entry=0x7f2ea02a9300, ot_ctx=ot_ctx@entry=0x7f2ea02a92d0)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_base.cc:3017
#11 0x0000000000560f34 in open_and_process_table (new_frm_mem=0x7f2ea02a9300, ot_ctx=0x7f2ea02a92d0,
    has_prelocking_list=false, prelocking_strategy=0x7f2ea02a9430, flags=0, counter=0x7f2ea02a939c,
    tables=0x7f2df401fcb8, lex=0x674f560, thd=0x674d970)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_base.cc:4484
#12 open_tables (thd=thd@entry=0x674d970, start=start@entry=0x7f2ea02a9388, counter=counter@entry=0x7f2ea02a939c,
    flags=flags@entry=0, prelocking_strategy=prelocking_strategy@entry=0x7f2ea02a9430)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_base.cc:4932
#13 0x0000000000561b46 in open_and_lock_tables (thd=0x674d970, tables=0x7f2df401fcb8, derived=true, flags=0,
    prelocking_strategy=0x7f2ea02a9430) at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_base.cc:5536
#14 0x00000000005939c3 in open_and_lock_tables (flags=0, derived=true, tables=0x7f2df401fcb8, thd=0x674d970)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_base.h:475
#15 execute_sqlcom_select (thd=thd@entry=0x674d970, all_tables=0x7f2df401fcb8)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_parse.cc:4698
#16 0x0000000000599fb8 in mysql_execute_command (thd=thd@entry=0x674d970)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_parse.cc:2284
#17 0x00000000005a12cd in mysql_parse (thd=thd@entry=0x674d970, rawbuf=<optimized out>, length=15,
    parser_state=parser_state@entry=0x7f2ea02aa760)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_parse.cc:5811
#18 0x00000000005a1da8 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x674d970,
    packet=packet@entry=0x6776ed1 "select * from v", packet_length=packet_length@entry=15)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_parse.cc:1061
#19 0x00000000005a3363 in do_command (thd=0x674d970)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_parse.cc:788
#20 0x000000000064cdec in do_handle_one_connection (thd_arg=thd_arg@entry=0x674d970)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_connect.cc:1484
#21 0x000000000064cee2 in handle_one_connection (arg=0x674d970)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_connect.cc:1391
#22 0x0000003133e07d14 in start_thread () from /lib64/libpthread.so.0
#23 0x0000003133af167d in clone () from /lib64/libc.so.6
(gdb) frame 8
#8  0x000000000061d7f9 in mysql_make_view (thd=0x674d970, parser=<optimized out>, table=0x7f2df401fcb8, flags=0)
    at /ssd/qa11/5.5/Percona-Server-5.5.28-rel29.1/sql/sql_view.cc:1650
1650      DBUG_ASSERT(thd->lex == table->view);
(gdb) p thd->lex
$4 = (LEX *) 0x674f560
(gdb) p table->view
$5 = (LEX *) 0x0

Can be reproduced using gypsy. I used (missing space before --thr... is correct)

$ ./gypsy --queryfile=qa.sql --host=127.0.0.1:15386 --duration=200000 --threads=20 --shuffle-queries=1 --log=2 --sqlout="/ssd/qa11/test.out" --random-seed=458059348 --auto-reconnect=1--thread-timer=5000 --retry-innodb-deadlock=10 --retry-innodb-timeout=10 --stop-on-error=2003 > qa.out

Noted in 5.1.69, 5.5.31, 5.6.11, 5.7.1.

View access in low memory conditions could raise a debugging assertion.