[Bug 23] New: MySQL default configuration causes error on 'pid' column
https://bugzilla.balabit.com/show_bug.cgi?id=23 Summary: MySQL default configuration causes error on 'pid' column Product: syslog-ng Version: 2.1.x Platform: PC OS/Version: Linux Status: NEW Severity: normal Priority: unspecified Component: syslog-ng AssignedTo: bazsi@balabit.hu ReportedBy: mark@la-croix.net Type of the Report: bug Estimated Hours: 0.0 When configuring a MySQL destination, the default 'pid' column type is INT. However, if a message comes through with no PID, the SQL command tries to insert an empty quoted string, which causes an error in MySQL 5.0. Example error: Error running SQL query; type='mysql', host='!MY_DB_HOST!', port='', user='syslog-ng', database='syslog-ng', error='1366: Incorrect integer value: \'\' for column \'pid\' at row 1', query='INSERT INTO !MY_LOG_CLIENT_HOST!_2008_09_23 (date, facility, level, host, program, pid, message) VALUES (\'2008-09-23 12:24:35\', \'1\', \'5\', \'!MY_LOG_CLIENT!\', \'root\', \'\', \'test\')' The message is coming from a TCP source from which a large percentage of messages have no PID associated. The workaround is to explicitly set the PID column to VARCHAR so it can handle empty strings, but it would be nice to see the empty PID case gracefully handled. -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
https://bugzilla.balabit.com/show_bug.cgi?id=23 --- Comment #1 from Balazs Scheidler <bazsi@balabit.hu> 2008-09-24 11:46:17 --- and how do you think this should be handled? as I see changing the default config would be fine, wouldn't it? e.g. change the default type to varchar. alternatively I could use the following template as a default pid value: ${PID:-0}, this would insert a 0 in case PID is empty. Which one do you prefer? -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
https://bugzilla.balabit.com/show_bug.cgi?id=23 --- Comment #2 from Mark LaCroix <mark@la-croix.net> 2008-09-24 15:25:31 --- I think defaulting the PID value to 0 would be preferable, since it is inefficient to store numbers as strings. Either way is fine, but the default configuration should work out of the box. Another solution would be to set NULL fields to NULL rather than an empty string. Is there a way to specify default values in the conf file? That would certainly help for people who want to customize the table structure. The NULL-for-NULL solution would cover this on the database side since they could specify NOT NULL with a default value. -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
https://bugzilla.balabit.com/show_bug.cgi?id=23 --- Comment #3 from Balazs Scheidler <bazsi@balabit.hu> 2008-09-24 15:47:22 --- the bad thing about SQL defaults, that it is almost impossible to create a static default that will work for all databases. The current one works for MySQL, but fails on Oracle as it uses slightly different syntax, and its varchar() type does not support more than 4096 characters. MSSQL has other quirks. Regarding the 'default' value, would something like this be ok? columns('id int not null auto_increment primary key', 'date datetime', 'host varchar(256)' values(default, '$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC', '$HOST') This would generate this INSERT: INSERT INTO <table> (date, host) VALUES ('2008-09-24 15:46:25', 'bzorp') e.g. it'd skip the column where the value has the keyword 'default' instead of a normal template. I think this would solve your problem as well, right? -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
https://bugzilla.balabit.com/show_bug.cgi?id=23 --- Comment #4 from Mark LaCroix <mark@la-croix.net> 2008-09-25 05:21:54 --- I'm not following you on this one. The default SQL destination uses MySQL and sets the PID column as INT. It is possible to send an empty string when the PID is not set however and this causes an error in MySQL 5.0. I believe there should be a solution for this. The easiest thing to do would be to change the PID column to VARCHAR, but I think there is some room for improvement in the underlying code. Currently when a value (PID in this case) is not set, it gets defaulted to an empty string. If it were set to NULL however, the database backend could properly process the query. This way, a user could set up the PID column to be "int not null default 0". When the NULL value tries to get inserted, it would default to the proper value of 0. -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
https://bugzilla.balabit.com/show_bug.cgi?id=23 --- Comment #5 from Balazs Scheidler <bazsi@balabit.hu> 2008-09-25 11:30:15 --- I'm not sure that zero length string equals to a NULL value. And currently the template() formatting in syslog-ng cannot return anything but an empty string. in text-based fields the empty strings should be '' instead of NULL. So the solution would be to use ${PID:-0} in the default template. This patch fixes this (git.balabit.hu): commit 2d5a4343db577c395e947be08dae7db48b9e1ea2 Author: Balazs Scheidler <bazsi@balabit.hu> Date: Thu Sep 25 11:28:22 2008 +0200 change the default value of the PID column in the default SQL config (fixes: pub#23) The default type for the PID column is numeric, which will not be able to process empty strings. Use ${PID:-0} as the default value, thus substituting 0 if the PID is unset. -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
https://bugzilla.balabit.com/show_bug.cgi?id=23 Balazs Scheidler <bazsi@balabit.hu> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |RESOLVED Resolution| |FIXED --- Comment #6 from Balazs Scheidler <bazsi@balabit.hu> 2008-09-25 13:30:02 --- setting resolution to FIXED. I'd be grateful if you could test the referenced patch, and set the state of this bugzilla ticket to "TESTED" -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
https://bugzilla.balabit.com/show_bug.cgi?id=23 --- Comment #7 from Mark LaCroix <mark@la-croix.net> 2008-09-26 15:35:11 ---
in text-based fields the empty strings should be '' instead of NULL.
Well, yes and no. Empty strings should be '', but MISSING strings should be NULL. Ideally, missing anything should become a NULL value, at least for the purposes of database insertion. Unfortunately it's not that simple. But it illustrates a core problem with the current DB interface. Everything is being treated as a string in the INSERT statement and there is no way to differentiate between an empty string value and a missing data value. I have tested the patch and it didn't work. A missing PID still produces an empty string in the INSERT statement. -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
https://bugzilla.balabit.com/show_bug.cgi?id=23 Balazs Scheidler <bazsi@balabit.hu> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|RESOLVED |REOPENED Resolution|FIXED | --- Comment #8 from Balazs Scheidler <bazsi@balabit.hu> 2008-09-29 11:46:56 --- (In reply to comment #7)
in text-based fields the empty strings should be '' instead of NULL.
Well, yes and no. Empty strings should be '', but MISSING strings should be NULL. Ideally, missing anything should become a NULL value, at least for the purposes of database insertion. Unfortunately it's not that simple.
But it illustrates a core problem with the current DB interface. Everything is being treated as a string in the INSERT statement and there is no way to differentiate between an empty string value and a missing data value.
true, syslog-ng provides the flexibility to use templates in column values, I think that's pretty nifty, but yes, this means that all columns are treated as strings. as almost everything is a string in a log message, I find this limitation not that severe.
I have tested the patch and it didn't work. A missing PID still produces an empty string in the INSERT statement.
hmm.. you're right, I used a construct that does not yet work in 2.1. I revert the patch, as it causes breakage and REOPEN this bug report. -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
https://bugzilla.balabit.com/show_bug.cgi?id=23 Balazs Scheidler <bazsi@balabit.hu> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|REOPENED |RESOLVED Resolution| |FIXED --- Comment #9 from Balazs Scheidler <bazsi@balabit.hu> 2009-04-30 10:38:17 --- I have committed this patch in syslog-ng 3.0 to implement support for NULL values, it basically allows you to specify a "NULL" value and in case the expanded string for a column matches the null string, it will insert a NULL column Together with support for "default values" this can achieve what you want. E.g. use this for your value template: ${PID:-@NULL@} and use @NULL@ as your NULL value, this means you get a NULL in that column, provided PID is unset. commit 10c11e65fcf02aca60b05d265be694d771fa57ad Author: Balazs Scheidler <bazsi@balabit.hu> Date: Tue Apr 14 13:16:04 2009 +0200 [afsql] implemented NULL value support Reported-By: Liam Kirsher -- Configure bugmail: https://bugzilla.balabit.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are watching all bug changes.
participants (1)
-
bugzilla＠bugzilla.balabit.com