Fatal error sql_create.c

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Fatal error sql_create.c

Petar Kozić
Hi,
Can someone tell me why I get this error:

Fatal error: sql_create.c:833 Fill File table Query failed: INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) SELECT batch.FileIndex, batch.JobId, Path.PathId, Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name = Filename.Name): ERR=Lock wait timeout exceeded; try restarting transaction 

This error appear only when I doing Full backup at Sunday on my about 10-12 linux instances.

Petar Kozić
System Administrator


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Josip Deanovic
On Sunday 2017-04-09 02:49:41 Petar Kozić wrote:

> Hi,
> Can someone tell me why I get this error:
>
> Fatal error: sql_create.c:833 Fill File table Query failed: INSERT INTO
> File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) SELECT
> batch.FileIndex, batch.JobId, Path.PathId,
> Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch
> JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name =
> Filename.Name): ERR=Lock wait timeout exceeded; try restarting
> transaction
>
> This error appear only when I doing Full backup at Sunday on my about
> 10-12 linux instances.
> *—*
>
> *Petar Kozić*
> System Administrator


Check the logs of your database server, check if the database
has enough space on the file system.

Run full backup manually and observe what is going on the backup
and the database server.

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Petar Kozić
This happend only on Sunday when Bacula run scheduled full backup on about 12 instances. Ofcorse, I have different run time in schedule, but because some instances have lot data, in some time 2-3 instances works in same time.

I have over 70GB free space on mysql server and thats not problem. I’m also increase innodb_timeout from 50 to 100 last week when I have same problem.

I have enabled batch insert, maybe thats problem or I must to increase inndob_timeout more.

Thanks.


Petar Kozić
System Administrator


On April 9, 2017 at 12:13:28 PM, Josip Deanovic ([hidden email]) wrote:

On Sunday 2017-04-09 02:49:41 Petar Kozić wrote:

> Hi,
> Can someone tell me why I get this error:
>
> Fatal error: sql_create.c:833 Fill File table Query failed: INSERT INTO
> File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) SELECT
> batch.FileIndex, batch.JobId, Path.PathId,
> Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch
> JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name =
> Filename.Name): ERR=Lock wait timeout exceeded; try restarting
> transaction
>
> This error appear only when I doing Full backup at Sunday on my about
> 10-12 linux instances.
> *—*
>
> *Petar Kozić*
> System Administrator


Check the logs of your database server, check if the database
has enough space on the file system.

Run full backup manually and observe what is going on the backup
and the database server.

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Gary R. Schmidt-3
On 09/04/2017 22:50, Petar Kozić wrote:

> This happend only on Sunday when Bacula run scheduled full backup on
> about 12 instances. Ofcorse, I have different run time in schedule, but
> because some instances have lot data, in some time 2-3 instances works
> in same time.
>
> I have over 70GB free space on mysql server and thats not problem. I’m
> also increase innodb_timeout from 50 to 100 last week when I have same
> problem.
>
> I have enabled batch insert, maybe thats problem or I must to increase
> inndob_timeout more.
>

How much free *temporary* filespace is there?

MySQL needs lots of /tmp, or however you have configured it.

You might want to run one of the various MySQL tuning tools out there, I
likr http://mysqltuner.pl.

        Cheers,
                Gary B-)

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Petar Kozić
/tmp folder is on root where I have 70 GB free.

On Apr 9, 2017 3:18 PM, "Gary R. Schmidt" <[hidden email]> wrote:
On 09/04/2017 22:50, Petar Kozić wrote:
> This happend only on Sunday when Bacula run scheduled full backup on
> about 12 instances. Ofcorse, I have different run time in schedule, but
> because some instances have lot data, in some time 2-3 instances works
> in same time.
>
> I have over 70GB free space on mysql server and thats not problem. I’m
> also increase innodb_timeout from 50 to 100 last week when I have same
> problem.
>
> I have enabled batch insert, maybe thats problem or I must to increase
> inndob_timeout more.
>

How much free *temporary* filespace is there?

MySQL needs lots of /tmp, or however you have configured it.

You might want to run one of the various MySQL tuning tools out there, I
likr http://mysqltuner.pl.

        Cheers,
                Gary    B-)

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Josip Deanovic
In reply to this post by Petar Kozić
On Sunday 2017-04-09 05:50:31 Petar Kozić wrote:

> This happend only on Sunday when Bacula run scheduled full backup on
> about 12 instances. Ofcorse, I have different run time in schedule, but
> because some instances have lot data, in some time 2-3 instances works
> in same time.
>
> I have over 70GB free space on mysql server and thats not problem. I’m
> also increase innodb_timeout from 50 to 100 last week when I have same
> problem.
>
> I have enabled batch insert, maybe thats problem or I must to increase
> inndob_timeout more.

Are you using spooling?
If yes, is your spool directory on the same file system as your database
data?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Petar Kozić
Yes, I am using spooling, but I don't have define spooling directory. Director and mysql is on same server. Storage is on same but on different mount point LVM.

On Apr 9, 2017 6:23 PM, "Josip Deanovic" <[hidden email]> wrote:
On Sunday 2017-04-09 05:50:31 Petar Kozić wrote:
> This happend only on Sunday when Bacula run scheduled full backup on
> about 12 instances. Ofcorse, I have different run time in schedule, but
> because some instances have lot data, in some time 2-3 instances works
> in same time.
>
> I have over 70GB free space on mysql server and thats not problem. I’m
> also increase innodb_timeout from 50 to 100 last week when I have same
> problem.
>
> I have enabled batch insert, maybe thats problem or I must to increase
> inndob_timeout more.

Are you using spooling?
If yes, is your spool directory on the same file system as your database
data?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Heitor Faria
In reply to this post by Josip Deanovic
> On Sunday 2017-04-09 02:49:41 Petar Kozić wrote:
>> Hi,
>> Can someone tell me why I get this error:
>>
>> Fatal error: sql_create.c:833 Fill File table Query failed: INSERT INTO
>> File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) SELECT
>> batch.FileIndex, batch.JobId, Path.PathId,
>> Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch
>> JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name =
>> Filename.Name): ERR=Lock wait timeout exceeded; try restarting
>> transaction

Hello, Peter: I suspect this error was here before on this list. There is a workaround which is increasing database lock wait timeout value, and probably deploy some configuration tuning for Bacula workload in order to prevent transactions taking that long.

>> This error appear only when I doing Full backup at Sunday on my about
>> 10-12 linux instances.
>> *—*
>>
>> *Petar Kozić*
>> System Administrator

Regards,
--
===========================================================================
Heitor Medrado de Faria | Bacula do Brasil
• Não seja tarifado pelo tamanho dos seus backups, conheça o Bacula Enterprise: http://www.bacula.com.br/enterprise/ 
• Ministro treinamento e implementação in-company do Bacula Community: http://www.bacula.com.br/in-company/ 
(61) 98268-4220 | www.bacula.com.br
============================================================================
Indicamos também as capacitações complementares:
• Shell básico e Programação em Shell com Julio Neves.
• Zabbix com Adail Host.
============================================================================

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Petar Kozić
Thank you very much, I also think it is innodb_timeout is relevant for this problem, because I was get this error last week and when I increase timeout from 50 to 100 this problem was disappear. After few days I was restart mysql, but I still to add this statement in my.cnf and timeout was revert to 50. 

Last night I was increase timeout to 150 and set in my.cnf

I will inform is the problem solved.

Thank you.


Petar Kozić
System Administrator

On April 9, 2017 at 7:55:19 PM, Heitor Faria ([hidden email]) wrote:

> On Sunday 2017-04-09 02:49:41 Petar Kozić wrote:

>> Hi,
>> Can someone tell me why I get this error:
>>
>> Fatal error: sql_create.c:833 Fill File table Query failed: INSERT INTO
>> File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) SELECT
>> batch.FileIndex, batch.JobId, Path.PathId,
>> Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq FROM batch
>> JOIN Path ON (batch.Path = Path.Path) JOIN Filename ON (batch.Name =
>> Filename.Name): ERR=Lock wait timeout exceeded; try restarting
>> transaction

Hello, Peter: I suspect this error was here before on this list. There is a workaround which is increasing database lock wait timeout value, and probably deploy some configuration tuning for Bacula workload in order to prevent transactions taking that long.

>> This error appear only when I doing Full backup at Sunday on my about
>> 10-12 linux instances.
>> *—*
>>
>> *Petar Kozić*
>> System Administrator

Regards,
--
===========================================================================
Heitor Medrado de Faria | Bacula do Brasil
• Não seja tarifado pelo tamanho dos seus backups, conheça o Bacula Enterprise: http://www.bacula.com.br/enterprise/
• Ministro treinamento e implementação in-company do Bacula Community: http://www.bacula.com.br/in-company/
(61) 98268-4220 | www.bacula.com.br
============================================================================
Indicamos também as capacitações complementares:
• Shell básico e Programação em Shell com Julio Neves.
• Zabbix com Adail Host.
============================================================================

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Petar Kozić
@Josip Deanovic
Are you using spooling?
If yes, is your spool directory on the same file system as your database
data?

No, I don’t spooling Data, only Attributes

My config:
Spool Data = no
Spool Attributes = yes


I was increase innodb_timewait_lockout = 150 but error appears again.

Some ideas ?




Petar Kozić
System Administrator


On April 10, 2017 at 9:06:36 AM, Petar Kozić ([hidden email]) wrote:

Are you using spooling?
If yes, is your spool directory on the same file system as your database
data?

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Josip Deanovic
On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:

> @Josip Deanovic
> Are you using spooling?
> If yes, is your spool directory on the same file system as your database
> data?
>
> No, I don’t spooling Data, only Attributes
>
> My config:
> Spool Data = no
> Spool Attributes = yes
>
>
> I was increase innodb_timewait_lockout = 150 but error appears again.
>
> Some ideas ?

Do you have anything useful in your mysql logs?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Petar Kozić
Yes, I have.

I was upload on pasterbin:

Log is contionous with same error.



Petar Kozić
System Administrator


On April 12, 2017 at 11:03:51 AM, Josip Deanovic ([hidden email]) wrote:

On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:

> @Josip Deanovic
> Are you using spooling?
> If yes, is your spool directory on the same file system as your database
> data?
>
> No, I don’t spooling Data, only Attributes
>
> My config:
> Spool Data = no
> Spool Attributes = yes
>
>
> I was increase innodb_timewait_lockout = 150 but error appears again.
>
> Some ideas ?

Do you have anything useful in your mysql logs?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Ana Emília M. Arruda
Hi Petar,

If you are using spool attributes, you need enough space in the SD working directory to store them before SD send attributes to Director.

Could you please check if you have enough space in the SD working directory?

Best regards,
Ana

On Wed, Apr 12, 2017 at 6:40 AM, Petar Kozić <[hidden email]> wrote:
Yes, I have.

I was upload on pasterbin:

Log is contionous with same error.



Petar Kozić
System Administrator


On April 12, 2017 at 11:03:51 AM, Josip Deanovic ([hidden email]) wrote:

On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:

> @Josip Deanovic
> Are you using spooling?
> If yes, is your spool directory on the same file system as your database
> data?
>
> No, I don’t spooling Data, only Attributes
>
> My config:
> Spool Data = no
> Spool Attributes = yes
>
>
> I was increase innodb_timewait_lockout = 150 but error appears again.
>
> Some ideas ?

Do you have anything useful in your mysql logs?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Petar Kozić
Thank you on your answer. Yes I have about 80GB free in drive where is SD working directory.



Petar Kozić
System Administrator



On April 13, 2017 at 9:18:39 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hi Petar,

If you are using spool attributes, you need enough space in the SD working directory to store them before SD send attributes to Director.

Could you please check if you have enough space in the SD working directory?

Best regards,
Ana

On Wed, Apr 12, 2017 at 6:40 AM, Petar Kozić <[hidden email]> wrote:
Yes, I have.

I was upload on pasterbin:

Log is contionous with same error.



Petar Kozić
System Administrator


On April 12, 2017 at 11:03:51 AM, Josip Deanovic ([hidden email]) wrote:

On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:
> @Josip Deanovic
> Are you using spooling?
> If yes, is your spool directory on the same file system as your database
> data?
>
> No, I don’t spooling Data, only Attributes
>
> My config:
> Spool Data = no
> Spool Attributes = yes
>
>
> I was increase innodb_timewait_lockout = 150 but error appears again.
>
> Some ideas ?

Do you have anything useful in your mysql logs?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users



------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Ana Emília M. Arruda
Hello Petar,

Could you please let us know which Bacula/MySQL Database version are you using?

Maybe it would be a good idea to have a look at "innodb_autoinc_lock_modeconfiguration.​

​Best regards,

Ana​



On Thu, Apr 13, 2017 at 4:23 PM, Petar Kozić <[hidden email]> wrote:
Thank you on your answer. Yes I have about 80GB free in drive where is SD working directory.



Petar Kozić
System Administrator



On April 13, 2017 at 9:18:39 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hi Petar,

If you are using spool attributes, you need enough space in the SD working directory to store them before SD send attributes to Director.

Could you please check if you have enough space in the SD working directory?

Best regards,
Ana

On Wed, Apr 12, 2017 at 6:40 AM, Petar Kozić <[hidden email]> wrote:
Yes, I have.

I was upload on pasterbin:

Log is contionous with same error.



Petar Kozić
System Administrator


On April 12, 2017 at 11:03:51 AM, Josip Deanovic ([hidden email]) wrote:

On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:
> @Josip Deanovic
> Are you using spooling?
> If yes, is your spool directory on the same file system as your database
> data?
>
> No, I don’t spooling Data, only Attributes
>
> My config:
> Spool Data = no
> Spool Attributes = yes
>
>
> I was increase innodb_timewait_lockout = 150 but error appears again.
>
> Some ideas ?

Do you have anything useful in your mysql logs?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users




------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Petar Kozić
I using MariaDB 10.1. 
Which value of innodb_autoinc_lock_mode is best for Bacula, do you know ?




Petar Kozić
System Administrator



On April 13, 2017 at 10:04:23 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hello Petar,

Could you please let us know which Bacula/MySQL Database version are you using?

Maybe it would be a good idea to have a look at "innodb_autoinc_lock_modeconfiguration.​

​Best regards,

Ana​



On Thu, Apr 13, 2017 at 4:23 PM, Petar Kozić <[hidden email]> wrote:
Thank you on your answer. Yes I have about 80GB free in drive where is SD working directory.



Petar Kozić
System Administrator



On April 13, 2017 at 9:18:39 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hi Petar,

If you are using spool attributes, you need enough space in the SD working directory to store them before SD send attributes to Director.

Could you please check if you have enough space in the SD working directory?

Best regards,
Ana

On Wed, Apr 12, 2017 at 6:40 AM, Petar Kozić <[hidden email]> wrote:
Yes, I have.

I was upload on pasterbin:

Log is contionous with same error.



Petar Kozić
System Administrator


On April 12, 2017 at 11:03:51 AM, Josip Deanovic ([hidden email]) wrote:

On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:
> @Josip Deanovic
> Are you using spooling?
> If yes, is your spool directory on the same file system as your database
> data?
>
> No, I don’t spooling Data, only Attributes
>
> My config:
> Spool Data = no
> Spool Attributes = yes
>
>
> I was increase innodb_timewait_lockout = 150 but error appears again.
>
> Some ideas ?

Do you have anything useful in your mysql logs?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users




------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Ana Emília M. Arruda
Hello Petar,


Maybe the script for MySQL tunning can help with MariaDB too: https://github.com/major/MySQLTuner-perl

Best regards,

Ana

On Thu, Apr 13, 2017 at 10:09 PM, Petar Kozić <[hidden email]> wrote:
I using MariaDB 10.1. 
Which value of innodb_autoinc_lock_mode is best for Bacula, do you know ?




Petar Kozić
System Administrator



On April 13, 2017 at 10:04:23 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hello Petar,

Could you please let us know which Bacula/MySQL Database version are you using?

Maybe it would be a good idea to have a look at "innodb_autoinc_lock_modeconfiguration.​

​Best regards,

Ana​



On Thu, Apr 13, 2017 at 4:23 PM, Petar Kozić <[hidden email]> wrote:
Thank you on your answer. Yes I have about 80GB free in drive where is SD working directory.



Petar Kozić
System Administrator



On April 13, 2017 at 9:18:39 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hi Petar,

If you are using spool attributes, you need enough space in the SD working directory to store them before SD send attributes to Director.

Could you please check if you have enough space in the SD working directory?

Best regards,
Ana

On Wed, Apr 12, 2017 at 6:40 AM, Petar Kozić <[hidden email]> wrote:
Yes, I have.

I was upload on pasterbin:

Log is contionous with same error.



Petar Kozić
System Administrator


On April 12, 2017 at 11:03:51 AM, Josip Deanovic ([hidden email]) wrote:

On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:
> @Josip Deanovic
> Are you using spooling?
> If yes, is your spool directory on the same file system as your database
> data?
>
> No, I don’t spooling Data, only Attributes
>
> My config:
> Spool Data = no
> Spool Attributes = yes
>
>
> I was increase innodb_timewait_lockout = 150 but error appears again.
>
> Some ideas ?

Do you have anything useful in your mysql logs?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users





------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Ana Emília M. Arruda
Hello Petar,

I'm sorry I didn't give you details about the best value for ' innodb_autoinc_lock_mode'  to use with bacula catalog.

Having "innodb_autoinc_lock_mode=2" is the best option for performance of bulk INSERTs (you can see some INSERT...SELECT statements in the logs you sent here).

I am not a DBA, but I would recommend you to have this value set to 2 for better performance and avoid the locks/timeouts. Please be careful with this value if you use replication (quite sure you don't need/use this for bacula catalog).

The issue you are having is related to attribute insertion in catalog after job runs. Since you are reporting this when dealing with some heavy (maybe?) simultaneous full backup jobs, you will need to have your MariaDB well tuned to get this working.

I would also recommend you to have your files pruned regularly from catalog, so this file table doesn't grow too much in the case you deal with millions of files.

You told in one of your emails: "I have enabled batch insert".  It is strongly recommended to build bacula with batch insert enabled and this is the default in newer versions.

Phil Stracchino gave very good recommendations herehttps://www.mail-archive.com/bacula-users@.../msg61883.html

The user was having a similar issue, please find here the complete thread: https://www.mail-archive.com/bacula-users@.../msg61787.html

Hope this helps.

Best regards,
Ana

On Thu, Apr 13, 2017 at 10:45 PM, Ana Emília M. Arruda <[hidden email]> wrote:
Hello Petar,


Maybe the script for MySQL tunning can help with MariaDB too: https://github.com/major/MySQLTuner-perl

Best regards,

Ana

On Thu, Apr 13, 2017 at 10:09 PM, Petar Kozić <[hidden email]> wrote:
I using MariaDB 10.1. 
Which value of innodb_autoinc_lock_mode is best for Bacula, do you know ?




Petar Kozić
System Administrator



On April 13, 2017 at 10:04:23 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hello Petar,

Could you please let us know which Bacula/MySQL Database version are you using?

Maybe it would be a good idea to have a look at "innodb_autoinc_lock_modeconfiguration.​

​Best regards,

Ana​



On Thu, Apr 13, 2017 at 4:23 PM, Petar Kozić <[hidden email]> wrote:
Thank you on your answer. Yes I have about 80GB free in drive where is SD working directory.



Petar Kozić
System Administrator



On April 13, 2017 at 9:18:39 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hi Petar,

If you are using spool attributes, you need enough space in the SD working directory to store them before SD send attributes to Director.

Could you please check if you have enough space in the SD working directory?

Best regards,
Ana

On Wed, Apr 12, 2017 at 6:40 AM, Petar Kozić <[hidden email]> wrote:
Yes, I have.

I was upload on pasterbin:

Log is contionous with same error.



Petar Kozić
System Administrator


On April 12, 2017 at 11:03:51 AM, Josip Deanovic ([hidden email]) wrote:

On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:
> @Josip Deanovic
> Are you using spooling?
> If yes, is your spool directory on the same file system as your database
> data?
>
> No, I don’t spooling Data, only Attributes
>
> My config:
> Spool Data = no
> Spool Attributes = yes
>
>
> I was increase innodb_timewait_lockout = 150 but error appears again.
>
> Some ideas ?

Do you have anything useful in your mysql logs?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users






------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fatal error sql_create.c

Petar Kozić
Hello Ana,

Thank you very much on detail description. I will set innodb_autoinc_lock_mode=2 and I will test this.

I don’t use replication.

Yes, I was build bacula with batch-insert. Batch insert works, I can see that when bacula send attributes and I show all process in mysql.

Thank you very much.


Petar Kozić
System Administrator


On April 14, 2017 at 3:33:47 AM, Ana Emília M. Arruda ([hidden email]) wrote:

Hello Petar,

I'm sorry I didn't give you details about the best value for ' innodb_autoinc_lock_mode'  to use with bacula catalog.

Having "innodb_autoinc_lock_mode=2" is the best option for performance of bulk INSERTs (you can see some INSERT...SELECT statements in the logs you sent here).

I am not a DBA, but I would recommend you to have this value set to 2 for better performance and avoid the locks/timeouts. Please be careful with this value if you use replication (quite sure you don't need/use this for bacula catalog).

The issue you are having is related to attribute insertion in catalog after job runs. Since you are reporting this when dealing with some heavy (maybe?) simultaneous full backup jobs, you will need to have your MariaDB well tuned to get this working.

I would also recommend you to have your files pruned regularly from catalog, so this file table doesn't grow too much in the case you deal with millions of files.

You told in one of your emails: "I have enabled batch insert".  It is strongly recommended to build bacula with batch insert enabled and this is the default in newer versions.

Phil Stracchino gave very good recommendations herehttps://www.mail-archive.com/bacula-users@.../msg61883.html

The user was having a similar issue, please find here the complete thread: https://www.mail-archive.com/bacula-users@.../msg61787.html

Hope this helps.

Best regards,
Ana

On Thu, Apr 13, 2017 at 10:45 PM, Ana Emília M. Arruda <[hidden email]> wrote:
Hello Petar,


Maybe the script for MySQL tunning can help with MariaDB too: https://github.com/major/MySQLTuner-perl

Best regards,

Ana

On Thu, Apr 13, 2017 at 10:09 PM, Petar Kozić <[hidden email]> wrote:
I using MariaDB 10.1. 
Which value of innodb_autoinc_lock_mode is best for Bacula, do you know ?




Petar Kozić
System Administrator



On April 13, 2017 at 10:04:23 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hello Petar,

Could you please let us know which Bacula/MySQL Database version are you using?

Maybe it would be a good idea to have a look at "innodb_autoinc_lock_modeconfiguration.​

​Best regards,

Ana​



On Thu, Apr 13, 2017 at 4:23 PM, Petar Kozić <[hidden email]> wrote:
Thank you on your answer. Yes I have about 80GB free in drive where is SD working directory.



Petar Kozić
System Administrator



On April 13, 2017 at 9:18:39 PM, Ana Emília M. Arruda ([hidden email]) wrote:

Hi Petar,

If you are using spool attributes, you need enough space in the SD working directory to store them before SD send attributes to Director.

Could you please check if you have enough space in the SD working directory?

Best regards,
Ana

On Wed, Apr 12, 2017 at 6:40 AM, Petar Kozić <[hidden email]> wrote:
Yes, I have.

I was upload on pasterbin:

Log is contionous with same error.



Petar Kozić
System Administrator


On April 12, 2017 at 11:03:51 AM, Josip Deanovic ([hidden email]) wrote:

On Wednesday 2017-04-12 01:07:41 Petar Kozić wrote:
> @Josip Deanovic
> Are you using spooling?
> If yes, is your spool directory on the same file system as your database
> data?
>
> No, I don’t spooling Data, only Attributes
>
> My config:
> Spool Data = no
> Spool Attributes = yes
>
>
> I was increase innodb_timewait_lockout = 150 but error appears again.
>
> Some ideas ?

Do you have anything useful in your mysql logs?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users






------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Loading...