View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0013433mantisbtdb oraclepublic2011-10-21 09:172015-03-16 19:25
Reporterdregad Assigned Todregad  
PrioritynormalSeverityminorReproducibilityhave not tried
Status closedResolutionfixed 
Product Version1.2.9 
Target Version1.3.0-beta.1Fixed in Version1.3.0-beta.1 
Summary0013433: Error ORA-00904: "PROTECTED": invalid identifier for the query
Description

There is an error when start mantis (login_page.php):

"Database query failed. Error received from database was #904: ORA-00904: "PROTECTED": invalid identifier for the query: SELECT basename, priority, protected FROM m_plugin_t WHERE enabled=:0 ORDER BY priority DESC."

Additional Information

This is with work-in-progress patch for Oracle in https://github.com/dregad/mantisbt/tree/oracle (assuming current HEAD 08582828cb)

Initially reported by adou in 0013227:0030017

TagsNo tags attached.
Attached Files
20111024_schema_oracle.sql (22,612 bytes)    
CREATE TABLE m_config_t (
config_id                VARCHAR(64) NOT NULL,
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
access_reqd              NUMBER(10) DEFAULT 0,
type                     NUMBER(10) DEFAULT 90,
value                    CLOB NOT NULL,
                 PRIMARY KEY (config_id, project_id, user_id)
);

CREATE INDEX idx_config ON m_config_t (config_id);

CREATE TABLE m_bug_file_t (
id                       NUMBER(10) NOT NULL,
bug_id                   NUMBER(10) DEFAULT 0 NOT NULL,
title                    VARCHAR(250) DEFAULT '',
description              VARCHAR(250) DEFAULT '',
diskfile                 VARCHAR(250) DEFAULT '',
filename                 VARCHAR(250) DEFAULT '',
folder                   VARCHAR(250) DEFAULT '',
filesize                 NUMBER(10) DEFAULT 0 NOT NULL,
file_type                VARCHAR(250) DEFAULT '',
date_added               DATE DEFAULT null NOT NULL,
content                  BLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_bug_file_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_bug_file_t BEFORE insert ON m_bug_file_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_bug_file_t.nextval into :new.id from dual; END;
/

CREATE INDEX idx_bug_file_bug_id ON m_bug_file_t (bug_id);

CREATE TABLE m_bug_history_t (
id                       NUMBER(10) NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
bug_id                   NUMBER(10) DEFAULT 0 NOT NULL,
date_modified            DATE DEFAULT null NOT NULL,
field_name               VARCHAR(32) DEFAULT '',
old_value                VARCHAR(128) DEFAULT '',
new_value                VARCHAR(128) DEFAULT '',
type                     NUMBER(5) DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_bug_history_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_bug_history_t BEFORE insert ON m_bug_history_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_bug_history_t.nextval into :new.id from dual; END;
/

CREATE INDEX idx_bug_history_bug_id ON m_bug_history_t (bug_id);

CREATE INDEX idx_history_user_id ON m_bug_history_t (user_id);

CREATE TABLE m_bug_monitor_t (
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
bug_id                   NUMBER(10) DEFAULT 0 NOT NULL,
                 PRIMARY KEY (user_id, bug_id)
);

CREATE TABLE m_bug_relationship_t (
id                       NUMBER(10) NOT NULL,
source_bug_id            NUMBER(10) DEFAULT 0 NOT NULL,
destination_bug_id       NUMBER(10) DEFAULT 0 NOT NULL,
relationship_type        NUMBER(5) DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_bug_relationship_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_bug_relationship_t BEFORE insert ON m_bug_relationship_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_bug_relationship_t.nextval into :new.id from dual; END;
/

CREATE INDEX idx_relationship_source ON m_bug_relationship_t (source_bug_id);

CREATE INDEX idx_relationship_destination ON m_bug_relationship_t (destination_bug_id);

CREATE TABLE m_bug_t (
id                       NUMBER(10) NOT NULL,
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
reporter_id              NUMBER(10) DEFAULT 0 NOT NULL,
handler_id               NUMBER(10) DEFAULT 0 NOT NULL,
duplicate_id             NUMBER(10) DEFAULT 0 NOT NULL,
priority                 NUMBER(5) DEFAULT 30 NOT NULL,
severity                 NUMBER(5) DEFAULT 50 NOT NULL,
reproducibility          NUMBER(5) DEFAULT 10 NOT NULL,
status                   NUMBER(5) DEFAULT 10 NOT NULL,
resolution               NUMBER(5) DEFAULT 10 NOT NULL,
projection               NUMBER(5) DEFAULT 10 NOT NULL,
category                 VARCHAR(64) DEFAULT '',
date_submitted           DATE DEFAULT null NOT NULL,
last_updated             DATE DEFAULT null NOT NULL,
eta                      NUMBER(5) DEFAULT 10 NOT NULL,
bug_text_id              NUMBER(10) DEFAULT 0 NOT NULL,
os                       VARCHAR(32) DEFAULT '',
os_build                 VARCHAR(32) DEFAULT '',
platform                 VARCHAR(32) DEFAULT '',
version                  VARCHAR(64) DEFAULT '',
fixed_in_version         VARCHAR(64) DEFAULT '',
build                    VARCHAR(32) DEFAULT '',
profile_id               NUMBER(10) DEFAULT 0 NOT NULL,
view_state               NUMBER(5) DEFAULT 10 NOT NULL,
summary                  VARCHAR(128) DEFAULT '',
sponsorship_total        NUMBER(10) DEFAULT 0 NOT NULL,
sticky                   NUMBER(1) DEFAULT '0',
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_bug_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_bug_t BEFORE insert ON m_bug_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_bug_t.nextval into :new.id from dual; END;
/

CREATE INDEX idx_bug_sponsorship_total ON m_bug_t (sponsorship_total);

CREATE INDEX idx_bug_fixed_in_version ON m_bug_t (fixed_in_version);

CREATE INDEX idx_bug_status ON m_bug_t (status);

CREATE INDEX idx_project ON m_bug_t (project_id);

CREATE TABLE m_bug_text_t (
id                       NUMBER(10) NOT NULL,
description              CLOB NOT NULL,
steps_to_reproduce       CLOB,
additional_information   CLOB,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_bug_text_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_bug_text_t BEFORE insert ON m_bug_text_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_bug_text_t.nextval into :new.id from dual; END;
/

CREATE TABLE m_bugnote_t (
id                       NUMBER(10) NOT NULL,
bug_id                   NUMBER(10) DEFAULT 0 NOT NULL,
reporter_id              NUMBER(10) DEFAULT 0 NOT NULL,
bugnote_text_id          NUMBER(10) DEFAULT 0 NOT NULL,
view_state               NUMBER(5) DEFAULT 10 NOT NULL,
date_submitted           DATE DEFAULT null NOT NULL,
last_modified            DATE DEFAULT null NOT NULL,
note_type                NUMBER(10) DEFAULT 0,
note_attr                VARCHAR(250) DEFAULT '',
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_bugnote_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_bugnote_t BEFORE insert ON m_bugnote_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_bugnote_t.nextval into :new.id from dual; END;
/

CREATE INDEX idx_bug ON m_bugnote_t (bug_id);

CREATE INDEX idx_last_mod ON m_bugnote_t (last_modified);

CREATE TABLE m_bugnote_text_t (
id                       NUMBER(10) NOT NULL,
note                     CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_bugnote_text_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_bugnote_text_t BEFORE insert ON m_bugnote_text_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_bugnote_text_t.nextval into :new.id from dual; END;
/

CREATE TABLE m_custom_field_project_t (
field_id                 NUMBER(10) DEFAULT 0 NOT NULL,
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
sequence                 NUMBER(5) DEFAULT 0 NOT NULL,
                 PRIMARY KEY (field_id, project_id)
);

CREATE TABLE m_custom_field_string_t (
field_id                 NUMBER(10) DEFAULT 0 NOT NULL,
bug_id                   NUMBER(10) DEFAULT 0 NOT NULL,
value                    VARCHAR(255) DEFAULT '',
                 PRIMARY KEY (field_id, bug_id)
);

CREATE INDEX idx_custom_field_bug ON m_custom_field_string_t (bug_id);

CREATE TABLE m_custom_field_t (
id                       NUMBER(10) NOT NULL,
name                     VARCHAR(64) DEFAULT '',
type                     NUMBER(5) DEFAULT 0 NOT NULL,
possible_values          VARCHAR(255) DEFAULT '',
default_value            VARCHAR(255) DEFAULT '',
valid_regexp             VARCHAR(255) DEFAULT '',
access_level_r           NUMBER(5) DEFAULT 0 NOT NULL,
access_level_rw          NUMBER(5) DEFAULT 0 NOT NULL,
length_min               NUMBER(10) DEFAULT 0 NOT NULL,
length_max               NUMBER(10) DEFAULT 0 NOT NULL,
advanced                 NUMBER(1) DEFAULT '0' NOT NULL,
require_report           NUMBER(1) DEFAULT '0' NOT NULL,
require_update           NUMBER(1) DEFAULT '0' NOT NULL,
display_report           NUMBER(1) DEFAULT '0' NOT NULL,
display_update           NUMBER(1) DEFAULT '1' NOT NULL,
require_resolved         NUMBER(1) DEFAULT '0' NOT NULL,
display_resolved         NUMBER(1) DEFAULT '0' NOT NULL,
display_closed           NUMBER(1) DEFAULT '0' NOT NULL,
require_closed           NUMBER(1) DEFAULT '0' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_custom_field_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_custom_field_t BEFORE insert ON m_custom_field_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_custom_field_t.nextval into :new.id from dual; END;
/

CREATE INDEX idx_custom_field_name ON m_custom_field_t (name);

CREATE TABLE m_filters_t (
id                       NUMBER(10) NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
is_public                NUMBER(1) DEFAULT NULL,
name                     VARCHAR(64) DEFAULT '',
filter_string            CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_filters_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_filters_t BEFORE insert ON m_filters_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_filters_t.nextval into :new.id from dual; END;
/

CREATE TABLE m_news_t (
id                       NUMBER(10) NOT NULL,
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
poster_id                NUMBER(10) DEFAULT 0 NOT NULL,
date_posted              DATE DEFAULT null NOT NULL,
last_modified            DATE DEFAULT null NOT NULL,
view_state               NUMBER(5) DEFAULT 10 NOT NULL,
announcement             NUMBER(1) DEFAULT '0' NOT NULL,
headline                 VARCHAR(64) DEFAULT '',
body                     CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_news_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_news_t BEFORE insert ON m_news_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_news_t.nextval into :new.id from dual; END;
/

CREATE TABLE m_project_category_t (
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
category                 VARCHAR(64) DEFAULT '',
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
                 PRIMARY KEY (project_id, category)
);

CREATE TABLE m_project_file_t (
id                       NUMBER(10) NOT NULL,
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
title                    VARCHAR(250) DEFAULT '',
description              VARCHAR(250) DEFAULT '',
diskfile                 VARCHAR(250) DEFAULT '',
filename                 VARCHAR(250) DEFAULT '',
folder                   VARCHAR(250) DEFAULT '',
filesize                 NUMBER(10) DEFAULT 0 NOT NULL,
file_type                VARCHAR(250) DEFAULT '',
date_added               DATE DEFAULT null NOT NULL,
content                  BLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_project_file_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_project_file_t BEFORE insert ON m_project_file_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_project_file_t.nextval into :new.id from dual; END;
/

CREATE TABLE m_project_hierarchy_t (
child_id                 NUMBER(10) NOT NULL,
parent_id                NUMBER(10) NOT NULL
);

CREATE TABLE m_project_t (
id                       NUMBER(10) NOT NULL,
name                     VARCHAR(128) DEFAULT '',
status                   NUMBER(5) DEFAULT 10 NOT NULL,
enabled                  NUMBER(1) DEFAULT '1' NOT NULL,
view_state               NUMBER(5) DEFAULT 10 NOT NULL,
access_min               NUMBER(5) DEFAULT 10 NOT NULL,
file_path                VARCHAR(250) DEFAULT '',
description              CLOB,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_project_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_project_t BEFORE insert ON m_project_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_project_t.nextval into :new.id from dual; END;
/

CREATE UNIQUE INDEX idx_project_name ON m_project_t (name);

CREATE INDEX idx_project_view ON m_project_t (view_state);

CREATE TABLE m_project_user_list_t (
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
access_level             NUMBER(5) DEFAULT 10 NOT NULL,
                 PRIMARY KEY (project_id, user_id)
);

CREATE INDEX idx_project_user ON m_project_user_list_t (user_id);

CREATE TABLE m_project_version_t (
id                       NUMBER(10) NOT NULL,
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
version                  VARCHAR(64) DEFAULT '',
date_order               DATE DEFAULT null NOT NULL,
description              CLOB,
released                 NUMBER(1) DEFAULT '1' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_project_version_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_project_version_t BEFORE insert ON m_project_version_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_project_version_t.nextval into :new.id from dual; END;
/

CREATE UNIQUE INDEX idx_project_version ON m_project_version_t (project_id, version);

CREATE TABLE m_sponsorship_t (
id                       NUMBER(10) NOT NULL,
bug_id                   NUMBER(10) DEFAULT 0 NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
amount                   NUMBER(10) DEFAULT 0 NOT NULL,
logo                     VARCHAR(128) DEFAULT '',
url                      VARCHAR(128) DEFAULT '',
paid                     NUMBER(1) DEFAULT '0' NOT NULL,
date_submitted           DATE DEFAULT null NOT NULL,
last_updated             DATE DEFAULT null NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_sponsorship_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_sponsorship_t BEFORE insert ON m_sponsorship_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_sponsorship_t.nextval into :new.id from dual; END;
/

CREATE INDEX idx_sponsorship_bug_id ON m_sponsorship_t (bug_id);

CREATE INDEX idx_sponsorship_user_id ON m_sponsorship_t (user_id);

CREATE TABLE m_tokens_t (
id                       NUMBER(10) NOT NULL,
owner                    NUMBER(10) NOT NULL,
type                     NUMBER(10) NOT NULL,
timestamp                DATE NOT NULL,
expiry                   DATE,
value                    CLOB NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_tokens_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_tokens_t BEFORE insert ON m_tokens_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_tokens_t.nextval into :new.id from dual; END;
/

CREATE TABLE m_user_pref_t (
id                       NUMBER(10) NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
default_profile          NUMBER(10) DEFAULT 0 NOT NULL,
default_project          NUMBER(10) DEFAULT 0 NOT NULL,
advanced_report          NUMBER(1) DEFAULT '0' NOT NULL,
advanced_view            NUMBER(1) DEFAULT '0' NOT NULL,
advanced_update          NUMBER(1) DEFAULT '0' NOT NULL,
refresh_delay            NUMBER(10) DEFAULT 0 NOT NULL,
redirect_delay           NUMBER(1) DEFAULT '0',
bugnote_order            VARCHAR(4) DEFAULT 'ASC' NOT NULL,
email_on_new             NUMBER(1) DEFAULT '0' NOT NULL,
email_on_assigned        NUMBER(1) DEFAULT '0' NOT NULL,
email_on_feedback        NUMBER(1) DEFAULT '0' NOT NULL,
email_on_resolved        NUMBER(1) DEFAULT '0' NOT NULL,
email_on_closed          NUMBER(1) DEFAULT '0' NOT NULL,
email_on_reopened        NUMBER(1) DEFAULT '0' NOT NULL,
email_on_bugnote         NUMBER(1) DEFAULT '0' NOT NULL,
email_on_status          NUMBER(1) DEFAULT '0',
email_on_priority        NUMBER(1) DEFAULT '0',
email_on_priority_min_severity NUMBER(5) DEFAULT 10 NOT NULL,
email_on_status_min_severity NUMBER(5) DEFAULT 10 NOT NULL,
email_on_bugnote_min_severity NUMBER(5) DEFAULT 10 NOT NULL,
email_on_reopened_min_severity NUMBER(5) DEFAULT 10 NOT NULL,
email_on_closed_min_severity NUMBER(5) DEFAULT 10 NOT NULL,
email_on_resolved_min_severity NUMBER(5) DEFAULT 10 NOT NULL,
email_on_feedback_min_severity NUMBER(5) DEFAULT 10 NOT NULL,
email_on_assigned_min_severity NUMBER(5) DEFAULT 10 NOT NULL,
email_on_new_min_severity NUMBER(5) DEFAULT 10 NOT NULL,
email_bugnote_limit      NUMBER(5) DEFAULT 0 NOT NULL,
language                 VARCHAR(32) DEFAULT 'english' NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_user_pref_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_user_pref_t BEFORE insert ON m_user_pref_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_user_pref_t.nextval into :new.id from dual; END;
/

CREATE TABLE m_user_print_pref_t (
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
print_pref               VARCHAR(27) DEFAULT '',
                 PRIMARY KEY (user_id)
);

CREATE TABLE m_user_profile_t (
id                       NUMBER(10) NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
platform                 VARCHAR(32) DEFAULT '',
os                       VARCHAR(32) DEFAULT '',
os_build                 VARCHAR(32) DEFAULT '',
description              CLOB,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_user_profile_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_user_profile_t BEFORE insert ON m_user_profile_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_user_profile_t.nextval into :new.id from dual; END;
/

CREATE TABLE m_user_t (
id                       NUMBER(10) NOT NULL,
username                 VARCHAR(32) DEFAULT '',
realname                 VARCHAR(64) DEFAULT '',
email                    VARCHAR(64) DEFAULT '',
password                 VARCHAR(32) DEFAULT '',
date_created             DATE DEFAULT null NOT NULL,
last_visit               DATE DEFAULT null NOT NULL,
enabled                  NUMBER(1) DEFAULT '1' NOT NULL,
protected                NUMBER(1) DEFAULT '0' NOT NULL,
access_level             NUMBER(5) DEFAULT 10 NOT NULL,
login_count              NUMBER(10) DEFAULT 0 NOT NULL,
lost_password_request_count NUMBER(5) DEFAULT 0 NOT NULL,
failed_login_count       NUMBER(5) DEFAULT 0 NOT NULL,
cookie_string            VARCHAR(64) DEFAULT '',
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_user_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_user_t BEFORE insert ON m_user_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_user_t.nextval into :new.id from dual; END;
/

CREATE UNIQUE INDEX idx_user_cookie_string ON m_user_t (cookie_string);

CREATE UNIQUE INDEX idx_user_username ON m_user_t (username);

CREATE INDEX idx_enable ON m_user_t (enabled);

CREATE INDEX idx_access ON m_user_t (access_level);

INSERT INTO m_user_t(username, realname, email, password, date_created, last_visit, enabled, protected, access_level, login_count, lost_password_request_count, failed_login_count, cookie_string) VALUES
		('administrator', '', 'root@localhost', '63a9f0ea7bb98050796b649e85481845', timestamp'2011-10-24 10:54:11', timestamp'2011-10-24 10:54:11', '1', '0', 90, 3, 0, 0, '15cbb5bab137c7be1bc564c3699031648ffc4faca9746aaf0382b4bbd83d929e');

ALTER TABLE m_bug_history_t MODIFY(
 old_value VARCHAR(255));

ALTER TABLE m_bug_history_t MODIFY(
 new_value VARCHAR(255));

CREATE TABLE m_email_t (
email_id                 NUMBER(10) NOT NULL,
email                    VARCHAR(64) DEFAULT '',
subject                  VARCHAR(250) DEFAULT '',
submitted                DATE DEFAULT null NOT NULL,
metadata                 CLOB NOT NULL,
body                     CLOB NOT NULL,
                 PRIMARY KEY (email_id)
);

CREATE SEQUENCE SEQ_m_email_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_email_t BEFORE insert ON m_email_t FOR EACH ROW WHEN (NEW.email_id IS NULL OR NEW.email_id = 0) BEGIN select SEQ_m_email_t.nextval into :new.email_id from dual; END;
/

ALTER TABLE m_bug_t ADD (
 target_version VARCHAR(64) DEFAULT '');

ALTER TABLE m_bugnote_t ADD (
 time_tracking NUMBER(10) DEFAULT 0 NOT NULL);

CREATE INDEX idx_diskfile ON m_bug_file_t (diskfile);

ALTER TABLE m_user_print_pref_t MODIFY(
 print_pref VARCHAR(64) NOT NULL);

ALTER TABLE m_bug_history_t MODIFY(
 field_name VARCHAR(64));

CREATE TABLE m_tag_t (
id                       NUMBER(10) NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
name                     VARCHAR(100) DEFAULT '',
description              CLOB,
date_created             DATE DEFAULT null NOT NULL,
date_updated             DATE DEFAULT null NOT NULL,
                 PRIMARY KEY (id, name)
);

CREATE SEQUENCE SEQ_m_tag_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_tag_t BEFORE insert ON m_tag_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_tag_t.nextval into :new.id from dual; END;
/

CREATE TABLE m_bug_tag_t (
bug_id                   NUMBER(10) DEFAULT 0 NOT NULL,
tag_id                   NUMBER(10) DEFAULT 0 NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
date_attached            DATE DEFAULT null NOT NULL,
                 PRIMARY KEY (bug_id, tag_id)
);

CREATE INDEX idx_typeowner ON m_tokens_t (type, owner);

CREATE TABLE m_plugin_t (
basename                 VARCHAR(40) NOT NULL,
enabled                  NUMBER(1) DEFAULT '0' NOT NULL,
                 PRIMARY KEY (basename)
);

ALTER TABLE m_user_pref_t MODIFY(
 redirect_delay NUMBER(10) DEFAULT 0);

ALTER TABLE m_custom_field_t MODIFY(
 possible_values VARCHAR(4000) DEFAULT '');

CREATE TABLE m_category_t (
id                       NUMBER(10) NOT NULL,
project_id               NUMBER(10) DEFAULT 0 NOT NULL,
user_id                  NUMBER(10) DEFAULT 0 NOT NULL,
name                     VARCHAR(128) DEFAULT '',
status                   NUMBER(10) DEFAULT 0 NOT NULL,
                 PRIMARY KEY (id)
);

CREATE SEQUENCE SEQ_m_category_t   ;

CREATE OR REPLACE TRIGGER TRIG_SEQ_m_category_t BEFORE insert ON m_category_t FOR EACH ROW WHEN (NEW.id IS NULL OR NEW.id = 0) BEGIN select SEQ_m_category_t.nextval into :new.id from dual; END;
/

CREATE UNIQUE INDEX idx_category_project_name ON m_category_t (project_id, name);

INSERT INTO m_category_t
	( project_id, user_id, name, status ) VALUES
	( '0', '0', 'General', '0' ) ;

ALTER TABLE m_bug_t ADD (
 category_id NUMBER(10) DEFAULT 1 NOT NULL);

ALTER TABLE m_bug_t DROP(category) CASCADE CONSTRAINTS;

DROP TABLE m_project_category_t CASCADE CONSTRAINTS;

ALTER TABLE m_project_t ADD (
 category_id NUMBER(10) DEFAULT 1 NOT NULL);
20111024_schema_oracle.sql (22,612 bytes)   
install.php.htm.zip (4,584 bytes)

Relationships

related to 0013227 closeddregad Oracle DB support multiple issues 
related to 0007644 closeddregad Problems when creating the Mantis database schema on Oracle 
child of 0013438 closeddregad adodb: Fatal error: Call to a member function FetchRow() on a non-object 

Activities

dregad

dregad

2011-10-21 09:21

developer   ~0030020

I'm surprised by this message, because "PROTECTED" is not a reserved keyword in Oracle [1]

Can you provide the SQL that was generated to build your schema ?

[1] http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/reservewords.htm
dregad

dregad

2011-10-24 03:35

developer   ~0030032

The script you provided is a bit strange:

  • the same SQL commands are repeated 4 times
  • the sql instructions stop with statement "ALTER TABLE m_project_t ADD (category_id NUMBER(10) DEFAULT 1 NOT NULL);"

This corresponds to Mantis schema version 74 but the current version is 183, so there are a lot of missing tables as well as outdated definitions including the plugins table which is missing 2 columns (protected and priority). That is certainly the cause of the error you're experiencing.

Please confirm:

  • if were there any errors either in Mantis (GUI), web server log or in Oracle during the installation
  • the schema version number stored in your database (in mantis config table m_config_t, look for value in row where config_id = 'database_version')
  • how the sql script you uploaded was generated - if that's not how you did it, try from install.php, checking "Print SQL Queries instead of Writing to the Database" then click "Install/Upgrade" and updload the printed SQL queries.
adou

adou

2011-10-24 03:39

reporter   ~0030033

My Oracle version is 10g, 10.2.0.5.0
adou

adou

2011-10-24 05:04

reporter   ~0030038

Provided a wrong file, sorry.
I just added a new file from a fresh install. The script is generated checking "Print SQL Queries instead of Writing to the Database".
The source that i've used is from this link: https://github.com/dregad/mantisbt/zipball/oracle
adou

adou

2011-10-24 06:24

reporter   ~0030039

I found this link about the problem:

http://dbaspot.com/oracle-faq/475947-ora-00904-string-invalid-identifier.html

"4. Some issues related to ORA-00904 have been attributed to incorrect setup of an Oracle database. While this is very uncommon, you should validate if the catproc.sql script has been executed properly and ask your admin to run this if its a new database thats throwing the error continously."

I'll confirm that my Oracle have installed correctly.
dregad

dregad

2011-10-24 06:53

developer   ~0030040

Last edited: 2011-10-24 06:54

adou, do you get this at the end of the sql generation ?

Fatal error: Call to a member function FetchRow() on a non-object in /xxx/mantisbt/library/adodb/drivers/adodb-oci8.inc.php on line 368

Call Stack:
0.0018 574752 1. {main}() /xxx/mantisbt/admin/install.php:0
0.3886 12819936 2. call_user_func_array() /xxx/mantisbt/admin/install.php:719
0.3886 12820252 3. db_index_exists() /xxx/mantisbt/admin/install.php:719
0.3888 12820296 4. ADODB_oci8->MetaIndexes() /xxx/mantisbt/core/database_api.php:645
adou

adou

2011-10-24 07:07

reporter   ~0030041

No, there isnt error.
I provide the page in html format with the results of the sql generation.
dregad

dregad

2011-10-24 07:21

developer   ~0030042

What do you get with
SQL> DESCRIBE m_plugin_t;

Also, you have not answered my question about the schema version number in 0013433:0030032
adou

adou

2011-10-24 08:08

reporter   ~0030043

desc m_plugin_t
Nombre Nulo Tipo

BASENAME NOT NULL VARCHAR2(40)
ENABLED NOT NULL NUMBER(1)

There isn't rows in table m_config_t
dregad

dregad

2011-10-25 13:21

developer   ~0030069

adou, your issue is probably resolved by the latest commits I pushed to https://github.com/dregad/mantisbt/tree/oracle. Please test and let me know.
adou

adou

2011-10-26 07:22

reporter   ~0030075

It's ok!

desc m_plugin_t
Nombre Nulo Tipo

BASENAME NOT NULL VARCHAR2(40)
ENABLED NOT NULL NUMBER(1)
PROTECTED NOT NULL NUMBER(1)
PRIORITY NOT NULL NUMBER(10)

The schema version number is 183.

Thanks!!
dregad

dregad

2011-10-26 07:57

developer   ~0030076

That's good to hear !

Please report any other issues you may find with the Oracle patch in 0013227.