Bug #47162 Mysql predefined query is blocked in
Submitted: 7 Sep 2009 5:55 Modified: 7 Oct 2009 6:15
Reporter: Saurabh Gupta Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.45 OS:Linux (Linux server34 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:12 EDT 2008 i686 i686 i386 GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: Execution of predefined query is getting hanged.

[7 Sep 2009 5:55] Saurabh Gupta
Description:
We were executing the following predefined query:
DELETE FROM BAT_transactions WHERE batch_job_id = ?

We were expecting that mysql server will delete all the entries with the given job id will get deleted.

But during execution of the predefined query, application got hanged and finally aborted by watchdog.

Following is the schema of transaction table:
CREATE TABLE BAT_transactions(
    batch_job_id                 SMALLINT UNSIGNED NOT NULL
  , subjob_id                    SMALLINT UNSIGNED NOT NULL
  , smsc_id                      SMALLINT NOT NULL
  , message_id                   VARCHAR(66) NOT NULL
  , msisdn                       VARCHAR(15) NOT NULL
  , first_name                   VARCHAR(30) NOT NULL
  , second_name                  VARCHAR(30) NOT NULL
  , status                       ENUM ('ACK', 'SUCCESS', 'NACK', 'UNSUCCESS', 'TIMEOUT') NOT NULL
  , err_codes                    INT UNSIGNED NOT NULL DEFAULT 0
  , submission_time              BIGINT UNSIGNED NOT NULL
  , INDEX (batch_job_id, subjob_id, msisdn)
  , INDEX (smsc_id, message_id)
  , INDEX (batch_job_id, subjob_id)
)
ENGINE=InnoDB;

Mysql configuration file setting:
/etc/my.cnf
[mysqld]
default-time-zone='GMT'
set-variable = max_connections=1000
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 32M
table_cache = 256
query_cache_size = 16M
join_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
#log-slow-queries = /usr/local/mysql/data/mysql-slow.log
log=/usr/local/mysql/data/mysql.log

port        = 3306
socket      = /tmp/bat_mysql.sock

Core was generated by `/usr/TextPass/bin/tp_bat'.
Program terminated with signal 6, Aborted.
#0  0x00a91402 in __kernel_vsyscall ()
(gdb) bt
#0  0x00a91402 in __kernel_vsyscall ()
#1  0x007d08b3 in __read_nocancel () from /lib/libc.so.6
#2  0x0049fa48 in vio_read (vio=0x8c333b8, buf=0x8c33458 "\a", size=16384) at /usr/include/bits/unistd.h:35
#3  0x0049fabe in vio_read_buff (vio=0x8c333b8, buf=0x8c37460 "\016", size=4) at viosocket.c:81
#4  0x004a056b in my_real_read (net=0x8c32ff8, complen=0xbff9ee28) at net.c:817
#5  0x004a095b in my_net_read (net=0x8c32ff8) at net.c:991
#6  0x0049a598 in cli_safe_read (mysql=0x8c32ff8) at client.c:595
#7  0x0049b5a5 in cli_read_query_result (mysql=0x8c32ff8) at client.c:2678
#8  0x0047d2df in execute (stmt=0x8c55858, packet=<value optimized out>, length=<value optimized out>) at libmysql.c:2516
#9  0x0047db5f in cli_stmt_execute (stmt=0x8c55858) at libmysql.c:2601
#10 0x0047e6f5 in mysql_stmt_execute (stmt=0x8c55858) at libmysql.c:2904
#11 0x0804c81a in DB_execute_predef_query (mysql_stmt=0x8c55858) at src/bat_db_handler.c:369
#12 0x0804efa2 in DB_trn_del_all_subjob_entries (bjob_id=2) at src/bat_db_transaction.c:306

How to repeat:
1. Create table as given above in description.

2. Write a program to execute following predefined query:
DELETE FROM BAT_transactions WHERE batch_job_id = ?

3. Link the program with libmysqlclient.so.15 and execute it.
[7 Sep 2009 6:15] Valeriy Kravchuk
Thank you for the problem report. Please, try to repeat with a newer version, 5.0.85. In case of the same problem, please, send the results of:

show table status like 'BAT_transactions'\G
EXPLAIN SELECT * FROM BAT_transactions WHERE batch_job_id = 10\G
[7 Oct 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".