-1

we use java SSH, recently we encountered the following problem frequently,not sure what happened, i searched so many times and no such shiro related scenario occured,we use shiro as authenticaiton framework, and customized the sessionDAO including session operations like "doCreate,doUpdate etc.",even config like this in applicaitonContext.xml:

 <tx:method name="do*" propagation="REQUIRES_NEW" />

the trace:

2018-01-22 18:02:03.482 [http-nio-8080-exec-762] INFO  org.apache.struts2.rest.RestActionInvocation - Executed action [//order/order!index!xhtml!200] took 574 ms (execution: 149 ms, result: 425 ms)
org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [update sessions set session=? where session_id=?]; Connection is read-only. Queries leading to data modification are not allowed; nested exception is java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:941)
        at com.shopping.web.authentication.dao.impl.ShiroSessionDao.doUpdate(ShiroSessionDao.java:48)
        at org.apache.shiro.session.mgt.eis.CachingSessionDAO.update(CachingSessionDAO.java:277)
        at org.apache.shiro.session.mgt.eis.CachingSessionDAO$$FastClassBySpringCGLIB$$2a5e5afd.invoke(<generated>)

anyone could please help? thanks a lot.

db configuration in applicationContext.xml:

 <!--  C3P0 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="driverClass" value="${jdbc.driverClass}"></property>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>

        <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
        <property name="maxIdleTime" value="${cpool.maxIdleTime}"/> 
    </bean>        
  <!-- Spring jdbcTempate used for authentication -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
       <property name="dataSource" ref="dataSource"></property>  
   </bean>

    <!--SessionFactory -->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource"></property>
        <property name="configLocation" value="classpath:hibernate.cfg.xml"></property>

        <property name="mappingLocations" value="classpath:com/shopping/web/entities/*.hbm.xml"></property>
        <property name="packagesToScan">  
            <list>  
                <value>com.shopping</value>  
            </list>  
        </property>
    </bean>

we use jdbctemplate together with hibernate5 with the same session managment.

    in db.proerties:
    jdbc.user=shopping
    jdbc.password=123456
    jdbc.driverClass=com.mysql.jdbc.Driver
  jdbc.jdbcUrl=jdbc:mysql://192.168.2.221:3306,192.168.2.222:3306,192.168.2.200:3306/shopping?useUnicode=true&characterEncoding=utf-8

    jdbc.initPoolSize=5
    jdbc.maxPoolSize=10
    cpool.maxIdleTime=25200
barry
  • 1
  • 1

1 Answers1

0

after hard work on this problem, we got the root cause: we use mysql cluster and c3p0 connection pool on server side to store data as well as shiro session,sometimes the web site need to reconnect to mysql cluster if there is no available connection to use in the pool, once reconnect, the default mode is read only, but the shiro session need update operation in database, this cause the problem. the solution is: change the mysql connection url as below: jdbc.jdbcUrl=jdbc:mysql://server1:3306,server2:3306,server3:3306/database?autoReconnectForPools=true&autoReconnect=true&failOverReadOnly=false&useUnicode=true&characterEncoding=utf-8 and autoReconnectForPools=true&autoReconnect=true&failOverReadOnly=false is the key to solution

barry
  • 1
  • 1