TheSQL data table seems to stuck at 16777215 rows

myisamMySQLmysql-5.1

I am creating a MySQL table and loading data from a local file.

CREATE TABLE `patent` (

  `seq_number` mediumint(9) unsigned NOT NULL auto_increment,
  `seq_id` varchar(24) default NULL,
  `date` date default NULL,
  `patent_number` varchar(16) default NULL,
  `pat_seq_number` smallint(6) unsigned default '0',
  `organism` text,
  `assignee` text,
  `seq_length` int(8) default '0',
  `seq` text,
  PRIMARY KEY  (`seq_number`),
  Index `seq_id` (`seq_id`),
  Index `patent_number` (`patent_number`),
  Index `pat_seq_number` (`pat_seq_number`),
  Index `seq_length` (`seq_length`)

) Engine = MyISAM MAX_ROWS=536870912;


load data local infile '/data/p_table.txt' into table  patent ignore 0 lines 
(seq_id, date, patent_number, pat_seq_number, organism, assignee, seq, seq_length);

It seems that I am stuck at the number of rows:

mysql> select count(*) from patent;

+----------+
| count(*) |
+----------+
| 16777215 |
+----------+

1 row in set (0.00 sec)

Although my txt data table has about 20 million lines (records).

I tried Engine=InnoDB

I also tried Engine = MyISAM MAX_ROWS=200000000 (200 million)

mysql> show table status like 'patent'\G

*************************** 1. row ***************************

           Name: patent

         Engine: MyISAM

        Version: 10

     Row_format: Dynamic

           Rows: 16777215

 Avg_row_length: 244

    Data_length: 4098768552

Max_data_length: 281474976710655

   Index_length: 715227136

      Data_free: 0

 Auto_increment: 16777216

    Create_time: 2013-04-14 12:46:10

    Update_time: 2013-04-14 12:50:43

     Check_time: 2013-04-14 12:54:08

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options: max_rows=536870912
        Comment: 

1 row in set (0.00 sec)

The manuals and online advices all say I should be able to create large tables.

her is my uanme linux output.

Linux … 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

Here is the verison for mySQL:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 318
Server version: 5.1.67 Source distribution

How can I create a larger table to increase the number of rows to larger than 2**24=1677215 rows?

Best Answer

The maximum value for your auto_increment 'seq_number' field is 16777215. You should make this field use an int or a bigint if you need more that 16,777,215 records.

See the MySQL Documentation for info about the mediumint(9) format.

https://dev.mysql.com/doc/refman/5.0/en/integer-types.html