Description:
If a user defined function is included in an order by clause, MySQL crashes. This happens on the latest 5.0 and 5.1 releases, I haven't tested earlier releases.
The crash occurs in Item_sum::update_used_tables because aggr_sel is set to NULL(0), or contains an integer instead of a pointer. Also, sometimes aggr_sel is a correct pointer, but aggr_sel->join will be set to NULL or an integer.
I've seen aggr_sel set to 0, 1, 4, 8 and a few other small values, and aggr_sel->join set to 0 and 1 during my testing.
The following is gdb output of one crash:
gdb --args ./mysqld --thread_handling=no-threads --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/development.pid --skip-external-locking --gdb --debug=/tmp/mysql.debug
GNU gdb 6.5
Copyright (C) 2006 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i586-suse-linux"...Using host libthread_db library "/lib/libthread_db.so.1".
(gdb) run
Starting program: /usr/src/packages/BUILD/mysql-5.1.20-beta/mysql-debug-5.1.20-beta/sql/mysqld --thread_handling=no-threads --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/development.pid --skip-external-locking --gdb --debug=/tmp/mysql.debug
Failed to read a valid object file image from memory.
[Thread debugging using libthread_db enabled]
[New Thread -1210136896 (LWP 6006)]
[New Thread -1221948528 (LWP 6009)]
[New Thread -1230341232 (LWP 6010)]
[New Thread -1238733936 (LWP 6011)]
[New Thread -1247126640 (LWP 6012)]
[New Thread -1260299376 (LWP 6013)]
[New Thread -1268692080 (LWP 6014)]
[New Thread -1277084784 (LWP 6015)]
[Thread -1260299376 (zombie) exited]
[New Thread -1285477488 (LWP 6016)]
070726 21:07:25 InnoDB: Started; log sequence number 0 46409
[New Thread -1255568496 (LWP 6017)]
070726 21:07:25 [Note] Event Scheduler: Loaded 0 events
070726 21:07:25 [Note] /usr/src/packages/BUILD/mysql-5.1.20-beta/mysql-debug-5.1.20-beta/sql/mysqld: ready for connections.
Version: '5.1.20-beta-debug' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - Debug (GPL)
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1210136896 (LWP 6006)]
0x081a0b15 in Item_sum::update_used_tables (this=0xb5157288) at item_sum.cc:471
471 used_tables_cache |= (1 << aggr_sel->join->tables) - 1;
(gdb) print aggr_sel
$1 = (class st_select_lex *) 0x0
(gdb) bt
#0 0x081a0b15 in Item_sum::update_used_tables (this=0xb5157288)
at item_sum.cc:471
#1 0x082ac338 in update_depend_map (join=0xb5157a18, order=0xb5157980)
at sql_select.cc:6767
#2 0x082ca108 in remove_const (join=0xb5157a18, first_order=0xb5157980,
cond=0x0, change_list=true, simple_order=0xb5158b11) at sql_select.cc:6824
#3 0x082cc40c in JOIN::optimize (this=0xb5157a18) at sql_select.cc:1009
#4 0x082cf0f4 in mysql_select (thd=0xb5108fc0, rref_pointer_array=0xb510a124,
tables=0xb5157608, wild_num=0, fields=@0xb510a0b4, conds=0x0, og_num=2,
order=0xb5157980, group=0xb5157888, having=0x0, proc_param=0x0,
select_options=2147764736, result=0xb5157a08, unit=0xb5109de4,
select_lex=0xb510a020) at sql_select.cc:2261
#5 0x082d395f in handle_select (thd=0xb5108fc0, lex=0xb5109d88,
result=0xb5157a08, setup_tables_done_option=0) at sql_select.cc:258
#6 0x08255d7c in execute_sqlcom_select (thd=0xb5108fc0, all_tables=0xb5157608)
at sql_parse.cc:4486
#7 0x082575e7 in mysql_execute_command (thd=0xb5108fc0) at sql_parse.cc:1838
#8 0x0826005f in mysql_parse (thd=0xb5108fc0,
inBuf=0xb5156ee0 "select id, avgcost(id, Cost) as Cost, avg(Cost) from test group by id order by Cost desc", length=88, found_semicolon=0xbf9532a0)
at sql_parse.cc:5388
#9 0x08260b3a in dispatch_command (command=COM_QUERY, thd=0xb5108fc0,
packet=0xb514eeb1 "select id, avgcost(id, Cost) as Cost, avg(Cost) from test
group by id order by Cost desc", packet_length=89) at sql_parse.cc:909
#10 0x08261c51 in do_command (thd=0xb5108fc0) at sql_parse.cc:668
#11 0x0824fcb4 in handle_one_connection (arg=0xb5108fc0) at sql_connect.cc:1094
#12 0x08243413 in handle_connection_in_main_thread (thd=0xb5108fc0)
at mysqld.cc:4267
#13 0x08248024 in create_new_thread (thd=0xb5108fc0) at mysqld.cc:4362
#14 0x08248584 in handle_connections_sockets (arg=0x0) at mysqld.cc:4587
#15 0x0824b097 in main (argc=9, argv=0xbf953704) at mysqld.cc:3952
How to repeat:
Compile the udf_examples.c (or just the avgcost part of it) into libudfexample.so with the following command:
gcc -shared -I/usr/include/mysql -o libudfexample.so udf_example.c -l mysqlclient
In MySQL Client:
> use test;
> create table test ( id int, Cost Real );
> insert into test set id=1, Cost=Rand()*100;
> insert into test set id=1, Cost=Rand()*100;
> insert into test set id=1, Cost=Rand()*100;
> insert into test set id=1, Cost=Rand()*100;
> insert into test set id=1, Cost=Rand()*100;
> insert into test set id=2, Cost=Rand()*100;
> insert into test set id=2, Cost=Rand()*100;
> insert into test set id=2, Cost=Rand()*100;
> insert into test set id=2, Cost=Rand()*100;
> insert into test set id=2, Cost=Rand()*100;
> select id, avg(Cost) from test group by id\G
*************************** 1. row ***************************
id: 1
avg(Cost): 47.300459097419
*************************** 2. row ***************************
id: 2
avg(Cost): 55.709741223333
2 rows in set (0.01 sec)
> create aggregate function avgcost returns real soname "libudfexample.so";
> select id, avg(Cost), avgcost(id,Cost) from test group by id\G
*************************** 1. row ***************************
id: 1
avg(Cost): 47.300459097419
avgcost(id,Cost): 47.3005
*************************** 2. row ***************************
id: 2
avg(Cost): 55.709741223333
avgcost(id,Cost): 55.7097
2 rows in set (0.00 sec)
> select id, avg(Cost), avgcost(id,Cost) from test group by id order by avg(Cost) DESC\G
*************************** 1. row ***************************
id: 2
avg(Cost): 55.7097412233332
avgcost(id,Cost): 55.7097
*************************** 2. row ***************************
id: 1
avg(Cost): 47.3004590974194
avgcost(id,Cost): 47.3005
2 rows in set (0.00 sec)
> select id, avg(Cost), avgcost(id,Cost) from test group by id order by avgcost(id,Cost) DESC\G
***CRASH***
Suggested fix:
This is a horrible hack which doesn't solve the root cause, but it does prevent the crash. I don't know if it has any long term consequences, but I haven't seen any in my testing.
diff -dur mysql-5.1.20-beta/sql/item_sum.cc mysql-5.1.20-beta-new/sql/item_sum.cc
--- mysql-5.1.20-beta/sql/item_sum.cc 2007-06-28 04:20:02.000000000 -0600
+++ mysql-5.1.20-beta-new/sql/item_sum.cc 2007-07-26 21:28:55.000000000 -0600
@@ -467,7 +467,8 @@
used_tables_cache&= PSEUDO_TABLE_BITS;
/* the aggregate function is aggregated into its local context */
- used_tables_cache |= (1 << aggr_sel->join->tables) - 1;
+ if (aggr_sel > (void*)0xFFFF && aggr_sel->join > (void*)0xFFFF)
+ used_tables_cache |= (1 << aggr_sel->join->tables) - 1;
}
}
Description: If a user defined function is included in an order by clause, MySQL crashes. This happens on the latest 5.0 and 5.1 releases, I haven't tested earlier releases. The crash occurs in Item_sum::update_used_tables because aggr_sel is set to NULL(0), or contains an integer instead of a pointer. Also, sometimes aggr_sel is a correct pointer, but aggr_sel->join will be set to NULL or an integer. I've seen aggr_sel set to 0, 1, 4, 8 and a few other small values, and aggr_sel->join set to 0 and 1 during my testing. The following is gdb output of one crash: gdb --args ./mysqld --thread_handling=no-threads --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/development.pid --skip-external-locking --gdb --debug=/tmp/mysql.debug GNU gdb 6.5 Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i586-suse-linux"...Using host libthread_db library "/lib/libthread_db.so.1". (gdb) run Starting program: /usr/src/packages/BUILD/mysql-5.1.20-beta/mysql-debug-5.1.20-beta/sql/mysqld --thread_handling=no-threads --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/development.pid --skip-external-locking --gdb --debug=/tmp/mysql.debug Failed to read a valid object file image from memory. [Thread debugging using libthread_db enabled] [New Thread -1210136896 (LWP 6006)] [New Thread -1221948528 (LWP 6009)] [New Thread -1230341232 (LWP 6010)] [New Thread -1238733936 (LWP 6011)] [New Thread -1247126640 (LWP 6012)] [New Thread -1260299376 (LWP 6013)] [New Thread -1268692080 (LWP 6014)] [New Thread -1277084784 (LWP 6015)] [Thread -1260299376 (zombie) exited] [New Thread -1285477488 (LWP 6016)] 070726 21:07:25 InnoDB: Started; log sequence number 0 46409 [New Thread -1255568496 (LWP 6017)] 070726 21:07:25 [Note] Event Scheduler: Loaded 0 events 070726 21:07:25 [Note] /usr/src/packages/BUILD/mysql-5.1.20-beta/mysql-debug-5.1.20-beta/sql/mysqld: ready for connections. Version: '5.1.20-beta-debug' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - Debug (GPL) Program received signal SIGSEGV, Segmentation fault. [Switching to Thread -1210136896 (LWP 6006)] 0x081a0b15 in Item_sum::update_used_tables (this=0xb5157288) at item_sum.cc:471 471 used_tables_cache |= (1 << aggr_sel->join->tables) - 1; (gdb) print aggr_sel $1 = (class st_select_lex *) 0x0 (gdb) bt #0 0x081a0b15 in Item_sum::update_used_tables (this=0xb5157288) at item_sum.cc:471 #1 0x082ac338 in update_depend_map (join=0xb5157a18, order=0xb5157980) at sql_select.cc:6767 #2 0x082ca108 in remove_const (join=0xb5157a18, first_order=0xb5157980, cond=0x0, change_list=true, simple_order=0xb5158b11) at sql_select.cc:6824 #3 0x082cc40c in JOIN::optimize (this=0xb5157a18) at sql_select.cc:1009 #4 0x082cf0f4 in mysql_select (thd=0xb5108fc0, rref_pointer_array=0xb510a124, tables=0xb5157608, wild_num=0, fields=@0xb510a0b4, conds=0x0, og_num=2, order=0xb5157980, group=0xb5157888, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb5157a08, unit=0xb5109de4, select_lex=0xb510a020) at sql_select.cc:2261 #5 0x082d395f in handle_select (thd=0xb5108fc0, lex=0xb5109d88, result=0xb5157a08, setup_tables_done_option=0) at sql_select.cc:258 #6 0x08255d7c in execute_sqlcom_select (thd=0xb5108fc0, all_tables=0xb5157608) at sql_parse.cc:4486 #7 0x082575e7 in mysql_execute_command (thd=0xb5108fc0) at sql_parse.cc:1838 #8 0x0826005f in mysql_parse (thd=0xb5108fc0, inBuf=0xb5156ee0 "select id, avgcost(id, Cost) as Cost, avg(Cost) from test group by id order by Cost desc", length=88, found_semicolon=0xbf9532a0) at sql_parse.cc:5388 #9 0x08260b3a in dispatch_command (command=COM_QUERY, thd=0xb5108fc0, packet=0xb514eeb1 "select id, avgcost(id, Cost) as Cost, avg(Cost) from test group by id order by Cost desc", packet_length=89) at sql_parse.cc:909 #10 0x08261c51 in do_command (thd=0xb5108fc0) at sql_parse.cc:668 #11 0x0824fcb4 in handle_one_connection (arg=0xb5108fc0) at sql_connect.cc:1094 #12 0x08243413 in handle_connection_in_main_thread (thd=0xb5108fc0) at mysqld.cc:4267 #13 0x08248024 in create_new_thread (thd=0xb5108fc0) at mysqld.cc:4362 #14 0x08248584 in handle_connections_sockets (arg=0x0) at mysqld.cc:4587 #15 0x0824b097 in main (argc=9, argv=0xbf953704) at mysqld.cc:3952 How to repeat: Compile the udf_examples.c (or just the avgcost part of it) into libudfexample.so with the following command: gcc -shared -I/usr/include/mysql -o libudfexample.so udf_example.c -l mysqlclient In MySQL Client: > use test; > create table test ( id int, Cost Real ); > insert into test set id=1, Cost=Rand()*100; > insert into test set id=1, Cost=Rand()*100; > insert into test set id=1, Cost=Rand()*100; > insert into test set id=1, Cost=Rand()*100; > insert into test set id=1, Cost=Rand()*100; > insert into test set id=2, Cost=Rand()*100; > insert into test set id=2, Cost=Rand()*100; > insert into test set id=2, Cost=Rand()*100; > insert into test set id=2, Cost=Rand()*100; > insert into test set id=2, Cost=Rand()*100; > select id, avg(Cost) from test group by id\G *************************** 1. row *************************** id: 1 avg(Cost): 47.300459097419 *************************** 2. row *************************** id: 2 avg(Cost): 55.709741223333 2 rows in set (0.01 sec) > create aggregate function avgcost returns real soname "libudfexample.so"; > select id, avg(Cost), avgcost(id,Cost) from test group by id\G *************************** 1. row *************************** id: 1 avg(Cost): 47.300459097419 avgcost(id,Cost): 47.3005 *************************** 2. row *************************** id: 2 avg(Cost): 55.709741223333 avgcost(id,Cost): 55.7097 2 rows in set (0.00 sec) > select id, avg(Cost), avgcost(id,Cost) from test group by id order by avg(Cost) DESC\G *************************** 1. row *************************** id: 2 avg(Cost): 55.7097412233332 avgcost(id,Cost): 55.7097 *************************** 2. row *************************** id: 1 avg(Cost): 47.3004590974194 avgcost(id,Cost): 47.3005 2 rows in set (0.00 sec) > select id, avg(Cost), avgcost(id,Cost) from test group by id order by avgcost(id,Cost) DESC\G ***CRASH*** Suggested fix: This is a horrible hack which doesn't solve the root cause, but it does prevent the crash. I don't know if it has any long term consequences, but I haven't seen any in my testing. diff -dur mysql-5.1.20-beta/sql/item_sum.cc mysql-5.1.20-beta-new/sql/item_sum.cc --- mysql-5.1.20-beta/sql/item_sum.cc 2007-06-28 04:20:02.000000000 -0600 +++ mysql-5.1.20-beta-new/sql/item_sum.cc 2007-07-26 21:28:55.000000000 -0600 @@ -467,7 +467,8 @@ used_tables_cache&= PSEUDO_TABLE_BITS; /* the aggregate function is aggregated into its local context */ - used_tables_cache |= (1 << aggr_sel->join->tables) - 1; + if (aggr_sel > (void*)0xFFFF && aggr_sel->join > (void*)0xFFFF) + used_tables_cache |= (1 << aggr_sel->join->tables) - 1; } }