Quoting Kern Sibbald <[EMAIL PROTECTED]>:
On Thursday 09 February 2006 21:07, Aleksandar Milivojevic wrote:
This is about one problem I have, and there's also a patch attached
which might be good to incorporate into future version of Bacula.
I'm in the middle of migrating Bacula database from PostgreSQL to
MySQL, and got into kind of trouble because of different constraints on
the columns in those two databases.
The job table has columns poolid and filesetid defined as NOT NULL in
MySQL, but there is no such constraint in PostgreSQL. Also, Bacula
utilizes foreign keys with MySQL, but not with PostgreSQL. Simmilary,
sqlite and sqlite3 backends also utilize foreign keys. While I was
running on PostgreSQL, I got some entries in job table with those
columns set to NULL. Of course, migration is failing on those.
Yes, the creation of the tables is very database dependent and was not
designed for portability. In hind-sight, one could probably make them much
more portable.
Foreign keys were initially used in PostgreSQL, but they slowed it down
considerably -- by a factor of 2-10 if I remember right ! As a consequence,
they were removed since they are not used (see below).
Hmmm... I'm kind of surprised there was such a huge performance
penalty (and much bigger then in MySQL/InnoDB). Anyhow, if they are
not used, dropping them from MySQL and/or SQLite should speed up those
two a bit as well...
The jobs in question seems to be either restores or failed jobs:
bacula=# select jobid, type, jobstatus from job where poolid is null or
filesetid is null;
jobid | type | jobstatus
-------+------+-----------
1040 | R | T
373 | R | T
98 | B | C
97 | B | R
99 | B | C
100 | B | C
101 | B | C
1146 | B | R
(8 rows)
Two 'R' jobs are also failed/canceled jobs (for whatever reason, Bacula
never marked them as such).
Should existance of these rows in database be considered bug in Bacula?
I assume that you are asking if the existence of NULLs in certain columns is
considered a bug. The reason I used NOT NULL was to avoid a lot of
unnecessary programming (testing each value for NULL prior to accessing it).
As a consequence, in most references in Bacula to values in the tables does
not check for NULL (within Bacula a zero value is equivalent for all
non-character fields). If an integer value is returned as NULL and Bacula
references that value, then it will most likely segfault.
Hmmm... Then I guess those two columns should be declared as NOT NULL
in PosgreSQL too (plus some in other tables). They are defined as NOT
NULL in MySQL and SQLite versions anyhow. Unless NOT NULL was dropped
from PostgreSQL version for some reason in the past?
Now, I guess I could simply delete those, and also all rows from other
tables that reference these rows (file, jobmedia, basefiles, and
unsavedfiles). My wild guess is that if I used MySQL initially, those
rows wouldn't exist anyhow (inserts would fail). Anyhow, looking at
the file table, there's about 32,000 rows that I'll need to delete...
Hopefully I'm not going to nuke anything too usefull...
Well, I have a hard time imagining that there are 32,000 bad rows,
and suspect
that if you delete them you will indeed be "nuking" something useful. A much
more conservative approach would be to replace any NULL values by 0 (zero).
Ah, I already nuked them. They belonged to failed jobs anyhow...
However, regardless of this problem I have, it might be good move to
utilize same features on all database backends (as long as they support
them), which would basically mean adding foreign key constrains to
PostgreSQL data definitions (yeah, it might slow database inserts a
bit).
I am not much in favor of the above suggestion, unless all databases that
Bacula supports (and future ones as well) have the same features. The
current Bacula code (everywhere including the database) with only a few
exceptions has been written to be the least common denominator. Over many
years of programming, I have found this to be the best approach. One rarely
experiences any performance penalties in adopting this philosophy, while the
reliability and portability are significantly improved.
Then I guess dropping foreign keys completely (from MySQL and SQLite)
would be a way to go (although, personally, I'd rather vote for
referential integrity over raw performance). Foreign keys are ignored
by MySQL if MyISAM tables are used (defualt) anyhow, they work only for
MySQL InnoDB tables. So not even all MySQL sites will have those
consistently.
By the way, now that I am working on version 1.39, I would like to integrate
the Python patch that someone sent me some time ago. I think it was you who
sent me the patch. In preparing to go on vacation, I saved all the patches I
have received, and have integrated them all. Unfortunately, I seem to have
saved the wrong Python patch (for detecting the installation directories).
If you are the one who sent me the patch, could you please resend it by
directly to me at "[EMAIL PROTECTED]". Sorry for the inconvenience, and
thanks.
I'm affraid it wasn't me...
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
