How to reset $last_read_uid value (4305736)

Customer uses an SQL table in SQL Server as log source. He has truncated the log table in SQL Server and he restarted uid column from 1. Now, SSB can not fetch records from table due to value of $last_read_uid variable.

How can we reset $last_read_uid value of SQL sources?

We have tried unchecking and checking again "Read old records" box but it did not work.

Syslog-ng / ssb stores the latest read record's id in its persist file, like it stores for text file sources what was the last line it read from it.

When syslog-ng starts two things could happen based on the read old records setting:

• If it is checked than read the last_read_uid from persist file and read every record from the sql table where the uid is greater than the last_read_uid.
  Then starts to poll the db and update the last_read_uid with the latest greatest uid what it read from the db.

• If it is not checked then first it runs a select max(uid) sql query for the table and update the last read uid with this value and start to poll the db from this point on.

That's why we have to reset this value manually in the persist file.

Note before you'd start:

This operation needs the syslog-ng service to be stopped while you manipulate the persist file!
We can minimize the downtime with using a quite long "oneliner", but keep in mind it still need to be stopped!

Prequisite:

Please check at the source definition if the "Read old records" option is checked. Otherwise even if we reset the number to 0, the syslog-ng service at start will gather the latest number (with a SELECT MAX(uid) FROM table; query) from that sql table, and start to query the newer records only.

The resetting operation itself:

Please log in to the core shell. From this point on everything we do should happen there.
First you have to found out what is your source's part in the persist file. Dump the persist file with the
/opt/syslog-ng/bin/persist-tool dump /opt/syslog-ng/var/syslog-ng.persist
command.
In my case the output was like this:
(core/master/ssb)root@ssb2:~# /opt/syslog-ng/bin/persist-tool dump /opt/syslog-ng/var/syslog-ng.persist
hostid = { "value": "2E FA 72 CE" }

pgsql,10.21.1.1,5432,sqllog,logs = { "version": 0, "big_endian": false, "last_read_id": "2" }

run_id = { "value": "06 00 00 00" }

In my case the sqlsource's name was psql, the host ip was 10.21.1.1, etc. So that is the line what I need to feed back to persist-tool, except I have to replace the last_read_id to 0.

To manipulate the persist file, you MUST stop the syslog-ng service!

To reduce downtime I used this long compound command in the shell:
systemctl stop syslog-ng.service && cp -a /opt/syslog-ng/var{,.bkp01} && echo 'pgsql,10.21.1.1,5432,sqllog,logs = { "version": 0, "big_endian": false, "last_read_id": "0" }' | /opt/syslog-ng/bin/persist-tool add -o /opt/syslog-ng/var - && systemctl start syslog-ng.service && echo Done.

So my terminal was look like this:
(core/master/ssb)root@ssb2:~# systemctl stop syslog-ng.service && cp -a /opt/syslog-ng/var{,.bkp01} && echo 'pgsql,10.21.1.1,5432,sqllog,logs = { "version": 0, "big_endian": false, "last_read_id": "0" }' | /opt/syslog-ng/bin/persist-tool add -o /opt/syslog-ng/var - && systemctl start syslog-ng.service && echo Done.
Escape character is '^d'
pgsql,10.21.1.1,5432,sqllog,logs   OK
Done.

In your case, the echo command MUST be adjusted to your sql source's data. You can use the very same line what persist-tool's dump gave you, except do not forget to replace the uid from that stored number to 0.

In case anything would go wrong, I included a cp -a ... command into that line, so it will make a copy of /opt/syslog-ng/var into /opt/syslog-ng/var.bkp01
So, if anything problem happens, as a rollback procedure you can copy back the old persist file and start the service manually.