Did you recently upgrade to MySQL 4.0? The solution I can see is to drop the index on Path and the index on Filename, then recreate them but create them on the full Path and the full Name rather than limiting it as is currently the case in the make_mysql_tables file.
I don't understand how the index on the Filename.Name and Path.Path fields change the constraints of records within the tables. An index by itself solely provides a way to speed up queries against those fields. An index by itself has no uniqueness properties or constraints on data inserted into the table.
A PRIMARY KEY on the other hand, is a constraint on the data in order to uniquely identify each record in the table. A PRIMARY KEY necessarily creates on index on the PRIMARY KEY field(s). That index is in addition and unrelated to other defined indexes, though, such as INDEX(Path(50)).
IT IS THE INDEX. :(
Why would MySQL be giving me duplicate errors on a MULTIPLE KEY INDEX?
I ran the following:
use bacula; alter table File drop index FilenameID; alter table Path drop index Path;
Error messages gone.
IT ISN'T THE INDEX!
When you ran the statement: alter table File drop index FilenameID; I think you REMOVED THE PRIMARY KEY from the File table.
The PRIMARY KEY(FilenameId) statement in make_mysql_tables was the only thing enforcing unique records within that table. You're not getting the error because you've setup MySQL to allow duplicates within the File table.
But, you still have the problem!
I've spent a lot of time on this thread because I'm currently deploying bacula on RHEL4, which installs MySQL 4.1 by default and it seems like more work for me to backport and maintain mysql 3.23.51 than it is to work out issues with bacula and MySQL 4.1. Comments about the keys and index scared me.
What you're saying and doing doesn't make any sense...
In your previous email, you posted this:
mysql> insert into Path(PathId,Path) values (47947,'C:/$VAULT$.AVG/');
ERROR 1062: Duplicate entry '47947' for key 1
mysql> insert into Path(PathId,Path) values (47948,'Z:/$VAULT$.AVG/');
ERROR 1062: Duplicate entry '47948' for key 1
mysql> insert into Path(Path) values (' ');
ERROR 1062: Duplicate entry '47946' for key 1
mysql> replace into Path(PathId,Path) values (47947,'C:/$VAULT$.AVG/');
ERROR 1062: Duplicate entry '47947' for key 1
mysql> update Path set Path='BOGUS PATH' where PathID=47947;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql>
MySQL is telling you that there's a duplicate entry for the PRIMARY KEY which uniquely identifies each record in the Path table. If we look at the Path creation SQL in /etc/bacula/make_mysql_tables, we can see what the structure is:
CREATE TABLE Path ( PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Path BLOB NOT NULL, PRIMARY KEY(PathId), INDEX (Path(50)) );
Since the PRIMARY KEY is solely on an AUTO_INCREMENT field, I don't see how the INDEX on Path has anything to do with anything, and I don't see how you could ever have a duplicate entry in that field, unless your table is fscked.
The index on Path should have no bearing whatsoever on the PRIMARY KEY, and hence the uniqueness of each row.
In fact, the index on the Path and Name fields has no behavioral bearing on anything if I understand MySQL correctly... It only serves to speed up queries and doesn't functionally change anything.
I think Kern is right in that your database is fscked.
Kern, could you try and elaborate on this statement... I'm really having trouble understanding what you mean:
It looks to me like MySQL 4.0 has changed the way they deal with indexing and keys since version 3.23.51. It appears as if they are using *only* the index to decide if the key is unique or not. The index was setup on the first 50 characters of the path. Previously, the index was used for indexing only and not to determine uniqueness.
I don't understand how INDEX (Path(50)) has any bearing on PRIMARY KEY(PathId), as we have 2 discrete indexes on the Path table, one for the PathId field which is necessary for the PRIMARY KEY, and another on Path... They're not really related at all, and the index on Path has no uniqueness constraints.
Sorry for the length of this email, I just really want to understand what's going on with the table structure. I've been pondering and testing these statements for 2 days now on MySQL 4.1, and I can't wrap my head around what you guys mean.
Regards, -- Jeff McCune OSU Department of Mathematics System Support (614) 292-4962 gpg --keyserver pgp.mit.edu --recv-key BAF3211A
------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
