Bug #12146 NDBCLUSTER tables SELECT MAX(a) AS a WHERE b=c returns 1 or null
Submitted: 25 Jul 2005 5:00 Modified: 2 Aug 2005 14:06
Reporter: Roger Tsang Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:4.1.13-max OS:Linux (Linux-2.4.22-1.2199.nptl)
Assigned to: Martin Skold CPU Architecture:Any

[25 Jul 2005 5:00] Roger Tsang
Description:
NDBCLUSTER tables SELECT MAX(a) AS a WHERE b=c returns 1 or null

In the latest bookmark4u from http://bookmark4u.sf.net after migrating from an existing MyISAM installation to NDBCLUSTER storage, the application breaks due to corrupted result returned from SELECT MAX(id).  We had to use an alternate SELECT statement.

This is the print out of the structure from phpMyAdmin:

bk4u_bookmark
Field 	Type 	Null 	Default
id  	int(11) 	No  	 
uid  	int(11) 	No  	0 
type  	char(1) 	Yes  	NULL 
parent  	int(11) 	Yes  	NULL 
title  	char(255) 	Yes  	NULL 
url  	char(255) 	Yes  	NULL 
comment  	char(255) 	Yes  	NULL 
rdate  	datetime 	Yes  	NULL 
visit  	int(11) 	Yes  	NULL 
public  	char(1) 	Yes  	NULL 
lastvisit  	datetime 	Yes  	NULL 
lastwhere  	char(15) 	Yes  	NULL 
total  	int(11) 	Yes  	NULL 
icon  	char(80) 	Yes  	NULL 
rate  	int(11) 	Yes  	0 
lnid  	int(11) 	Yes  	NULL 
rtime  	int(11) 	Yes  	-1 
chkdate  	datetime 	Yes  	NULL 

 Indexes:
Keyname 	Type 	Cardinality 	Field
PRIMARY 	PRIMARY 	4256  	id
uid
parent 	INDEX 	4256  	parent
uid
uid 	INDEX 	4256  	uid
id
parent

 Space usage:
Type 	Usage
Data 	0 	Bytes
Index 	0 	Bytes
Total 	0 	Bytes
	  	 Row Statistics:
Statements 	Value
Format 	fixed
Rows 	4,256
Row size  ΓΈ 	0 Bytes
Next Autoindex 	4,762

How to repeat:
Populate the bk4u_bookmark table by installing bookmark4u, creating a user, and saving some bookmarks.  Then migrate to NDBCLUSTER storage engine by doing ALTER TABLE on your existing bookmark4u database tables.  You will run into a problem adding new bookmarks for the user when using NDBCLUSTER storage engine.  The following query will always give you 1 as the result.  Use a valid uid.

SQL query: 
SELECT MAX( id ) AS id
FROM `bk4u_bookmark`
WHERE uid =4

Returns:
 id
1

`id` is a unique id assigned to each bookmark object belonging to the user.  MAX(id) should indicate the number of objects the user has.

Suggested fix:
Modify the SQL query in the appropriate PHP code to work around this NDBCLUSTER bug.

SQL query:
SELECT uid, MAX(id) AS id FROM `bk4u_bookmark` WHERE uid=4 GROUP BY uid

Returns:
 uid  	 id
4 	4760
[2 Aug 2005 11:57] Martin Skold
Cannot repeat the problem by trying the two queries on a similar table
(after inserting/deleting data randomly).
Are you sure the problem was not something happening during the ALTER TABLE?
Try and connect directly to MySQL server and analyse the table.
Can you reproduce by running the two variants of queries directly after each other?
Can you record the exact sequence of operations on the table?
[2 Aug 2005 13:48] Roger Tsang
The problem occurs with an already populated table.  Let me know if you need more info.

Database changed
mysql> show tables;
+----------------------+
| Tables_in_bookmark4u |
+----------------------+
| bk4u_address         |
| bk4u_bookmark        |
| bk4u_calendar        |
| bk4u_config          |
| bk4u_memo            |
| bk4u_passwd          |
| bk4u_passwd_temp     |
| bk4u_trash           |
+----------------------+
8 rows in set (0.05 sec)

mysql> SELECT MAX(id) AS id FROM bk4u_bookmark WHERE uid=4;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.05 sec)

mysql> SELECT uid,MAX(id) AS id FROM bk4u_bookmark WHERE uid=4 GROUP BY uid;
+-----+------+
| uid | id   |
+-----+------+
|   4 | 4781 |
+-----+------+
1 row in set (0.02 sec)

mysql> SELECT MAX(id) AS id FROM bk4u_bookmark WHERE uid=4;                 
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

mysql> ANALYZE TABLE bk4u_bookmark;
+--------------------------+---------+----------+----------------------------------------------------------+
| Table                    | Op      | Msg_type | Msg_text                                                 |
+--------------------------+---------+----------+----------------------------------------------------------+
| bookmark4u.bk4u_bookmark | analyze | note     | The storage engine for the table doesn't support analyze |
+--------------------------+---------+----------+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM bk4u_bookmark;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| bk4u_bookmark |          0 | PRIMARY  |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| bk4u_bookmark |          0 | PRIMARY  |            2 | uid         | A         |         100 |     NULL | NULL   |      | BTREE      |         |
| bk4u_bookmark |          1 | parent   |            1 | parent      | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| bk4u_bookmark |          1 | parent   |            2 | uid         | A         |         100 |     NULL | NULL   |      | BTREE      |         |
| bk4u_bookmark |          1 | uid      |            1 | uid         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| bk4u_bookmark |          1 | uid      |            2 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| bk4u_bookmark |          1 | uid      |            3 | parent      | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.01 sec)
[2 Aug 2005 13:57] Roger Tsang
The mysql statements in the previous comment are in sequence as seen on the console.

I haven't run into an ALTER TABLE problem.  Also I did a full HOT backup via ndb_mgm, shutdown all mysqld, ndbd --init on all, then ndb_restore in single user mode prior to submitting the previous comment.  No ALTER TABLE statements were used to populate the NDBCLUSTER storage, yet the problem persists.

mysql> SHOW TABLE STATUS LIKE 'bk4u_bookmark';
+---------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| Name          | Engine     | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+---------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| bk4u_bookmark | ndbcluster |       9 | Fixed      |  100 |              0 |           0 |            NULL |            0 |         0 |           4782 | NULL        | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+---------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)
[2 Aug 2005 14:06] Martin Skold
Can you do EXPLAIN on the two queries.
[2 Aug 2005 20:14] Roger Tsang
I didn't write the application but this is how I understand it.  `id` is a bookmark item identifier for the user with `uid`.  `id` is incremented when appropriate.  When adding a bookmark, the application performs the first SELECT query to determine what the next `id` should be for the `uid`.  This query works with MyISAM tables however.

As you can see the first SELECT query does not return the MAX(id) for the `uid`.  After migrating from MyISAM to NDBCLUSTER, I had to change the query in the application to the second SELECT statement to get the _actual_ MAX(id) for the `uid`.  Do you see the problem?