Oracle database patch problems with 1.8

[coop]

So, did you simply update those lines from:

alter table hosts add ipmi_error nvarchar2(128) DEFAULT '' NOT NULL

to:

alter table hosts add ipmi_error nvarchar2(128) DEFAULT '' NULL

Actually we just changed it to:

alter table hosts add ipmi_error nvarchar2(128) DEFAULT ''

[untergeek]

We also suffer from these:

update dservices set ip=(select dhosts.ip from dhosts where dservices.dhostid=dhosts.dhostid)
                                *
ERROR at line 1:
ORA-00904: "DHOSTS"."IP": invalid identifier

alter table dhosts drop column ip
                               *
ERROR at line 1:
ORA-00904: "IP": invalid identifier

[untergeek]

CREATE UNIQUE INDEX dservices_1 on dservices (dcheckid,type,key_,ip,port)
                                   *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

How would I have duplicate keys?

[untergeek]

In addition, I see this:

update graphs set ymin_type=yaxistype
                            *
ERROR at line 1:
ORA-00904: "YAXISTYPE": invalid identifier 


update graphs set ymax_type=yaxistype
                            *
ERROR at line 1:
ORA-00904: "YAXISTYPE": invalid identifier 


alter table graphs drop column yaxistype
                               *
ERROR at line 1:
ORA-00904: "YAXISTYPE": invalid identifier

Any ideas on how to correct this one?

[untergeek]

This one perplexes me also:

alter table items drop column nextcheck
                              *
ERROR at line 1:
ORA-00904: "NEXTCHECK": invalid identifier

[coop]

Took me a minute to confirm and double check with our DBA. We didn't get any of those errors during the patch.

One suggestion might be to double check the credentials you are using when you apply the patch.... do you have all the rights you need?


The last problem I encountered was going through the frontend setup. During the DB config screen we kept getting this error:

Warning: ociplogon() [function.ociplogon]: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor in /usr/local/apache2/htdocs/zabbix/include/db.inc.php on line 99

With sqlora8 we had been using the TNS name for the db instance and it connected just fine. With the new version I had to put the actual database name in for this to work.

Also, we use a non-standard Oracle port for security. The db.inc.php has a hard coded port number on line 100 which was the Oracle default of 1521. Since we use a different port number, I also changed this entry. Not sure if that had any effect but we're connected now.

[untergeek]

I think we've compensated and explained away each of these but the graph ones with the YAXISTYPE where a number should be.

update graphs set ymin_type=yaxistype
                            *
ERROR at line 1:
ORA-00904: "YAXISTYPE": invalid identifier 


update graphs set ymax_type=yaxistype
                            *
ERROR at line 1:
ORA-00904: "YAXISTYPE": invalid identifier 


alter table graphs drop column yaxistype
                               *
ERROR at line 1:
ORA-00904: "YAXISTYPE": invalid identifier

Any idea what should go here? It's obviously meant to be a variable, but what's supposed to go there? Nothing is declared in the patch.sql so we are flying blind here.

[coop]

I think we've compensated and explained away each of these but the graph ones with the YAXISTYPE where a number should be.

update graphs set ymin_type=yaxistype
                            *
ERROR at line 1:
ORA-00904: "YAXISTYPE": invalid identifier 


update graphs set ymax_type=yaxistype
                            *
ERROR at line 1:
ORA-00904: "YAXISTYPE": invalid identifier 


alter table graphs drop column yaxistype
                               *
ERROR at line 1:
ORA-00904: "YAXISTYPE": invalid identifier

Any idea what should go here? It's obviously meant to be a variable, but what's supposed to go there? Nothing is declared in the patch.sql so we are flying blind here.

Are you trying to run the patch a second time? The 3 lines in patch.sql that reference yaxistype are:

update graphs set ymin_type=yaxistype;
update graphs set ymax_type=yaxistype;
alter table graphs drop column yaxistype;

So since the last line is to drop the column.... If your running this a second time and the column has already been dropped, it's possible that's why your seeing the errors about the yaxistype identifier not being found anywhere...

Things are up and running on our end. Configuring the new zabbix_server.conf for the host, db and port took a minute. It seemed to reject the port number if the comment was on the end of the line...? Could have just been me too.... but we were able to get it up and running and wow is it fast compared to the older setup!!! Thanks Zabbix Team!!!

[untergeek]

Yes, we were running it a second time.

After correcting all of the noted items we restored the db backup and ran the patch again. The only one that still got us was this:

CREATE UNIQUE INDEX dservices_1 on dservices (dcheckid,type,key_,ip,port)
                                   *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

What seems to be our problem here? Where should I look to fix this?

[coop]

Yes, we were running it a second time.

After correcting all of the noted items we restored the db backup and ran the patch again. The only one that still got us was this:

CREATE UNIQUE INDEX dservices_1 on dservices (dcheckid,type,key_,ip,port)
                                   *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

What seems to be our problem here? Where should I look to fix this?

I'm still really new to Zabbix but the info seems to indicate the dservices table has duplicate entries and the patch is trying to unique index. Any chance you can use sqlplus to see what's in the table? That might help you to narrow down at least what the patch is looking at.....

[untergeek]

My solution (since this was bound to discovery stuff):

Shutdown apache and zabbix_server
Truncate the tables DHOSTS and DSERVICES
Drop and recreate the indexes DHOSTS_1, DSERVICES_1 and DSERVICES_2 using the installation schema.

Restart apache and the zabbix_server.

Sure, it's a kludge. And I had to re-discovery all of my hosts, but it worked.

[JRE]

It seems to be a limitation of older Oracle. It's great to hear it is fixed.

So I'm evaluating Zabbix for my company and over the last week have fired up an installation running against an Oracle 11g R2 database, and I'm getting the nvarchar2 problem using 2048 character. 2000 characters works. This doesn't seem to be a older version of Oracle problem as 11gR2 is pretty new, specifically:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 17:40:47 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Could it be character set related? Also, I didn't see in the Zabbix documentation which character sets we should use when creating the Zabbix database. How much does that matter?

SQL> select * from nls_database_parameters

NLS_CHARACTERSET
AL32UTF8

NLS_NCHAR_CHARACTERSET
AL16UTF16

Ideas? Thanks in advance =)

[dalle]

I have the same issue I think that it's about character set used diuring creation of database.
Which character set we should use?
I'm currently using AL16UTF16 so may be about it's rappresentation of nvarchar2?

[dalle]

Ok I've solved this issue it's not a zabbix bug!
You need to create database with NCHARSET=UTF8
and after all will work like a charm!

[dalle]

What is your version of Oracle?

this issue it's not about Oracle
Oracle database should be created with NCHARSET=UTF8 it's a trouble of rappresentation of byte etc...
and after... all will work like a charm!