Bug 794386 (JBEPP-1430)

Can you be more specific about what you would add to:
http://docs.redhat.com/docs/en-US/JBoss_Enterprise_Portal_Platform/5.1/html-single/Installation_Guide/index.html#sect-Reference_Guide-Database_Configuration ?

Beside a warning note that the 2 DB must be different ?

Yes, a warning note would be great.

Also would be great if we have more database samples. With postgres, for example, we can't simply use schemas for each datasource(specifying the schema using "new-connection-sql"), we have to use 2 databases. 

Thanks.

Release Notes Docs Status: Added: Not Required
Affects: Added: Release Notes

Release Notes Text: Added: CAUSE:

CONSEQUENCE:

FIX:

RESULT:

Package: JBoss_Enterprise_Portal_Platform-Installation_Guide-5.2-web-en-US-5.2.1-1.el6eng

Task Info: https://brewweb.devel.redhat.com//taskinfo?taskID=4057523

Changelog:
* Mon Feb 20 2012 Jared Morgan <> - 5.2.1-1
- Incorporated changes to https://bugzilla.redhat.com/show_bug.cgi?id=794386.

Link: 
http://documentation-stage.bne.redhat.com/docs/en-US/JBoss_Enterprise_Portal_Platform/5.2/html-single/Installation_Guide/index.html#sect-Reference_Guide-Database_Configuration

In "Task: Create Database Tables" I have added steps that ensure customers create two separate databases.

In "Task: Configure the JCR Database Datasource" and "Task: Configure the JCR Database Datasource", I have added an Important admonition that prompts users to ensure they specify the correct database relevant to the procedure.

Customers can get the required connection-url and driver-class info from the database and JDBC documentation. We should not duplicate this information, because it may change and then our guide would be incorrect by proxy.

I have clarified the EAP guide (Getting Started Guide) that contains some DB config examples. There is no use repeating information between platforms, particularly when the EAP is what everything runs on anyhow ;)

Remarks enabled to identify sections changes. Review required on all work.

    Technical note added. If any revisions are required, please edit the "Technical Notes" field
    accordingly. All revisions will be proofread by the Engineering Content Services team.
    
    New Contents:
It was discovered that there was insufficient information in the Installation Guide regarding how to replace the default Hypersonic Database (HSQLDB) with a certified database that Red Hat supports. Detailed steps have been added to the Database Configuration section of the guide, which explain how to replace HSQLDB, and the requirements JBoss Enterprise Portal Platform has regarding separate databases for JCR and IDM.

"You must create a separate database for both the JCR and IDM services. If you do not create a separate database for each service, database table conflicts will occur and JBoss Enterprise Portal Platform will not start." It's not because of table conflicts, it's more about connection pool issues. Also AFAIK it will start but not perform well. I would stick with the fact that they have to be different.

Also there is a stupidity that comes from legacy support. 
The JNDI Names depend if you have 1 or n portal containers, if you just have one it would be like mentioned in the doc:

<jndi-name>gatein-idm</jndi-name>
        <connection-url>jdbc:mysql://mysql-hostname:3306/gateinidm</connection-url>

If you have 2 or more then you need to add the name of portal container in the JNDI Name with a suffix, gatein-idm becomes gatein-idm_portal (same for gatein-jcr) and the databases must then be different.

here are the datasources for 1 portal container (like when you just install EPP)
<datasources>
    <no-tx-datasource>
        <jndi-name>gatein-idm</jndi-name>
        <connection-url>jdbc:mysql://mysql-hostname:3306/gateinidm</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>gateinuser</user-name>
        <password>gateinpassword</password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <idle-timeout-minutes>0</idle-timeout-minutes>
        <prepared-statement-cache-size>32</prepared-statement-cache-size>
    </no-tx-datasource>

    <local-tx-datasource>
        <jndi-name>gatein-jcr</jndi-name>
        <connection-url>jdbc:mysql://mysql-hostname:3306/gateinjcr</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>gateinuser</user-name>
        <password>gateinpassword</password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <idle-timeout-minutes>0</idle-timeout-minutes>
        <prepared-statement-cache-size>32</prepared-statement-cache-size>
    </local-tx-datasource>
</datasources>

here is for 2, where portal container /portal and /customer are used:
<datasources>
    <no-tx-datasource>
        <jndi-name>gatein-idm_portal</jndi-name>
        <connection-url>jdbc:mysql://mysql-hostname:3306/gateinidm1</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>gateinuser</user-name>
        <password>gateinpassword</password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <idle-timeout-minutes>0</idle-timeout-minutes>
        <prepared-statement-cache-size>32</prepared-statement-cache-size>
    </no-tx-datasource>

    <local-tx-datasource>
        <jndi-name>gatein-jcr_portal</jndi-name>
        <connection-url>jdbc:mysql://mysql-hostname:3306/gateinjcr1</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>gateinuser</user-name>
        <password>gateinpassword</password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <idle-timeout-minutes>0</idle-timeout-minutes>
        <prepared-statement-cache-size>32</prepared-statement-cache-size>
    </local-tx-datasource>

    <no-tx-datasource>
        <jndi-name>gatein-idm_customer</jndi-name>
        <connection-url>jdbc:mysql://mysql-hostname:3306/gateinidm2</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>gateinuser</user-name>
        <password>gateinpassword</password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <idle-timeout-minutes>0</idle-timeout-minutes>
        <prepared-statement-cache-size>32</prepared-statement-cache-size>
    </no-tx-datasource>

    <local-tx-datasource>
        <jndi-name>gatein-jcr_customer</jndi-name>
        <connection-url>jdbc:mysql://mysql-hostname:3306/gateinjcr2</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>gateinuser</user-name>
        <password>gateinpassword</password>

        <min-pool-size>5</min-pool-size>
        <max-pool-size>20</max-pool-size>
        <idle-timeout-minutes>0</idle-timeout-minutes>
        <prepared-statement-cache-size>32</prepared-statement-cache-size>
    </local-tx-datasource>
</datasources>

Note that the database names don't matter, only the jndi-name do.

Thomas provided excellent info in a mailing list relating to this bug:

<snip>
you need (IDM + JCR databases) * (NB portal container).

When you install SP on top of EPP you add 1 portal container (you have
http://localhost:8080/portal and http://localhost:8080/ecmdemo)

The datasource descriptors for SP are included in the SP package

</snip>

I have incorporated this info into the Datasource Configuration section.

(In reply to comment #8)
> "You must create a separate database for both the JCR and IDM services. If you
> do not create a separate database for each service, database table conflicts
> will occur and JBoss Enterprise Portal Platform will not start." It's not
> because of table conflicts, it's more about connection pool issues. Also AFAIK
> it will start but not perform well. I would stick with the fact that they have
> to be different.

No problem. I have cut down that sentence to imply "Just have separate databases, OK"

> 
> Also there is a stupidity that comes from legacy support. 
> The JNDI Names depend if you have 1 or n portal containers, if you just have
> one it would be like mentioned in the doc:
> 
> <jndi-name>gatein-idm</jndi-name>
>        
> <connection-url>jdbc:mysql://mysql-hostname:3306/gateinidm</connection-url>
> 
> If you have 2 or more then you need to add the name of portal container in the
> JNDI Name with a suffix, gatein-idm becomes gatein-idm_portal (same for
> gatein-jcr) and the databases must then be different.
> 
> here are the datasources for 1 portal container (like when you just install
> EPP)
> <datasources>
>     <no-tx-datasource>
>         <jndi-name>gatein-idm</jndi-name>
>        
> <connection-url>jdbc:mysql://mysql-hostname:3306/gateinidm</connection-url>
>         <driver-class>com.mysql.jdbc.Driver</driver-class>
>         <user-name>gateinuser</user-name>
>         <password>gateinpassword</password>
> 
>         <min-pool-size>5</min-pool-size>
>         <max-pool-size>20</max-pool-size>
>         <idle-timeout-minutes>0</idle-timeout-minutes>
>         <prepared-statement-cache-size>32</prepared-statement-cache-size>
>     </no-tx-datasource>
> 
>     <local-tx-datasource>
>         <jndi-name>gatein-jcr</jndi-name>
>        
> <connection-url>jdbc:mysql://mysql-hostname:3306/gateinjcr</connection-url>
>         <driver-class>com.mysql.jdbc.Driver</driver-class>
>         <user-name>gateinuser</user-name>
>         <password>gateinpassword</password>
> 
>         <min-pool-size>5</min-pool-size>
>         <max-pool-size>20</max-pool-size>
>         <idle-timeout-minutes>0</idle-timeout-minutes>
>         <prepared-statement-cache-size>32</prepared-statement-cache-size>
>     </local-tx-datasource>
> </datasources>
> 
> here is for 2, where portal container /portal and /customer are used:
> <datasources>
>     <no-tx-datasource>
>         <jndi-name>gatein-idm_portal</jndi-name>
>        
> <connection-url>jdbc:mysql://mysql-hostname:3306/gateinidm1</connection-url>
>         <driver-class>com.mysql.jdbc.Driver</driver-class>
>         <user-name>gateinuser</user-name>
>         <password>gateinpassword</password>
> 
>         <min-pool-size>5</min-pool-size>
>         <max-pool-size>20</max-pool-size>
>         <idle-timeout-minutes>0</idle-timeout-minutes>
>         <prepared-statement-cache-size>32</prepared-statement-cache-size>
>     </no-tx-datasource>
> 
>     <local-tx-datasource>
>         <jndi-name>gatein-jcr_portal</jndi-name>
>        
> <connection-url>jdbc:mysql://mysql-hostname:3306/gateinjcr1</connection-url>
>         <driver-class>com.mysql.jdbc.Driver</driver-class>
>         <user-name>gateinuser</user-name>
>         <password>gateinpassword</password>
> 
>         <min-pool-size>5</min-pool-size>
>         <max-pool-size>20</max-pool-size>
>         <idle-timeout-minutes>0</idle-timeout-minutes>
>         <prepared-statement-cache-size>32</prepared-statement-cache-size>
>     </local-tx-datasource>
> 
>     <no-tx-datasource>
>         <jndi-name>gatein-idm_customer</jndi-name>
>        
> <connection-url>jdbc:mysql://mysql-hostname:3306/gateinidm2</connection-url>
>         <driver-class>com.mysql.jdbc.Driver</driver-class>
>         <user-name>gateinuser</user-name>
>         <password>gateinpassword</password>
> 
>         <min-pool-size>5</min-pool-size>
>         <max-pool-size>20</max-pool-size>
>         <idle-timeout-minutes>0</idle-timeout-minutes>
>         <prepared-statement-cache-size>32</prepared-statement-cache-size>
>     </no-tx-datasource>
> 
>     <local-tx-datasource>
>         <jndi-name>gatein-jcr_customer</jndi-name>
>        
> <connection-url>jdbc:mysql://mysql-hostname:3306/gateinjcr2</connection-url>
>         <driver-class>com.mysql.jdbc.Driver</driver-class>
>         <user-name>gateinuser</user-name>
>         <password>gateinpassword</password>
> 
>         <min-pool-size>5</min-pool-size>
>         <max-pool-size>20</max-pool-size>
>         <idle-timeout-minutes>0</idle-timeout-minutes>
>         <prepared-statement-cache-size>32</prepared-statement-cache-size>
>     </local-tx-datasource>
> </datasources>
> 
> Note that the database names don't matter, only the jndi-name do.

No problem. I've taken this information and broken up the Database Configuration section into Single and Multiple portal container instruction sets.

Build info to follow.

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