4

I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full.

I changed my tmp_table_size and heap_size but mysql still says same error.

I try How to solve "The table ... is full" with "innodb_file_per_table"? this settings but same error.

I create dummy table and Add new column it is ok. but my real table mysql says error.

My variables : innodb_data_file_path | ibdata1:12M:autoextend

Community
  • 1
eusso
  • 61
  • 1
  • 5

1 Answers1

1

There is no maximum in the file_path command you provided that limits the size of the ibdata1 file. Perhaps you don't have enough disk space left. It would be a good idea to add more disk space to the current volume or clean up first as mysql needs that volume.

If you're worried about the ibdata files filling up disk space, you can always add another ibdata file and place that file anywhere that has sufficient disk space. You would have to set permissions so that the mysql user could access the directory where you place the second file.

Example: 

innodb_data_file_path = ibdata1:12M:autoextend

becomes

innodb_data_file_path = /ibdata/ibdata1:(NN)M;/disk2/some_directory/ibdata2:128M:autoextend

Where (NN)M is the current size of the ibdata1 file.

The second file will start out with a fixed size of 128M and will autoextend.

Here is the process

Increasing InnoDB table space

Shut down the MySQL server.

If the previous last data file is defined with the keyword autoextend, change its definition to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

Add a new data file to the end of innodb_data_file_path, optionally making that file auto-extending. Only the last data file in the innodb_data_file_path can be specified as auto-extending.

Set permissions for mysql.

chown mysql:mysql -R /disk2/some_directory/ibdata2

Start the MySQL server again.

The other thing to keep in mind with ibdata files is the fact that they can't be easily shrunk. If you are not using innodb_file_per_table, then ibdata will just grow and grow and even if you delete database objects. If you have deleted databases and are sure that your ibdata file will remain small, you can always recreate that file and the iblogs to give disk space back to the OS. Rolando wrote a post that will help you out with that.

Community
  • 1