Inserted the same user twice into table user_info

[bejondshao]

I use tag mitreid-connect-1.3.2, mysql 5.7.16. I config mysql db, build war and deploy to apache.

The default mysql scripts is unable to startup. I copy loading_temp_tables.sql from /db/hsql/loading_temp_tables.sql. I modify it from

CREATE TEMPORARY TABLE IF NOT EXISTS system_scope_TEMP ( scope VARCHAR(256), description VARCHAR(4096), icon VARCHAR(256), restricted BOOLEAN, default_scope BOOLEAN );

to

CREATE TEMPORARY TABLE IF NOT EXISTS system_scope_TEMP ( scope VARCHAR(256), description VARCHAR(4096), icon VARCHAR(256), restricted BOOLEAN, default_scope BOOLEAN, structured BOOLEAN DEFAULT false NOT NULL, structured_param_description VARCHAR(256) );

and modify mysql_database_tables.sql from:

CREATE TABLE IF NOT EXISTS system_scope ( id BIGINT AUTO_INCREMENT PRIMARY KEY, scope VARCHAR(256) NOT NULL, description VARCHAR(4096), icon VARCHAR(256), restricted BOOLEAN DEFAULT false NOT NULL, default_scope BOOLEAN DEFAULT false NOT NULL, UNIQUE (scope) );

to:

CREATE TABLE IF NOT EXISTS system_scope ( id BIGINT AUTO_INCREMENT PRIMARY KEY, scope VARCHAR(256) NOT NULL, description VARCHAR(4096), icon VARCHAR(256), restricted BOOLEAN DEFAULT false NOT NULL, default_scope BOOLEAN DEFAULT false NOT NULL, structured BOOLEAN DEFAULT false NOT NULL, structured_param_description VARCHAR(256), UNIQUE (scope) );

Finally it starts up. I can login now. Then I restart tomcat. The second time I login. I get the same error. And the log shows

context with path [/openid-connect-server-webapp] threw exception [Request processing failed; nested exception is java.lang.IllegalStateException: Expected single result, got 2] with root cause java.lang.IllegalStateException: Expected single result, got 2 at org.mitre.util.jpa.JpaUtil.getSingleResult(JpaUtil.java:40) at org.mitre.openid.connect.repository.impl.JpaUserInfoRepository.getByUsername(JpaUserInfoRepository.java:51) at org.mitre.openid.connect.service.impl.DefaultUserInfoService.getByUsername(DefaultUserInfoService.java:50) at org.mitre.openid.connect.web.UserInfoInterceptor.preHandle(UserInfoInterceptor.java:93) at org.springframework.web.servlet.HandlerExecutionChain.applyPreHandle(HandlerExecutionChain.java:134) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:958) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:100) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.mitre.openid.connect.filter.AuthorizationRequestFilter.doFilter(AuthorizationRequestFilter.java:112) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

[bejondshao]

And issue also happens with postgresql.

The first on tomcat startup, the table user_info contains only one "admin". Shutdown tomcat, start it again. Then run SELECT id, sub, preferred_username, name, email, email_verified FROM user_info; and I got:

"1" "90342.ASDFJWFA" "admin" "Demo Admin" "admin@example.com" true "2" "01921.FLANRJQW" "user" "Demo User" "user@example.com" true "3" "90342.ASDFJWFA" "admin" "Demo Admin" "admin@example.com" true "4" "01921.FLANRJQW" "user" "Demo User" "user@example.com" true

The third time start tomcat, it gets 3 "admin" user. Do I need to set something to disable run users.sql?

Duplicate rows happen to table client_scope.

'1','openid' '1','profile' '1','email' '1','address' '1','phone' '1','offline_access' '1','openid' '1','profile' '1','email' '1','address' '1','phone' '1','offline_access' '1','openid' '1','profile' '1','email' '1','address' '1','phone' '1','offline_access' '4','address' '4','phone' '4','openid' '4','email' '4','profile'

But table system_scope is fine. There's no duplicate rows in system_scope.

[bejondshao]

my data-config.xml is

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"> 
        <property name="driverClassName" value="com.mysql.jdbc.Driver" /> 
        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/oic_cp1250" /> 
        <property name="username" value="user" /> 
        <property name="password" value="123" /> 
    </bean> 

    <bean id="jpaAdapter" class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter"> 
        <property name="databasePlatform" value="org.eclipse.persistence.platform.database.MySQLPlatform" /> 
        <property name="showSql" value="true" /> 
    </bean> 

    <!-- You can optionally initialize the database with test values here, 
        but this is not recommended for real systems -->

    <jdbc:initialize-database data-source="dataSource"> 
        <jdbc:script location="classpath:/db/mysql/mysql_database_tables.sql"/> 
        <jdbc:script location="classpath:/db/mysql/security-schema.sql"/> 
        <jdbc:script location="classpath:/db/mysql/loading_temp_tables.sql"/>
        <jdbc:script location="classpath:/db/mysql/users.sql"/> 
        <jdbc:script location="classpath:/db/mysql/clients.sql"/> 
        <jdbc:script location="classpath:/db/mysql/scopes.sql"/> 
    </jdbc:initialize-database> 

[bejondshao]

The workaround I choose now is

1. create new database with encoding cp1250. Run mvn clean package with the data-config.xml below:

<bean id="jpaAdapter" class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter"> 
    <property name="databasePlatform" value="org.eclipse.persistence.platform.database.MySQLPlatform" /> 
    <property name="showSql" value="true" /> 
</bean> 

<!-- You can optionally initialize the database with test values here, 
    but this is not recommended for real systems -->

<jdbc:initialize-database data-source="dataSource"> 
    <jdbc:script location="classpath:/db/mysql/mysql_database_tables.sql"/> 
    <jdbc:script location="classpath:/db/mysql/security-schema.sql"/> 
    <jdbc:script location="classpath:/db/mysql/loading_temp_tables.sql"/>
</jdbc:initialize-database> 

2. Stop tomcat. Go to mysql client, and run loading_temp_tables.sql(loading_temp_tables.sql comes first), mysql/mysql_database_index.sql, mysql/clients.sql, mysql/scopes.sql and mysql/users.sql manually.
3. Then start tomcat. This time, the user_info would not contain duplicate users.

[jricher]

You should only be using the temp_tables scripts on development databases where you need to bootstrap a system. Production systems should not be running them on startup, and it's not recommended to run them on MySQL or other on-disk databases anyway.