Bug #69035 MySQL Cluster Un-Signed Auto Increment Column

• Submitted: 22 Apr 2013 20:27
• Modified: 26 Apr 2013 5:45
• Reporter: Sajjad Tariq
• Status: Not a Bug
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S2 (Serious)
• Version: 7.2.10
• OS: Linux (CentOS release 5.8 (Final))
• 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] MySQL Verification Team

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] MySQL Verification Team

Thank you for confirming.
I am closing this bug now - please reopen, if needed.

Best Regards,
Umesh