MySQL Bugs: #31732: row count(*) overflows after 32-bit integer precision in spite of being bigint

Bug #31732	row count(*) overflows after 32-bit integer precision in spite of being bigint
Submitted:	21 Oct 2007 1:14	Modified:	11 Dec 2007 19:18
Reporter:	Cengiz Gunay	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux (SuSE 10.3 x86_64)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	32-bit, 32bit, 64-bit, 64bit, BIGINT, count, INT, INTEGER, overflow, row

Description:
Filling a MyISAM table with more than 4,294,967,295 rows (limit of unsigned 32-bit int) makes the count(*) function report the overflow part. That is (4,294,967,295 + 1) rows is reported as a table with only 1 row!

However, running other queries allow me to actually use the data. For instance count(distinct x) reports the correct number (because it's less than 4 billion). 

I am not sure at what stage does this error happen. Is it during the calculation of count(*) even though it can support 64-bit integers (bigint)? Or maybe it's a limitation of the querying engine that it cannot hold more than int number of rows.

In any case, it's a serious problem because I can no longer see the table contents with regular select statements. For instance, in the above 1-row table example, select * from table; returns a single row rather than 4 billion rows.

How to repeat:
Fill more than 4,294,967,295 rows into a table and run:

select count(*) from table;

should return only the overflowing number of rows.

Suggested fix:

should return the actual number of rows.

Thank you for the report.

Please indicate storage engine you have problem with.

I don't understand the question. The first sentence in my report clearly says that I'm using the MyISAM engine. I guess it took you a month to ask this question.

Thank you for the feedback.

Please provide output of select @@big_tables;

mysql> select @@big_tables;
+--------------+
| @@big_tables |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

Thank you for hte feedback.

Could you please also provide output of SHOW TABLE STATUS for problematic table and indicate which package do you use. If you compiled MySQL yourself please provide configuration line.

mysql> show table status from sensordb like 'sensor_values';
+---------------+--------+---------+------------+------------+----------------+--------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+------------------------------------+
| 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                            |
+---------------+--------+---------+------------+------------+----------------+--------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+------------------------------------+
| sensor_values | MyISAM |      10 | Fixed      | 2777958656 |            109 | 304135815936 | 12103423998558207 |  95866478592 |         0 |           NULL | 2007-11-22 10:21:30 | 2007-12-04 11:36:21 | NULL       | latin1_swedish_ci |     NULL |                | Sensor values from all three cells |
+---------------+--------+---------+------------+------------+----------------+--------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+------------------------------------+
1 row in set (0.00 sec)

Above the number of rows is wrong, it exhibits the overflow problem. It should be close to seven billion instead. Indeed, searches in the table use the correct number of rows.

I am using the SuSE RPMs:
mysql-client-5.0.45-22
mysql-5.0.45-22

Do I need to compile MySQL myself to get the row count right? If so, what compilation setting do I need to use?

Thank you for the feedback.

I finally got how to repeat the problem.

1. Use binary compiled without option --with-big-tables.
2. Create table t1(f1 int)
3. Fill this table with more than 4294967295 rows.
4. Restart server.

mysql> use test
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 t3;
+------------+
| count(*)   |
+------------+
| 3221225473 | 
+------------+
1 row in set (0.00 sec)

mysql>  insert into t3 select * from t2; select count(*) from t3;
Query OK, 1073741824 rows affected (17 min 2.81 sec)
Records: 1073741824  Duplicates: 0  Warnings: 0

+------------+
| count(*)   |
+------------+
| 4294967297 | 
+------------+
1 row in set (0.00 sec)

mysql> show table status like 't3'\G
*************************** 1. row ***************************
           Name: t3
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 4294967298
 Avg_row_length: 7
    Data_length: 30064771086
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-12-10 22:17:48
    Update_time: 2007-12-11 00:03:12
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> \q   
Bye

$./bin/mysqladmin shutdown -uroot --socket=/tmp/mysql_ssmirnova.sock

$./libexec/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --log-error --socket=/tmp/mysql_ssmirnova.sock --port=33050 --bind-address=127.0.0.1 &

$ ./bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.54-debug Source distribution

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

mysql> use test
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 t3;
+----------+
| count(*) |
+----------+
|        2 | 
+----------+
1 row in set (0.00 sec)

mysql> show table status like 't3'\G
*************************** 1. row ***************************
           Name: t3
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 2
 Avg_row_length: 15032385543
    Data_length: 30064771086
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-12-10 22:17:48
    Update_time: 2007-12-11 00:03:12
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql>

> Do I need to compile MySQL myself to get the row count right? If so, what compilation setting do I need to use?

Binaries whould be compiled with --with-big-tables option. Binaries provided by MySQL are compiled with this option.

Thank you for figuring this out! I will try to compile my own version or get the binaries from MySQL to test this.

If that solves my problem, I will notify the SuSE people reponsible for the mysql packages.