Bug #69035 MySQL Cluster Un-Signed Auto Increment Column
Submitted: 22 Apr 2013 20:27 Modified: 26 Apr 2013 5:45
Reporter: Sajjad Tariq Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:7.2.10 OS:Linux (CentOS release 5.8 (Final))
Assigned to: CPU Architecture:Any
Tags: Auto-increment, MySQL Cluster, Un-Signed

[22 Apr 2013 20:27] Sajjad Tariq
Description:
Following query yields no results, but row is present.
mysql> select count(*) from cso.cso_eml where cso_id=15905477;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Row is retrieved by another key.
mysql> select cso_id from cso.cso_eml where cso_st_no=980077784405539;
+----------+
| cso_id   |
+----------+
| 15905477 |
+----------+
1 row in set (0.00 sec)

Row is retrieved by cast;
mysql> SELECT count(*) FROM `cso`.`cso_eml` where cast(cso_id as unsigned)=15905477;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

How to repeat:
Create a table with following primary key and auto-increment;

 `CSO_ID` int(11) NOT NULL AUTO_INCREMENT,

mysql> select count(*) from cso.cso_eml;
+----------+
| count(*) |
+----------+
|     6193 |
+----------+
1 row in set (0.03 sec)

mysql> explain partitions select * from cso.cso_eml where cso_id=15905477;
+----+-------------+---------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | cso_eml | p15        | eq_ref | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+---------+------------+--------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain partitions select * from cso.cso_eml where cast(cso_id as unsigned)=15905477;
+----+-------------+---------+---------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | partitions                                                                            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+---------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | cso_eml | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23 | ALL  | NULL          | NULL | NULL    | NULL | 6193 | Using where |
+----+-------------+---------+---------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> explain partitions select cso_id from cso.cso_eml where cso_st_no=980077784405539;
+----+-------------+---------+---------------------------------------------------------------------------------------+------+------------------+------------------+---------+-------+------+-------+
| id | select_type | table   | partitions                                                                            | type | possible_keys    | key              | key_len | ref   | rows | Extra |
+----+-------------+---------+---------------------------------------------------------------------------------------+------+------------------+------------------+---------+-------+------+-------+
|  1 | SIMPLE      | cso_eml | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23 | ref  | CSO_STRT_END_INX | CSO_STRT_END_INX | 8       | const |  123 |       |
+----+-------------+---------+---------------------------------------------------------------------------------------+------+------------------+------------------+---------+-------+------+-------+
1 row in set (0.00 sec)
[24 Apr 2013 16:55] Umesh Shastry
Hello Sajjad,

I can not repeat described behavior on 7.2.10.
Could you please provide repeatable testcase(table structure, required data)?

Would it be possible for you to check if it's repeatable on the latest GA 7.2.12?

Thanks,
Umesh
[26 Apr 2013 5:42] Sajjad Tariq
Hi,

I have created a temp table by using same table structure, and ported the table data on the same server. But, was not able to recreate the issue. Application couldn't wait hence, we altered the table column to un-signed and that fixed the issue.

Thank you,

Sajjad
[26 Apr 2013 5:45] Umesh Shastry
Thank you for confirming.
I am closing this bug now - please reopen, if needed.

Best Regards,
Umesh