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). > > 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. > When using SQLite or MySQL (if InnoDB engine is used), inserting > those rows into database would fail anynow (as well as thousands of > other rows in file table that depend on them). > > 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). > > 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. > I've included a patch that does this. The patch might look > bigger than it really is, since I needed to reorder table creation. > Basically, all it does is adding foreign keys to the places where they > are defined in other backends (used MySQL backend as reference). I > haven't made an update script, since updating PostgreSQL tables to > start using foreign keys might require deletion of live data from > database (I'll leave it to someone with more internal knowledge of > Bacula). I'll be happy to look at your patch, but if it adds foreign keys to the PostgreSQL tables, it is unlikely I will want to accept it due to the huge performance penalty it imposes for zero gain (foreign keys though referenced are not used in Bacula). > > When we are at patching... How about adding ON DELETE CASCADE to table > definitions (wherever foreign keys are used)? This would make some > stuff much easier (for example, the task that is now in front of me) if > database backend supports foreign keys (sqlite and postgresql do, mysql > depending on version and storage engine used). Again I am not very much in favor of such an idea since it imposes a serious performance penalty AND it increases the complexity of the Bacula code. The increased Bacula complexity occurs because Bacula will still be required to implement its own "on delete cascade" as it currently does so that it works correctly with databases that do not have that feature. 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. Regards, Kern ------------------------------------------------------- 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
