Bug #30638 why doesn't > 4294967295 rows work in myisam on windows ?
Submitted: 26 Aug 2007 10:16 Modified: 1 Nov 2007 3:03
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.0.46, 5.0.48 OS:Microsoft Windows
Assigned to: Alexey Botchkov CPU Architecture:Any

[26 Aug 2007 10:16] Shane Bester
Description:
Hi!

I populate a table with >= 2^32 rows and the row count wraps around in windows!
There are no table full errors, and no warnings, just wrong results!

Look here: Windows 5.0.46
--------------------------
mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `t1` (`c1` tinyint unsigned not null,key (`c1`)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table `t1` disable keys;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1),(4),(6),(7);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1; #8
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

...... <insert a bunch of records> ...........

mysql> insert into t1 select * from t1 limit 2147483647; #4294967295
Query OK, 2147483647 rows affected (53 min 3.13 sec)
Records: 2147483647  Duplicates: 0  Warnings: 0

mysql> flush tables;
Query OK, 0 rows affected (0.17 sec)

mysql> show table status;
+------+--------+------------+-------------+------------------+-
| Name | Engine | Rows       | Data_length | Max_data_length  |
+------+--------+------------+-------------+------------------+-
| t1   | MyISAM | 4294967295 | 30064771065 | 1970324836974591 |
+------+--------+------------+-------------+------------------+-
1 row in set (0.30 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.03 sec)

mysql> show table status;
+------+--------+------+-------------+------------------+
| Name | Engine | Rows | Data_length | Max_data_length  | +------+--------+------+-------------+------------------+
| t1   | MyISAM |    0 | 30064771072 | 1970324836974591 |
+------+--------+------+-------------+------------------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 | <------ wrong results from SQL!!!!
+----------+
1 row in set (0.00 sec)

mysql> select count(c1) from t1;
+-----------+
| count(c1) |
+-----------+
|         0 | <------ wrong results from SQL!!!!
+-----------+
1 row in set (0.05 sec)

On linux 5.0.48:
----------------

<same test as above>

mysql> show table status;
+------+--------+------------+-------------+------------------+
| Name | Engine | Rows       | Data_length | Max_data_length  |
+------+--------+------------+-------------+------------------+
| t1   | MyISAM | 4294967296 | 30064771072 | 1970324836974591 |
+------+--------+------------+-------------+------------------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+------------+
| count(*)   |
+------------+
| 4294967296 |
+------------+
1 row in set (0.05 sec)

mysql> select count(c1) from t1;
+------------+
| count(c1)  |
+------------+
| 4294967296 |
+------------+
1 row in set (0.00 sec)

How to repeat:
drop table if exists `t1`;
create table `t1` (`c1` mediumint unsigned not null,`c2` mediumint unsigned not null,`c3`
bigint unsigned not null, key `c1` (`c1`,`c2`,`c3`)) engine=myisam default charset=utf8;
alter table `t1` disable keys;
insert into t1 values (1,2,3),(4,5,6),(6,7,8),(1,4,7);
insert into t1 select * from t1; #8
insert into t1 select * from t1; #16
insert into t1 select * from t1; #32
insert into t1 select * from t1; #64
insert into t1 select * from t1; #128
insert into t1 select * from t1; #256
insert into t1 select * from t1; #512
insert into t1 select * from t1; #1024
insert into t1 select * from t1; #2048
insert into t1 select * from t1; #4096
insert into t1 select * from t1; #8172
insert into t1 select * from t1; #16384
insert into t1 select * from t1; #32768
insert into t1 select * from t1; #65536
insert into t1 select * from t1; #131072
insert into t1 select * from t1; #262144
insert into t1 select * from t1; #524288
insert into t1 select * from t1; #1048576
insert into t1 select * from t1; #2097152
insert into t1 select * from t1; #4194304
insert into t1 select * from t1; #8388608
insert into t1 select * from t1; #16777216
insert into t1 select * from t1; #33554432
insert into t1 select * from t1; #67108864
insert into t1 select * from t1; #134217728
insert into t1 select * from t1; #268435456
insert into t1 select * from t1; #536870912
insert into t1 select * from t1; #1073741824
insert into t1 select * from t1; #2147483648
insert into t1 select * from t1 limit 2147483647; #4294967295
show table status;
insert into t1 values (1);
show table status;
select count(*) from t1;

Suggested fix:
windows is a major platform for mysql.  it should support same number of rows as linux version, since there's no special coding involved.
[26 Aug 2007 11:01] Shane Bester
smaller testcase, which I used (1 tinyint col).

Attachment: bug30638.sql (application/octet-stream, text), 1.57 KiB.

[2 Oct 2007 8:32] Georgi Kodinov
Probably related to bug #30639
[4 Oct 2007 8:41] Georgi Kodinov
Most likely fixed by the fix for 30639.
[18 Oct 2007 10:57] 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/35827

ChangeSet@1.2546, 2007-10-18 14:52:19+05:00, holyfoot@mysql.com +1 -0
  Bug #30638 why doesn't > 4294967295 rows work in myisam on windows.
  
  The BIG_TABLES define wasn't enabled on Windows.
  #define added
[29 Oct 2007 8:42] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:45] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:49] Bugs System
Pushed into 6.0.4-alpha
[1 Nov 2007 3:03] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

MyISAM tables could not exceed 4294967295 (2^32 - 1) rows on Windows.