Bug #29226 Server crash (was 0 rows returned when xxx_id=32)
Submitted: 19 Jun 2007 22:56 Modified: 26 Jun 2007 5:53
Reporter: Mike Miller Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.41/5.0BK/5.1BK OS:Linux
Assigned to: Assigned Account CPU Architecture:Any

[19 Jun 2007 22:56] Mike Miller
Description:
I am encountering a problem with version 5.0.41 (also on 5.0.37) on Suse linux and this
same problem does not occur on Windows for the same version. We are doing a query for some I18N
related data and we get "o rows" returned when the where clause is
"where xxx_id=32". Selecting for a range between 31 and 33 works fine
but when id=32 - we do not get the data back.

Below is a set of statement that I have been able to boil down to show the problem
on my machine. It creates the tables, inserts the rows and does the two selects.
The first select on range works while the 2nd select does not return any rows.

I am hoping this is our problem but I am not exactly sure where else to look.

Thanks in advance!

How to repeat:
See attached file for script.

Suggested fix:
I have tried the same script but changed the numeric(20) to bigint(20) and it works fine then.
[19 Jun 2007 23:15] Miguel Solorzano
Thank you for the bug report: Do you expect the below result:

Query OK, 1 row affected (0.00 sec)

+-------------+----------+------------------------------+-----------------------------------------------------------------------------------------------+
| ACTIVITY_ID | LANGUAGE | NAME                         | DESCRIPTION                                                                                   |
+-------------+----------+------------------------------+-----------------------------------------------------------------------------------------------+
|          32 | en       | Override Return Product Rule | Allow associate to process a return that does not comply with applicable return product rule. | 
+-------------+----------+------------------------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

However I got a crash after that query with a source server and I am going to
test with latest source and I only want to confirm if the bug is now a crash
instead of bad result. Thanks in advance.
[19 Jun 2007 23:42] Miguel Solorzano
Crash back trace

Attachment: back-trace-50-bug29226.txt (text/plain), 17.36 KiB.

[20 Jun 2007 0:32] Miguel Solorzano
Thank you for the bug report. Changed synopsis to a server crash.

[miguel@light 5.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM ACTIVITY_I18N WHERE ACTIVITY_ID = 32;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
----------------------------------------------------------------------
+-------------+----------+------------------------------+-----------------------------------------------------------------------------------------------+
| ACTIVITY_ID | LANGUAGE | NAME                         | DESCRIPTION                                                                                   |
+-------------+----------+------------------------------+-----------------------------------------------------------------------------------------------+
|          32 | en       | Override Return Product Rule | Allow associate to process a return that does not comply with applicable return product rule. |
+-------------+----------+------------------------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+-------------+----------+------------------------------+-----------------------------------------------------------------------------------------------+
| ACTIVITY_ID | LANGUAGE | NAME                         | DESCRIPTION                                                                                   |
+-------------+----------+------------------------------+-----------------------------------------------------------------------------------------------+
|          32 | en       | Override Return Product Rule | Allow associate to process a return that does not comply with applicable return product rule. |
+-------------+----------+------------------------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.23-debug |
+--------------+
1 row in set (0.00 sec)

mysql> 

[miguel@light mysql-5.0]$ bk changes | head
ChangeSet@1.2492, 2007-06-19 12:10:48+02:00, df@pippilotta.erinye.com +1 -0
  Merge dfischer@bk-internal.mysql.com:/home/bk/mysql-5.0
  into  pippilotta.erinye.com:/shared/home/df/mysql/build/mysql-5.0
  MERGE: 1.2485.1.2

ChangeSet@1.2485.1.2, 2007-06-19 12:03:10+02:00, df@pippilotta.erinye.com +1 -0
  try again.

ChangeSet@1.2485.1.1, 2007-06-18 07:42:13+02:00, df@pippilotta.erinye.com +0 -0
  Merge dfischer@bk-internal.mysql.com:/home/bk/mysql-5.0

[miguel@light mysql-5.1]$ bk changes | head
ChangeSet@1.2518, 2007-06-19 12:12:42+02:00, df@pippilotta.erinye.com +1 -0
  Merge pippilotta.erinye.com:/shared/home/df/mysql/build/mysql-5.0
  into  pippilotta.erinye.com:/shared/home/df/mysql/build/mysql-5.1
  MERGE: 1.1810.2871.51

ChangeSet@1.2517, 2007-06-19 12:11:19+02:00, df@pippilotta.erinye.com +1 -0
  Merge dfischer@bk-internal.mysql.com:/home/bk/mysql-5.1
  into  pippilotta.erinye.com:/shared/home/df/mysql/build/mysql-5.1
  MERGE: 1.2503.1.3
[20 Jun 2007 12:54] Mike Miller
Yes, I was expecting a single row returned.  No, I don't believe I had a server crash because I was still able to do additional queries after the failed query.
[25 Jun 2007 20:45] Igor Babaev
Here's how this problem can be easily reproduced:

mysql> CREATE TABLE t1 (
    ->   id numeric(20) NOT NULL,
    ->   lang varchar(8) NOT NULL,
    ->   msg varchar(32) NOT NULL,
    ->   PRIMARY KEY (id,lang)
    -> );
Query OK, 0 rows affected (0.44 sec)

mysql> INSERT INTO t1 VALUES (33, 'en', 'zzzzzzz');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (31, 'en', 'xxxxxxx');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (32, 'en', 'yyyyyyy');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE id=32;
ERROR 2013 (HY000): Lost connection to MySQL server during query

While on the server side we have:

Version: '5.0.46-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1256604768 (LWP 20026)]
mysqld: mi_search.c:495: _mi_prefix_search: Assertion `vseg < vseg_end' failed.
[26 Jun 2007 5:53] Igor Babaev
This problem is the result of the same bug that was reported in bug entry
#29087.
That's why this bug is marked as a duplicate of bug #29087.