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.
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).
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.
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).
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...)