Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    4

    Question Unanswered: InnoDB Table Full

    First of all, hello to everybody. I find this forum very interesting and attended by very skilled people.
    I have a problem with MySQL 4.0.1. on Linux RedHat 7.1.
    I get the message "Table MYTABLE full" while populating it via a batch procedure I wrote. The problem is that I already followed most of the advice found in this forum. In particular:

    I started mysqld with --big-tables option (by editing my /etc/my.cnf)
    I created MYTABLE with max-rows=10000000 option

    The strange thing is that I have a 300.000 rows table who is working perfectly, while MYTABLE "crashes" after 3.000 inserts (I should insert at least 70.000 recs :-(( ).
    Can anyone help me? Thanks in advance.

  2. #2
    Join Date
    Jul 2002
    Posts
    55
    I've only used InnoDB under Win32 and I'm no expert so bear with me

    Have you checked the physical size of the InnoDB tablespace? In my.cnf (or my.ini under Win32) there should be a directive that specifies the size of the tablespace - basically, the size of the data file to generate. Once that tablespace is used up, you have to generate a further InnoDB data file to extend the original.

    I'm not sure about the max-rows directive, but I'm guessing that it means absolutely nothing if the tablespace is only a few Mb in size (for example).

    I'm interested to hear how it works out.

    Mull.

  3. #3
    Join Date
    Jul 2002
    Posts
    4
    Before reading your post I was sure that my tablespace's size was correct. Indeed in my my.cnf I set this value to 2Gbytes (enough to host my table). Unfortunately, watching at the physical size on the FileSystem, I discovered that's, in effect, only 64MBytes. Which means that's full, because of my 300.000 rows table whose size is almost 40Mb.
    Now the problem I have to face is: "How can I force MySQL to resize idbdata1?" (I don't have problems about data loss. I'm just coding, now. I can do whatever...
    Any ideas? Thank you.

  4. #4
    Join Date
    Jul 2002
    Posts
    55
    You can't resize it on-the-fly. Once InnoDB has created the tablespace, you're stuck with it. If you need more, you need to create a new file (and, IIRC, InnoDB transparently uses the new tablespace to extend existing tables).

    Use the innodb_data_file_path directive:

    Code:
    innodb_data_file_path = tbdata1:1000M;1bdata2:500M
    This should create two files, one of 1000Mb and the other of 500Mb. If you need to add more tablespace, shut down the server, change my.cnf to specify an additional data file and restart - I think (check the docs at mysql.com to make sure...)

    Mull.

  5. #5
    Join Date
    Jul 2002
    Posts
    4
    It seems it's working. Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •