[SAK-23674] Replace the use of HSQLDB with H2 using mysql compatibility mode Created: 06-Jun-2013  Updated: 22-Jan-2018

Status: OPEN
Project: Sakai
Component/s: Config
Affects Version/s: 2.9.1
Fix Version/s: None

Type: Feature Request Priority: Major
Reporter: Charles Severance Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File SAK-23674.patch.txt     Text File h2-allow-mysql-comments.patch    
Issue Links:
Relate
is related to SAK-28053 Remove support for HSQLDB (1.8) OPEN
Property addition/change required:
Yes
CLE Team Issue:
Yes

 Description   

Current use of HSQLDB is out of date and requires additional code and SQL files in order to support it. Use of H2 would eliminate the need for the additional SQL files and would improve speed when running without MySQL or Oracle. It also allows us to test the MySQL based SQL handling.



 Comments   
Comment by Aaron Zeckoski (Inactive) [ 06-Jun-2013 ]

NOTE: simply removing hsqldb from tomcat shared/lib causes a number of failures in projects so there is hardcoded use of the hsqldb library in Sakai code (not related to hibernate though as the hibernate code can fully startup without a failure)

common/common-composite-component/src/java/defaultJdbc.properties:jdbc.driverClassName=org.hsqldb.jdbcDriver
config/configuration/bundles/src/bundle/org/sakaiproject/config/bundle/default.sakai.properties:#driverClassName@javax.sql.BaseDataSource=org.hsqldb.jdbcDriver
gradebook/app/standalone-app/src/hibernate/hibernate.properties:hibernate.connection.driver_class=org.hsqldb.jdbcDriver
help/help-component/src/webapp/WEB-INF/components.xml: <value>org.hsqldb.jdbcDriver</value>
kernel/component-manager/src/main/bundle/org/sakaiproject/config/kernel.properties:driverClassName@javax.sql.BaseDataSource=org.hsqldb.jdbcDriver
kernel/kernel-component/src/main/webapp/WEB-INF/db-components.xml: <value>org.hsqldb.jdbcDriver</value>
mailarchive/mailarchive-james/james/src/webapp/apps/james/SAR-INF/config.xml: <driver>org.hsqldb.jdbcDriver</driver>
metaobj/metaobj-impl/api-impl/src/bundle/hibernate.properties:hibernate.connection.driver_class=org.hsqldb.jdbcDriver

NOTE: startup failure was caused by help creating an in-memory database using hsqldb. I solved this by removing the HSQLDB dependency and switching it over to using H2 instead.

Comment by Charles Severance [ 06-Jun-2013 ]

It also has an Oracle emulation mode - so with a little more effort we could do some testing of the Oracle variants of out SQL syntax - that would be a nice benefit.

Comment by Aaron Zeckoski (Inactive) [ 08-Jun-2013 ]

OK, so here are my initial findings about the mysql compatibility mode (which I have used with other projects without issue). It seems that our use of mysql is a bit more complex than dspace or matterhorn so as a result we have the following issues (at least) which cause the startup to fail using H2 in MYSQL mode.
1) Like HSQLDB, H2 does not support "show columns from CONTENT_RESOURCE like 'FILE_SIZE'" (or simply... it cannot handle the LIKE in SHOW COLUMNS...) and does not have an equivalent and compatible mechanism which can check if a column exists in a table (without becoming incompatible with MYSQL). I worked around this by running a check which pulls all the columns and then checks if any are equal to "file_size".

2) H2 cannot handle the length limits - TABLE_NAME(64), RECORD_ID(128) must be rewritten to drop the length limits.
CREATE UNIQUE INDEX SAKAI_LOCKS_INDEX ON SAKAI_LOCKS ( TABLE_NAME(64), RECORD_ID(128) );
I worked around this by removing the limits since we practically never actually exceed 64 and 128 chars in the data.

3) H2 does not support COMMENT='...' in CREATE statements. I have corrected this with a patch (attached and sent to the H2 maintainers):
https://code.google.com/p/h2database/issues/detail?id=474

4) H2 does not appear to support the index/FK syntax generated by hibernate for the MySQL5 dialects. For example:
2013-06-08 18:12:31,355 WARN Thread-3 org.sakaiproject.springframework.orm.hibernate.AddableSessionFactoryBean - Unsuccessful schema statement: alter table SAM_PUBLISHEDSECUREDIP_T add index FK1EDEA25B9482C945 (ASSESSMENTID), add constraint FK1EDEA25B9482C945 foreign key (ASSESSMENTID) references SAM_PUBLISHEDASSESSMENT_T (ID)
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "ALTER TABLE SAM_PUBLISHEDSECUREDIP_T ADD INDEX FK1EDEA25B9482C945 (ASSESSMENTID),[*] ADD CONSTRAINT FK1EDEA25B9482C945 FOREIGN KEY (ASSESSMENTID) REFERENCES SAM_PUBLISHEDASSESSMENT_T (ID) "; SQL statement:
alter table SAM_PUBLISHEDSECUREDIP_T add index FK1EDEA25B9482C945 (ASSESSMENTID), add constraint FK1EDEA25B9482C945 foreign key (ASSESSMENTID) references SAM_PUBLISHEDASSESSMENT_T (ID) [42000-172]
... There are hundreds of these failures.
No solution yet.

5) H2 cannot convert string TRUE and FALSE to 0 and 1 automatically (or it does not seem to anyway)
2013-06-08 18:13:09,165 ERROR Thread-3 org.sakaiproject.component.app.scheduler.ScheduledInvocationManagerImpl - failed to schedule ScheduledInvocationRunner job
org.quartz.JobPersistenceException: Couldn't store job: Value too long for column "IS_DURABLE VARCHAR(1) NOT NULL": "'FALSE' (5)"; SQL statement:
INSERT INTO QRTZ_JOB_DETAILS (JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_VOLATILE, IS_STATEFUL, REQUESTS_RECOVERY, JOB_DATA) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-172] [See nested exception: org.h2.jdbc.JdbcSQLException: Value too long for column "IS_DURABLE VARCHAR(1) NOT NULL": "'FALSE' (5)";
No solution yet.

6) H2 cannot handle the same ALTER syntax for primary keys as MySQL:
ALTER TABLE SAKAI_CLUSTER ADD ( PRIMARY KEY (SERVER_ID) ) binds:
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement " ALTER TABLE SAKAI_CLUSTER ADD ( PRIMARY[*] KEY (SERVER_ID) ) "; expected "identifier"; SQL statement:
ALTER TABLE SAKAI_CLUSTER ADD ( PRIMARY KEY (SERVER_ID) ) [42001-172]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
at org.h2.message.DbException.get(DbException.java:169)
at org.h2.message.DbException.getSyntaxError(DbException.java:194)
at org.h2.command.Parser.readColumnIdentifier(Parser.java:2812)
at org.h2.command.Parser.parseAlterTableAddColumn(Parser.java:5032)
at org.h2.command.Parser.parseAlterTable(Parser.java:4881)
at org.h2.command.Parser.parseAlter(Parser.java:4350)
at org.h2.command.Parser.parsePrepared(Parser.java:306)
at org.h2.command.Parser.parse(Parser.java:279)
at org.h2.command.Parser.parse(Parser.java:251)
at org.h2.command.Parser.prepareCommand(Parser.java:218)
at org.h2.engine.Session.prepareLocal(Session.java:425)
at org.h2.engine.Session.prepareCommand(Session.java:374)
No solution yet.

One of these failures (or perhaps another one which is not reported in the logs) causes the BaseAuthzGroupService to end up with a null m_storage which causes the overall startup to fail.

Caused by: java.lang.NullPointerException
at org.sakaiproject.authz.impl.BaseAuthzGroupService.getAuthzGroup(BaseAuthzGroupService.java:369)
at org.theospi.portfolio.security.model.DefaultRealmManagerImpl.init(DefaultRealmManagerImpl.java:48)

Comment by Aaron Zeckoski (Inactive) [ 08-Jun-2013 ]

I am starting to think the opportunity cost for this is too high compared to the other things we could be accomplishing with this time. I am also thinking it is unlikely that the MySQL compatibility mode is going to be strong enough to be 100% compatible which means that many things will have to be handled with a settings and custom SQL like we are already doing for hsqldb.
Alternatively, we are likely to have to put a lot of hours into creating H2 patches and I am not yet confident that the H2 team is going to be responsive to getting our patches back in, We will see how things go with the first patch I have sent them.

Comment by Charles Severance [ 08-Jun-2013 ]

I agree that this is not critical - if it were easy - that would be great. My guess is that the thing that will slowly get us is keeping future versions of Hibernate working with the increasingly antique HSQLDB.

Comment by David Horwitz [ 10-Jun-2013 ]

as to 1 in your list that I think is an old conversion utility (from 2.5 if I recall) so could prob be cleaned up ...

Comment by Aaron Zeckoski (Inactive) [ 10-Jun-2013 ]

#1 is already worked around

Comment by Aaron Zeckoski (Inactive) [ 10-Jun-2013 ]

The H2 patch is now part of H2 (revision 4847, should be next release after the current which is 1.3.172)

Comment by Aaron Zeckoski (Inactive) [ 10-Jun-2013 ]

#4 stackoverflow question - http://stackoverflow.com/questions/17024482/h2-database-unsuccessful-schema-statement-when-add-index-and-foreign-key-constra

Comment by Aaron Zeckoski (Inactive) [ 17-Jun-2013 ]

Reply from H2 team:
A good place to start understanding the source is the org.h2.command.ddl package, where you'll find both the AlterTableAddConstraint class and the CreateIndex command.

BTW: my previous gave the wrong syntax for adding an index. It is:
create index FK1EDEA25B9482C945 on SAM_PUBLISHEDSECUREDIP_T (ASSESSMENTID)

On Tuesday, 11 June 2013 12:49:04 UTC+2, Steve McLeod wrote:
H2 doesn't currently support adding an index AND a constraint in one SQL statement. H2 needs two statements, which would be

alter table SAM_PUBLISHEDSECUREDIP_T
add index FK1EDEA25B9482C945 (ASSESSMENTID)

and
alter table SAM_PUBLISHEDSECUREDIP_T
add constraint FK1EDEA25B9482C945 foreign key (ASSESSMENTID)
references SAM_PUBLISHEDASSESSMENT_T (ID)
It would be nice if you can create a patch to fix this. I believe it would be relatively straight-forward once you get familiar with how H2 handles "alter table".

Comment by Aaron Zeckoski (Inactive) [ 01-Jul-2013 ]

Putting this ticket off for now

Generated at Wed Sep 18 13:45:46 CDT 2019 using Jira 8.0.3#800011-sha1:073e8b433c2c0e389c609c14a045ffa7abaca10d.