Bug #19202 Incorrect errorhandling in select count(*) wrt temporary error
Submitted: 19 Apr 2006 18:27 Modified: 3 Jul 2006 16:24
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:4.1,5.0,5.1 OS:Linux (Linux 32 Bit OS)
Assigned to: Tomas Ulin

[19 Apr 2006 18:27] Jonathan Miller
Description:
This is true if ndb_use_exact_count is on

in a cluster with 2 MySQLDs without Bin Log and Disk Data do the following:

MySQLD 1;
1) Create database 
2) create some tables
3) load some data;
MySQLD 2;
1) Create the database
2) do "USE DATABASE"
3) do "SHOW TABLES;" so that the 2nd MySQLD get populated from cluster.
4) Then select a count from a table
leaving this porcess logged in.
MySQLD 1;
1) Drop database and follow steps 1 - 3 from above
MySQLD 2;
1) Do a select count for any of the tables of that database.

Example:

While the database is being loaded on MySQLD 1 you get output like the following on MySQLD2:
mysql> select count(*) from teller;
+----------+
| count(*) |
+----------+
|    99688 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from teller;
+----------+
| count(*) |
+----------+
|   101388 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from teller;
+----------+
| count(*) |
+----------+
|   105808 |
+----------+
1 row in set (0.06 sec)

Once you drop the database and recreate on MySQLD1 you get the following on MySQLD2

mysql> select count(*) from teller;
+------------+
| count(*)   |
+------------+
| 4294967295 |
+------------+
1 row in set (0.03 sec)

mysql> select count(*) from teller;
+------------+
| count(*)   |
+------------+
| 4294967295 |
+------------+
1 row in set (0.02 sec)

No matter which table you try:

mysql> select count(*) from account;
+------------+
| count(*)   |
+------------+
| 4294967295 |
+------------+
1 row in set (0.02 sec)

mysql> select count(*) from teller;
+------------+
| count(*)   |
+------------+
| 4294967295 |
+------------+
1 row in set (0.01 sec)

mysql> select count(*) from history;
+------------+
| count(*)   |
+------------+
| 4294967295 |
+------------+
1 row in set (0.02 sec)

Trying to just exit and restart the client does not help:

mysql> exit
Bye
$ mysql -u root --port=3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.10-beta

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

mysql> use TPCB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from teller;
+------------+
| count(*)   |
+------------+
| 4294967295 |
+------------+
1 row in set (0.03 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.06 sec)

mysql> select count(*) from teller;
+------------+
| count(*)   |
+------------+
| 4294967295 |
+------------+
1 row in set (0.02 sec)

The only way to get it back is  to restart MySQLD2

$ mysql -u root --port=3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.10-beta

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

mysql> use TPCB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from teller;
+----------+
| count(*) |
+----------+
|    19992 |
+----------+
1 row in set (0.08 sec)

Now if I set mysql> set ndb_use_exact_count = 0;

and repeat the process I get:

mysql> select count(*) from teller;
+----------+
| count(*) |
+----------+
|    19992 |
+----------+
1 row in set (1.71 sec)

mysql> select count(*) from teller;
ERROR 1146 (42S02): Table 'TPCB.teller' doesn't exist

How to repeat:
see above
[21 Apr 2006 8:06] Jonas Oreland
incorrect error handling in get_table_statistics.
note the only incorrect data is row count.
solution: return table definition has changed, or get new table and get statistics on that.
             not sure which applies...
[23 Apr 2006 6:40] Jonas Oreland
Also nodefail/node restart can give this
[26 Apr 2006 12:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5564
[1 Jun 2006 11:39] Tomas Ulin
already pushed to 5.0 and 5.1
[29 Jun 2006 17:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8502
[29 Jun 2006 20:15] Tomas Ulin
pushed to 5.1.12 5.0.24 and 4.1.21

with this patch a warning will be issued if for some reaso the correct count is not retrieved...
[3 Jul 2006 16:24] Jon Stephens
Documented in 4.1.21/5.0.23/5.1.12 changelogs. Closed.
[7 Jul 2006 8:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8893