Re: [Bacula-devel] Database problem with development tree and mysql5.0.21

[Kern S. <ke...@si...>]

On Monday 12 June 2006 05:47, Robert Nelson wrote:
> Apparently this is caused by a difference in the default sql_mode of MySQL
> between Unix versions of MySQL and the Windows version.
>
> On Windows the sql_mode defaults to
> "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".  This can
> be achieved on Unix using the --sql-mode switch when starting MySQL.
>
> Either the tables should be fixed to allow NULL values where appropriate or
> the INSERT statements should be fixed to supply the correct value.  The
> effected fields I've found so far are listed below.  I suspect there may be
> others.

There are certain values that are not filled in when some records are created 
because the information is not available.  These values should not in general 
be NULL, since for performance reasons, Bacula does not always test for NULL. 
As a consequence, the no value fields are normally filled with zero.  The 
syntax for MySQL at least through version 4 was such that "NOT NULL" caused 
some value to be added as a default, so neither MySQL nor Bacula had a 
problem.

Now, if as you note, it is possible to modify how MySQL behaves (in this case 
more toward the SQL standard if I am not mistaken) we need to modify the NOT 
NULL to be some default such as DEFAULT 0 (the exact correction may depend on 
the field type), or we can simply modify the code to supply a zero value on 
the first insert.  As a first step, I probably prefer to correct the schema 
as doing so you are sure that the existing code will work.  If we modify the 
code (also a possibility), it is very easy to miss one place in some obscure 
SQL statement and at some later time, it will die.   Possibly a combination 
of the two would be the most appropriate ...

>
> Job
>     ClientId
>     StartTime
>     EndTime
>     JobBytes    (Just make the default 0 instead of NULL)
>     PoolId
>     FileSetId
>
> Media
>     FirstWritten
>     LastWritten
>     LabelDate
>     InitialWrite
>
>
>
>   _____
>
> From: bac...@li...
> [mailto:bac...@li...] On Behalf Of Robert
> Nelson
> Sent: Sunday, June 11, 2006 3:36 PM
> To: bac...@li...
> Subject: [Bacula-devel] Database problem with development tree and
> mysql5.0.21
>
>
> While testing the Windows port of the director I encountered the following
> error labeling a new volume:
> Connecting to Storage daemon TapeDrive4 at TestSrvr:9103 ...
>
> Sending label command for Volume "Full-0001" Slot 0 ...
>
> Invalid slot=0 defined, cannot autoload Volume.
>
> 3301 Issuing autochanger "loaded drive 0" command.
>
> 3302 Autochanger "loaded drive 0", result is Slot 1.
>
> 3000 OK label. Volume=Full-0001 Device="DLT4000" (Tape3)
>
> sql_create.c:473 Create DB Media record INSERT INTO Media
> (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,VolCapacityBytes,Recyc
>l
> e,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,VolStatus,Slot,VolByte
>s
> ,InChanger,VolReadTime,VolWriteTime,VolParts,EndFile,EndBlock,LabelType,Sto
>r ageId,DeviceId,LocationId) VALUES
> ('Full-0001','DLT-IV',0,1,0,0,1,31536000,0,0,0,'Append',0,1,1,0,0,0,0,0,0,5
>, 0,0) failed. ERR=Field 'FirstWritten' doesn't have a default value
>
> Do not forget to mount the drive!!!
>
> 11-Jun 15:04 TestSrvr-dir: *Console*.2006-06-11_15.03.27 Fatal error:
> sql_create.c:471 sql_create.c:471 insert INSERT INTO Media
> (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,VolCapacityBytes,Recyc
>l
> e,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,VolStatus,Slot,VolByte
>s
> ,InChanger,VolReadTime,VolWriteTime,VolParts,EndFile,EndBlock,LabelType,Sto
>r ageId,DeviceId,LocationId) VALUES
> ('Full-0001','DLT-IV',0,1,0,0,1,31536000,0,0,0,'Append',0,1,1,0,0,0,0,0,0,5
>, 0,0) failed:
>
> Field 'FirstWritten' doesn't have a default value

-- 
Best regards,

Kern

  (">
  /\
  V_V