View Issue Details

IDProjectCategoryView StatusLast Update
0007644mantisbtdb oraclepublic2015-03-16 19:24
ReporterMartin FuchsAssigned Todregad 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version1.1.0a2 
Target Version1.3.0-beta.1Fixed in Version1.3.0-beta.1 
Summary0007644: Problems when creating the Mantis database schema on Oracle
Description

As reported in Issue 0005289 there are a numer of problems when trying to create an Oracle database schema for Mantis:

==============================================

Oracle Specific Issues:

Identifiers to long on tables:
mantis_user_pref table

  • email_on_priority_minimum_severity
  • email_on_status_minimum_severity
  • email_on_bugnote_minimum_severity
  • email_on_reopened_minimum_severity
  • email_on_closed_minimum_severity
  • email_on_resolved_minimum_severity
  • email_on_feedback_minimum_severity
  • email_on_assigned_minimum_severity
    mantis_user table
  • lost_password_in_progress_count (resolved???)

Trigger Identifiers to long:

  • TRIG_SEQ_mantis_bug_relationship (schema 3)
  • TRIG_SEQ_mantis_project_version (schema 17)

Index Identifiers to long:

  • idx_project_version_project_version (schema 30)
  • idx_bug_relationship_source_bug_id (schema 32)

I get an "such colum list already indexed" error for:
CREATE INDEX idx_news_id ON mantis_news (id) (schema 40)


For date fields to take:

core/adodb/drivers/adodb-oci8.inc.php Line#84
Needs to include the HH24:MI:SS

Steps To Reproduce

create an schema SQL script using the admin/install.php for Oracle.
You may have to ignore the error on line 266 for example by changing "if ( $t_result )" into "if (true)".

After this save the script into a SQL file, change the "END;;" trigger endings into:
"END;
/"

Then try to execute the script using SQL*Plus - you will see the errors mentioned above.

TagsNo tags attached.

Relationships

related to 0005289 closedgrangeway Use ADODB DataDict for DB Creation / Upgrade 
related to 0013433 closeddregad Error ORA-00904: "PROTECTED": invalid identifier for the query 
child of 0013227 closeddregad Oracle DB support multiple issues 
child of 0013438 closeddregad adodb: Fatal error: Call to a member function FetchRow() on a non-object 

Activities

Martin Fuchs

Martin Fuchs

2007-09-29 16:05

reporter   ~0015771

If the suffix "_table" of table names will be changed to "_tbl" the currently longest name "mantis_custom_field_project_table" will be short enough to be created in Oracle databases with the 31 characters of "mantis_custom_field_project_tbl". If it's feasable, the table names could be changed only for Oracle databases to provide backward compatibility for MySQL schemas.

vboctor

vboctor

2009-10-27 03:58

manager   ~0023392

  • Does this issue still exist in 1.2.0rc2?
  • Are you able to provide or test a fix?
watergad

watergad

2009-10-27 13:21

reporter   ~0023416

Tested with RC2 (I use prefix "m" and postfix "_t" to avoid length problems)

CREATE INDEX idx_project_id ON m_project_t (id);
-- Already indexed error

INSERT INTO m_user_t....
-- You have to use to_date('blablabla','dd.mm.yyyy') function here to respect DB locale. Fails to insert date with given string. More, why not to use SYSDATE here? It's an momentary user creation, what for is that manual date definition...

CREATE INDEX idx_email_id ON m_email_t (email_id);
-- Already indexed error

ALTER TABLE m_user_pref_t MODIFY(
redirect_delay NUMBER(10) DEFAULT 0 NOT NULL);
-- Already NOT NULL

category_migrate;
-- wrong SQL statement error

And the DB creation script ended with:
Fatal error: Call to a member function FetchRow() on a non-object in D:\WWW\mantis\library\adodb\drivers\adodb-oci8.inc.php on line 355

And TRIGGER endings, quite annoying ( 0011014 ), they must be ended with:
END;
/
Otherwise the whole following code goes to the trigger body.

But its completely useless unless 0010996 is not solved.

Hope this will help.

Related Changesets

MantisBT: master 2ce60e47

2011-08-10 12:34:07

dregad

Details Diff
Fix 0013227: Oracle database support (oci8)

Mantis 1.2.6 currently does not work with Oracle DB:
1. Installation:
1.1. Oracle DB autocreates PK, so index creation for same field forbidden
1.2. Oracle DB uses datetime literal format timestamp'YYYY-MM-DD HH-MI:SS'
1.3. Oracle DB don't allows altering field property NOT NULL into NOT NULL
1.4. Oracle DB max object length is 30 chars, so some index names must be reduced
1.5. Oracle DB means empty string as NULLs, so NOT NULL restriction must be disabled for some field
1.6. Oracle DB can resolve database server name through TNS, so database name cannot be required
2. General:
2.2. Direct DB query execution result accessing instead of db_fetch_array() 1.3. usage didn't works with Oracle DB
2.4. Oracle DB binds variable by name, so bind names in statement must be sorted to address them.
2.5. Oracle DB handles NULL/DEFAULT values with specific way.
2.6. Oracle DB returns NULL value as true PHP null
2.7. Oracle DB handles sequence access with specific syntax
2.8. Nothing returned by db_prepare_string() in case of oci8
2.9. Oracle DB max object length is 30 chars, so table names must be reduced
2.10. Oracle DB uses LOB literal format similar to mssql
2.11. GetRowAssoc returns empty field values with oci8, it's need to enable returning both associative and indexed arrays.

The original patch was provided by DKuranov. He reckons that this also resolves
issues 0006853, 0007644, 0010437, 0010996, 0011265, 0011270, 0011276, 0012152, 0012478

Porting to 1.3 - Conflicts:
admin/install.php
admin/schema.php
core/database_api.php
manage_tags_page.php
mod - admin/install.php Diff File
mod - admin/schema.php Diff File
mod - core/database_api.php Diff File

Issue History

Date Modified Username Field Change
2006-12-09 16:34 Martin Fuchs New Issue
2006-12-09 16:42 ryandesign Relationship added related to 0005289
2007-09-29 16:05 Martin Fuchs Note Added: 0015771
2009-10-27 03:58 vboctor Note Added: 0023392
2009-10-27 03:58 vboctor Status new => feedback
2009-10-27 13:21 watergad Note Added: 0023416
2011-08-09 11:55 dregad Relationship added parent of 0013227
2011-10-24 07:36 dregad Relationship added related to 0013433
2011-10-24 08:56 dregad Relationship added parent of 0013438
2013-10-17 12:08 dregad Changeset attached => MantisBT master 2ce60e47
2013-10-17 12:51 dregad Target Version => 1.3.0-beta.1
2013-10-17 12:52 dregad Status feedback => resolved
2013-10-17 12:52 dregad Fixed in Version => 1.3.0-beta.1
2013-10-17 12:52 dregad Resolution open => fixed
2013-10-17 12:52 dregad Assigned To => dregad
2014-04-12 19:39 grangeway Issue cloned: 0017202
2014-12-08 00:34 vboctor Status resolved => closed
2015-03-16 19:23 dregad Relationship replaced child of 0013227
2015-03-16 19:24 dregad Relationship replaced child of 0013438