Bug #58346 mysql lost connection on mysql_next_result or mysql_more_results
Submitted: 20 Nov 2010 12:59 Modified: 17 Jul 2013 19:28
Reporter: Serg Koba Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:5.1.49, 5.1 bzr OS:Linux (Ubuntu 10.10)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: mysql_more_results, mysql_next_result
Triage: Triaged: D1 (Critical) / R3 (Medium) / E3 (Medium)

[20 Nov 2010 12:59] Serg Koba
Description:
I am trying to write UDF which needs to make several CALLs.
I am using mysql_real_connect with  CLIENT_MULTI_RESULTS.
When I do more than 1 CALL iI recieve command out of sync - it's ok, the recommended solution to use mysql_next_result or mysql_more_results.

But when I do this I recieve  Lost connection during query on mysql_next_result.

error log is:
101120 14:54:32 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=4
max_threads=151
threads_connected=4
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 345929 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x21bc89c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xb253236c thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2d) [0xd7359d]
/usr/sbin/mysqld(handle_segfault+0x494) [0xa3bdd4]
[0x12e400]
/usr/lib/mysql/plugin/lib_mysqludf_udf.so(moveFrog+0x19c3) [0x1fb19e]
/usr/lib/mysql/plugin/lib_mysqludf_udf.so(move_neutrals+0x443) [0x1fd654]
/usr/sbin/mysqld(Item_func_udf_int::val_int()+0x5d) [0x99963d]
/usr/sbin/mysqld(Item::save_in_field(Field*, bool)+0x193) [0x96ea63]
/usr/sbin/mysqld(sp_eval_expr(THD*, Field*, Item**)+0x93) [0xbcec03]
/usr/sbin/mysqld(sp_rcontext::set_variable(THD*, unsigned int, Item**)+0x3b) [0xbd305b]
/usr/sbin/mysqld(select_dumpvar::send_data(List<Item>&)+0xc5) [0xa240e5]
/usr/sbin/mysqld(JOIN::exec()+0x10a8) [0xac6a28]
/usr/sbin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0xe7) [0xac1d17]
/usr/sbin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x164) [0xac8094]
/usr/sbin/mysqld(+0x262dcf) [0xa4adcf]
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x258c) [0xa50edc]
/usr/sbin/mysqld(sp_instr_stmt::exec_core(THD*, unsigned int*)+0x1d) [0xbc89cd]
/usr/sbin/mysqld(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*)+0xd0) [0xbce670]
/usr/sbin/mysqld(sp_instr_stmt::execute(THD*, unsigned int*)+0x13a) [0xbceb0a]
/usr/sbin/mysqld(sp_head::execute(THD*)+0x60c) [0xbcb79c]
/usr/sbin/mysqld(sp_head::execute_procedure(THD*, List<Item>*)+0x54e) [0xbcd15e]
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x2ee2) [0xa51832]
/usr/sbin/mysqld(sp_instr_stmt::exec_core(THD*, unsigned int*)+0x1d) [0xbc89cd]
/usr/sbin/mysqld(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*)+0xd0) [0xbce670]
/usr/sbin/mysqld(sp_instr_stmt::execute(THD*, unsigned int*)+0x13a) [0xbceb0a]
/usr/sbin/mysqld(sp_head::execute(THD*)+0x60c) [0xbcb79c]
/usr/sbin/mysqld(sp_head::execute_procedure(THD*, List<Item>*)+0x54e) [0xbcd15e]
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x2ee2) [0xa51832]
/usr/sbin/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x42a) [0xa56f3a]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xe75) [0xa57dc5]
/usr/sbin/mysqld(do_command(THD*)+0xf4) [0xa59084]
/usr/sbin/mysqld(handle_one_connection+0x2a0) [0xa47730]
/lib/libpthread.so.0(+0x5cc9) [0x212cc9]
/lib/libc.so.6(clone+0x5e) [0x4f26be]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x21d75e58 = SELECT move_neutrals( NAME_CONST('g_id',1), NAME_CONST('p_num2',11)) INTO fake FROM DUAL
thd->thread_id=46
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

How to repeat:
if ( mysql_real_query( mysql, query, strlen(query) ) ) {
				fprintf( stderr, "Error on Query: %s\n", mysql_error( mysql ));
				return -1;
			}
			 do {
			    /* did current statement return data? */
			    qryResult = mysql_store_result(mysql);
			    if (qryResult)
			    {
			      /* yes; process rows and free the result set */
			      while(mysql_fetch_row(qryResult));
			      mysql_free_result(qryResult);
			    }
			    else          /* no result set or error */
			    {
			      if (mysql_field_count(mysql) == 0)
			      {
				fprintf( stderr,"%lld rows affected\n",
				      mysql_affected_rows(mysql));
			      }
			      else  /* some error occurred */
			      {
				fprintf( stderr,"Could not retrieve result set\n");
				break;
			      }
			    }
			    /* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
			    if ((status = mysql_next_result(mysql)) > 0)
			      fprintf( stderr,"Could not execute statement\n");
			  } while (status == 0);
[22 Nov 2010 23:39] Sveta Smirnova
Thank you for the report.

Please provide definition of move_neutrals.
[24 Nov 2010 13:14] Serg Koba
my_bool move_neutrals_init(
	UDF_INIT *initid
,	UDF_ARGS *args
,	char *message
){
	my_bool status;
	if(args->arg_count!=2){
		strcpy(
			message
		,	"Expect exactly 2 argument"
		);
		status = 1;
	} else
	if(*((longlong*)args->args[1])<10){
		strcpy(
			message
		,	"Not neutral's player_num <10."
		);
		status = 1;
	} else {
		status = 0;
	}
	return status;
}

void move_neutrals_deinit(
	UDF_INIT *initid
){	
}

long long move_neutrals(
	UDF_INIT *initid
,	UDF_ARGS *args
,   char *is_null
,	char *error
){

	//vars
	MYSQL* mysql = NULL;
	longlong retval = 0
	

	//init mysql
	mysql = mysql_init( mysql );
	if ( !mysql ) {
		fprintf( stderr, "Error on Init: %s\n", mysql_error( mysql ));
		*error = 1;
		return 0;
	}
	//connect
	if ( !mysql_real_connect( mysql, "localhost", "root", "mypass", "mydbase", 3306, NULL, CLIENT_MULTI_RESULTS ) ) {
		fprintf( stderr, "Error on Connect: %s\n", mysql_error( mysql ));
		*error = 1;
		return 0;
	}
	
	moveFrog(mysql);

	//close
	mysql_close( mysql );

	return 1;
}

long long moveFrog(MYSQL *mysql){
	char query[450];
	int i;
	char ci[10];
	  for (i=0;i<2;i++) {
			query[0] = 0;
			itoa(i,ci,10);
			strcpy(query,"CALL player_move_unit(");
			strcat(query,ci);
			strcat(query,");");
			fprintf( stderr, "<<<DEBUG QUERY>>>: %s\n", query);
			if ( mysql_real_query( mysql, query, strlen(query) ) ) {
				fprintf( stderr, "Error on Query: %s\n", mysql_error( mysql ));
				return -1;
			}
			 do {
			    /* did current statement return data? */
			    qryResult = mysql_store_result(mysql);
			    if (qryResult)
			    {
			      /* yes; process rows and free the result set */
			      while(mysql_fetch_row(qryResult));
			      mysql_free_result(qryResult);
			    }
			    else          /* no result set or error */
			    {
			      if (mysql_field_count(mysql) == 0)
			      {
				fprintf( stderr,"%lld rows affected\n",
				      mysql_affected_rows(mysql));
			      }
			      else  /* some error occurred */
			      {
				fprintf( stderr,"Could not retrieve result set\n");
				break;
			      }
			    }
			     fprintf( stderr,"next:%i\n",mysql_next_result(mysql));
			    /* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
			    if ((status = mysql_next_result(mysql)) > 0)
			      fprintf( stderr,"Could not execute statement\n");
			  } while (status == 0);
		}
	return 1;
}

Connection is lost exactly on mysql_next_result, when I execute it after first query with CALL.
[24 Nov 2010 18:26] Sveta Smirnova
Thank you for the feedback.

Are you trying to use CALL in UDF? This is not supported. Therefore closing report as such.
[25 Nov 2010 8:47] Serg Koba
So using CALL from just C API application is supported, but not supported using CALL in mysql_query from UDF? right?
It is strange because this code worked fine for me on Windows XP sp3 with Mysql 5.1.48.
[25 Nov 2010 11:34] Sveta Smirnova
Thank you for the feedback.

Just to clarify please confirm if UDF  connects to server A while installed for server B.
[25 Nov 2010 13:13] Serg Koba
In my case UDF successfully connects to server, and mysql_real_query with CALL is successfully executed on server, but I can't process multi-result set returned by stored procedure, because connection crashes on mysql_next_result.
[25 Nov 2010 13:16] Sveta Smirnova
Thank you for the feedback.

Does it connect to same server where it is installed?
[25 Nov 2010 13:17] Serg Koba
Yes the same server, I use 'localhost' as server to connect.
[25 Nov 2010 13:37] Sveta Smirnova
Thank you for the feedback.

Will it crash if you call same stored procedure outside of UDF, but rather standalone application?
[26 Nov 2010 20:02] Serg Koba
Works fine, when I use mysql_next_result in standalone application.
[30 Nov 2010 11:54] Sveta Smirnova
Thank you for the feedback.

> Connection is lost exactly on mysql_next_result, when I execute it after first query with
CALL.

You call mysql_next_result twice in the same iteration. This can lead to unpredictable behavior including crash. Therefore closing as "Not a Bug".
[30 Nov 2010 12:00] Serg Koba
Sorry for my mistake in code I posted,
but this line 

fprintf( stderr,"next:%i\n",mysql_next_result(mysql));

was added after I received an error to find out the returned value, but mysql lost connection on it too. Not even printing a value to error log and before I actually execute second mysql_next_result.
[30 Nov 2010 20:36] Sveta Smirnova
Thank you for the feedback.

We have internal discussion about if this can be supported, so I paste analysis here and set status to "Verified" for decision if we will fix it.

If compile UDF with gdb stack trace shows server crashes in mysql_next_result:

#0  0x0000003429e0b002 in pthread_kill () from /lib64/libpthread.so.0
(gdb) bt
#0  0x0000003429e0b002 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b2ac65 in my_write_core (sig=11) at stacktrace.c:331
#2  0x0000000000698286 in handle_segfault (sig=11) at mysqld.cc:2609
#3  <signal handler called>
#4  0x00002aaaaae348bb in mysql_next_result (mysql=0x2093658) at libmysql.c:5244
#5  0x00002aaaaabc1e9e in moveFrog (nysql=0x2093658) at bug58346_2.c:116
#6  0x00002aaaaabc1cf7 in move_neutrals (initid=0x20a8ca0, args=0x20a8c60, is_null=0x20a8cd1 "", error=0x20a8cd0 "") at bug58346_2.c:70
#7  0x00000000005e03a2 in udf_handler::val_int (this=0x20a8c50, null_value=0x20a8bf3 "") at sql_udf.h:105
#8  0x00000000005efb55 in Item_func_udf_int::val_int (this=0x20a8ba0) at item_func.cc:3166
#9  0x00000000005cdc09 in Item::val_int_result (this=0x20a8ba0) at item.h:741
#10 0x00000000005f261d in Item_func_set_user_var::save_item_result (this=0x20a9078, item=0x20a8ba0) at item_func.cc:4147
#11 0x00000000006828e4 in select_dumpvar::send_data (this=0x20a8db8, items=@0x204b998) at sql_class.cc:2795
#12 0x000000000071c972 in JOIN::exec (this=0x20cfb18) at sql_select.cc:1816
#13 0x000000000071f3c1 in mysql_select (thd=0x2049978, rref_pointer_array=0x204ba60, tables=0x0, wild_num=0, fields=@0x204b998, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2147764736, result=0x20a8db8, unit=0x204b468, select_lex=0x204b890) at sql_select.cc:2544
#14 0x0000000000716e74 in handle_select (thd=0x2049978, lex=0x204b3c8, result=0x20a8db8, setup_tables_done_option=0) at sql_select.cc:269
#15 0x00000000006b389c in execute_sqlcom_select (thd=0x2049978, all_tables=0x0) at sql_parse.cc:5144
#16 0x00000000006aa7ef in mysql_execute_command (thd=0x2049978) at sql_parse.cc:2293
#17 0x00000000006b5cd3 in mysql_parse (thd=0x2049978, rawbuf=0x20a8578 "SELECT move_neutrals( NAME_CONST('g_id',1), NAME_CONST('p_num2',11)) INTO @fake FROM DUAL", length=89, found_semicolon=0x40a81eb0)
    at sql_parse.cc:6068
#18 0x00000000006a8067 in dispatch_command (command=COM_QUERY, thd=0x2049978, packet=0x209c6a9 "SELECT move_neutrals( NAME_CONST('g_id',1), NAME_CONST('p_num2',11)) INTO @fake FROM DUAL", 
    packet_length=89) at sql_parse.cc:1261
#19 0x00000000006a6fca in do_command (thd=0x2049978) at sql_parse.cc:889
#20 0x00000000006a5207 in handle_one_connection (arg=0x2049978) at sql_connect.cc:1136
#21 0x0000003429e061b5 in start_thread () from /lib64/libpthread.so.0
#22 0x00000034292cd39d in clone () from /lib64/libc.so.6
#23 0x0000000000000000 in ?? ()
(gdb) q

Line 5244 of libmysql.c contains:

  if (mysql->last_used_con->server_status & SERVER_MORE_RESULTS_EXISTS)
    DBUG_RETURN((*mysql->methods->next_result)(mysql));

Looks like mysql->last_used_con of UDF and new connection are mixed thus expression "mysql->last_used_con->server_status & SERVER_MORE_RESULTS_EXISTS" crashes.
[30 Nov 2010 20:37] Sveta Smirnova
MTR test case used to repeat the problem:

--source include/have_udf.inc

show variables like '%plugin%';

delimiter |;
create procedure player_move_unit(in ce varchar(255))
begin
select ce;
end
|

delimiter ;|

CREATE FUNCTION move_neutrals RETURNS INTEGER SONAME "libbug58346.so";

SELECT move_neutrals( NAME_CONST('g_id',1), NAME_CONST('p_num2',11)) INTO @fake FROM DUAL;
select @fake;
[30 Nov 2010 20:40] Sveta Smirnova
a bit modified code for UDF

Attachment: bug58346_2.c (text/plain), 2.59 KiB.

[29 Jun 2013 21:03] Roland Bouman
I have almost the same issue - open a MySQL connection inside a UDF. But in my case it's not a CALL, but multiple semi-colon separated statements in the call to mysql_real_query. 

Server is 5.5, appropriate flags are set when creating the connection
[29 Jun 2013 21:15] Roland Bouman
I checked with CALL too. Crashes.
[2 Jul 2013 9:52] Georgi Kodinov
For the record the mysql server and the mysql client are using a differently compiled libmysqlclient code. The server version has some additional functionality to support the server code.
[2 Jul 2013 10:00] Georgi Kodinov
Can you please check if it still crashes in 5.6 ?
[17 Jul 2013 19:28] Sveta Smirnova
Versions 5.5.32, 5.6.13, 5.7.2 still crash