| Bug #62136 | failed to fetch select result using embedded mysqld | ||
|---|---|---|---|
| Submitted: | 10 Aug 2011 5:59 | Modified: | 25 Apr 2012 19:12 |
| Reporter: | Qi Zhou | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Embedded Library ( libmysqld ) | Severity: | S2 (Serious) |
| Version: | 5.5.15, 5.1.62, 5.5.22 | OS: | Linux (Red Hat Enterprise Linux AS release 4 (Nahant Update 3)) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | libmysqld, regression | ||
[10 Aug 2011 10:39]
MySQL Verification Team
Thank you for the bug report. Are you able to provide a complete test case (dump file + c code).? Thanks.
[11 Aug 2011 13:09]
Qi Zhou
#include <stdio.h>
#include <mysql.h>
#include <stdlib.h>
#include <string.h>
int main()
{
MYSQL* sql;
MYSQL_STMT* stmt;
MYSQL_BIND bind;
unsigned* indice;
int ret;
int result;
unsigned long length;
char query1[] = "create database if not exists mydb";
char query2[] = "use mydb";
char query3[] = "create table if not exists mytable (id int not null, col int not null)";
char query4[] = "insert into mytable (id, col) values (1,100)";
char query5[] = "select col from mytable where id = ?";
char *server_options[] = { "","--defaults-file=my.cnf","--basedir=./"};
int num_elements = sizeof(server_options)/ sizeof(char *);
if(mysql_server_init(num_elements, server_options, NULL))
{
puts("failed to initialize server");
return 1;
}
sql = mysql_init(NULL);
if(!mysql_real_connect(sql, NULL , NULL , NULL , NULL, 3306, NULL, 0))
printf("failed to connect\n");
if(mysql_query(sql, query1))
printf("failed to create database:%s\n", mysql_error(sql));
ret = mysql_query(sql, query2);
if(ret)
printf("failed to use database:%s\n", mysql_error(sql));
ret = mysql_query(sql, query3);
if(ret)
printf("failed to create table:%s\n", mysql_error(sql));
ret = mysql_query(sql, query4);
if(ret)
printf("failed to insert record:%s\n", mysql_error(sql));
stmt = mysql_stmt_init(sql);
if(!stmt)
printf("failed to init stmt:%s\n", mysql_error(sql));
ret = mysql_stmt_prepare(stmt, query5, strlen(query5));
if(ret)
printf("failed to prepare:%s\n", mysql_stmt_error(stmt));
result = 1;
memset(&bind, 0 , sizeof(bind));
bind.buffer_type = MYSQL_TYPE_LONG;
bind.is_unsigned = (my_bool)0;
bind.is_null = 0;
bind.buffer = (char*)&result;
bind.buffer_length = 4;
bind.length = &length;
ret = mysql_stmt_bind_param(stmt, &bind);
if(ret)
printf("failed to bind param:%s\n", mysql_stmt_error(stmt));
ret = mysql_stmt_execute(stmt);
if(ret)
printf("failed to execute stmt:%s\n", mysql_stmt_error(stmt));
memset(&bind, 0 , sizeof(bind));
bind.buffer_type = MYSQL_TYPE_LONG;
bind.is_unsigned = (my_bool)0;
bind.is_null = 0;
bind.buffer = (char*)&result;
bind.buffer_length = 4;
bind.length = &length;
ret = mysql_stmt_bind_result(stmt, &bind);
if(ret)
printf("failed to bind result:%s\n", mysql_stmt_error(stmt));
ret = mysql_stmt_store_result(stmt);
if(ret)
printf("failed to store result:%s\n", mysql_stmt_error(stmt));
ret = mysql_stmt_fetch(stmt);
if(ret)
printf("failed to fetch result:%s\n", mysql_stmt_error(stmt));
mysql_stmt_close(stmt);
mysql_close(sql);
mysql_server_end();
return 0;
}
this is my source code, and you need put my.cnf in the execute directory,my.cnf :
[server]
language=../../mysql/share/english
datadir=./data
[embedded]
language=../../mysql/share/english
language(../../mysql/share/english) points to mysql/share/english.
and you need mkdir data as datadir
this program failed on mysql_stmt_store_result and mysql_stmt_fetch, if modify the source as I suggested before, it would pass.
looking forward to your answer.
[12 Aug 2011 2:13]
Qi Zhou
test file
Attachment: sql.cpp (text/plain), 2.57 KiB.
[15 Aug 2011 5:39]
Qi Zhou
here is my source code:
#include <stdio.h>
#include <mysql.h>
#include <stdlib.h>
#include <string.h>
int main()
{
MYSQL* sql;
MYSQL_STMT* stmt;
MYSQL_BIND bind;
unsigned* indice;
int ret;
int result;
unsigned long length;
char query1[] = "create database if not exists mydb";
char query2[] = "use mydb";
char query3[] = "create table if not exists mytable (id int not null, col int not
null)";
char query4[] = "insert into mytable (id, col) values (1,100)";
char query5[] = "select col from mytable where id = ?";
char *server_options[] = { "","--defaults-file=my.cnf","--basedir=./"};
int num_elements = sizeof(server_options)/ sizeof(char *);
if(mysql_server_init(num_elements, server_options, NULL))
{
puts("failed to initialize server");
return 1;
}
sql = mysql_init(NULL);
if(!mysql_real_connect(sql, NULL , NULL , NULL , NULL, 3306, NULL, 0))
printf("failed to connect\n");
if(mysql_query(sql, query1))
printf("failed to create database:%s\n", mysql_error(sql));
ret = mysql_query(sql, query2);
if(ret)
printf("failed to use database:%s\n", mysql_error(sql));
ret = mysql_query(sql, query3);
if(ret)
printf("failed to create table:%s\n", mysql_error(sql));
ret = mysql_query(sql, query4);
if(ret)
printf("failed to insert record:%s\n", mysql_error(sql));
stmt = mysql_stmt_init(sql);
if(!stmt)
printf("failed to init stmt:%s\n", mysql_error(sql));
ret = mysql_stmt_prepare(stmt, query5, strlen(query5));
if(ret)
printf("failed to prepare:%s\n", mysql_stmt_error(stmt));
result = 1;
memset(&bind, 0 , sizeof(bind));
bind.buffer_type = MYSQL_TYPE_LONG;
bind.is_unsigned = (my_bool)0;
bind.is_null = 0;
bind.buffer = (char*)&result;
bind.buffer_length = 4;
bind.length = &length;
ret = mysql_stmt_bind_param(stmt, &bind);
if(ret)
printf("failed to bind param:%s\n", mysql_stmt_error(stmt));
ret = mysql_stmt_execute(stmt);
if(ret)
printf("failed to execute stmt:%s\n", mysql_stmt_error(stmt));
memset(&bind, 0 , sizeof(bind));
bind.buffer_type = MYSQL_TYPE_LONG;
bind.is_unsigned = (my_bool)0;
bind.is_null = 0;
bind.buffer = (char*)&result;
bind.buffer_length = 4;
bind.length = &length;
ret = mysql_stmt_bind_result(stmt, &bind);
if(ret)
printf("failed to bind result:%s\n", mysql_stmt_error(stmt));
ret = mysql_stmt_store_result(stmt);
if(ret)
printf("failed to store result:%s\n", mysql_stmt_error(stmt));
ret = mysql_stmt_fetch(stmt);
if(ret)
printf("failed to fetch result:%s\n", mysql_stmt_error(stmt));
mysql_stmt_close(stmt);
mysql_close(sql);
mysql_server_end();
return 0;
}
you need create my.cnf in the execute's directory such as :
[server]
language=../../mysql/share/english
datadir=./data
[embedded]
language=../../mysql/share/english
language(../../mysql/share/english) points to mysql/share/english.
and you need assign datadir for the data which means you need create the directory if it doesn`t exist.
This program failed in mysql_stmt_store_result() and mysql_stmt_fetch(), if modify the source code as I suggested before, it would pass.
Looking forward to your answer.
[24 Aug 2011 5:23]
Qi Zhou
i think i selected a wrong category of this bug. changing to libmysqld
[20 Feb 2012 18:34]
Sveta Smirnova
Thank you for the report. Verified as described. Problem is same fetch works for regular client, but not for one which connects to embedded server. In version 5.5 replace "int num_elements = (sizeof(server_options)/ sizeof(char *));" with "int num_elements = (sizeof(server_options)/ sizeof(char *)) - 1;"
[25 Apr 2012 19:12]
Paul DuBois
Noted in 5.1.63, 5.5.24, 5.6.6 changelogs. mysql_store_result() and mysql_use_result() are not for use with prepared statements and are not intended to be called following mysql_stmt_execute(), but failed to return an error when invoked that way in libmysqld.
Description: I was using libmysqld to select records with mysql_stmt_fetch(), yet it was successful with libmysqld of v5.0.51, but failed in v5.5.15. I read the sources, and found that in mysql5.5.15, we judge mysql status MYSQL_STATUS_STATEMENT_GET_RESULT instead of MYSQL_STATUS_GET_RESULT，but unfortunately it is not changed in libmysqld/lib_sql.cc，may you forget it？ How to repeat: char sql[] ="select * from mytable where key = ?"; .... mysql_stmt_prepare(stmt, sql, strlen(sql)); ... mysql_stmt_bind_param(stmt, bind); mysql_stmt_execute(stmt); mysql_stmt_bind_result(stmt, binds); if(mysql_stmt_store_result(stmt)) { failed here. } Suggested fix: I think the emb_stmt_execute(MYSQL_STMT *stmt) libmysqld/lib_sql.cc(321) should be changed as follow(notice line 346~348): 321 static int emb_stmt_execute(MYSQL_STMT *stmt) 322 { 323 DBUG_ENTER("emb_stmt_execute"); 324 uchar header[5]; 325 THD *thd; 326 my_bool res; 327 328 int4store(header, stmt->stmt_id); 329 header[4]= (uchar) stmt->flags; 330 thd= (THD*)stmt->mysql->thd; 331 thd->client_param_count= stmt->param_count; 332 thd->client_params= stmt->params; 333 334 res= test(emb_advanced_command(stmt->mysql, COM_STMT_EXECUTE, 0, 0, 335 header, sizeof(header), 1, stmt) || 336 emb_read_query_result(stmt->mysql)); 337 stmt->affected_rows= stmt->mysql->affected_rows; 338 stmt->insert_id= stmt->mysql->insert_id; 339 stmt->server_status= stmt->mysql->server_status; 340 if (res) 341 { 342 NET *net= &stmt->mysql->net; 343 set_stmt_errmsg(stmt, net); 344 DBUG_RETURN(1); 345 } 346 else if (stmt->mysql->status == MYSQL_STATUS_GET_RESULT) 347 stmt->mysql->status= MYSQL_STATUS_STATEMENT_GET_RESULT; 348 DBUG_RETURN(0); 349 } please review this file, i`am not familiar with the code, may other solutions? I’m looking forward to your reply。