Re: database problem update after upgrade to 15.0.2

--===============1079944022593882839==
Content-Language: de-AT
Content-Type: multipart/alternative;
	boundary="_000_PAXPR09MB6529B81F0A1CE9E910D1648BEBEF2PAXPR09MB6529eurp_"

--_000_PAXPR09MB6529B81F0A1CE9E910D1648BEBEF2PAXPR09MB6529eurp_
Content-Type: text/plain; charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

Hello Marcin,

Thanks, this explains it pretty good.

In the meantime I created a new datebase using make_mysql_tables and made a=
 quick comparison of both schemas using mysqldump -d .

I found some more (hopefully) minor differences (order of columns, int(11) =
vs. int(10) unsigned, missing indexes).   Not sure if everything is related=
 to bacula update scripts because we run bacula quite a long time and did a=
 lot of bacula version updates, but also operating system and mysql/mariadb=
 upgrades.

Best regards
Ulrich



________________________________
Von: Marcin Haba <[email protected]>
Gesendet: Samstag, 18. Mai 2024 10:37
An: Ulrich Leodolter <[email protected]>
Cc: [email protected] <[email protected]>
Betreff: Re: [Bacula-devel] database problem update after upgrade to 15.0.2

Hello Ulrich,

I know this problem. I found and reported it a year ago. You can read about=
 it here:

https://gitlab.bacula.org/bacula-community-edition/bacula-community/-/issue=
s/2684

Best regards,
Marcin Haba (gani)

On Sat, 18 May 2024 at 10:18, Ulrich Leodolter <[email protected]<m=
ailto:[email protected]>> wrote:
hi,

after upgrading to verions 15.0.2 our nightly update stats job failed:

18-May 06:15 vlbackup.obvsg.at-dir JobId 0: Fatal error: bdb.h:146 bdb.h:14=
6 query INSERT INTO JobHisto (JobId, Job, Name, Type, Level,ClientId, JobSt=
atus,SchedTime, StartTime, EndTime, RealEndTime, RealStartTime, JobTDate,Vo=
lSessionId, VolSessionTime, JobFiles, JobBytes, ReadBytes,JobErrors, JobMis=
singFiles, PoolId, FileSetId, PriorJobId, PriorJob, PurgedFiles, HasBase, H=
asCache, Reviewed, Comment, FileTable, isVirtualFull,CompressRatio,Rate,Las=
tReadStorageId,WriteStorageId,LastReadDevice,WriteDevice,StatusInfo,Encrypt=
ed)SELECT JobId, Job, Name, Type, Level, ClientId, JobStatus,SchedTime, Sta=
rtTime, EndTime, RealEndTime, RealStartTime, JobTDate,VolSessionId, VolSess=
ionTime, JobFiles, JobBytes, ReadBytes,JobErrors, JobMissingFiles, PoolId, =
FileSetId, PriorJobId, PriorJob, PurgedFiles, HasBase, HasCache, Reviewed, =
Comment, FileTable, isVirtualFull,CompressRatio,Rate,LastReadStorageId,Writ=
eStorageId,LastReadDevice,WriteDevice,StatusInfo,Encrypted FROM Job WHERE J=
obStatus IN ('T','W','f
,'A','E')AND NOT EXISTS (SELECT JobHisto.JobId FROM JobHisto WHERE JobHisto=
.Jobid=3DJob.JobId)AND JobTDate < 1715746502  failed:
Unknown column 'FileTable' in 'field list'

after upgrading to version 15.0.2 i have updated the database using the upd=
ate_mysql_tables which is installed into scripts directory.

the source code of this script is bacula/src/cats/update_mysql_tables.in<ht=
tp://update_mysql_tables.in>,  but there are also scripts to upgrade betwee=
n specific versions in bacula/updatedb.

it seems these update scripts are not in sync because only  bacula/src/cats=
/update_mysql_tables.in<http://update_mysql_tables.in> contains the FileTab=
le update:

ALTER TABLE Job ADD COLUMN FileTable CHAR(20) default 'File';
ALTER TABLE JobHisto ADD COLUMN FileTable CHAR(20) default 'File';

there is no such alter FileTable in any of the bacula/updatedb scripts.

it seems it is not a good idea to use scripts from bacula/updatedb after ve=
rsion upgrade because of missing changes, is that correct?

i have now added the columns manually und update stats job runs with proble=
ms, but i need now the check the complete schema because i am unsure which =
scripts i used in the past.

Best regards
Ulrich
_______________________________________________
Bacula-devel mailing list
[email protected]<mailto:[email protected]=
t>
https://lists.sourceforge.net/lists/listinfo/bacula-devel


--

"Greater love hath no man than this, that a man lay down his life for his f=
riends." Jesus Christ

"Wi=EAkszej mi=B3o=B6ci nikt nie ma nad t=EA, jak gdy kto =BFycie swoje k=
=B3adzie za przyjaci=F3=B3 swoich." Jezus Chrystus

--_000_PAXPR09MB6529B81F0A1CE9E910D1648BEBEF2PAXPR09MB6529eurp_
Content-Type: text/html; charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
2">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
Hello Marcin,</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
Thanks, this explains it pretty good.</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
In the meantime I created a new datebase using make_mysql_tables and made a=
 quick comparison of both schemas using mysqldump -d .</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
I found some more (hopefully) minor differences (order of columns, int(11) =
vs. int(10) unsigned, missing indexes).&nbsp; &nbsp;Not sure if everything =
is related to bacula update scripts because we run bacula quite a long time=
 and did a lot of bacula version updates,
 but also operating system and mysql/mariadb upgrades.</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
Best regards</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
Ulrich</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
<br>
</div>
<div class=3D"elementToProof" style=3D"font-family: Aptos, Aptos_EmbeddedFo=
nt, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; c=
olor: rgb(0, 0, 0);">
<br>
</div>
<div id=3D"appendonsend"></div>
<hr style=3D"display:inline-block;width:98%" tabindex=3D"-1">
<div id=3D"divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" st=
yle=3D"font-size:11pt" color=3D"#000000"><b>Von:</b> Marcin Haba &lt;ganius=
[email protected]&gt;<br>
<b>Gesendet:</b> Samstag, 18. Mai 2024 10:37<br>
<b>An:</b> Ulrich Leodolter &lt;[email protected]&gt;<br>
<b>Cc:</b> [email protected] &lt;[email protected]=
forge.net&gt;<br>
<b>Betreff:</b> Re: [Bacula-devel] database problem update after upgrade to=
 15.0.2</font>
<div>&nbsp;</div>
</div>
<div>
<div dir=3D"ltr">Hello Ulrich,<br>
<br>
I know this problem. I found and reported it a year ago. You can read about=
 it here:<br>
<br>
<a href=3D"https://gitlab.bacula.org/bacula-community-edition/bacula-commun=
ity/-/issues/2684">https://gitlab.bacula.org/bacula-community-edition/bacul=
a-community/-/issues/2684</a><br>
<br>
Best regards,<br>
Marcin Haba (gani)<br>
</div>
<br>
<div class=3D"x_gmail_quote">
<div dir=3D"ltr" class=3D"x_gmail_attr">On Sat, 18 May 2024 at 10:18, Ulric=
h Leodolter &lt;<a href=3D"mailto:[email protected]">ulrich.leodolt=
[email protected]</a>&gt; wrote:<br>
</div>
<blockquote class=3D"x_gmail_quote" style=3D"margin:0px 0px 0px 0.8ex; bord=
er-left:1px solid rgb(204,204,204); padding-left:1ex">
<div class=3D"x_msg3723570689341726283">
<div dir=3D"ltr">
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cali=
bri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
hi,</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cali=
bri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cali=
bri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
after upgrading to verions 15.0.2 our nightly update stats job failed:</div=
>
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cali=
bri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">18-May 06:15 vlbackup.obvsg.at-dir JobId 0: Fat=
al error: bdb.h:146 bdb.h:146 query INSERT INTO JobHisto (JobId, Job, Name,=
 Type, Level,ClientId, JobStatus,SchedTime,
 StartTime, EndTime, RealEndTime, RealStartTime, JobTDate,VolSessionId, Vol=
SessionTime, JobFiles, JobBytes, ReadBytes,JobErrors, JobMissingFiles, Pool=
Id, FileSetId, PriorJobId, PriorJob, PurgedFiles, HasBase, HasCache, Review=
ed, Comment, FileTable, isVirtualFull,CompressRatio,Rate,LastReadStorageId,=
WriteStorageId,LastReadDevice,WriteDevice,StatusInfo,Encrypted)SELECT
 JobId, Job, Name, Type, Level, ClientId, JobStatus,SchedTime, StartTime, E=
ndTime, RealEndTime, RealStartTime, JobTDate,VolSessionId, VolSessionTime, =
JobFiles, JobBytes, ReadBytes,JobErrors, JobMissingFiles, PoolId, FileSetId=
, PriorJobId, PriorJob, PurgedFiles,
 HasBase, HasCache, Reviewed, Comment, FileTable, isVirtualFull,CompressRat=
io,Rate,LastReadStorageId,WriteStorageId,LastReadDevice,WriteDevice,StatusI=
nfo,Encrypted FROM Job WHERE JobStatus IN ('T','W','f</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">,'A','E')AND NOT EXISTS (SELECT JobHisto.JobId =
FROM JobHisto WHERE JobHisto.Jobid=3DJob.JobId)AND JobTDate &lt; 1715746502=
&nbsp; failed:</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">Unknown column 'FileTable' in 'field list'</spa=
n></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)"><br>
</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">after upgrading to version 15.0.2 i have update=
d the database using the update_mysql_tables which is installed into script=
s directory.</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)"><br>
</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">the source code of this script is bacula/src/ca=
ts/<a href=3D"http://update_mysql_tables.in" target=3D"_blank">update_mysql=
_tables.in</a>,&nbsp; but there are also scripts
 to upgrade between specific versions in bacula/updatedb.</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)"><br>
</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">it seems these update scripts are not in sync b=
ecause only &nbsp;bacula/src/cats/<a href=3D"http://update_mysql_tables.in"=
 target=3D"_blank">update_mysql_tables.in</a>
 contains the FileTable update:</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)"><br>
</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">ALTER TABLE Job ADD COLUMN FileTable CHAR(20) d=
efault 'File';<br>
ALTER TABLE JobHisto ADD COLUMN FileTable CHAR(20) default 'File';</span></=
div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)"><br>
</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">there is no such alter FileTable in any of the =
bacula/updatedb scripts.</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)"><br>
</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">it seems it is not a good idea to use scripts f=
rom bacula/updatedb after version upgrade because of missing changes, is th=
at correct?</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)"><br>
</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">i have now added the columns manually und updat=
e stats job runs with problems, but i need now the check the complete schem=
a because i am unsure which scripts
 i used in the past.</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)"><br>
</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">Best regards</span></div>
<div style=3D"font-size:14.6667px; color:rgb(0,0,0)"><span style=3D"backgro=
und-color:rgb(255,255,255)">Ulrich</span></div>
</div>
_______________________________________________<br>
Bacula-devel mailing list<br>
<a href=3D"mailto:[email protected]" target=3D"_blank">Bac=
[email protected]</a><br>
<a href=3D"https://lists.sourceforge.net/lists/listinfo/bacula-devel" rel=
=3D"noreferrer" target=3D"_blank">https://lists.sourceforge.net/lists/listi=
nfo/bacula-devel</a><br>
</div>
</blockquote>
</div>
<br clear=3D"all">
<div><br>
</div>
<span class=3D"x_gmail_signature_prefix">-- </span><br>
<div dir=3D"ltr" class=3D"x_gmail_signature">
<div dir=3D"ltr">
<pre>&quot;Greater love hath no man than this, that a man lay down his life=
 for his friends.&quot; Jesus Christ

&quot;Wi=EAkszej mi=B3o=B6ci nikt nie ma nad t=EA, jak gdy kto =BFycie swoj=
e k=B3adzie za przyjaci=F3=B3 swoich.&quot; Jezus Chrystus</pre>
</div>
</div>
</div>
</body>
</html>

--_000_PAXPR09MB6529B81F0A1CE9E910D1648BEBEF2PAXPR09MB6529eurp_--


--===============1079944022593882839==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline


--===============1079944022593882839==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
Bacula-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-devel

--===============1079944022593882839==--