0

I want to change my database and set it to mysql I go through How to migrate from H2 to MySQL but I couldn't connect to any consols!

here is the changase made to master-datasources.xml:

       <datasource>
           <name>WSO2_EMM_DB</name>
           <description>The datasource used for EMM</description>
           <jndiConfig>
              <name>jdbc/WSO2EMMDB</name>
           </jndiConfig>
           <definition type="RDBMS">
              <configuration>
                 <url>jdbc:mysql://localhost:3306/WSO2EMM_DB</url> // I also try by setting ip address
                 <username>admin</username>
                 <password>******</password>
                 <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                 <maxActive>50</maxActive>
                 <maxWait>60000</maxWait>
                 <testOnBorrow>true</testOnBorrow>
                 <validationQuery>SELECT 1</validationQuery>
                 <validationInterval>30000</validationInterval>
              </configuration>
           </definition>
        </datasource>

The WSO2_CARBON_DB and WSO2AM_DB are also edited like WSO2AM_DB.

I check the databases, and all of them are created, here is the image.

Server is runing on win 7 and mysql version is 5.6 .

Here is the carbon log and the error in the web browser.

Edit:

Here is some part of carbon log:

 {org.wso2.carbon.user.core.util.DatabaseUtil} -  Database Error - Unknown database 'wso2carbon_db' {org.wso2.carbon.user.core.util.DatabaseUtil} com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'wso2carbon_db'   
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)    
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)     
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)     
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)  
at com.mysql.jdbc.Util.handleNewInstance(Util.java:353)
...

Edit 2:

here is the master-datasources.xml with changes:

<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">
<providers>
    <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
</providers>

<datasources>

    <datasource>
       <name>wso2_emm_db</name>
       <description>The datasource used for EMM</description>
       <jndiConfig>
          <name>jdbc/WSO2EMMDB</name>
       </jndiConfig>
       <definition type="RDBMS">
          <configuration>
             <url>jdbc:mysql://localhost:3306/wso2_emm_db</url>
             <username>admin</username>
             <password>123456</password>
             <driverClassName>com.mysql.jdbc.Driver</driverClassName>
             <maxActive>50</maxActive>
             <maxWait>60000</maxWait>
             <testOnBorrow>true</testOnBorrow>
             <validationQuery>SELECT 1</validationQuery>
             <validationInterval>30000</validationInterval>
          </configuration>
       </definition>
    </datasource>

    <datasource>
       <name>wso2_carbon_db</name>
       <description>The datasource used for registry and user manager</description>
       <jndiConfig>
          <name>jdbc/WSO2CarbonDB</name>
       </jndiConfig>
       <definition type="RDBMS">
          <configuration>
             <url>jdbc:mysql://localhost:3306/wso2_carbon_db</url>
             <username>admin</username>
             <password>123456</password>
             <driverClassName>com.mysql.jdbc.Driver</driverClassName>
             <maxActive>50</maxActive>
             <maxWait>60000</maxWait>
            <!-- <minIdle>5</minIdle>-->
             <testOnBorrow>true</testOnBorrow>
             <validationQuery>SELECT 1</validationQuery>
             <validationInterval>30000</validationInterval>
          </configuration>
       </definition>
    </datasource>

    <datasource>
        <name>WSO2_IDENTITY_DB</name>
        <description>The datasource used for Identity configurations</description>
        <jndiConfig>
            <name>jdbc/WSO2IdentityDB</name>
        </jndiConfig>
        <definition type="RDBMS">
            <configuration>
                <url>jdbc:h2:repository/database/WSO2IDENTITY_DB;DB_CLOSE_ON_EXIT=FALSE</url>
                <username>wso2carbon</username>
                <password>wso2carbon</password>
                <driverClassName>org.h2.Driver</driverClassName>
                <maxActive>50</maxActive>
                <maxWait>60000</maxWait>
                <testOnBorrow>true</testOnBorrow>
                <validationQuery>SELECT 1</validationQuery>
                <validationInterval>30000</validationInterval>
            </configuration>
        </definition>
    </datasource>

    <datasource>
        <name>SOCIAL_CACHE</name>
        <description>The datasource used for storing the cached social objects.</description>
        <jndiConfig>
            <name>jdbc/test</name>
        </jndiConfig>
        <definition type="RDBMS">
            <configuration>
                <url>jdbc:h2:repository/database/WSO2SOCIAL_CACHE_DB;DB_CLOSE_ON_EXIT=FALSE</url>
                <username>wso2carbon</username>
                <password>wso2carbon</password>
                <driverClassName>org.h2.Driver</driverClassName>
                <maxActive>50</maxActive>
                <maxWait>60000</maxWait>
            </configuration>
        </definition>
    </datasource>

    <datasource>
        <name>SOCIAL_CASSANDRA_DB</name>
        <description>The cassandra  datasource used for storing social activities</description>
        <definition type="RDBMS">
            <configuration>
                <url>jdbc:cassandra://localhost:9160/EVENT_KS</url>
                <username>admin@admin.com</username>
                <password>admin</password>
                <driverClassName>org.apache.cassandra.cql.jdbc.CassandraDriver</driverClassName>
            </configuration>
        </definition>
    </datasource>

    <datasource>
       <name>wso2am_db</name>
       <description>The datasource used for API Manager database</description>
       <jndiConfig>
          <name>jdbc/WSO2AM_DB</name>
       </jndiConfig>
       <definition type="RDBMS">
          <configuration>
             <!-- add line below -->
             <defaultAutoCommit>false</defaultAutoCommit>
             <url>jdbc:mysql://localhost:3306/wso2am_db</url>
             <username>admin</username>
             <password>123456</password>
             <driverClassName>com.mysql.jdbc.Driver</driverClassName>
             <maxActive>50</maxActive>
             <maxWait>60000</maxWait>
             <testOnBorrow>true</testOnBorrow>
             <validationQuery>SELECT 1</validationQuery>
             <validationInterval>30000</validationInterval>
          </configuration>
       </definition>
    </datasource>

    <datasource>
        <name>JAGH2</name>
        <description>The datasource used for by the Jaggery Storage Manager</description>
        <jndiConfig>
            <name>jdbc/test</name>
        </jndiConfig>
        <definition type="RDBMS">
            <configuration>
                <url>jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000</url>
                <username>sa</username>
                <password></password>
                <driverClassName>org.h2.Driver</driverClassName>
                <maxActive>50</maxActive>
                <maxWait>60000</maxWait>
            </configuration>
        </definition>
    </datasource>
</datasources>

Edit 3:

No need to change user-mgt.xml and registry xml so I remove them from the question. After this change I restart the server now the consols loaded but I couldn't log in to emm consol I get error 500 : 

192.168.1.157 - - [28/Jul/2015:19:05:17 +0430] "POST /emm/acs HTTP/1.1" 500 1042 "https://192.168.1.157:9443/commonauth" "Mozilla/5.0 (Windows NT 6.1; WOW64) 

AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36"

But I can log in to publisher and store consols.

here is the full log for http-access and wso2carbon after restarting.

Edit 4: I change the master-datasources.xml in edit 2 , and here is the databases

Community
  • 1
  • 1
user3806649
  • 1,257
  • 2
  • 18
  • 42

2 Answers2

1

Seems like your configuration has naming issues. Can we follow these steps?

  1. Get a fresh EMM pack.
  2. Create 4 empty mysql databases using exactly these names, WSO2AM_DB, WSO2CARBON_DB,WSO2EMM_DB and WSO2IDENTITY_DB.
  3. Replace your master-datasources.xml file with the attached file[1] in this answer.
  4. Change mysql username and password to match yours.
  5. Run the pack using "sh ./wso2server.sh -Dsetup" command.

You should be able to resolve your issues by following these. Otherwise it's a waste of time trying to fix the issues in your existing pack.

[1] - MASTER-DATASOURCES.XML File 

<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">
    <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
    </providers>

    <datasources>
        <datasource>
            <name>WSO2_EMM_DB</name>
            <description>The datasource used for EMM</description>
            <jndiConfig>
                <name>jdbc/WSO2EMMDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/WSO2EMM_DB</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
        <datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/WSO2CARBON_DB</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>WSO2_IDENTITY_DB</name>
            <description>The datasource used for Identity configurations</description>
            <jndiConfig>
                <name>jdbc/WSO2IdentityDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/WSO2IDENTITY_DB</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>SOCIAL_CACHE</name>
            <description>The datasource used for storing the cached social objects.</description>
            <jndiConfig>
                <name>jdbc/test</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:h2:repository/database/WSO2SOCIAL_CACHE_DB;DB_CLOSE_ON_EXIT=FALSE</url>
                    <username>wso2carbon</username>
                    <password>wso2carbon</password>
                    <driverClassName>org.h2.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>SOCIAL_CASSANDRA_DB</name>
            <description>The cassandra  datasource used for storing social activities</description>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:cassandra://localhost:9160/EVENT_KS</url>
                    <username>admin@admin.com</username>
                    <password>admin</password>
                    <driverClassName>org.apache.cassandra.cql.jdbc.CassandraDriver</driverClassName>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>WSO2AM_DB</name>
            <description>The datasource used for API Manager database</description>
            <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/WSO2AM_DB</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
      <defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
            </definition>
        </datasource>

        <datasource>
            <name>JAGH2</name>
            <description>The datasource used for by the Jaggery Storage Manager</description>
            <jndiConfig>
                <name>jdbc/test</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000</url>
                    <username>sa</username>
                    <password></password>
                    <driverClassName>org.h2.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                </configuration>
            </definition>
        </datasource>
    </datasources>
</datasources-configuration>

Thanks

Kasun Delgolla
  • 219
  • 1
  • 8
  • It succesfully installed and now I can access to consols. but there is a problem with sql syntax. here is the carbon [log](http://s000.tinyupload.com/?file_id=26457478361217155213) – user3806649 Jul 31 '15 at 15:15
  • sql syntax error was because of j connector. now I get error in enrollment process when I enter a pin code and accept it. here is the [log](https://ceit.aut.ac.ir/~9031020/enrollment-error.txt) – user3806649 Jul 31 '15 at 16:09
  • I find same problem in [here](http://stackoverflow.com/questions/24328578/mysql-throws-unknown-column-mac-in-field-list), as I already create tables, I change table name using this 'ALTER TABLE devices CHANGE wifi_mac mac varchar(100)' ! – user3806649 Jul 31 '15 at 19:11
  • Can you post the result of "DESCRIBE devices;"? – Kasun Delgolla Aug 03 '15 at 05:14
  • Seems like you don't have any issue with that. It should work with the configuration you have. Please make sure whether you point to the exact database which you made this change. If you can not resolve it, please share your EMM_HOME/repository/deployment/server/jaggeryapps/emm/sqlscripts/db.js as well. – Kasun Delgolla Aug 03 '15 at 09:16
0

You might be missing WSO2CARBON_DB in your DB server or you might not installed the MySQL JDBC driver. Please follow [1] to get your configuration corrected.

[1]- https://docs.wso2.com/display/EMM110/Setting+up+MySQL

Thanks

Kasun Delgolla
  • 219
  • 1
  • 8
  • You mean WSO2_CARBON_DB? I created 3 databases, and I check it. here is the [screen shot](http://tinypic.com/view.php?pic=15cdq2g&s=8#.VbdCbiuUfeI) and also check for j/connector in mysql installer. ([here](http://tinypic.com/r/2w5kayq/8) is this screen shot) and the jar file is located in Carbone_Home\repository\components\lib\mysql-connector-java.jar – user3806649 Jul 28 '15 at 08:54
  • 1
    The reason is, you don't have WSO2CARBON_DB. It's case sensitive. So, go to your DB configurations (masterdatasources.xml) in repository/conf/datasources, and rename WSO2CARBON_DB to wso2_carbon_db and do the same to the others as well. – Kasun Delgolla Jul 28 '15 at 09:03
  • As I see, all your DB names are different in the config file and the actual DB. So rename your config file (repository/conf/datasources/master-datasources.xml) as well as user-mgt.xml and registry.xml to match the actual databases. – Kasun Delgolla Jul 28 '15 at 09:06
  • I change datasource 's name to db name with lowercase in master-datasources.xm and also change jdbc/WSO2CarbonDB to jdbc/wso2_carbon_db in user-mgt.xml and registry.xml, and restart the service, but still I couldn't load consols, here is the Error: TID: [0] [EMM] [2015-07-28 14:17:34,933] ERROR `{org.wso2.carbon.user.core.internal.Activator} - Cannot start User Manager Core bundle {org.wso2.carbon.user.core.internal.Activator} java.lang.RuntimeException: Error in looking up data source: Name [wso2_carbon_db] is not bound in this Context. Unable to find [wso2_carbon_db]`. – user3806649 Jul 28 '15 at 09:54
  • Can you share your conf/user-mgt.xml, conf/datasources/master-datasources.xml and conf/registry xml ? – Kasun Delgolla Jul 28 '15 at 10:00
  • Also I check my [screen shot](http://tinypic.com/r/9an87t/8) when creating DB all of them are in uppercase – user3806649 Jul 28 '15 at 10:32
  • Still the JDBC URLs are wrong. Check your master-datasources.xml, each JDBC URL should point to the exact DB name. – Kasun Delgolla Jul 28 '15 at 10:53
  • I change the url but still I get same error. I also change master-datasources.xml in question . should I also change under ? – user3806649 Jul 28 '15 at 12:19
  • I think the better option would be to run a grep from and check if there any places which uses 'wso2carbon_db', and replace them with 'WSO2CarbonDB', usually 'WSO2CarbonDB' is what used by default and it may be searched at several places at statup. – Chamila Wijayarathna Jul 28 '15 at 12:37
  • @ChamilaWijayarathna, How can I do this? I run `dir | findstr wso2carbon_db` in Carbon_Home, but notting listed! – user3806649 Jul 28 '15 at 15:05
  • grep -rn "wso2carbon_db" – Chamila Wijayarathna Jul 28 '15 at 15:39
  • @ChamilaWijayarathna, I use windows machine – user3806649 Jul 28 '15 at 18:42
  • According to your latest logs, what I see is you still haven't given the correct name for WSO2EMM_DB. Please double check your master-datasources.xml and your DB list to see you have the names correctly. If you can't find a difference, please post them. If you can access store and publisher, that means you have your CARBON_DB configured correctly. Now the issue is with WSO2EMM_DB. We can focus only about that configuration. – Kasun Delgolla Jul 30 '15 at 06:01
  • @KasunDelgolla, the master-datasources.xml in edit 2 is last version and also I update the db list. which tag I should change? – user3806649 Jul 30 '15 at 06:27