problem with commiting transactions in SQL backend after upgrade to 3.6 #469
Comments
|
Hi, there were some refactors and fixes -and based on your riport, a new bug - around transactions in 3.6.2. |
|
Hello, Indeed, there is a bug in transaction handling. Here is a workaround: use retry_sql_inserts(2) because the second retry will call a commit and that will fix the problem. |
|
oh thanks for the hint with the retry_sql_inserts(2), i'll try that. |
|
i also wanted to mention, that together with those transaction problems came memory leaks. |
|
oops, looks like the "retry_sql_inserts(2)" really works. i was using value of "1", because i did not needed any more retries, as it was obvious there is error in data. However i have no problem with using value "2". I'll set this on other servers and will let know if it looks ok. |
|
Hello, i've updated config on my servers However it vould be fine if syslog handles transactions correctly even for "retry_sql_inserts(1)". Many thanks |
When an insert failed(after retries), syslog-ng didn't drop the connection
and thus an invalid transaction was kept in open state. When a new message
arrived, syslog-ng wanted to append that to the invalid transaction,
which caused failure and the message was dropped.
The problem occured when retries was set to 1.
if (self->failed_message_counter < self->num_retries - 1) // if (0 < 0)
{
// retry AND return false, which means to the caller
// that the connection should be suspended
}
else
{
// drop msg AND return true, which means to the caller
// that connection should be kept open
}
This is why a simple workaround (setting reties to 2) could solve this problem.
fixes syslog-ng#469
Signed-off-by: Laszlo Budai <Laszlo.Budai@balabit.com>
|
PR #491 should solve this. |
When an insert failed(after retries), syslog-ng didn't drop the connection
and thus an invalid transaction was kept in open state. When a new message
arrived, syslog-ng wanted to append that to the invalid transaction,
which caused failure and the message was dropped.
The problem occured when retries was set to 1.
if (self->failed_message_counter < self->num_retries - 1) // if (0 < 0)
{
// retry AND return false, which means to the caller
// that the connection should be suspended
}
else
{
// drop msg AND return true, which means to the caller
// that connection should be kept open
}
This is why a simple workaround (setting reties to 2) could solve this problem.
fixes syslog-ng#469
Signed-off-by: Laszlo Budai <Laszlo.Budai@balabit.com>
When an insert failed(after retries), syslog-ng didn't drop the connection
and thus an invalid transaction was kept in open state. When a new message
arrived, syslog-ng wanted to append that to the invalid transaction,
which caused failure and the message was dropped.
The problem occured when retries was set to 1.
if (self->failed_message_counter < self->num_retries - 1) // if (0 < 0)
{
// retry AND return false, which means to the caller
// that the connection should be suspended
}
else
{
// drop msg AND return true, which means to the caller
// that connection should be kept open
}
This is why a simple workaround (setting reties to 2) could solve this problem.
fixes syslog-ng#469
Signed-off-by: Laszlo Budai <Laszlo.Budai@balabit.com>
Hello,
i'm storing some parsed messages from maillog into postgres database using "sql" backend.
For better performance, tha backend has set these flags:
"
flags(dont-create-tables,explicit-commits)
flush_lines(20)
retry_sql_inserts(1)
"
Especially interesting is "explicit-commits".
From time to time, there are some mangled messages which are rejected by SQL server due to some invalid data in string.
While on 3.4 version, after such error, the message was dropped and everything was fine. Next messages were correctly inserted into DB.
However after upgrade to version 3.6 (precisely 3.6.2) there seems to be a problem with closing DB transaction after insert error.
If some insert error appear (due to incorrect data, or whatever), actual message is dropped, but next messages are not inserted as well.
All messages after the error are rejected by Postgres DB due to:
"
syslog-ng[21099]: Error running SQL query; type='pgsql', host='localhost', port='5432', user='syslog', database='lbstat', error='ERROR: current transaction is aborted, commands ignored until end of transaction block
"
All the messages going into that particular destination are rejected by DB backend and than dropped by syslog.
There seem to be some problem, that syslo does not correctly close the transaction after the error.
And DB refuse to execute any other commands in that transaction. After syslog-ng is reloaded, db connection is re-opened and works fine - until another insert error (due to invalid data). Then all new messages are dropped again.
When i disable the flag "explicit-commits", than eveything works as expected (as there are no transactions).
Of course i may overlooked someting, or there may be some change in 3.6, which i failed to notice.
But i'm pretty sure that my setup was just fine on 3.4 and and the problem started after upgrade to 3.6.
A can reproduce the problem on my virtual testing environment, so i can provide any logs/dumps/debugs.
Have anyone any similar problem or a idea what else to check?
Many thanks in advance
Tomas Novosad
The text was updated successfully, but these errors were encountered: