Page MenuHomePhabricator

Install a new MediaWiki with Oracle 11g as Backend: Unable to complete the process
Closed, ResolvedPublic

Description

Author: dbq343

Description:
Everything works fine during the installation process. Only at the last step this exception is thrown:

Creating main page with default content... failed

Information

Could not insert main page: ORA-02091: transaction rolled back ORA-02291: integrity constraint (MEDIAWIKI.REVISION_FK2) violated - parent key not found


Version: 1.22.0
Severity: blocker
OS: Linux
Platform: PC

Details

Reference
bz38411

Event Timeline

bzimport raised the priority of this task from to High.Nov 22 2014, 1:12 AM
bzimport set Reference to bz38411.
Reedy added a comment.Jul 15 2012, 6:01 PM

Which version of MediaWiki?

dbq343 wrote:

(In reply to comment #1)

Which version of MediaWiki?

1.19.1

freak wrote:

Can you give me the version of DB too ... i'll check it tomorrow.

dbq343 wrote:

11.2.0.1.0 x6(In reply to comment #3)

Can you give me the version of DB too ... i'll check it tomorrow.

11.2.0.1.0 x64

dbq343 wrote:

Now its working fine. Oracle Standard, not Enterprise and Charset WE8MSWIN1252. But when i'm trying to protect the main page i'm getting this error:

A database error has occurred. Did you forget to run maintenance/update.php after upgrading? See: https://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script
Query: INSERT INTO /*Q*/PAGE_RESTRICTIONS (pr_page,pr_type,pr_level,pr_cascade,pr_expiry) VALUES (:pr_page, :pr_type, :pr_level, :pr_cascade, :pr_expiry)
Function: DatabaseOracle::insertOneRow
Error: 1400 ORA-01400: cannot insert NULL into ("MEDIAWIKI"."PAGE_RESTRICTIONS"."PR_ID")

freak wrote:

ORA-01400 issue was fixed in gerrit 16810.

The actual problem reported by this bug was obviously a problem with DB, not MW software, so i'm closing this bug.

t.i.ivanov wrote:

I get the same error with 1.20.2 and 1.21.1:

Install

Setting up database... done
Creating database user... done
Creating tables... done
Populating default interwiki table... done
Initializing statistics... done
Generating secret keys... done
Creating administrator user account... done
Creating main page with default content... failed
Information

Could not insert main page: ORA-02091: transaction rolled back ORA-02291: integrity constraint (WIKIUSER.REVISION_FK2) violated - parent key not found

My Database is:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production (32bit)
Charset: AL32UTF8
Linux OS

I see other people having the same issue:
http://www.mediawiki.org/wiki/Thread:Project:Support_desk/MediWiki_with_Oracle#MediWiki_with_Oracle_15193

freak wrote:

I'm guessing you tried re-running a failed installation without clearing the old state.

tables.sql has two insert statements that create user_id=0 user and revision_id=0 revision, to prevent that FK violations. You can check to see if that row exists in users and revisions table.

My advice would be to drop the user you're trying to create during installation and run the installer again.

t.i.ivanov wrote:

I do drop the user before every installation:

drop user "WIKIUSER" CASCADE;

create user wikiuser identified by SOMEPASSHERE default tablespace users temporary tablespace temp quota unlimited on users;

grant connect, resource to wikiuser;
grant alter session to wikiuser;
grant ctxapp to wikiuser;
grant execute on ctx_ddl to wikiuser;
grant create view to wikiuser;
grant create synonym to wikiuser;
grant create table to wikiuser;
grant create sequence to wikiuser;
grant create trigger to wikiuser;

Here I have screenshotted my install steps:

www.niti95.com/uploads/Installation_steps.pdf

Thanks for the help in advance.

freak wrote:

Weird, i've just tried 1.21.1 and 1.22alpha on 11gR2 with the same procedure you tried and it works as expected.

Can you try after a failed install manually inserting that dummy user (it's in maintenance/oracle/tables.sql on line 29).

t.i.ivanov wrote:

I hope I understood you correctly:

oracle@main-server:~$ sqlplus wikiuser/SOMEPASSHERE

SQL> INSERT INTO mwmwuser VALUES (user_user_id_seq.nextval,'Anonymous',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '', current_timestamp, current_timestamp, 0);
INSERT INTO mwmwuser VALUES (user_user_id_seq.nextval,'Anonymous',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '', current_timestamp, current_timestamp, 0)

ERROR at line 1:
ORA-00001: unique constraint (WIKIUSER.MWMWUSER_U01) violated

SQL> SELECT * FROM USER_CONSTRAINTS where table_name='MWMWUSER';
MWMWUSER_PK Primary_Key
SYS_C0012478 Check USER_ID IS NOT NULL
SYS_C0012479 Check USER_NAME IS NOT NULL

If I did something wrong, please let me know.

Regards

freak wrote:

Ok this is getting weirder. Installer complains that the dummy mwuser is missing, but it exists.

Could you check if you happen to have case-sensitive login enabled?

t.i.ivanov wrote:

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON;

NAME TYPE VALUE


sec_case_sensitive_logon boolean TRUE

I will disable it and try again the install.

t.i.ivanov wrote:

racle@main-server:~$ sqlplus / as sysdba

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON;

NAME TYPE VALUE


sec_case_sensitive_logon boolean FALSE

SQL> exit

oracle@main-server:~$ sqlplus wikiuser/SOME PASSWORD HERE

SQL> INSERT INTO mwmwuser VALUES (user_user_id_seq.nextval,'Anonymous',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'', current_timestamp, current_timestamp, 0);
INSERT INTO mwmwuser VALUES (user_user_id_seq.nextval,'Anonymous',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'', current_timestamp, current_timestamp, 0)

ERROR at line 1:
ORA-00001: unique constraint (WIKIUSER.MWMWUSER_U01) violated

No difference :(

freak wrote:

no don't try to insert ... do as you did before: drop wikiuser (check if there are others like "WIKIUSER" and drop those too) and retry the installation.

t.i.ivanov wrote:

Actually the result above is from the following sequence:

  1. I made sure sec_case_sensitive_logon is FALSE (also restarted DB just in case as it said it may take a while)
  2. Dropped and recreated the user as per my post #9
  3. Made the installation as per the PDF file in #9
  4. Run manually the INSERT statement from your #10 again to see if something changed.

I am really out of ideas. Could it be something oci8 or PHP (5.3.3-1ubuntu9.10) related?

Regards

t.i.ivanov wrote:

There are no other wiki like users. I made ULTRAWIKI database entirely for this installation. I completely erased the mediawiki folder from my apache to make sure, there is no caching from older install attempts.
I can create another DB for the test install, if you think there might be something wrong with the current.

freak wrote:

Have you tried dropping the user and creating it trough the installer?

Enter SYSTEM and it's password, uncheck "Use same account..." and enter wikiuser and pass in the second set of fields and check "Create if missing..."?

I'm just fireing blind here ... i just can't seem to reproduce the error on my setups. I don't have 11gR1 anywhere (i have 10gR2 and 11gR2).

t.i.ivanov wrote:

  1. Dropped the user manually
  2. Unchecked: Use the same account as for installation
  3. Entered

Database username: wikiuser
Database password: some pass here

  1. Checked: Create the account if it does not already exist

Now the results fails here:

Install

Setting up database... done
Creating database user... failed
Information

DB connection error.

Check the host, username and password and try again.

One small note:

The web installer would not start until I commented out lines 90 and 91 here:
includes/installer/OracleInstaller.php

I saw it in this post:
http://dolicapax.org/?p=152#comment-420

I pass the database in the web installer as localhost/ultrawiki

freak wrote:

About lines 90 and 91 in the installer:

EZconnect fix hasn't been backported yet, well it was, but the change hasn't been merged with 1.21.

freak wrote:

We (me + Todor) found the issue, bug i still can't figure out the cause.

For some reason user_user_id_seq skipps the first value (0).

I'm attaching a patch for maintenance/oracle/tables.sql file and will try to put it in repo asap.

freak wrote:

Patch for oracle tables.sql

Attached:

t.i.ivanov wrote:

Thank you very much, Jure!

The installer now completed successfully. I will test in the next two days the other functionalities.

Regards

Jure: Should this ticket be reopened for the patch in comment 22? Is there a related Gerrit change?

freak wrote:

You've got a point ... reopening until this patch gets put into the code, so it can be tracked.

No Gerrit change exists yet for this patch.

freak wrote:

*** Bug 52094 has been marked as a duplicate of this bug. ***

Petr: FYI, you can get an automatic notification from Gerrit in Bugzilla if you follow http://www.mediawiki.org/wiki/Gerrit/Commit_message_guidelines :)

Change 80538 had a related patch set uploaded by Bartosz Dziewoński:
Fix Oracle installation SQL

https://gerrit.wikimedia.org/r/80538

Change 80538 merged by jenkins-bot:
Fix Oracle installation SQL

https://gerrit.wikimedia.org/r/80538

I just cleaned up the commit message, the patch above I merged was the same as what Jure submitted in comment 22. I hope this resolves the issue!