MySQL Bugs: #24566: Incorrect key file for table ( the size of table is more than 2G)

Bug #24566	Incorrect key file for table ( the size of table is more than 2G)
Submitted:	24 Nov 2006 7:45	Modified:	5 Apr 2007 19:53
Reporter:	Techie Ou	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.1.12, 5.1.16-BK	OS:	Windows (windows xp sp2)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	corruption, myisam

Description:
I use the version of '5.1.12' of MySQL database test my project, I discove a bug that I think, and it make the table in database to crash. 
I test more three time, olny the size of table data is than more 2G, the table must be crashed, return err message is : "Incorrect key file for table '.\syslog_db_2006_11_22\traffic_medium_report_172_16_161_1.MYI'; try to repair it".

I use myisamchk to fix the traffic_medium_report_172_16_161_1 table, this tool tell me fix successed. but i use " select *  from traffic_medium_report_172_16_161_1" to query data, but only display th front of data, the behind of data can't display. 
I use " SELECT * FROM traffic_medium_report_172_16_161_1 order by syslog_time desc limit 100;"  to query data, return err message is "Table 'traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired";

my pc system information:
OS: windows xp sp2
memery: 768M

mysql information:
mysql version: 5.1.12
mysql err log:
061122 15:24:41 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=RHOU-bin' to avoid this problem.
061122 15:24:42 [Note] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: ready for connections.
Version: '5.1.12-beta-community-nt-log'  socket: ''  port: 13361  MySQL Community Server (GPL)
061122 15:24:42 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50018, now running 50112. Please use scripts/mysql_fix_privilege_tables
061122 15:24:42 [ERROR] mysql.user has no `Event_priv` column at position 29
061122 15:24:42 [ERROR] SCHEDULER: The system tables are damaged. The scheduler subsystem will be unusable during this run.
061122 15:25:07 [ERROR] Got error 127 when reading table './syslog_db_2006_11_22/traffic_medium_report_172_16_161_1'

How to repeat:

-- all queries in the syslog_db_2006_11_12 database
-- ---------------------------------

1. the first: create database and table;

creat database syslog_db_2006_11_12;
//--- table
DROP TABLE IF EXISTS `syslog_db_2006_11_22`.`traffic_medium_report_172_16_161_1`;
CREATE TABLE  `syslog_db_2006_11_22`.`traffic_medium_report_172_16_161_1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `duration` int(4) DEFAULT NULL,
  `source_ip` int(10) unsigned DEFAULT NULL,
  `source_port` smallint(6) unsigned DEFAULT NULL,
  `dest_ip` int(10) unsigned DEFAULT NULL,
  `dest_port` smallint(6) unsigned DEFAULT NULL,
  `subnet` tinyint(4) unsigned DEFAULT NULL,
  `trans_dir` tinyint(4) unsigned DEFAULT NULL,
  `protocol` tinyint(4) unsigned DEFAULT NULL,
  `service` varchar(20) DEFAULT NULL,
  `packet_sent` int(10) unsigned DEFAULT NULL,
  `packet_recv` int(10) unsigned DEFAULT NULL,
  `syslog_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time_index_traffic_report` (`syslog_time`)
) ENGINE=MyISAM AUTO_INCREMENT=37325073 DEFAULT CHARSET=utf8;

2. insert data to table
 example:
insert into traffic_medium_report_172_16_161_1(`duration`,`source_ip`,`source_port`,`dest_ip`,`dest_port`,`subnet`,`trans_dir`,`protocol`,`service`,`packet_sent`,`packet_recv`,`syslog_time`) values  (30,2886771115,7561,3699869905,4672,0,1,17,'UNKNOWN',55,55,'2006-11-22 22:53:18'), (30,2886771115,7561,1092089717,4672,0,1,17,'UNKNOWN',55,55,'2006-11-22 22:53:18'), (30,2886771115,7561,3590149134,4672,0,1,17,'UNKNOWN',55,55,'2006-11-22 22:53:18'), (30,2886771115,7561,3260354057,4672,0,1,17,'UNKNOWN',55,55,'2006-11-22 22:53:18'), (30,2886771115,7561,1459864540,4672,0,1,17,'UNKNOWN',55,55,'2006-11-22 22:53:18')

 note: insert data to table until the size of 'traffic_medium_report_172_16_161_1' table is more than 2G, the table must be crash.

Thank you for a problem report. Please, take a look at http://bugs.mysql.com/bug.php?id=24022, and run mysql_fix_privilege_tables.sql as suggested there. You have the same (known) bug in your 5.1.12. Check your test again after that, and inform about the results.

Valeriy Kravchuk :

I according to your method to run mysql mysql_fix_privilege_tables.sql, and i am starting MySQL service to test my project, but the tabe must be crashed when the size of 'traffic_medium_report_172_16_161_1' table is more than 2G.
I have tested three times, the problem was happened when the size of table exceed 2G. please quick check this problem, i wish you  support me.
If you need other infomation,please tell me.

I am sure I don't update from 5.0 to 5.1, I download 5.1.12 zip to install my system, i start MySQL service to test my project. but the problem appeared only the size of table exceed 2G.
I try to test three times, but i don't solve this problem.

mysql return errors log information:
061127 10:55:09 [Note] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: ready for connections.
Version: '5.1.12-beta-community-nt'  socket: ''  port: 13361  MySQL Community Server (GPL)
061127 10:55:09 [Note] SCHEDULER: Loaded 0 events
061127 12:31:50 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Incorrect key file for table '.\syslog_db_2006_11_27\traffic_medium_report_172_16_161_1.MYI'; try to repair it
061127 12:37:17 [Note] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Normal shutdown

061127 12:37:17 [Note] SCHEDULER: Purging queue. 0 events
061127 12:37:19 [Warning] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Forcing close of thread 16743  user: 'root'

061127 12:37:19 [Warning] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Forcing close of thread 5578  user: 'root'

061127 12:37:19 [Note] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Shutdown complete

061127 12:37:52 [Note] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: ready for connections.
Version: '5.1.12-beta-community-nt'  socket: ''  port: 13361  MySQL Community Server (GPL)
061127 12:37:52 [Note] SCHEDULER: Loaded 0 events
061127 12:38:45 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:38:46 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:38:47 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:38:47 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:38:48 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:38:48 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:39:02 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:39:02 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:39:02 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:39:03 [ERROR] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Table './syslog_db_2006_11_27/traffic_medium_report_172_16_161_1' is marked as crashed and should be repaired
061127 12:47:07 [Note] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Normal shutdown

061127 12:47:07 [Note] SCHEDULER: Purging queue. 0 events
061127 12:47:07 [Note] F:\AceNet1\Reporter\mysql\bin\mysqld-nt.exe: Shutdown complete

Please, try to repeat with a newer version, 5.1.14, and, in case of the same problem, send the results of SHOW TABLE STATUS and SHOW CREATE TABLE for that traffic_medium_report_172_16_161_1 table.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

hi techie, please check if you have myisam_use_mmap enabled or disabled.

show global variables like '%mmap%';

If it's enabled, try to disable it in my.ini and see if problems go away.

verified on 5.1.16BK using this testcase:

-----------------
drop table if exists t1;
create table t1(c1 bigint not null auto_increment primary key,c2 char(255),key(c2))engine=myisam;
insert into t1(c2) values ('a');
insert into t1(c2) select c2 from t1 limit 1000000; #2
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #4
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #8
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #16
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #32
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #64
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #128
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #256
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #512
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #1024
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #2048
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #4096
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #8179
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #16384
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #32k
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #64k
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #128k
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #256k
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #512k
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #1mil
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #1mil
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #2mil
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #3mil
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #4mil
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #5mil
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #6mil
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #7mil
select * from t1 order by c2 desc limit 10;
insert into t1(c2) select c2 from t1 limit 1000000; #8mil
select * from t1 order by c2 desc limit 10;
check table t1;
insert into t1 values ();
check table t1;
------------------------

my output:

...
mysql> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (4 min 23.54 sec)

mysql> insert into t1 values();
ERROR 126 (HY000): Incorrect key file for table '.\test\t1.MYI'; try to repair it
mysql> check table t1;
+---------+-------+----------+-------------------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                                    |
+---------+-------+----------+-------------------------------------------------------------+
| test.t1 | check | warning  | Table is marked as crashed                                  |
| test.t1 | check | warning  | Size of datafile is: 2388824328       Should be: 2388824064 |
| test.t1 | check | error    | Found 9048577 keys of 9048576                               |
| test.t1 | check | error    | Corrupt                                                     |
+---------+-------+----------+-------------------------------------------------------------+
4 rows in set (1 min 41.27 sec)

Hi,Shane.
I set  'myisam_use_mmap' variable to disable,but the bug appear in the version of mysql 5.1.14, but the bug don't appear in the version of mysql 5.1.11.

Techie, it's ok - i had verified the bug using the above testcase anyway. it's not related to mmap setting.  thanks for a bug report!

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/22876

ChangeSet@1.2500, 2007-03-25 00:24:39+04:00, svoj@mysql.com +1 -0
  BUG#24566 - Incorrect key file for table ( the size of table is more than 2G)
  Accessing a file that is bigger than 2G may report that read/write operation
  failed. This may affect anything that uses my_pread/my_pwrite functions, e.g.
  MyISAM, ARCHIVE, binary log.
  
  For MyISAM INSERT may report that table is crashed when writing to a table
  that is bigger than 2G.
  
  This is fixed by using proper offset type in my_pread/my_pwrite functions on
  systems that do not have native pread/pwrite calls.
  
  Affects systems that do not have native pread/pwrite calls, e.g. Windows.
  
  No test case for this fix, since it requires huge table.

Ok to push from me.

Pushed into 5.1.18-beta

Noted in 5.1.18 changelog.

Access via my_pread() or my_pwrite() to table files larger than 2GB 
could fail on some systems.

Bug #28559 is likely a duplicate of this one.