Error during startup of Registry when MySQL is configured as the database

Hi,
 

I have installed wso2registry on Red Hat system. I get MySql related errors.
 
I get a blank browser screen and an error in Tomcat  ./apache-tomcat-5.5.26/logs/catalina.out file:
 
—----------------------------------------------------------------
[ERROR] 3 Servlet /wso2registry threw load() exception

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'wso2registry.um_roles' doesn't exist

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1026)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)

        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)

        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)

        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)

        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)

        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)

        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)

        at org.wso2.usermanager.readwrite.DefaultUserStoreReader.getAllRoleNames(DefaultUserStoreReader.java:74)

        at org.wso2.registry.utils.AuthorizationUtil.populateUserStore(AuthorizationUtil.java:101)

        at org.wso2.registry.jdbc.realm.RegistryRealm.init(RegistryRealm.java:65)

        at org.wso2.registry.jdbc.realm.RegistryRealm.<init>(RegistryRealm.java:36)

        at org.wso2.registry.servlet.RegistryServlet.init(RegistryServlet.java:118)

        at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1139)...

—----------------------------------------------------------------
 

The message suggests that Registry application during the initialisation step is expecting some table names ('wso2registry.um_roles' ) to be in the lower case. The table 'wso2registry.um_roles' exists in the database with the name 'wso2registry.UM_ROLES'.

 
The steps I have followed:
 
a) Software used:

- Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
- mysql  Ver 14.12 Distrib 5.0.17c, for redhat-linux-gnu (i386) using readline 5.0
b) I have used ./wso2registry-1.0/database-scripts/mysql-complete.sql script to create schema. I have used MySql Control Centre 0.9.4-beta to execute the script. When viewed in MySql Control Centre all the tables have upper case names.
 
c) I have configured registry.xml file to point to MySql database:
 —----------------------------------------------------------------
<wso2regsitry>

 <currentConfig>mysql-db</currentConfig>

<dbconfig name="mysql-db">

  <url>jdbc:mysql://localhost:3306/wso2registry</url>

  <userName>regadmin</userName>

  <password>password</password>

  <driverName>com.mysql.jdbc.Driver</driverName>

 </dbconfig>

...

—----------------------------------------------------------------
 
I have started Tomcat and pointed the browser Firefox 2.0.0.13 at the http://serverIpAddress:8080/wso2registry/web
 
3. The Fix.
 
Experimenting, I have found that all um_* tables in the schema need to be in a lower case. I have created the following script which worked (is appended below).
 
I have re-created the database and restarted the application with no errors. If you convert the whole sql script into the lower case you get errors as well, e.g 'wso2registry.ARTIFACTS' table does not exist.
 
Regards, Jurek
 
—----------------------------------------------------------------
CREATE TABLE ARTIFACTS (AID INTEGER AUTO_INCREMENT,

                PATH VARCHAR (500) NOT NULL,

                MEDIA_TYPE VARCHAR (500),

                DIRECTORY INTEGER NOT NULL,

                STATE INTEGER,

                AUTHOR VARCHAR (500),

                CREATED_TIME TIMESTAMP,

                DESCRIPTION VARCHAR (10000),

                PRIMARY KEY (AID),

                UNIQUE(PATH));
 
CREATE TABLE PROPERTIES (P_ID INTEGER AUTO_INCREMENT,

                AID INTEGER NOT NULL,

                PKEY VARCHAR (100) NOT NULL,

                PVALUE VARCHAR (500) NOT NULL,

                PRIMARY KEY (P_ID),

                UNIQUE (AID, PKEY),

                FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID));
 
CREATE TABLE CHILDREN (CID INTEGER AUTO_INCREMENT,

            PARENT_ID INTEGER NOT NULL,

            CHILD_ID INTEGER NOT NULL,

            PRIMARY KEY (CID),

            UNIQUE (PARENT_ID, CHILD_ID),

            FOREIGN KEY (PARENT_ID) REFERENCES ARTIFACTS (AID),

            FOREIGN KEY (CHILD_ID) REFERENCES ARTIFACTS (AID));
 
CREATE TABLE TAGS (TN_ID INTEGER AUTO_INCREMENT,

            TAG_NAME VARCHAR (500) NOT NULL,

            AID INTEGER NOT NULL,

            USER_ID VARCHAR (20) NOT NULL,

            TAGGED_TIME TIMESTAMP NOT NULL,

            PRIMARY KEY (TN_ID),

            UNIQUE (TAG_NAME, AID, USER_ID),

            FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID));
 
CREATE TABLE COMMENTS (CM_ID INTEGER AUTO_INCREMENT,

            AID INTEGER NOT NULL,

            USER_ID VARCHAR (20) NOT NULL,

            COMMENT_TEXT VARCHAR (500) NOT NULL,

            COMMENTED_TIME TIMESTAMP NOT NULL,

            PRIMARY KEY (CM_ID),

            FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID));
 
CREATE TABLE LOGS (LOG_ID INTEGER AUTO_INCREMENT,

            RID INTEGER,

            USER_ID VARCHAR (20) NOT NULL,

            LOGGED_TIME TIMESTAMP NOT NULL,

            ACTION INTEGER NOT NULL,

            ACTION_DATA VARCHAR (500),

            PRIMARY KEY (LOG_ID));
 
CREATE TABLE RATINGS (R_ID INTEGER AUTO_INCREMENT,

            AID INTEGER NOT NULL,

            USER_ID VARCHAR (20) NOT NULL,

            RATING INTEGER NOT NULL,

            RATED_TIME TIMESTAMP NOT NULL,

            PRIMARY KEY (R_ID),

            FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID));
 
CREATE TABLE VERSIONS (

            AID INTEGER NOT NULL,

            VN INTEGER NOT NULL,

            CONTENT LONGBLOB,

            AUTHOR VARCHAR (500),

            UPDATED_TIME TIMESTAMP,

            UNIQUE (AID,VN),

            FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID));
 
CREATE TABLE DEPENDENCY (AID INTEGER NOT NULL,

                          VN INTEGER NOT NULL,

                          DAID INTEGER NOT NULL,

                          DVN INTEGER NOT NULL,

                          UNIQUE (AID,VN,DAID,DVN),

                          FOREIGN KEY (DAID) REFERENCES ARTIFACTS (AID),

                          FOREIGN KEY (AID) REFERENCES ARTIFACTS (AID));
 
create table um_users (id varchar(255), user_name varchar(255) not null unique, password varchar(255) not null, primary key (id));

create table um_user_attributes (id varchar(255), attr_name varchar(255) not null, attr_value varchar(255), user_id varchar(255), foreign key (user_id) references um_users(id) on delete cascade, primary key (id));

create table um_roles (id varchar(255), role_name varchar(255) not null unique, primary key (id));

create table um_role_attributes (id varchar(255), attr_name varchar(255) not null, attr_value varchar(255), role_id varchar(255), foreign key (role_id) references um_roles(id) on delete cascade, primary key (id));

create table um_permissions (id varchar(255), resource_id varchar(255) not null, action varchar(255) not null, primary key (id));

create table um_role_permissions (id varchar(255), permission_id varchar(255), role_id varchar(255), is_allowed smallint not null, foreign key (permission_id) references um_permissions(id) on delete  cascade, foreign key (role_id) references um_roles(id) on delete cascade, primary key (id));

create table um_user_permissions (id varchar(255), is_allowed smallint not null, permission_id varchar(255), user_id varchar(255), foreign key (permission_id) references um_permissions(id) on delete cascade, foreign key (user_id) references um_users(id) on delete cascade, primary key (id));

create table um_user_roles (id varchar(255), role_id varchar(255) not null, user_id varchar(255) not null, unique (user_id, role_id), foreign key (role_id) references um_roles(id) on delete cascade, foreign key (user_id) references um_users(id) on delete cascade, primary key (id));
—-------------End--Koniec--Fin---------------------------------------