commit with the oracle sql driver
So, extensive googling has failed me on this. I'm using the sql driver to output to an oracle database, with debugging mode, I can see that the insert queries are indeed being executed, however my tables remain empty. I've been through the source code, and it doesnt look like a commit is being performed anywhere. Though my google efforts, I did find one reference where someone patched the libdbi-drivers oracle driver to do a commit on success after every query, but that is insanely expensive (and ugly). I have started modifying the code to add the flush_lines and flush_timeout option to the sql driver. But before I got too carried away, I was wondering why this is the way it is the way it is, or if I'm missing something.
Followup. I finished adding flush_lines and flush_timeout to the sql driver. They do what you'd expect, a commit after X number of lines or X seconds. I also fixed a bug that was causing a "." (dot) in the table name to be replaced by an underscore (dots are fully valid in oracle as they denote schema-table separation). I'm starting on converting the code over to using bind variables as well. The performance difference between plain text queries and prepared statements is very large. Especially since we'll be doing hundreds of inserts per second. One of the users in the IRC channel suggested I ask about any licensing issues. Not sure what exactly since the syslog-ng code is GPL, but figured I'd ask anyway. If all is kosher, once all my changes are complete, I can send a patch file. Sent: Tuesday, March 02, 2010 12:46:12 PM From: Patrick H. <syslogng@feystorm.net> To: syslog-ng@lists.balabit.hu Subject: [syslog-ng] commit with the oracle sql driver
So, extensive googling has failed me on this. I'm using the sql driver to output to an oracle database, with debugging mode, I can see that the insert queries are indeed being executed, however my tables remain empty. I've been through the source code, and it doesnt look like a commit is being performed anywhere. Though my google efforts, I did find one reference where someone patched the libdbi-drivers oracle driver to do a commit on success after every query, but that is insanely expensive (and ugly).
I have started modifying the code to add the flush_lines and flush_timeout option to the sql driver. But before I got too carried away, I was wondering why this is the way it is the way it is, or if I'm missing something. ------------------------------------------------------------------------
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
Hi Patrick, I asked our syslog-ng testers, and currently they are not aware of any issues with logging to Oracle, so if it was not working for you, please file a bug report at https://bugzilla.balabit.com/ describing the syslog-ng version and platform you use. As for the patch, it is most welcome, and many thanks for your work. From the legal side, we will need you to sign and fax us a contribution agreement (http://www.balabit.com/dl/CLA_patch.pdf). As you might know, syslog-ng has a commercial version as well, and the agreement is needed so that we can include your patch in the common codebase of the two versions. Regards, Robert Fekete Patrick H. wrote:
Followup. I finished adding flush_lines and flush_timeout to the sql driver. They do what you'd expect, a commit after X number of lines or X seconds. I also fixed a bug that was causing a "." (dot) in the table name to be replaced by an underscore (dots are fully valid in oracle as they denote schema-table separation). I'm starting on converting the code over to using bind variables as well. The performance difference between plain text queries and prepared statements is very large. Especially since we'll be doing hundreds of inserts per second.
One of the users in the IRC channel suggested I ask about any licensing issues. Not sure what exactly since the syslog-ng code is GPL, but figured I'd ask anyway. If all is kosher, once all my changes are complete, I can send a patch file.
Sent: Tuesday, March 02, 2010 12:46:12 PM From: Patrick H. <syslogng@feystorm.net> To: syslog-ng@lists.balabit.hu Subject: [syslog-ng] commit with the oracle sql driver
So, extensive googling has failed me on this. I'm using the sql driver to output to an oracle database, with debugging mode, I can see that the insert queries are indeed being executed, however my tables remain empty. I've been through the source code, and it doesnt look like a commit is being performed anywhere. Though my google efforts, I did find one reference where someone patched the libdbi-drivers oracle driver to do a commit on success after every query, but that is insanely expensive (and ugly).
I have started modifying the code to add the flush_lines and flush_timeout option to the sql driver. But before I got too carried away, I was wondering why this is the way it is the way it is, or if I'm missing something. ------------------------------------------------------------------------
______________________________________________________________________________
Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
------------------------------------------------------------------------
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
On Tue, 2010-03-02 at 16:59 -0700, Patrick H. wrote:
Followup. I finished adding flush_lines and flush_timeout to the sql driver. They do what you'd expect, a commit after X number of lines or X seconds. I also fixed a bug that was causing a "." (dot) in the table name to be replaced by an underscore (dots are fully valid in oracle as they denote schema-table separation). I'm starting on converting the code over to using bind variables as well. The performance difference between plain text queries and prepared statements is very large. Especially since we'll be doing hundreds of inserts per second.
One of the users in the IRC channel suggested I ask about any licensing issues. Not sure what exactly since the syslog-ng code is GPL, but figured I'd ask anyway. If all is kosher, once all my changes are complete, I can send a patch file.
I'd really love to integrate your patches and until my plugins efforts is not complete we require you to sign a contributory license agreement, just like MySQL did [1]. Once my plugins work will be done, we'd go towards a common core under the GPL, without having to sign the CLA and commercial plugins for our premium edition. This would: 1) be better and easier for contributors because they can retain all their rights 2) be better for us, not having to maintain two independent branches of syslog-ng (which is right now about 6 branches: syslog-ng PE, syslog-ng OSE, syslog-ng Store Box, with 1-3 currently supported versions from each) Hopefully I can finish the plugin support in 3.2, but I'd like to integrate your patches earlier if you are willing to sign the CLA. [1] I don't really know what's up with MySQL these days, after being purchased by Sun and then by Oracle.
Sent: Tuesday, March 02, 2010 12:46:12 PM From: Patrick H. <syslogng@feystorm.net> To: syslog-ng@lists.balabit.hu Subject: [syslog-ng] commit with the oracle sql driver
So, extensive googling has failed me on this. I'm using the sql driver to output to an oracle database, with debugging mode, I can see that the insert queries are indeed being executed, however my tables remain empty. I've been through the source code, and it doesnt look like a commit is being performed anywhere. Though my google efforts, I did find one reference where someone patched the libdbi-drivers oracle driver to do a commit on success after every query, but that is insanely expensive (and ugly).
I have started modifying the code to add the flush_lines and flush_timeout option to the sql driver. But before I got too carried away, I was wondering why this is the way it is the way it is, or if I'm missing something.
____________________________________________________________________
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
-- Bazsi
Yes, I signed the CLA and sent it in to info@balabit.com (thats where the instructions say to send it). I also put it all in a bug report (https://bugzilla.balabit.com/show_bug.cgi?id=73). I ended up not doing anything with prepared statements as the only way to do so without ripping out the libdbi driver and replacing it with one that has prepared statements built is to do server side prepared statements. I was originally considering this before I found a little oracle setting that you can set when you establish a session (cursor_sharing=force, basically recognizes that a query is the same as a previous one, so it doesnt have to do permission checks and all that stuff every time). But to support that I had to add a setting which will run a specified statement when a new session is established. But anyway, the bug report has all the full details on the changes. Sent: Saturday, March 06, 2010 2:30:37 AM From: Balazs Scheidler <bazsi@balabit.hu> To: Syslog-ng users' and developers' mailing list <syslog-ng@lists.balabit.hu> Subject: Re: [syslog-ng] commit with the oracle sql driver
On Tue, 2010-03-02 at 16:59 -0700, Patrick H. wrote:
Followup. I finished adding flush_lines and flush_timeout to the sql driver. They do what you'd expect, a commit after X number of lines or X seconds. I also fixed a bug that was causing a "." (dot) in the table name to be replaced by an underscore (dots are fully valid in oracle as they denote schema-table separation). I'm starting on converting the code over to using bind variables as well. The performance difference between plain text queries and prepared statements is very large. Especially since we'll be doing hundreds of inserts per second.
One of the users in the IRC channel suggested I ask about any licensing issues. Not sure what exactly since the syslog-ng code is GPL, but figured I'd ask anyway. If all is kosher, once all my changes are complete, I can send a patch file.
I'd really love to integrate your patches and until my plugins efforts is not complete we require you to sign a contributory license agreement, just like MySQL did [1].
Once my plugins work will be done, we'd go towards a common core under the GPL, without having to sign the CLA and commercial plugins for our premium edition. This would: 1) be better and easier for contributors because they can retain all their rights
2) be better for us, not having to maintain two independent branches of syslog-ng (which is right now about 6 branches: syslog-ng PE, syslog-ng OSE, syslog-ng Store Box, with 1-3 currently supported versions from each)
Hopefully I can finish the plugin support in 3.2, but I'd like to integrate your patches earlier if you are willing to sign the CLA.
[1] I don't really know what's up with MySQL these days, after being purchased by Sun and then by Oracle.
Sent: Tuesday, March 02, 2010 12:46:12 PM From: Patrick H. <syslogng@feystorm.net> To: syslog-ng@lists.balabit.hu Subject: [syslog-ng] commit with the oracle sql driver
So, extensive googling has failed me on this. I'm using the sql driver to output to an oracle database, with debugging mode, I can see that the insert queries are indeed being executed, however my tables remain empty. I've been through the source code, and it doesnt look like a commit is being performed anywhere. Though my google efforts, I did find one reference where someone patched the libdbi-drivers oracle driver to do a commit on success after every query, but that is insanely expensive (and ugly).
I have started modifying the code to add the flush_lines and flush_timeout option to the sql driver. But before I got too carried away, I was wondering why this is the way it is the way it is, or if I'm missing something.
____________________________________________________________________
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.campin.net/syslog-ng/faq.html
Hi, On Sat, 2010-03-06 at 13:12 -0700, Patrick H. wrote:
Yes, I signed the CLA and sent it in to info@balabit.com (thats where the instructions say to send it). I also put it all in a bug report (https://bugzilla.balabit.com/show_bug.cgi?id=73).
I ended up not doing anything with prepared statements as the only way to do so without ripping out the libdbi driver and replacing it with one that has prepared statements built is to do server side prepared statements. I was originally considering this before I found a little oracle setting that you can set when you establish a session (cursor_sharing=force, basically recognizes that a query is the same as a previous one, so it doesnt have to do permission checks and all that stuff every time). But to support that I had to add a setting which will run a specified statement when a new session is established.
I've started integrating your patches to OSE 3.2, but I'm afraid it is not completely ready for prime time yet. flush_timeout() handling was racy, and since it is not trivial to solve that, I've commented that out. Also, I've added explicit "BEGIN" commands and an explicit flag to enable explicit BEGIN/COMMIT commands. This is the commit message: commit d41bc26a63986e9ce06a9a9b0fa50fa82eae2002 Author: Balazs Scheidler <bazsi@balabit.hu> Date: Sat Apr 10 16:36:19 2010 +0200 afsql: Added explicit COMMIT support Instead of committing all INSERT statements separately, use flush_lines parameter to control transaction size. e.g. with the following configuration destination d_sql { sql(... flags(explicit-commits) flush-lines(100)); }; The SQL driver will generate a transaction for every 100 lines. This patch was originally contributed by Patrick Hemmer, was somewhat modified by me. The patch is not yet complete, time based transaction closure is commented out as its submitted implementation was not thread safe. Submitted-By: Patrick Hemmer Thanks for your contribution. -- Bazsi
participants (3)
-
Balazs Scheidler
-
Fekete Robert
-
Patrick H.