Drupal under ORACLE 9i/10g - a call for volunteer testers.

By archimedes-1 on 2 Oct 2006 at 17:12 UTC

Hello all. This topic serves as both an announcement and a call for volunteers.

After seeing numerous calls and wishes for Drupal to be seen to run with an ORACLE back end, I decided to have a bash at the task. A few months ago I got stuck in and, after some work, was finally pleased to see Core Drupal 4.7 and the ORACLE 9i/10g platform cuddling up to each other. Having spent a fair amount of time in the testing phase, I found the results to be favourable and was very pleasantly surprised to see that, performance-wise, it wasn't being left to eat the dust left behind by a similar Drupal installation running under MySQL. I have written a few paragraphs about my thoughts here.

I would now like to move things along to the next stage and enlist the help of some willing Drupal users who are both comfortable with ORACLE and who would like to have a go at running Drupal on an ORACLE database. A single person can test Drupal's many features for correctness only so much.

If this proves to be a successful first rung on the ladder, my hope is that, with the coming together of ORACLE and Drupal, some organisations with rather strict database policies may at last be able to gain access to the latter; and that ORACLE Real Application Cluster technology becomes an option for a highly scalable Drupal implementation. It certainly makes for interesting times.

I have primarily been working under Linux and ORACLE 9i/10g but have also managed to do some limited testing under Windows 2000 and ORACLE 10g Express Edition. Much of that time was spent trying to get PHP with the OCI8 extension to work properly (but that's another story).
By the way, be aware of this as it may also happen to you - and under Linux too! I have found recent releases of OCI8 to be rather problematic during these last few months.

With that, it remains to say that the software is available from my site where you can also read about some of the frustrations encountered during this journey. The link to my site can be found in my profile here, under the personal information section.

Hoping to hear from some people soon.

Thanks.

Comments

Great for moving Drupal into larger enterprises

robertdouglass commented 2 October 2006 at 18:11

This is a really commendable effort, kudos to you. Another piece of Drupal news that is definitely Digg-worthy.

- Robert Douglass

-----
Lullabot | My Drupal book | My Digg stories

that digg code

MySchizoBuddy commented 3 October 2006 at 02:43

Hi robert,
can u post that digg code you have in your message. :)
thanks in advance

sure

robertdouglass commented 3 October 2006 at 07:14

It looks like this:

<script>digg_url = 'http://digg.com/software/Finally_Drupal_runs_on_Oracle';</script>
<script src="http://digg.com/api/diggthis.js"></script>

Only people on this site who have permissions to use the "Full HTML" input format will be able to use it on drupal.org, though. If you want to discuss how Drupal.org should interact with sites like Digg and Del.icio.us, please start a new thread.

- Robert Douglass

-----
Lullabot | My Drupal book | My Digg stories

Good Start and congrats

sangamreddi commented 2 October 2006 at 19:24

I am buying new server thsi week and planning to install Oracle 9i/10g. I love to tets drupal under oracle. soon i'll post the output.

Congrats once again and keep up teh good work.

Sunny
www.gleez.com | www.sandeepone.com

how does this correspond to

greggles

he/him

English

Denver, Colorado, USA

commented 2 October 2006 at 20:36

how does this correspond to souvent22's work?

His work is here: http://cvs.drupal.org/viewcvs/drupal/contributions/sandbox/souvent/alter...
And discussed here: http://groups.drupal.org/node/1496

Is this the same work? If not - I hope you can collaborate!

--
Growing Venture Solutions
Drupal Implementation and Support in Denver, CO

--
Morris Animal Foundation

Not Sure

Souvent22 commented 3 October 2006 at 19:39

Not sure. I think when I tried Arch's site the first time a while back it was down "looking for a new home" :) (could be wrong).

As mentioned in a few of my posts, what is in my sandbox is a collaboration of my own work, and those from other threads. It was just so much code dispersed over so many threads, there needed to be a place so we can collaborate on the same code base and discuss the same code base as a community. I'm not saying my sandbox is the best place, I'm open to whatever, but we def. need to be collborating on the same code base. I am excited at the progress and push towards stabalizing Oracle and MSSQL as viable backends for Drupal.

But I'd love to collaborate and get this stabliazed. I also think a nice handbook is neccessary to help the installation of Oracle/Drupal/PHP pretty easy. As I have currently found, getting the bugger oci8 to work is annoying. I still have yet to get it to compile correctly under linux (fallen back to using Zend core for Oracle). Windows isn't so bad. The performance boost though is VERY worth the effort to get it working if you are using and working with large datasets (40 million+ rows/ 500GB+).

Testing and Handbook

Souvent22 commented 3 October 2006 at 19:33

I have also started testing Drupal with Oracle. I have also started a handbook, which people are welcome to critque before I release a final draft. Arch, thanks a lot for your work, it has helped me a lot.

Handbook Draft link: http://www.eb3it.com/oracle_handbook

Worthless Link?

Vietyank commented 23 July 2007 at 12:31

I found no handbook here. Just commercial links.

Same Problem with Single Session

Souvent22 commented 3 October 2006 at 19:43

Arch,
I have the SAME exact problem with the session just lasting for one page. It seems Zend/Oracle does a "double/send", meaning it makes a random anonymous call, and some how passes a uid 0, which then updates the sessions table and sets your uid to 0, thus invalidating your session. I have no idea why it does this though.

I like your solution though....hmm...wonder if we can get the same thing working under Linux. I'm off to the drawing board. :).

Also, I think a lot of the stuff on your blog could be used for a Drupal handbook on Oracle. Would you have any objections to allowing your blog content to be used for a Drupal handbook?

Another database: modules?

jmajb commented 4 October 2006 at 12:18

This is a nice effort, but...
I'm new to Drupal and I'm on the way to implement Drupal with PostgreSQL as a database, which is supported for some time already I understand. But I see that lots of modules only support MySQL, which is a problem for me. When Oracle is becoming a third supported database it will become even harder for the makers of modules to support all databases.

Jac

Translated SQL

Souvent22 commented 4 October 2006 at 17:03

Yes, I see your point. I think, and again, this is just personal opinion, that:

PostGres and MySQL should be the two main supported databases, for the following reasons:

Both are currently the standard, and widley supported
(most importantly) I think that between the 2 databases you get a nice snippet of SQL flavors

From MySQL/PostGres, we should be able to "translate" the SQL statements as need be. There should be no reason for EVERY module to implement EVERY database.

So bascially what I'm trying to say is that I am not expecting anyone to change their modules, but I would like them to support both MySQL AND PostGres. Because if their code supports both, than we should be able to "transalte" any SQL statement into most databases. I do want some SQL in a few modules cleaned up. One problem I have run into running Drupal on Oracle is "dirty sql" statements (http://drupal.org/node/83556 , http://drupal.org/node/83560).

But right now, it seems MySQL is the standard, and PostGres is considered a great alternative. I think we should make a push for module to implement both. Perhaps not a "requirement", but a "strong" suggestion.

- Souvent22

...

sepeck commented 4 October 2006 at 23:30

I draw your attention to this page.
http://drupal.org/node/1395
It may need updates based on your work.

General note for people reading this thread. If people could work with module contributors to 'clean up' their SQL, this would be helpful as many module contributors are building their first modules and it is often a learning experience for them.

-Steven Peck
---------
Test site, always start with a test site.
_{Drupal Best Practices Guide -|- Black Mountain}

-Steven Peck
---------
Test site, always start with a test site.
_{Drupal Best Practices Guide}

Ah, Yes, Thanks

Souvent22 commented 5 October 2006 at 00:15

Beautiful sepeck. Yes thanks. The Drupal-SQL bible. :)

Open Source vs. Open Standards

willi.firulais commented 12 November 2006 at 20:41

The whole world is talking about open source (what is fine, by the way) - but when open source development does not mean to implement open standards then it's the wrong way.

If every statement would have been developed to (real) SQL standards (and not MySQL "Standards"), then a porting to other databases may not be needed - it would simply run.

thx, Willi

..

greggles

he/him

English

Denver, Colorado, USA

commented 13 November 2006 at 03:48

willi.firulais - thanks for the perspective. I look forward to seeing your patches in the issue queue on the modules I maintain because I'm sure that there are some areas that involve non-standard SQL.

Regards,
Greg

--
Knaddison Family | mmm Beta Burritos

--
Morris Animal Foundation

It's No Silver Bullet

archimedes-1 commented 17 November 2006 at 14:33

If every statement would have been developed to (real) SQL standards (and not MySQL "Standards"), then a porting to other databases may not be needed - it would simply run.

Sorry to disagree with you but that statement sounds very optimistic to me. Outside of the theoretical realm, that philosophy is not enough to ensure that any non-trivial piece of software will run correctly on many database management systems - even if every single platform in the world was fully compliant with the latest SQL standards. DBMSs are not "generally all the same": they are not all just a simple bunch of data files with a SQL compiler thrown in. Think of locking models and concurrency for starters.

First-hand experience has confirmed that the vast majority of SQL statements in core Drupal 4.7 would pass your real SQL standards test yet that in itself did not guarantee that those same queries would run without issues under, say, Oracle. This extends to the general case, not just Drupal or Oracle.

The painful fact is that things are not as simple on the ground as your statement asserts. When you have a sophisticated, mission-critical application it is very difficult to resist the forces that come into play when performance/resources/hard cash is at stake. Each database platform has its own distinct set of pressure points and Achilles' heel(s). When your own database platform is pushed beyond that point, what may seem to be a few inconsequential platform-neutral queries may actually turn out to have been a poisoned chalice. In spite of the great effort put into maintaining platform-neutrality, it's almost inevitable that you'll discard the flag of neutrality and end up writing vendor-specific SQL to put the fire out. And that's if you're very lucky and the general architecture of the software doesn't (albeit unintentionally) completely miss the mark with the recommended way things are supposed to be done for that platform, in the name of platform-neutrality or standards compliance. Unfortunately, things are not as simple as those four little words: "it would simply run."

Fully Agree

willi.firulais commented 18 November 2006 at 22:13

I fully agree with your (Archimedes) comments on this 'standard' topic.
What I tried to say is exactly for the reason you have mentioned very good points on.
But if the 'standard' topic would be stressed in the same manner as the open source topic (in everyones mind) - and/or by the open source - maybe it would become better in future (Santa Claus is near :-) to write portable solutions (eg. databases providers will have some interest in being standard sql complient, etc.)

Btw thank's a lot four your work and doing this port. I am very happy with it and playing currently around with drupal on oracle.

regards, willi

I have seen sometimes type of sql coding that was especially written for one sql dialect - without the people knowing what they were doing (this does not mean drupal). And when trying porting such things this becomes a nightmare.

In response to some questions...

archimedes-1 commented 5 October 2006 at 07:58

@greggles
This was a solo and independent development effort that I kicked off and completed earlier this year. I did publish back in June but, owing to some web hosting issues, my site went down and stayed down for a good period. I was indeed looking for a new home for the site. I'm happy to say that those issues are history (as well as that particular hosting account). Details can be found in the (now familiar) node 39260.

@souvent22
Regarding OCI8
For Linux, I found that replacing the OCI8 source bundled with PHP 5.1.5 with that of OCI8 1.2.2 from the PECL sorted out all the issues I had.

For Win2K, I found that replacing the php_oci8.dll bundled with PHP 5.1.6 (from PHP.net) with its namesake from Zend Core for ORACLE 1.5 sorted out the stability issues. I mention this in my scribblings.

My experience has been that we seem to be in a slightly shaky period for OCI8's stability but I'm sure it'll all be back to normal not long from now. It's a shame that this is happening right now as it muddies the waters somewhat.

I do agree regarding using Windows: the Drupal/ORACLE 10g XE response time was very impressive.

I also came across these "dirty sql" statements you mentioned. I drew up a catalogue of these slightly strange queries (I called them "whacky" SQL) during the work but I didn't bother re-publishing them when the site went back up. I mention one of them in an earlier post. I will revisit this when looking at Drupal 5 source.

Anyhow, that's all for now. Thanks to all for their kind words. I shall be checking back here from time to time but right now the best way to catch my attention regarding getting involved with testing is to drop me a note via email. Thanks.

A trial in importing devel into oradrup

mike2854 commented 5 October 2006 at 09:03

I have tried to add new class to support devel module,
I think this would be help in development.
The performance is ok,
but I see that every query will be picked to matching,
I am afraid the process time will be observable
after numbers of modules have been installed.
I hope this kind of project would carry on,
as I want to have drupal on oracle server.

oradrup10051610.zip

The PECL OCI8 installer enabled PEAR::Oracle as well!

bshensky commented 18 October 2006 at 05:20

FYI, I published a posting that detailed my efforts to get php_oci8 on Fedora 4 running WITH ONLY PEAR+PECL+RPMs and WITHOUT RECOMPILING PHP.

http://bshensky.livejournal.com/#entry_8085

Hope this helps here...

-Brian

Some exciting noises coming from Oracle Develop conference

archimedes-1 commented 26 October 2006 at 08:19

Oracle Announces New Connection Pooling Feature

This entry from Christopher Jones's blog talks about a new Oracle Database 11g feature: Database Resident Connection Pooling.

This sounds exciting from a Drupal under ORACLE perspective. I had earmarked some time to pursue the implementation of connection pooling in an effort to dramatically cut the cost of a page access. It seems that ORACLE 11g, when it reaches production, will offer this feature out of the box. Yet another ally in the battle for scalability.

Can't find table "access"

jorditr commented 8 November 2006 at 17:01

Hi you all.

I've downloaded the pack and installed it adding all the changes as I plan some Drupal project over Oracle. The overall look is good and the creation of the schema has been pretty easy. Despite the different changes and patches suggested on the documentation it doesn't work dropping that error message:

Warning: ORA-00942: la tabla o vista no existe . Query: SELECT * FROM "access" WHERE status = 1 AND type = :bv1 AND LOWER(:bv2) LIKE LOWER(mask) in c:\appserv\www\drupal\includes\database.oracle9i.inc on line 255

Warning: ORA-00942: la tabla o vista no existe . Query: SELECT * FROM "access" WHERE status = 0 AND type = :bv1 AND LOWER(:bv2) LIKE LOWER(mask) in c:\appserv\www\drupal\includes\database.oracle9i.inc on line 255

Behind it we can see the usual first time page inviting to create the first admin account and contents (with no theme on the background).

I have no clue what else we can do as the table access has been properly created withing the schema (I've used TOAD for it). Any idea?, has anyone faced the same trouble?, please?

Finally it works properly

jorditr commented 10 November 2006 at 16:29

Hi you all and BIG thanks to Archimedes you contacted me very fast with some help.

Well, it was a very small detail and bump! it was working. I plan to do a heavy research on it next week. I'll expose here my conclusions :-)

OraDrup part of Drupal 5.0 ??

jorditr commented 8 November 2006 at 17:12

BTW, according to the people that has tested it possitively, is it going to be included on Drupal 5.0? I downloaded the last beta 1 and it wasn't included. It would be a very important step ahead to distribute the Oracle and DB2 database layers! :-)

Probabley Not

Souvent22 commented 20 November 2006 at 15:52

Probabley not. There are a lot of issues that are out of our contral that a re hindering the Oracle effort. Mainly, the OCI8 library is not stable enough in a 'standard' format. Currently, only Zend seems to be able to compile it stababley (via Zend/Oracle). However, Zend/Oracle for some reason does not work with Drupal.

Hmm...note:
(Personal thought as I'm typing)

This could be due to the new way the 5.1 handles sessions, in which drupal will not work with PHP 5.1.6+ on any DB playform. There is a patch for this. Perhaps by grabbing the patch from drupal, applying it, then Zend/Oracle would work w/o this problem?

Anyway, there is that problem, and then a lot of the quries in Drupal do not follow the standards set forth by drupal, which would allow easy integratoin with any DB platform (e.g. auto_increments, LIMIT, etc.).

We are planning on fixing all this. I am hoping ot make a big push to get this into 6.0, and in that time hopefully a backport to 5.0 will come into the picture as beta testing.

Oracle with fedora and php 1.5.6

Marwin_menezes commented 19 November 2006 at 15:02

Hi Arch,
I am facing the same problem as Souvent22 with Zend/Oracle php 5.1.6 on fedora 4.0. The session lasts for one page. when I login I get the problem of access being denied. I have read your post regarding the problem.
"Regarding OCI8 For Linux, I found that replacing the OCI8 source bundled with PHP 5.1.5 with that of OCI8 1.2.2 from the PECL sorted out all the issues I had". what I would like to know if this solution is applicable to php 5.1.6 for the Zend/Oracle platform on linux.

problem with oracle

Marwin_menezes commented 20 November 2006 at 15:11

Hi Arch,
I tried recompiling php with OCI8 1.2.2 from the PECL but I still get the error. following is the error in apache log

[Mon Nov 20 20:30:05 2006] [error] [client 10.244.0.33] PHP Notice: Trying to get property of non-object in /var/www/html/drupal/includes/bootstrap.inc on line 577, referer: http://10.244.5.107/drupal/?time=1164034782
[Mon Nov 20 20:30:05 2006] [error] [client 10.244.0.33] PHP Notice: Undefined property: stdClass::$uid in /var/www/html/drupal/includes/session.inc on line 40, referer: http://10.244.5.107/drupal/?time=1164034782

It seems like a sessions problem rather then a problem with oci8 can anybody please help.

Single Page Sessions

archimedes-1 commented 21 November 2006 at 21:04

Well, it seems this single-page session problem has manifested itself on Drupal under MySQL too. It appears to be independent of database platform. Perhaps the OCI8 extension is totally innocent after all with respect to this problem?

I built a fresh copy of Apache 2.2.3 and PHP 5.2.0 today under Fedora Core 4 and was seeing the same symptoms: you log in successfully only to get an "access denied" message on trying to navigate to any subsequent page. This happens under both MySQL and ORACLE.

The following pages suggest the reason behind the problem and provide a patch:
http://drupal.org/node/92802
http://drupal.org/node/93945

I've applied the patch to session.inc and: no more single-page sessions! This also happens under both MySQL and ORACLE.

While I'm on the subject, I can also vouch for PHP 5.2.0 being bundled with a copy of the OCI8 extension that works fine.

oracle with Drupal

Marwin_menezes commented 23 November 2006 at 04:22

Thanks for the tip Arch. I finally got it all to work.. I am using fedora 4 with zend/oracle php 5.1.6 platform. This is the easiest way to do it cause you dont have to compile php with oracle support.

Glad to see some movement with Oracle

MVRider commented 17 January 2007 at 03:14

I'm also in need a Oracle support in order for us to be able to use Drupal. What is the current state of development? Has this been tested and/or anyone running it in a production enviroment?

I'm looking at using Drupal with an Oracle backend for a Human Rights community site.

Any help would be great!

Thanks Again!

It works

Marwin_menezes commented 12 February 2007 at 06:03

Yes this works I am using it on production for a client site. http://content.meevee.com. This uses drupal 4.7 + oracle 10g and php 5.1.6

Groups

sepeck commented 9 February 2007 at 16:20

There is a group that is dedicated to improving cross platform capabilities in future versions of Drupal.
http://groups.drupal.org/enterprise

-Steven Peck
---------
Test site, always start with a test site.
_{Drupal Best Practices Guide -|- Black Mountain}

-Steven Peck
---------
Test site, always start with a test site.
_{Drupal Best Practices Guide}

Oracle and CCK

Lioz commented 21 February 2007 at 10:06

i successfully managed to run Drupal 4.7 under oracle 10g using the Oradrup module. Still have problems with the CCK module. The problem is that the module tries to create its own tables while creating the content-types. Oradrup doesn't manage the CREATE_TABLE feature on an oracle DB. Does anyone have experienced the same trouble? Any idea how to resolve it? Thanks.

http://www.signalkuppe.com

I can't install any modules

infoentropy commented 9 March 2007 at 17:27

I'm having the same problem as Lioz but worse. I can't figure out how to install any modules. Does anyone have a modified module *.install file that successfully installs a contributed module to Oracle and OraDrup?

I would go on the oracle DB and create the tables myself but I do not have access to the server.

I'm facing the same problem

archimede commented 12 March 2007 at 17:51

I'm facing the same problem with CCK.

To make things worse, I have little experience with PHP. :(

I'm currently reviewing CCK code to make it Oracle-compliant (which is something I'd rather avoid, but we need this module), and I think the basic types (number and varchar) are now working.

Correct me if I'm wrong, but it seems to me CCK touches (INSERT or UPDATE) the db once for every field: if true, I'd really like to change that as well, but I'm afraid it would be beyond my limited capabilities. Anyone can confirm or reject my impression (and maybe help)?

Also, are there significant differences in CCK structure and code for Drupal 5?

Thanks.

Alessandro

EDIT - I just realized my nick is quite similar to Archimedes' nick. We are not the same person, of course, and it wasn't my intention to generate misunderstandings in this regard: it's just the "standard" nick I use on the web. Please accept my apologies.

cck working but..

Lioz commented 22 March 2007 at 15:49

cck now works on oracle db (with date/text/integer/link/image fileds).
the problem now is with the views module: cck field names are too long for view queries so i often get errors like

user warning: ORA-00972: identifier is too long. Query: SELECT node.nid, node.changed AS node_changed_changed, node.title AS node_title, node.changed AS node_changed, node_data_field_not_asb_p.field_not_asb_p_value AS node_data_field_not_asb_p_field_not_asb_p_value FROM DPnode node LEFT JOIN DPnode_content_notizia node_data_field_not_asb_p ON node.vid = node_data_field_not_asb_p.vid WHERE (node.type IN ('content_notizia')) AND (node.sticky = '1') ORDER BY node_changed_changed DESC in /usr/local/apache-2.2.4/htdocs/drupal4/includes/database.oracle9i.inc on line 238.

http://www.signalkuppe.com

Connectivity Problems

SatyaPrasad_Alamuru commented 20 August 2007 at 05:39

Hi all,
We are new to Drupal World. Wanted to connect Oracle with Drupal. Can some help with the setps to follow for making drupal talk to oracle.

Have Oracle 10g release1 installed on Linux machine, trying to access the Oracle server through client installed on WIndows Development machine.

Apache Version: 20.0.59 and PHP 5.2.3

Included OCI8 dll in php.ini file.

Error is unable to connect Oracle Server.

Thanks a head.

Drupal under ORACLE 9i/10g - a call for volunteer testers.

Comments

New forum topics