Description:
Problem:
--------------
I have been loading data intp a large database. As suggested in the manual I used "myisamchk" prior to entering any data to disable indexing and then loaded the data in chunks using "LOAD DATA INFILE". The primary data table in the database is 167GB, consisting of 3453338112 rows. When all the data was loaded I tried to rebild the index with "myisamchk" and got an error saying that it had "Found too many records".
Command:
-----------------
[veritas@n2 ~/SRTM]$ /u/work/VERITAS/mysql/bin/myisamchk -O sort_buffer_size=512M -O key_buffer_size=512M -O read_buffer_size=32M -O write_buffer_size=32M --tmpdir=/work/VERITAS -r -q /u/work/VERITAS/mysql-db/srtm/Elevation
- check record delete-chain
- recovering (with sort) MyISAM-table '/u/work/VERITAS/mysql-db/srtm/Elevation'
Data records: 3415898112
- Fixing index 1
myisamchk: error: Key 1 - Found too many records; Can't continue
MyISAM-table '/u/work/VERITAS/mysql-db/srtm/Elevation' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
Database:
---------------
mysql> show table status from srtm;
+------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| 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 |
+------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Elevation | MyISAM | 9 | Fixed | 3453338112 | 52 | 179720640000 | 10 | 1024 | 0 | NULL| 2005-05-22 17:25:42 | 2005-05-23 12:17:35 | 2005-05-22 17:27:00 | latin1_swedish_ci | NULL | | |
| Parameters | MyISAM | 9 | Dynamic | 4 | 31 | 124 | 4294967295 | 2048 | 0 | NULL| 2005-05-22 17:25:42 | 2005-05-22 17:35:21 | NULL | latin1_swedish_ci | NULL | | |
+------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
2 rows in set (0.00 sec)
mysql> describe Elevation;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Longitude | int(11) | | PRI | 0 | |
| Latitude | int(11) | | PRI | 0 | |
| Elevation | smallint(6) | | | 0 | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
System Type:
--------------------
Linux n2.neutrino.hosted.ats.ucla.edu 2.4.20-30.8.legacysmp #1 SMP Fri Feb 20 17:13:00 PST 2004 i686 i686 i386 GNU/Linux
Many Thanks for your time.
How to repeat:
Repeating this bug requires the generation of a large database with indexing disabled.
1) The SQL I used for table creation is:
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Elevation | CREATE TABLE `Elevation` (
`Longitude` int(11) NOT NULL default '0',
`Latitude` int(11) NOT NULL default '0',
`Elevation` smallint(6) NOT NULL default '0',
PRIMARY KEY (`Longitude`,`Latitude`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2) I then disabled indexing with
/u/work/VERITAS/mysql/bin/mysqladmin -S /u/work/VERITAS/mysql/lib/mysql.sock flush-tables
/u/work/VERITAS/mysql/bin/myisamchk --keys-used=0 -r -q /u/work/VERITAS/mysql-db/srtm/Elevation
3) The data I loaded were the NASA Shuttle Radar Topography Mission FTP site. I can make the code to do this available if it helps.
4) Generate the index using
/u/work/VERITAS/mysql/bin/myisamchk -O sort_buffer_size=512M -O key_buffer_size=512M -O read_buffer_size=32M -O write_buffer_size=32M --tmpdir=/work/VERITAS -r -q /u/work/VERITAS/mysql-db/srtm/Elevation
Suggested fix:
Fix myisamchk or indicate maximum table size in the section of the manual dealing with optimizing data insertion.
