0

PC is Intel Core 2 Duo with 4G of RAM. Windows 7 32bit innodb_buffer_pool_size=1G with innodb_buffer_pool_instances=2

  1. I was loading data from CSVs into mysql table using mysql import command. Mysql gave me Table Full Error: 1114 at when my table size was exactly 4G. (Size of .idb file in the ProgramData directory.)

  2. After that error, I am not able to query the able using SELECT command or not even able to use mysqldump on it. When I do so, Mysql57 service stops and I have to restart it. So inshort, I am not able to query my table.

  3. I checked my error log and I got this error message every time, I query the table.
    2016-12-23T23:08:40.158223Z 3 [ERROR] InnoDB: Trying to access page number 3 in space 86, space name ccrawlg/ccrawloct16, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server. 2016-12-23T23:08:40.161223Z 3 [ERROR] InnoDB: Server exits.

  4. After searching the web, I seemed to me that my database is corrupted and then I tried inndo_force_recovery=1to6 in my my.ini file but unable to do mysqldump and take a backup.

  5. All the above steps happened twice with me as I retried loading all my csvs after first failure.

  6. When I retried, I noticed that the size of idb file was exactly the same as the previous idb file in kbs when i said - Table is full.

Edit:-

  1. My disk doesn't lack in space, there is sufficient space on the disk. It's just that mysqlimport gave table full error at idb size of 4Gs.

  2. My problem is exactly same as this post here :-
    https://stackoverflow.com/questions/38245974/mysql-server-crashes-innodb-outside-the-tablespace-bounds

  3. Import statement:-
    `mysqlimport -i --fields-terminated-by="," --lines-terminated-by="\n" -uroot -psomething --local ccrawlg ccrawloct2016 --verbose

  4. Show create table is below

CREATE TABLE ccrawloct2016

(
  'email' varchar(50) NOT NULL,
  'source' longtext,
  `date` varchar(20) DEFAULT NULL,
  `domain` mediumtext,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1. Link to my.ini file :-
    https://drive.google.com/file/d/0BztaImknhgDFWUFsdEE2MXFxWDA/view?usp=sharing

I hope some expert out there could help me get out of this problem. I will provide more info needed.

Thanks in advance,

  • 1
    Consider moving to a 64-bit platform. – mustaccio Dec 24 '16 at 16:12
  • @mustaccio No other choice, Sir ? Can't I add more than 4G data in innodb while on 32 bit ? Moving to 64bit is not easy for me. Is there any steps, I can do to resolve the issue.? – Vikas Gautam Dec 24 '16 at 16:19
  • Do you use FAT filesystem, by chance? – mustaccio Dec 24 '16 at 17:22
  • No, its NTFS. I saw in C drives' properties. – Vikas Gautam Dec 24 '16 at 17:25
  • This is exactly same problem as I have :- stackoverflow.com/questions/38245974/… – Vikas Gautam Dec 24 '16 at 18:45
1
  1. DROP the table.
  2. Re-import, but make sure the table is MyISAM, not InnoDB.

Now you can at least see if the data is bad. And you can experiment with INSERT INTO inno_table SELECT * FROM myisam_table. There is a chance (slim chance) that CSV is the problem. MyISAM is more compact on disk, so a larger file can be loaded before hitting 4GB (in the .MYD file).

Another thought... Did you have indexes (other than the PRIMARY KEY)? Try loading without the indexes in place.

Was the data in PK order? That will make the loading faster and more efficient and take less space. Can you sort the CSV before importing?

Generally, loading with the PK in place is best for InnoDB. Please provide the CREATE TABLE so we know what you are working with.

Another thing to try: Use the mysql commandline tool; within it, use the LOAD DATA command. (I don't hold out much hope for this because I would assume that mysqlimport uses the same command.)

Yet another:

Break the source file into multiple pieces (each under, say, 1GB). Import them into different tables. Then use SQL statements to put them together. And get rid of the intermediate tables.

  • I have added the two requirements in my question. I can't DROP/DUMP/SELECT the table. it crashes the server. I have huge data. I just loaded 25% of the data when it gave the table full error. No, I don't have indexes. I am very new to the mysql databases. No the csvs are not sorted. 4G is likely to hit anyway. – Vikas Gautam Dec 24 '16 at 19:44
  • Actually, stackoverflow.com/questions/38245974/… link explains the issue more better than me. I will provide anything i have, if needed. – Vikas Gautam Dec 24 '16 at 19:50
  • I have my source csvs in 27 parts. my previous two tries were in different orders. For now, I am trying the same process in different order. So CSVs aren't corrupted. I will definitely try the 'Different tables' approach, if anything not works out. – Vikas Gautam Dec 24 '16 at 20:14
  • 27 parts -- some are over 4GB? Or the total is 4GB? – Rick James Dec 24 '16 at 22:40
  • No, there are 27 csvs (total 90.5GBs in size). I am splitting them first in the 512 MB files and then importing them. – Vikas Gautam Dec 25 '16 at 8:51
0

For me, the solution was to moving to 64bit operating system as suggested by @mustaccio originally in the comments. Thanks to @Rick_james and @mustaccio for their support and instant replies.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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