Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

Run this to move everything into a bigger auto_increment keyed table

CREATE TABLE `patentnew` (
  `seq_number` int 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 AUTO_INCREMENT=16777216;
ALTER TABLE patentnew DISABLE KEYS;
INSERT INTO patentnew SELECT * FROM patent;
ALTER TABLE patentnew ENABLE KEYS;
ALTER TABLE patent RENAME patentold;
ALTER TABLE patentnew RENAME patent;
share|improve this answer
    
+1 for an easy fix! –  Max Vernon Apr 15 '13 at 21:07

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

share|improve this answer
1  
This is definitely root cause. +1 !!! –  RolandoMySQLDBA Apr 15 '13 at 21:02
    
Problem solved. Thanks for the quick reply!! –  wright17 Apr 15 '13 at 21:19
    
My pleasure! Any chance you might accept this as the answer, by clicking the grey checkmark under the votes to the left? –  Max Vernon Apr 16 '13 at 18:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.