Loading...
 
Skip to main content

Architecture / Installation


Oracle Problem Reports

Hello everyone.

I am trying to install a TikiWiki 1.9.3.2 version into an Oracle 9ir2 database.
I had followed the installation procedures and got some errors.
(I not using anymore the chandra's Oracle9iInstall instructions because those
aparently apply to an old version).
I just ignore the reported errors and follow to admin page.
So I got the following message. After that, I create the table manually and could get to the homepage.
As I procced with installation and test I will report other problems within this topic.


Context:
File /tiki19/tiki-index.php
Url /tiki19/tiki-index.php
Query:
select count(*) from "tiki_stats" where "object"=:0 and "type"=:1 and "day"=:2
Values:
0 HomePage
1 wiki
2 1149735600
Message:
Error Message ORA-00942: table or view does not exist


Just for reference, aparently there is a problem in the tiki-1.9-oci8.sql script, just in the end:


CREATE TABLE `tiki_stats` (
`object` varchar(255) default '' NOT NULL,
`type` varchar(20) default '' NOT NULL,
`day` number(14) default '0' NOT NULL,
`hits` number(14) default '0' NOT NULL,
PRIMARY KEY ("`object`","`type`","`day`")
) ;


I had run the following while connected in the SQLPlus manually:


CREATE TABLE "tiki_stats" (
"object" varchar(255) default '' NOT NULL,
"type" varchar(20) default '' NOT NULL,
"day" number(14) default '0' NOT NULL,
"hits" number(14) default '0' NOT NULL,
PRIMARY KEY ("object","type","day")
)

Searching into the errors logged into the install.php page I noticed some inserts that failed.
Some of them were run mannually after correction, some just rerun, and some i am still looking
for how to rerun...

Found at log:


INSERT INTO tiki_languages(lang, language) VALUES('en','English')
INSERT INTO tiki_live_support_modules(name) VALUES('wiki')
INSERT INTO tiki_live_support_modules(name) VALUES('forums')
INSERT INTO tiki_live_support_modules(name) VALUES('image galleries')
INSERT INTO tiki_live_support_modules(name) VALUES('file galleries')
INSERT INTO tiki_live_support_modules(name) VALUES('directory')
INSERT INTO tiki_live_support_modules(name) VALUES('workflow')
INSERT INTO tiki_live_support_modules(name) VALUES('charts')
INSERT INTO users_permissions (permName, permDesc, level, type) VALUES('tiki_p_admin_users', 'Can admin users', 'admin', 'user')
INSERT INTO "tiki_quicktags" ("taglabel","taginsert","tagicon","tagcategory") VALUES ('italic','\'\'text\'\'','images/ed_format_italic.gif','wiki')


And here the commands I had run mannually:


INSERT INTO "tiki_languages"("lang", "language") VALUES('en','English')
INSERT INTO "tiki_live_support_modules"("name") VALUES('wiki')
INSERT INTO "tiki_live_support_modules"("name") VALUES('forums')
INSERT INTO "tiki_live_support_modules"("name") VALUES('image galleries')
INSERT INTO "tiki_live_support_modules"("name") VALUES('file galleries')
INSERT INTO "tiki_live_support_modules"("name") VALUES('directory')
INSERT INTO "tiki_live_support_modules"("name") VALUES('workflow')
INSERT INTO "tiki_live_support_modules"("name") VALUES('charts')
INSERT INTO "users_permissions" ("permName", "permDesc", "level", "type") VALUES('tiki_p_admin_users', 'Can admin users', 'admin', 'user')
INSERT INTO "tiki_quicktags" ("taglabel","taginsert","tagicon","tagcategory") VALUES ('italic','text','images/ed_format_italic.gif','wiki')



Those next ones i just rerun as is (perhaps a hidden character in the script?)


INSERT INTO "tiki_menu_options" ("menuId","type","name","url","position","section","perm","groupname") VALUES (42,'o','Home','tiki-index.php',10,,,'')
INSERT INTO "tiki_menu_options" ("menuId","type","name","url","position","section","perm","groupname") VALUES (42,'s','Homework','tiki-hw_student_assignments.php','280','feature_homework','tiki_p_hw_student','')
INSERT INTO "tiki_menu_options" ("menuId","type","name","url","position","section","perm","groupname") VALUES (42,'o','Assignments','tiki-hw_teacher_assignments.php','282','feature_homework','tiki_p_hw_student','')
INSERT INTO "tiki_menu_options" ("menuId","type","name","url","position","section","perm","groupname") VALUES (42,'o','Last Changes','tiki-hw_teacher_assignments.php','284','feature_homework','tiki_p_hw_student','')
INSERT INTO "tiki_menus" ("menuId","name","description","type") VALUES ('42','Application menu','Main extensive navigation menu','d')


And those ones i will see the best way to run because it is still failing:


INSERT INTO "tiki_user_modules" ("name","title","data","parse") VALUES ('mnu_application_menu', 'Menu', '{menu id=42}', 'n')
INSERT INTO tiki_integrator_reps VALUES ('1','Doxygened (1.3.4) Documentation','','index.html','doxygen.css','n','y','0','Use this repository as rule source for all your repositories based on doxygened docs. To setup yours just add new repository and copy rules from this repository 😊')
INSERT INTO tiki_integrator_rules VALUES ('1','1','1','.*]*?>(.*?)

> INSERT INTO "tiki_user_modules" ("name","title","data","parse") VALUES ('mnu_application_menu', 'Menu', '{menu id=42}', 'n')

for this one the underlying table has a blob column.
I dont know exactly why this column is a blob and not a clob,
but i changed the table structure manually as follows and the insert worked:


DROP TABLE "tiki_user_modules";
CREATE TABLE "tiki_user_modules" (
"name" varchar(200) default '' NOT NULL,
"title" varchar(40) default NULL,
"data" clob default empty_clob(),
"parse" char(1) default NULL,
PRIMARY KEY ("name")
);



First thing noticed:

the left menu with admin options are not visible.
I will try to find out why...

> First thing noticed:
>
> the left menu with admin options are not visible.
> I will try to find out why...

The last update that i run (the one that i changed the table structure) make the menu apear again ! 😀


Another problem.
After activated the Forum feature and go to the forum menu, the following message apear.
Another table creation problem??


Context:
File /tiki19/tiki-forums.php
Url /tiki19/tiki-forums.php
Query:
select * from "tiki_forums" order by "section" asc,"created" desc
Values:
Message:
Error Message ORA-00942: table or view does not exist


Sure. Another create table problem.
The table "tiki_forums" was not created because
the column outbound_mails_for_inbound_mails has more then 30 characters lenght.
I will recreate the table changing the column name to
outbound_mail_for_inbound_mail
so it has exactly 30 characters.
Probably I will have to revise some php code to correct the sql statements too.
The create table script follow attached.
As I see php page changes i will attach them too.

Oh, another thing: I will rerun the create trigger statement!

A message for the devel team: I know the life isnt easy (i was a developer too in the past) 😊 , but try to keep table/column/indexes and any other object names below 30 characters lenght.
The Oracle admins who use the Tiki will thanks.. 😀

I have found the string

outbound_mails_for_inbound_mails

within these files:


./tiki-admin_forums.php
./lib/commentslib.php
./templates/tiki-admin_forums.tpl


I have just changed all strings within those files by the column name.
I hope this patch works.

Devel team, may you check these and incorporate into the next build ❓
Follow the changed files as attach.


After I had successfuly created a forum, I had the following test case
that came to an error:

1. Click the "List Forum" link
A list with the only forum I created apeared.

2. Click into the forum name
An error page with the message
ORA-00932: inconsistent datatypes: expected - got CLOB
apeared.

I think it is because the a."data" column used within the select.
I will try to comment out the columns to see what happens.
If it work I will attach the changed file here.

The file: /tiki19/tiki-view_forum.php
The URL: /tiki19/tiki-view_forum.php?forumId=1
The bind values:
0=1
1=s
2=0
The underlying select is:


select a."threadId",a."object",a."objectType",a."parentId", a."userName",a."commentDate",a."hits",a."type",a."points", a."votes",a."average",a."title",a."data",a."hash",a."user_ip", a."summary",a."smiley",a."message_id",a."in_reply_to",a."comment_rating", NVL(max(b."commentDate"), a."commentDate") as "lastPost", count(b."threadId") as "replies" from "tiki_comments" a left join "tiki_comments" b on b."parentId"=a."threadId" where a."object"=:0 and a."type" = :1 and a."objectType" = 'forum' and a."parentId" = :2 group by a."threadId",a."object",a."objectType",a."parentId",a."userName",a."commentDate",a."hits",a."type",a."points",a."votes",a."average",a."title",a."data",a."hash",a."user_ip",a."summary",a."smiley",a."message_id",a."in_reply_to",a."comment_rating" order by "commentDate" desc, "threadId"


I have found the select that create the problem within the ./lib/commentslib.php
I have just stripped out the problematic column from the select.
I am not a PHP programmer, and do not have enough time to check if the change I made
has any collateral effect. But it apparently worked fine.
The file with accumulated changes until now into the comments.php is attached.


Another test case:

1.Create a forum (with any configuration)
2.Click the List Forum link
3.Click the Forum you just created.
4.Click the "New Topic" button
5.Add a title and a comment and click preview button
6.Click the post button

An Error message came up:
ORA-00923: FROM keyword not found where expected

File: /tiki19/tiki-view_forum_thread.php
URL: /tiki19/tiki-view_forum_thread.php?comments_parentId=2&topics_threshold=0&topics_offset=1&topics_sort_mode=commentDate_desc&topics_find=&forumId=1
The query:


select s."user", p."value" as 'realName', "timestamp", "tikihost" from "tiki_sessions" s left join "tiki_user_preferences" p on s."user":0 and s.user = p.user and p.prefName = 'realName' where s.user is not null;



I tried to run the select above into the sqlplus and get the same error.
I corrected the select for the below and it run ok into the sqlplus.
The select is within the ./lib/tikilib.php.
I corrected the select. The file is attached.
After that I rerun the test and another error came up. I will resolve this error in another post.


select
s."user",
p."value" as realName,
"timestamp",
"tikihost"
from
"tiki_sessions" s
left join "tiki_user_preferences" p
on s."user"''
and s."user" = p."user"
and p."prefName" = 'realName'
where s."user" is not null
/


Follow the same test case below:

> Another test case:
>
> 1.Create a forum (with any configuration)
> 2.Click the List Forum link
> 3.Click the Forum you just created.
> 4.Click the "New Topic" button
> 5.Add a title and a comment and click preview button
> 6.Click the post button
...
> After that I rerun the test and another error came up. I will resolve this error in another post.

The error that came up is:
ORA-01741: illegal zero-length identifier
The select:


select tc1."threadId", tc1."object", tc1."objectType", tc1."parentId", tc1."userName", tc1."commentDate", tc1."hits", tc1."type", tc1."points", tc1."votes", tc1."average", tc1."title", tc1."data", tc1."hash", tc1."user_ip", tc1."summary", tc1."smiley", tc1."message_id", tc1."in_reply_to", tc1."comment_rating" from "tiki_comments" as tc1 left outer join "tiki_comments" as tc2 on tc1."in_reply_to" = tc2."message_id" and tc1."parentId" = :0 and tc2."parentId" = :1 where tc1."objectType" = :2 and tc1."object"=:3 and tc1."parentId"=:4 and tc1."average">=:5 and (tc1."in_reply_to" = :6 or (tc2."in_reply_to" = "" or tc2."in_reply_to" is null or tc2.message_id is null or tc2.parentid = 0)) order by tc1."commentDate" desc,tc1."threadId"


The select can be found at the file ./lib/commentslib.php
After that another error came up. I am tired of correting and posting each one so i corrected this third error
too. 🙄
The corrected version of the file is attached.

Some of the corrections can be not portable because I do not check them into other databases. I will leave this to the development team 😁


I have got a serious problem now.
After activate the search module in the menu, I tried to
search the forum messages. Somewhere in the code, the tiki
issues the following query to the database (the query as reformatted):


Image
Copy to clipboard
SELECT c."title" AS name, SUBSTR(c."data", 1, 240) AS data, c."hits" AS hits, c."commentDate" AS lastModif, f."name"||': '||"title" AS pageName, f."forumId" AS id1, c."threadId" AS id2 MATCH(c."title",c."data") AGAINST ('Espanha') AS relevance FROM "tiki_comments" c, "tiki_forums" f WHERE c."objectType" = 'forum' AND f."forumId" = c."object" AND MATCH(c."title",c."data") AGAINST ('Espanha') ORDER BY relevance desc, c."hits"


Unfortunatly, this query does not work into Oracle because of MATCH..AGAINST operator.
There are alternatives using Oracle Text to replicate the funcion, but probably the
feature will have to be rewrite.

I am wondering the easyest way to rewrite the stuff using Oracle Text.

Well,

this is what I have got until now:
To create a textual index (an index that can be used for textual queries)
you need to run a command like this one:

Image

Copy to clipboard
create index "tiki_comments_data_ft" on "tiki_comments"("data") indextype is ctxsys.context;


But after running the command I got:

Image

Copy to clipboard
ERROR at line 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-20000: Oracle Text error: DRG-10582: column DATA does not exist in table tiki_comments ORA-06512: at "CTXSYS.DRUE", line 157 ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 204


This error is a bug in Oracle versions from 8i to 9i, relative to the case sensitive sintax used
to create the tables. All database is created with the " mark in table, column and index names,
and is used within all the php pages.
Unfortunatly, there is not a patch for this bug in the Oracle version I am using (9ir2).
Is is corrected into the >=10g versions, but I don't think upgrade is an option for me
right now.

One option would be to remove all double quote references within all database queries into the
php code ( perhaps under the oci8 driver? ). If we do, the Oracle would be case insensitive,
and there will be no problem to create the textual index. I don't know if this would be easy to implement.
As I am not a PHP programmer, and have no PHP programmers to help me, I don't think I have enough time
to correct this by myself and probably will have to abandon my efforts.
The search feature have a very high importance in my context. So I think I will have to search for
other options.

If someone else could help me, fell free to contact me.