[ZBX-5623] Oracle database error during 2.0 upgrade: ORA-01451 Created: 2012 Sep 26  Updated: 2017 May 30  Resolved: 2012 Sep 27

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Installation (I)
Affects Version/s: 2.0.0, 2.0.3
Fix Version/s: None

Type: Incident report Priority: Minor
Reporter: Oleksii Zagorskyi Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: dbpatches, oracle, upgrade
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Zabbix 1.8.15, Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


 Description   

2.0 DB upgrade script for Oracle contains:

ALTER TABLE triggers MODIFY templateid DEFAULT NULL;
ALTER TABLE triggers MODIFY templateid NULL;

It generates an error during upgrade:

ALTER TABLE triggers MODIFY templateid NULL
*
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL

Looks like second line is redundant and should be removed.

 Comments   
Comment by Oleksii Zagorskyi [ 2012 Sep 26 ]

Some details - this 1.8 database was recently converted from MySQL to Oracle (successfully).

And now we are trying to upgrade 1.8 to 2.0 (1.8 zabbix installation seem is working fine with Oracle)

Comment by Alexander Vladishev [ 2012 Sep 26 ]

The first statement set default value to NULL, the second - removes NOT NULL constraint. It's valid statements.

Please attach the "triggers" table structure with all indexes and constraints before an upgrade.

Comment by Oleksii Zagorskyi [ 2012 Sep 27 ]

Before the attempt to upgrade to 20:

SQL> describe triggers;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRIGGERID                                 NOT NULL NUMBER(20)
 EXPRESSION                                         NVARCHAR2(255)
 DESCRIPTION                                        NVARCHAR2(255)
 URL                                                NVARCHAR2(255)
 STATUS                                             NUMBER(10)
 VALUE                                              NUMBER(10)
 PRIORITY                                           NUMBER(10)
 LASTCHANGE                                         NUMBER(10)
 COMMENTS                                           NVARCHAR2(2048)
 ERROR                                              NVARCHAR2(128)
 TEMPLATEID                                         NUMBER(20)
 TYPE                                               NUMBER(10)
 VALUE_FLAGS                               NOT NULL NUMBER(10)
 FLAGS                                     NOT NULL NUMBER(10)

So the column TEMPLATEID already allowed NULL values. Now I know that during the mysql -> oracle conversion there were some manual manipulations with database structure (performed by experienced DBA).

http://www.techonthenet.com/oracle/errors/ora01451.php
"You tried to execute an ALTER TABLE MODIFY attempting to change a column to allow NULL values, but the column either already allows NULL values or the column is used in a primary key or check constraint and can not accept NULL values."

This is not a zabbix bug, sorry, CLOSED

Generated at Fri Apr 26 22:49:31 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.