MySQL Bugs: #19202: Incorrect errorhandling in select count(*) wrt temporary error

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	CPU Architecture:	Any

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

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...

Also nodefail/node restart can give this

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

already pushed to 5.0 and 5.1

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

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...

Documented in 4.1.21/5.0.23/5.1.12 changelogs. Closed.

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