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: | |
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
[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?