2

Hi I use bacula and one of the tables is full:

JobId 8946: Fatal error: sql_create.c:860 Fill File table Query failed: INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) SELECT batch.FileIndex, batch.JobId, Path.PathId, Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name = Filename.Name): ERR=The table 'File' is full

Then I run:

mysql> show table status from current_bacula like 'File';
+------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| File | InnoDB |      10 | Compact    | 52153488 |            142 |  7419723776 |               0 |   2593128448 | 2605711360 |      569929045 | 2013-04-15 21:03:59 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.31 sec)

The my.cnf file has:

innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:12800M

As I understand the command:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

does work only for MyISAM engine doesn't? (Please correct me if I'm wrong)

So my question is: Can I modify only the las autotextend:max for example, something like this:

innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:76800M

Should I perform another action beyond of restarting service. is it safe? I know that I can define another ibdataN file, but I'd want to impact as little as possible the other databases currenlty running on the server.

What do you recommend me?

I have mysql-5.1.67-1.el6_3.x86_64 on CentOS 6.4

Thanks in advance!

1
  • Sometimes I have yet horrific dreams about the times when I needed to use bacula...
    – peterh
    Dec 20, 2013 at 15:17

1 Answer 1

1

You could start another ibdata file

However, before you do, you need to know what occupies the ibdata1 and ibdata2.

  • Table Data (if innodb_file_per_table is disabled)
  • Table Indexes (if innodb_file_per_table is disabled)
  • MVCC (Multiversioning Concurrency Control) Data
  • Table Metadata
  • Rollback segments
  • Undo Logs
  • Double Write Buffer

InnoDB Architecture

InnoDB Architecture

I had dealt with adding ibdata3 in the past with one of my former employer's clients (See my DBA StackExchange post How to solve “The table … is full” with “innodb_file_per_table”?). It was necessary because ibdata2 hit the ext3 ceiling of 2,196,875,759,616 bytes. You do not have that situation.

You could go with your idea

innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:76800M

but you are better off removing the max altogether

innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend

CAVEAT

The reason you ran out of space has to do the undo logs. The ibdata file holds 1023 undo logs. Those logs require wiggle room to operate. Without that internal wiggle room, MVCC information cannot hold snapshots of data in the event of a rollback. Your internal wiggle room is 2605711360 or about 2.5G, which is apparently not enough. With my previous employer's client, they had 106G of wiggle room and it was not enough for them.

SUGGESTION

If you want leave all your data inside ibdata1 and ibdata2, and change the setting to

innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:76800M

you need to monitor the wiggle room.

You could monitor it like this

IBDATA_FILESIZE=8187281408
#
# 8187281408 is 7680M + 128M
#
SQL="SELECT SUM(data_length+index_length)"
SQL="${SQL} FROM information_schema.tables WHERE ENGINE='InnoDB'"
IBDATA_DATAINDEX=`mysql -uroot -p... -ANe"${SQL}"`
(( WIGGLE_ROOM = IBDATA_FILESIZE - IBDATA_DATAINDEX )))
echo ${WIGGLE_ROOM}

Every time you run this, you will know how much free space. You will not need to know which table is involved because any InnoDB table has the potential to complain that it is full when the wiggle room (space for undo information) isn't there.

1
  • I wonder how you estimate that my wiggle room is 2605711360 :) merry christmas...
    – sebelk
    Dec 25, 2013 at 18:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .