New MySQL database loading method seems to be missing files..

[lhoekenga]

I've updated to the overlay based on MitreID 1.3.x, and I'm trying to use an external SQL backend (MySQL).

It's having problems loading the tables...

2017-10-20 11:04:36,000 - ERROR [org.springframework.web.context.ContextLoader:351] - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.jdbc.datasource.init.DataSourceInitializer#0': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.CannotReadScriptException: Cannot read SQL script from ServletContext resource [/usr/local/shibboleth-idp/conf/schema/mysql/loading_temp_tables.sql]; nested exception is java.io.FileNotFoundException: Could not open ServletContext resource [/usr/local/shibboleth-idp/conf/schema/mysql/loading_temp_tables.sql]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1578)
Caused by: org.springframework.jdbc.datasource.init.CannotReadScriptException: Cannot read SQL script from ServletContext resource [/usr/local/shibboleth-idp/conf/schema/mysql/loading_temp_tables.sql]; nested exception is java.io.FileNotFoundException: Could not open ServletContext resource [/usr/local/shibboleth-idp/conf/schema/mysql/loading_temp_tables.sql]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:453)
Caused by: java.io.FileNotFoundException: Could not open ServletContext resource [/usr/local/shibboleth-idp/conf/schema/mysql/loading_temp_tables.sql]
    at org.springframework.web.context.support.ServletContextResource.getInputStream(ServletContextResource.java:141)

loading_template_tables.sql is referenced in system/conf/oidc-protocol-storage.xml:

<jdbc:script location="%{idp.home}/conf/schema/%{oidc.db.schema.type}/loading_temp_tables.sql"/>

The comment from MitreID 1.3's loading_template_tables.sql says:

-- Temporary tables used during the bootstrapping process to safely load users and clients. -- These are not needed if you're not using the users.sql/clients.sql files to bootstrap the database.

It looks like either loading_template_tables.sql should be added, or these should be commented out in system/conf/oidc-protocol-storage.xml:

        <jdbc:script location="%{idp.home}/conf/schema/%{oidc.db.schema.type}/loading_temp_tables.sql"/>
        <jdbc:script location="%{idp.home}/conf/schema/%{oidc.db.schema.type}/scopes.sql"/>
        <jdbc:script location="%{idp.home}/conf/schema/%{oidc.db.schema.type}/clients.sql"/>

[lhoekenga]

The insert statements in scopes.sql also assumes a different table schema. It seems similar to https://github.com/mitreid-connect/OpenID-Connect-Java-Spring-Server/issues/1303

It looks like the SQL files have changed a bit between the 1.3.1 MitreID release and what's in master. Probably should fix some of this there.

[mmoayyed]

This probably should be included:

<jdbc:script location="%{idp.home}/conf/schema/%{oidc.db.schema.type}/loading_temp_tables.sql"/>

Would you prefer to send a PR or shall I simply make the change?

[lhoekenga]

I think the only thing that needs to be loaded is

        <jdbc:script location="%{idp.home}/conf/schema/%{oidc.db.schema.type}/%{oidc.db.schema.type}_database_index.sql"/>

• security-schema.sql creates the users and authorities tables space.
• loading_temp_tables.sql makes temporary tables to store sample data in, which is then merged into the real versions of the appropriate tables.
• clients.sql, scopes.sql and users.sql are all sample data.