[Bacula-users] bacula max_rows limit 4294967295 on File table

Hi all,

I've run into a problem with mysql and bacula. Backups are now failing 
on updating the File table with messages like this:

03-Jun 08:17 archive3-dir JobId 8074: Fatal error: Can't fill File table 
Query failed: INSERT INTO File (FileIndex, JobId, PathId, FilenameId, 
LStat, MD5)SELECT batch.FileIndex, batch.JobId, Path.PathId, 
Filename.FilenameId,batch.LStat, batch.MD5 FROM batch JOIN Path ON 
(batch.Path = Path.Path) JOIN Filename ON (batch.Name = Filename.Name): 
ERR=Duplicate entry '4294967295' for key 1

Bacula uses auto-increment for the File table, and I've apparently hit 
the 4.2 billion max_rows limit. I've been running bacula for years, and 
with tens of millions of files per cycle, I've apparently used up the 
auto-increment numbers. (4.2B/42M = 100 backup cycles = 50 months = 4 
years.) I just moved bacula and the database to a new server and 
upgraded them three months ago, but kept all my data.

I checked, and almost all my FileIds are clustered sequentially in the 
last 50 million of the 4.2 billion sequence... except for a few old 
backups that I could purge.

Mysql won't let me reset the auto-increment to 1 with alter table. It 
forces any new auto-increment value to be at least max(FileId) + 1. [1]

Mysql won't go any larger than 4294967295 for a 32-bit mysql table? [2]

Even if I increase this limit, FileId is int(10), which means 9.9 
billion would be the largest number acceptable to that column anyway.

What's the move here?

I hear that max_rows is larger on a 64-bit mysql server. Do I have to 
move my database to a 64-bit server to get around this?

Does bacula have a way of renumbering File table entries?

Do I have to dump my history and start over every four years?

Thanks,

Brian

mysql> show table status like 'File' \G
*************************** 1. row ***************************
          Name: File
        Engine: MyISAM
       Version: 10
    Row_format: Dynamic
          Rows: 55149182
Avg_row_length: 111
   Data_length: 6174414032
Max_data_length: 1099511627775
  Index_length: 3687694336
     Data_free: 3085472
Auto_increment: 4294967296
   Create_time: 2008-03-24 11:12:47
   Update_time: 2008-06-03 11:20:55
    Check_time: 2008-05-15 09:36:53
     Collation: latin1_swedish_ci
      Checksum: NULL
Create_options: max_rows=4294967295 avg_row_length=120
       Comment:
1 row in set (0.00 sec)





mysql> create table foo (id int(10));
Query OK, 0 rows affected (0.00 sec)

mysql> alter table foo max_rows=100000000000;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'foo' \G
*************************** 1. row ***************************
          Name: foo
        Engine: MyISAM
       Version: 10
    Row_format: Fixed
          Rows: 0
Avg_row_length: 0
   Data_length: 0
Max_data_length: 21474836479
  Index_length: 1024
     Data_free: 0
Auto_increment: NULL
   Create_time: 2008-06-03 13:55:22
   Update_time: 2008-06-03 13:55:22
    Check_time: NULL
     Collation: latin1_swedish_ci
      Checksum: NULL
Create_options: max_rows=4294967295     <============== but still
       Comment:
1 row in set (0.00 sec)



mysql> describe File;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| FileId     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| FileIndex  | int(10) unsigned | NO   |     | 0       |                |
| JobId      | int(10) unsigned | NO   | MUL | 0       |                |
| PathId     | int(10) unsigned | NO   | MUL | 0       |                |
| FilenameId | int(10) unsigned | NO   | MUL | 0       |                |
| MarkId     | int(10) unsigned | NO   |     | 0       |                |
| LStat      | tinyblob         | NO   |     |         |                |
| MD5        | tinyblob         | NO   |     |         |                |
+------------+------------------+------+-----+---------+----------------+



[1] http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
[2] http://jeremy.zawodny.com/blog/archives/000796.html

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users