[ZBX-7273] importing data.sql for Oracle inserts \r\n literally Created: 2013 Nov 04  Updated: 2017 May 30  Resolved: 2013 Nov 04

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Installation (I)
Affects Version/s: 2.0.9
Fix Version/s: 2.0.10rc1, 2.2.0rc1

Type: Incident report Priority: Blocker
Reporter: Aleksandrs Saveljevs Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

For instance, here is the default message for one of the actions:

"Trigger:

{TRIGGER.NAME}

\r\nTrigger status:

{TRIGGER.STATUS}

\r\nTrigger severity:

{TRIGGER.SEVERITY}

\r\nTrigger URL:

{TRIGGER.URL}

\r\n\r\nItem values:\r\n\r\n1.

{ITEM.NAME1}

(

{HOST.NAME1}

:

{ITEM.KEY1}

):

{ITEM.VALUE1}

\r\n2.

{ITEM.NAME2}

(

{HOST.NAME2}

:

{ITEM.KEY2}

):

{ITEM.VALUE2}

\r\n3.

{ITEM.NAME3}

(

{HOST.NAME3}

:

{ITEM.KEY3}

):

{ITEM.VALUE3}

\r\n\r\nOriginal event ID:

{EVENT.ID}

"

 Comments   
Comment by Aleksandrs Saveljevs [ 2013 Nov 04 ]

It seems to have broken way back in pre-2.0.1 r28167 in ZBX-5061 and ZBX-5123:

$ svn log -v -c 28167 ^/ 
------------------------------------------------------------------------
r28167 | sasha | 2012-06-08 18:25:52 +0300 (Fri, 08 Jun 2012) | 2 lines
Changed paths:
   M /branches/2.0
   M /branches/2.0/ChangeLog
   M /branches/2.0/create/bin/gen_data.pl
   M /branches/2.0/src/libs/zbxdbhigh/host.c

....I..... [ZBX-5061] fixed Oracle SQL errors when executing data.sql
........S. [ZBX-5123] fixed Oracle SQL error while linking a template by server
------------------------------------------------------------------------

Because of this part of the diff:

Index: branches/2.0/create/bin/gen_data.pl
===================================================================
--- branches/2.0/create/bin/gen_data.pl (revision 28166)
+++ branches/2.0/create/bin/gen_data.pl (revision 28167)
@@ -165,7 +165,7 @@
 
                        $_ =~ s/&pipe;/|/g;
 
-                       if ($output{'database'} eq 'mysql')
+                       if ($output{'database'} eq 'mysql' || $output{'database'} eq 'oracle')
                        {
                                $_ =~ s/&eol;/\\r\\n/g;
                        }
Comment by Aleksandrs Saveljevs [ 2013 Nov 04 ]

This should be fixed in both 2.0 and trunk.

Currently, the suggested fix is to insert newlines using "' || chr(13) || chr(10) '", as proposed here: http://stackoverflow.com/questions/5812148/how-to-enter-newline-character-in-oracle , although other alternatives might be considered as well.

Before r28167, the code was like that:

if ($output{'database'} eq 'mysql')
{
	$_ =~ s/&eol;/\\r\\n/g;
}
else
{
	$_ =~ s/&eol;/\x0D\x0A/g;
}

The reason second option might not work for Oracle is because sqlplus utility treats two consecutive newlines as statement termination, as hinted at in ZBX-5061. This has to be checked.

Comment by Alexander Vladishev [ 2013 Nov 04 ]

Fixed in the development branch svn://svn.zabbix.com/branches/dev/ZBX-7273

Comment by Andris Mednis [ 2013 Nov 05 ]

Successfully tested.

Comment by Alexander Vladishev [ 2013 Nov 05 ]

Fixed in versions pre-2.0.10 r39904 and pre-2.2.0 r39906.

Generated at Mon Mar 03 05:51:37 EET 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.