Forum Stats

  • 3,836,754 Users
  • 2,262,182 Discussions
  • 7,900,094 Comments

Discussions

using MySQL with OEP

887796-Oracle
887796-Oracle Member Posts: 83 Employee
edited Dec 23, 2014 8:17AM in Complex Event Processing

Hi Team,

I have an OEP running with Oracle DB but I need to move to MySQL.

Has anyone done this?

What is required?

Thanks

Best Answer

  • Sandeep Bishnoi-Oracle
    Sandeep Bishnoi-Oracle Member Posts: 25
    Answer ✓

    Here are the steps which should be used:

    1. Created a bundle jar for mysql driver(followed steps as per the OEP documentation )

        Goto $ORACLE_HOME/oep/bin in server installation directory

        Run the following script

    sh bundler.sh -source /scratch/sbishnoi/view_storage/sbishnoi_india/cep/wlevs_cql/release/target/server/oep/bin/mysql-connector-java-commercial-5.1.34-bin.jar -name mysql5 -version 5.1.34 -factory com.mysql.jdbc.jdbc2.optional.MysqlXADataSource com.mysql.jdbc.Driver -service javax.sql.XADataSource java.sql.Driver -targetdir .

    The generated jar will be in the current directory and its name will be mysql5_5.1.34.jar.

    Please note that the connector jar mysql-connector-java-commercial-5.1.34-bin.jar was part of MySQL Server installation and wasn't download it separately.

    2. Manually edit the MANIFEST.MF of newly generated jar mysql5_5.1.34.jar to remove unwanted imports which may cause you to download dependencies.

    The import section for my jar looks like as follows:

    Import-Package: javax.crypto,javax.xml.stream,javax.sql,javax.naming,javax.xml.transform.dom,javax.naming.spi,org.xml.sax,org.xml.sax.help

    ers,org.w3c.dom,javax.xml.transform.sax,javax.transaction.xa,javax.management,javax.xml.transform.stax,javax.xml.parsers,javax.xml.transfo

    rm,org.osgi.framework,javax.xml.transform.stream,javax.xml.datatype,javax.net.ssl,javax.net

    3.  Copy the driver bundle jar into following server directory inside domain directory:

    $ cp mysql5_5.1.34.jar …….user_projects/test_domain/defaultserver/modules/ext/

    Create modules/ext directories if it doesn’t exist and then copy the jar.

    4. Prior to starting server, Edit the server’s configuration to define the datasource.

    Here is  config.xml file which we tried:

    <data-source>

    <name>mysql</name>

        <data-source-params>

    <global-transactions-protocol>None</global-transactions-protocol>

    </data-source-params>

    <connection-pool-params>

    <test-table-name>SELECT 1 FROM DUAL</test-table-name>

    <initial-capacity>5</initial-capacity>

    <max-capacity>10</max-capacity>

    </connection-pool-params>

    <driver-params>

    <url>jdbc:mysql://10.159.165.60/mysql</url>

    <driver-name>com.mysql.jdbc.Driver</driver-name>

    <properties>

    <element><name>user</name><value>mysql</value></element>

    <element><name>password</name><value>mysql</value></element>

    <element><name>com.bea.core.datasource.serviceName</name><value>mysql5</value></element>

    <element><name>com.bea.core.datasource.serviceVersion</name><value>5.1.34</value></element>

    <element><name>com.bea.core.datasource.serviceObjectClass</name><value>javax.sql.XADataSource</value></element>

    </properties>

    <use-xa-data-source-interface>false</use-xa-data-source-interface>

    </driver-params>

    </data-source>

    5. Now Start the server. Make sure MySQL Database is running on the URL mentioned in config file.

    Although on server startup, you will see the following exception(not to worry about):

    [[email protected] defaultserver]$ sh startwlevs.sh

    Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=128m; support was removed in 8.0

    <Dec 19, 2014 6:39:52 AM PST> <Notice> <LibExt> <BEA-000000> <The extension library bundle "mysql5" was started successfully>

    <Dec 19, 2014 6:39:54 AM PST> <Notice> <LoggingService> <BEA-320400> <The log file /net/adc2101019/scratch/sbishnoi/view_storage/sbishnoi_india/cep/wlevs_cql/release/target/server/user_projects/test_domain/defaultserver/server.log will be rotated. Reopen the log file if tailing has stopped. This can happen on some platforms, such as Windows.>

    <Dec 19, 2014 6:39:54 AM PST> <Notice> <LoggingService> <BEA-320401> <The log file has been rotated to /net/adc2101019/scratch/sbishnoi/view_storage/sbishnoi_india/cep/wlevs_cql/release/target/server/user_projects/test_domain/defaultserver/server.log00014. Log messages will continue to be logged in /net/adc2101019/scratch/sbishnoi/view_storage/sbishnoi_india/cep/wlevs_cql/release/target/server/user_projects/test_domain/defaultserver/server.log.>

    <Dec 19, 2014 6:39:58 AM PST> <Error> <JDBC> <BEA-001112> <Test "select count(*) from SELECT 1 FROM DUAL" set up for pool "mysql" failed with exception: "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1 FROM DUAL' at line 1".>

    <Dec 19, 2014 6:40:00 AM PST> <Error> <JDBC> <BEA-001112> <Test "select count(*) from SELECT 1 FROM DUAL" set up for pool "mysql" failed with exception: "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1 FROM DUAL' at line 1".>

    <Dec 19, 2014 6:40:00 AM PST> <Error> <JDBC> <BEA-001111> <Unable to verify the test "select count(*) from SELECT 1 FROM DUAL" set up for pool "mysql". Connections will not be tested. The test will not be used by isValid either.>

    These are related to some configuration test query execution on database to ensure connectivity. The exception here is SQL syntax which is not a connectivity issue so we can safely assume that connection is obtained.

Answers

  • 887796-Oracle
    887796-Oracle Member Posts: 83 Employee

    I changed the config file of the server to use mysql driver as follows:

       <data-source>

            <name>MWEB_DS</name>

            <connection-pool-params>

                <test-table-name>SQL SELECT 1</test-table-name>

                <initial-capacity>1</initial-capacity>

                <max-capacity>2</max-capacity>

            </connection-pool-params>

            <driver-params>

                <url>jdbc:mysql://myserver/db</url>

                <driver-name>com.mysql.jdbc.Driver</driver-name>

                <properties>

                    <element>

                        <name>user</name>

                        <value>oep_call_data</value>

                    </element>

                    <element>

                        <name>password</name>

                        <value>oep_call_data</value>

                    </element>

                </properties>

            </driver-params>

        </data-source>

    but I am getting this error when the server starts (no application is deployed):

    <Dec 13, 2014 10:48:28 AM IST> <Error> <SDS> <BEA-1001100> <There was an unexpected throwable from file "OSGI-INF/datasource.xml" in bundle "com.bea.core.datasource.ce". The exception at depth 0 has message "null".>

    java.security.PrivilegedActionException: weblogic.common.resourcepool.ResourceSystemException: Cannot load driver class com.mysql.jdbc.Driver for datasource 'MWEB_DS'.

    at java.security.AccessController.doPrivileged(Native Method)

    at com.bea.core.security.privileged.internal.PrivilegedServiceImpl.doAsBootUser(PrivilegedServiceImpl.java:54)

    at com.bea.core.datasource.internal.DataSourceServiceBase.managedLifecycleInitialize(DataSourceServiceBase.java:120)

    at com.bea.core.datasource.internal.DataSourceServiceImpl.managedLifecycleInitialize(DataSourceServiceImpl.java:40)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

    at java.lang.reflect.Method.invoke(Method.java:606)

    at com.bea.core.sds.internal.ReflectionHelper.localInvoke(ReflectionHelper.java:299)

    at com.bea.core.sds.internal.ReflectionHelper.activateManagedObject(ReflectionHelper.java:1684)

    at com.bea.core.sds.internal.ServiceInstance.activateInstantiation(ServiceInstance.java:1810)

    at com.bea.core.sds.internal.ServiceInstance.access$4200(ServiceInstance.java:50)

    at com.bea.core.sds.internal.ServiceInstance$ServiceFactoryImpl.update(ServiceInstance.java:1904)

    at com.bea.core.sds.internal.ServiceInstance$ServiceFactoryImpl.access$2100(ServiceInstance.java:1847)

    at com.bea.core.sds.internal.ServiceInstance$Advertiser.activateAdvertise(ServiceInstance.java:1092)

    at com.bea.core.sds.internal.ServiceInstance$Advertiser.doAdvertise(ServiceInstance.java:1117)

    at com.bea.core.sds.internal.ServiceInstance$Advertiser.run(ServiceInstance.java:1431)

    at com.bea.core.sds.internal.SDSWorker.run(SDSWorker.java:125)

    at java.lang.Thread.run(Thread.java:745)

    Caused by: weblogic.common.resourcepool.ResourceSystemException: Cannot load driver class com.mysql.jdbc.Driver for datasource 'MWEB_DS'.

    at weblogic.jdbc.common.internal.JDBCUtil.parseException(JDBCUtil.java:322)

    at weblogic.jdbc.common.internal.ConnectionEnvFactory.loadDriver(ConnectionEnvFactory.java:82)

    at weblogic.jdbc.common.internal.ConnectionEnvFactory.<init>(ConnectionEnvFactory.java:141)

    at weblogic.jdbc.common.internal.ConnectionPool.initPooledResourceFactory(ConnectionPool.java:1108)

    at weblogic.common.resourcepool.ResourcePoolImpl.start(ResourcePoolImpl.java:226)

    at weblogic.jdbc.common.internal.ConnectionPool.doStart(ConnectionPool.java:1624)

    at weblogic.jdbc.common.internal.ConnectionPool.start(ConnectionPool.java:200)

    at weblogic.jdbc.common.internal.ConnectionPoolManager.createAndStartPool(ConnectionPoolManager.java:524)

    at weblogic.jdbc.common.internal.ConnectionPoolManager.createAndStartPool(ConnectionPoolManager.java:440)

    at com.bea.core.datasource.internal.DataSourceServiceBase$1.run(DataSourceServiceBase.java:123)

    ... 19 more

    Any ideas as for why I am getting this?

    Thanks

  • Sandeep Bishnoi-Oracle
    Sandeep Bishnoi-Oracle Member Posts: 25
    Answer ✓

    Here are the steps which should be used:

    1. Created a bundle jar for mysql driver(followed steps as per the OEP documentation )

        Goto $ORACLE_HOME/oep/bin in server installation directory

        Run the following script

    sh bundler.sh -source /scratch/sbishnoi/view_storage/sbishnoi_india/cep/wlevs_cql/release/target/server/oep/bin/mysql-connector-java-commercial-5.1.34-bin.jar -name mysql5 -version 5.1.34 -factory com.mysql.jdbc.jdbc2.optional.MysqlXADataSource com.mysql.jdbc.Driver -service javax.sql.XADataSource java.sql.Driver -targetdir .

    The generated jar will be in the current directory and its name will be mysql5_5.1.34.jar.

    Please note that the connector jar mysql-connector-java-commercial-5.1.34-bin.jar was part of MySQL Server installation and wasn't download it separately.

    2. Manually edit the MANIFEST.MF of newly generated jar mysql5_5.1.34.jar to remove unwanted imports which may cause you to download dependencies.

    The import section for my jar looks like as follows:

    Import-Package: javax.crypto,javax.xml.stream,javax.sql,javax.naming,javax.xml.transform.dom,javax.naming.spi,org.xml.sax,org.xml.sax.help

    ers,org.w3c.dom,javax.xml.transform.sax,javax.transaction.xa,javax.management,javax.xml.transform.stax,javax.xml.parsers,javax.xml.transfo

    rm,org.osgi.framework,javax.xml.transform.stream,javax.xml.datatype,javax.net.ssl,javax.net

    3.  Copy the driver bundle jar into following server directory inside domain directory:

    $ cp mysql5_5.1.34.jar …….user_projects/test_domain/defaultserver/modules/ext/

    Create modules/ext directories if it doesn’t exist and then copy the jar.

    4. Prior to starting server, Edit the server’s configuration to define the datasource.

    Here is  config.xml file which we tried:

    <data-source>

    <name>mysql</name>

        <data-source-params>

    <global-transactions-protocol>None</global-transactions-protocol>

    </data-source-params>

    <connection-pool-params>

    <test-table-name>SELECT 1 FROM DUAL</test-table-name>

    <initial-capacity>5</initial-capacity>

    <max-capacity>10</max-capacity>

    </connection-pool-params>

    <driver-params>

    <url>jdbc:mysql://10.159.165.60/mysql</url>

    <driver-name>com.mysql.jdbc.Driver</driver-name>

    <properties>

    <element><name>user</name><value>mysql</value></element>

    <element><name>password</name><value>mysql</value></element>

    <element><name>com.bea.core.datasource.serviceName</name><value>mysql5</value></element>

    <element><name>com.bea.core.datasource.serviceVersion</name><value>5.1.34</value></element>

    <element><name>com.bea.core.datasource.serviceObjectClass</name><value>javax.sql.XADataSource</value></element>

    </properties>

    <use-xa-data-source-interface>false</use-xa-data-source-interface>

    </driver-params>

    </data-source>

    5. Now Start the server. Make sure MySQL Database is running on the URL mentioned in config file.

    Although on server startup, you will see the following exception(not to worry about):

    [[email protected] defaultserver]$ sh startwlevs.sh

    Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=128m; support was removed in 8.0

    <Dec 19, 2014 6:39:52 AM PST> <Notice> <LibExt> <BEA-000000> <The extension library bundle "mysql5" was started successfully>

    <Dec 19, 2014 6:39:54 AM PST> <Notice> <LoggingService> <BEA-320400> <The log file /net/adc2101019/scratch/sbishnoi/view_storage/sbishnoi_india/cep/wlevs_cql/release/target/server/user_projects/test_domain/defaultserver/server.log will be rotated. Reopen the log file if tailing has stopped. This can happen on some platforms, such as Windows.>

    <Dec 19, 2014 6:39:54 AM PST> <Notice> <LoggingService> <BEA-320401> <The log file has been rotated to /net/adc2101019/scratch/sbishnoi/view_storage/sbishnoi_india/cep/wlevs_cql/release/target/server/user_projects/test_domain/defaultserver/server.log00014. Log messages will continue to be logged in /net/adc2101019/scratch/sbishnoi/view_storage/sbishnoi_india/cep/wlevs_cql/release/target/server/user_projects/test_domain/defaultserver/server.log.>

    <Dec 19, 2014 6:39:58 AM PST> <Error> <JDBC> <BEA-001112> <Test "select count(*) from SELECT 1 FROM DUAL" set up for pool "mysql" failed with exception: "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1 FROM DUAL' at line 1".>

    <Dec 19, 2014 6:40:00 AM PST> <Error> <JDBC> <BEA-001112> <Test "select count(*) from SELECT 1 FROM DUAL" set up for pool "mysql" failed with exception: "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1 FROM DUAL' at line 1".>

    <Dec 19, 2014 6:40:00 AM PST> <Error> <JDBC> <BEA-001111> <Unable to verify the test "select count(*) from SELECT 1 FROM DUAL" set up for pool "mysql". Connections will not be tested. The test will not be used by isValid either.>

    These are related to some configuration test query execution on database to ensure connectivity. The exception here is SQL syntax which is not a connectivity issue so we can safely assume that connection is obtained.

  • 887796-Oracle
    887796-Oracle Member Posts: 83 Employee

    Hi,

    Thanks for the answer. It is working now.

    As for the JDBC error messages I used this in my config file:

    <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name>

This discussion has been closed.