CODEWITHSUNDEEP
mysqlgrailsspring-securitygrails-4
kofhearts
kofhearts

Reputation: 3774

switch user not working when connected to mysql 8 database?

I am trying to get switch user feature in spring security to work. I am using grails 4.0.10 and mysql 8.

I created a sample hello world grails app and followed the switch user guide from the documentation. https://grails.github.io/grails-spring-security-core/4.0.x/index.html#switchUser

If i use the default h2 database then it works but if i switch to the mysql 8 database it is throwing page not found 404 error and it is not switching.

i have published the code in github. here is the link. https://github.com/sanjaygir/switching

I have created a simple page in secure controller. The page is index.gsp that has a form to switch to another user. Logged in user should be displayed at the top of this page. In bootstrap file i have created two users. one admin and another regular user.

i have a local database with this configuration

 dataSource:
            dbCreate: create
            url: jdbc:mysql://localhost:3307/switch?useUnicode=yes&characterEncoding=UTF-8
            username: root
            password: password

In order to run this app you need a mysql 8 db running. please change the mysql db name and username and password in the above section in application.yml.

After the app fires please go directly to http://localhost:8080/secure/index and then enter in the textbox "user" and click on the button switch. It will throw an error page not found and if you go back to http://localhost:8080/secure/index you can not see at the top loggedin user name. That means the switch was not successful.

here is the simple code for secure/index.gsp

<%@ page contentType="text/html;charset=UTF-8" %>
<html>
<head>
    <title></title>
</head>

<body>

<sec:ifLoggedIn>
    Logged in as <sec:username/>
</sec:ifLoggedIn>


<form action='${request.contextPath}/login/impersonate' method='POST'>
    Switch to user: <input type='text' name='username'/> <br/>
    <input type='submit' value='Switch'/>
</form>




</body>
</html>

i hope i have made it clear. this is a simple hello world app created to see switch user feature i n action. I am puzzled why switch user works with default h2 db but not when connected to mysql 8. if anyone have any idea i appreciate your help. Thanks

UPDATE:

Today i switched the database to mysql version 5 and it works. I changed the following configuration in application.yml

hibernate:
    cache:
        queries: false
        use_second_level_cache: false
        use_query_cache: false
dataSource:
    pooled: true
    jmxExport: true
    driverClassName: com.mysql.jdbc.Driver
    dialect: org.hibernate.dialect.MySQL5InnoDBDialect
    username: root
    password: 'password'

environments:
    development:
        dataSource:
            dbCreate: create-drop
            url: jdbc:mysql://localhost:3306/switch?useUnicode=yes&characterEncoding=UTF-8

in build.gradle i used

runtime 'mysql:mysql-connector-java:5.1.19'

still i am not sure why it doesnt work in mysql 8.

Upvotes: 0

Views: 153

Answers (1)

kofhearts
kofhearts

Reputation: 3774

i finally found the bug. i cannot believe what caused the 404 not found issue.it was a single line in the configuration file.

before the application.yml looked like this

---
grails:
    profile: web
    codegen:
        defaultPackage: rcroadraceweb4
    gorm:
        reactor:
            # Whether to translate GORM events into Reactor events
            # Disabled by default for performance reasons
            events: false
info:
    app:
        name: '@info.app.name@'
        version: '@info.app.version@'
        grailsVersion: '@info.app.grailsVersion@'
spring:
    jmx:
        unique-names: true
    main:
        banner-mode: "off"
    groovy:
        template:
            check-template-location: false
    devtools:
        restart:
            additional-exclude:
                - '*.gsp'
                - '**/*.gsp'
                - '*.gson'
                - '**/*.gson'
                - 'logback.groovy'
                - '*.properties'
management:
    endpoints:
        enabled-by-default: false


server:
    servlet:
        context-path: '/roadrace'


---

hibernate:
    cache:
        queries: false
        use_second_level_cache: false
        use_query_cache: false


grails:
    plugin:
        databasemigration:
            updateOnStart: true
            updateOnStartFileName: changelog.groovy

    controllers:
        upload:
            maxFileSize: 2000000
            maxRequestSize: 2000000

    mail:
        host: "localhost"
        port: 25
        default:
            to: 'root@localhost'
            from: '[email protected]'

dataSource:
    type: com.zaxxer.hikari.HikariDataSource
    pooled: true
    driverClassName: com.mysql.cj.jdbc.Driver
    dialect: org.hibernate.dialect.MySQL8Dialect
    dbCreate: none
    properties:
        minimumIdle: 5
        maximumPoolSize: 10
        poolName: main-db
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true
        useLocalSessionState: true
        rewriteBatchedStatements: true
        cacheResultSetMetadata: true
        cacheServerConfiguration: true
        elideSetAutoCommits: true
        maintainTimeStats: false
dataSources:
    logging:
        # This is not used unless `useJdbcAccessLogger` or `useJdbcLogger` is set to `true`
        # This does not need to be setup unless it is in use.
        type: com.zaxxer.hikari.HikariDataSource
        pooled: true
        driverClassName: com.mysql.cj.jdbc.Driver
        properties:
            minimumIdle: 2
            maximumPoolSize: 5
            poolName: logging-db
            cachePrepStmts: true
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            useServerPrepStmts: true
            useLocalSessionState: true
            rewriteBatchedStatements: true
            cacheResultSetMetadata: true
            cacheServerConfiguration: true
            elideSetAutoCommits: true
            maintainTimeStats: false

environments:
    development:
        dataSource:
            dbCreate: none
            url: jdbc:mysql://localhost:3307/dev2?useUnicode=yes&characterEncoding=UTF-8
            username: root
            password: password
        grails:
#            mail:
#                host: "smtp.gmail.com"
#                port: 465
#                username: "[email protected]"
#                password: "1asdfqwef1"
#                props:
#                    "mail.smtp.auth": "true"
#                    "mail.smtp.socketFactory.port": "465"
#                    "mail.smtp.socketFactory.class": "javax.net.ssl.SSLSocketFactory"
#                    "mail.smtp.socketFactory.fallback": "false"

    test:
        dataSource:
#            dialect: org.hibernate.dialect.MySQL5InnoDBDialect
            dbCreate: none
            url: jdbc:mysql://localhost:3307/test?useUnicode=yes&characterEncoding=UTF-8
            username: root
            password: password


    production:
---
logging:
    level:
        root: INFO
        org.springframework: WARN
        grails.plugin.springsecurity.web.access.intercept.AnnotationFilterInvocationDefinition: ERROR
        grails.plugins.DefaultGrailsPluginManager: WARN
        org.hibernate: ERROR # TODO: we need to lower this, and fix the warnings this is talking about.
        rcroadraceweb4: DEBUG
        com.runnercard: DEBUG
        liquibase.ext.hibernate.snapshot.HibernateSnapshotGenerator: ERROR

---
#debug: true
#useJdbcSessionStore: true
---
environments:
    nateDeploy:
        behindLoadBalancer: true
        grails:
            insecureServerURL: 'https://nate-dev.nate-palmer.com/roadrace'
            serverURL: 'https://nate-dev.nate-palmer.com/roadrace'
        dataSource:
            url: 'jdbc:mysql://10.1.10.240:3306/rcroadwebDEV?serverTimezone=America/Denver'

after the fix it looks like this

---
grails:
    profile: web
    codegen:
        defaultPackage: rcroadraceweb4
    gorm:
        reactor:
            # Whether to translate GORM events into Reactor events
            # Disabled by default for performance reasons
            events: false
info:
    app:
        name: '@info.app.name@'
        version: '@info.app.version@'
        grailsVersion: '@info.app.grailsVersion@'
spring:
    jmx:
        unique-names: true
    main:
        banner-mode: "off"
    groovy:
        template:
            check-template-location: false
    devtools:
        restart:
            additional-exclude:
                - '*.gsp'
                - '**/*.gsp'
                - '*.gson'
                - '**/*.gson'
                - 'logback.groovy'
                - '*.properties'
management:
    endpoints:
        enabled-by-default: false


server:
    servlet:
        context-path: '/roadrace'


---

hibernate:
    cache:
        queries: false
        use_second_level_cache: false
        use_query_cache: false


grails:
    plugin:
        databasemigration:
            updateOnStart: true
            updateOnStartFileName: changelog.groovy

    controllers:
        upload:
            maxFileSize: 2000000
            maxRequestSize: 2000000

    mail:
        host: "localhost"
        port: 25
        default:
            to: 'root@localhost'
            from: '[email protected]'

dataSource:
    type: com.zaxxer.hikari.HikariDataSource
    pooled: true
    driverClassName: com.mysql.cj.jdbc.Driver
    dialect: org.hibernate.dialect.MySQL8Dialect
    dbCreate: none
    properties:
        minimumIdle: 5
        maximumPoolSize: 10
        poolName: main-db
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true
        useLocalSessionState: true
        rewriteBatchedStatements: true
        cacheResultSetMetadata: true
        cacheServerConfiguration: true
        elideSetAutoCommits: true
        maintainTimeStats: false
dataSources:
    logging:
        # This is not used unless `useJdbcAccessLogger` or `useJdbcLogger` is set to `true`
        # This does not need to be setup unless it is in use.
        type: com.zaxxer.hikari.HikariDataSource
        pooled: true
        driverClassName: com.mysql.cj.jdbc.Driver
        properties:
            minimumIdle: 2
            maximumPoolSize: 5
            poolName: logging-db
            cachePrepStmts: true
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            useServerPrepStmts: true
            useLocalSessionState: true
            rewriteBatchedStatements: true
            cacheResultSetMetadata: true
            cacheServerConfiguration: true
            elideSetAutoCommits: true
            maintainTimeStats: false

environments:
    development:
        dataSource:
            dbCreate: none
            url: jdbc:mysql://localhost:3307/dev2?useUnicode=yes&characterEncoding=UTF-8
            username: root
            password: password
#        grails:
#            mail:
#                host: "smtp.gmail.com"
#                port: 465
#                username: "[email protected]"
#                password: "1asdfqwef1"
#                props:
#                    "mail.smtp.auth": "true"
#                    "mail.smtp.socketFactory.port": "465"
#                    "mail.smtp.socketFactory.class": "javax.net.ssl.SSLSocketFactory"
#                    "mail.smtp.socketFactory.fallback": "false"

    test:
        dataSource:
#            dialect: org.hibernate.dialect.MySQL5InnoDBDialect
            dbCreate: none
            url: jdbc:mysql://localhost:3307/test?useUnicode=yes&characterEncoding=UTF-8
            username: root
            password: password


    production:
---
logging:
    level:
        root: INFO
        org.springframework: WARN
        grails.plugin.springsecurity.web.access.intercept.AnnotationFilterInvocationDefinition: ERROR
        grails.plugins.DefaultGrailsPluginManager: WARN
        org.hibernate: ERROR # TODO: we need to lower this, and fix the warnings this is talking about.
        rcroadraceweb4: DEBUG
        com.runnercard: DEBUG
        liquibase.ext.hibernate.snapshot.HibernateSnapshotGenerator: ERROR

---
#debug: true
#useJdbcSessionStore: true
---
environments:
    nateDeploy:
        behindLoadBalancer: true
        grails:
            insecureServerURL: 'https://nate-dev.nate-palmer.com/roadrace'
            serverURL: 'https://nate-dev.nate-palmer.com/roadrace'
        dataSource:
            url: 'jdbc:mysql://10.1.10.240:3306/rcroadwebDEV?serverTimezone=America/Denver'

it was this line in environments > development block

 #        grails:

it worked after commenting the grails line.

but all the contents of grails block is commented so i am still confused why having grails uncommented would have this big issue. anyways solved after days of hard search!

Upvotes: 0

Related Questions