Connection is read-only问题的产生原因与解决方法

2019独角兽企业重金招聘Python工程师标准>>>

经过

1、项目中运用了MySQL存储过程中使用动态行转列，使用了存储过程。

2、定义的service接口名为：

    /**
     * 根据参数查询业绩目标列表
     * @param pageNo
     * @param pageSize
     * @param orgNo
     * @param businessNo
     * @param year
     * @see AssetPlan
     * @return
     */
    PageModel selectAssetPlanList(int pageNo,int pageSize, String orgNo, String businessNo, String year);

3、在调用此方法的时候报错：Connection is read-only

 

错误详细信息

2017-11-14 15:39:57.971 [   ] INFO  org.springframework.beans.factory.xml.XmlBeanDefinitionReader 315 : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2017-11-14 15:39:58.930 [   ] INFO  org.springframework.jdbc.support.SQLErrorCodesFactory 126 : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
2017-11-14 15:40:20.333 [   ] ERROR com.hzcf.flagship.web.AssetPlanController 53 : 
### Error querying database.  Cause: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
### The error may exist in class path resource [com/hzcf/flagship/mapping/AssetPlanMapper.xml]
### The error may involve com.hzcf.flagship.dao.AssetPlanMapper.selectAssetPlanList-Inline
### The error occurred while setting parameters
### SQL: CALL f_asset_plan_list(?,?,?,?,?)
### Cause: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
; SQL []; 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
org.springframework.dao.TransientDataAccessResourceException: 
### Error querying database.  Cause: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
### The error may exist in class path resource [com/hzcf/flagship/mapping/AssetPlanMapper.xml]
### The error may involve com.hzcf.flagship.dao.AssetPlanMapper.selectAssetPlanList-Inline
### The error occurred while setting parameters
### SQL: CALL f_asset_plan_list(?,?,?,?,?)
### Cause: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
; SQL []; 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:107)
	...
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
	... 106 more
2017-11-14 15:45:27 JRebel: Reloading class 'com.hzcf.flagship.web.AssetPlanController'.

 

产生原因

一般如果报了这个错，是项目中配置了只读事务的问题。

结合我在此处使用了存储过程，存储过程中使用了INTO关键字，决定了存储过程为非只读操作。所以会报错。

 

项目中配置的aop事务

	<!-- aop切入点 -->
    <bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut" scope="prototype">
		<property name="patterns">
			<list>
				<value>com.hzcf.flagship.service.*</value>
			</list>
		</property>
	</bean>
	<aop:config>
		<aop:advisor advice-ref="druid-stat-interceptor" pointcut-ref="druid-stat-pointcut"/>
	</aop:config>

	<!-- 事务控制 -->
	<tx:advice id="txAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="save*" propagation="REQUIRED" rollback-for="Exception"/>
			<tx:method name="do*" propagation="REQUIRED" rollback-for="Exception"/>
			<tx:method name="insert*" propagation="REQUIRED" rollback-for="Exception"/>
			<tx:method name="add*" propagation="REQUIRED" rollback-for="Exception"/>
			<tx:method name="update*" propagation="REQUIRED" rollback-for="Exception"/>
			<tx:method name="edit*" propagation="REQUIRED" rollback-for="Exception"/>
			<tx:method name="remove*" propagation="REQUIRED" rollback-for="Exception"/>
			<tx:method name="delete*" propagation="REQUIRED" rollback-for="Exception"/>
			<tx:method name="get*" read-only="true"/>
			<tx:method name="select*" read-only="true"/>
			<tx:method name="find*" read-only="true"/>
		</tx:attributes>
	</tx:advice>

 

解决办法

因为我项目中事务切入点是所有的Service方法。

只需把selectAssetPlanList()方法名称改成queryAssetPlanList()。使此方法不加入aop事务中。就ok了。

 

存储过程（不是创建存储过程的sql语句）

BEGIN
SET @SQL = NULL;
SELECT
	GROUP_CONCAT(
		DISTINCT CONCAT(
			'MAX(IF(ap.month = ''',
			ap.month,
			''', ap.plan_value, 0)) AS ''',
			ap.month,
			''''
		)
	) INTO @SQL
FROM
	f_asset_plan ap;

SET @SQL = CONCAT(
	'Select ap.org_name, ap.business_name, ap.year, ',
	@SQL,
	' From f_asset_plan ap where 1=1 '
);

IF v_org_no is not null then 
set @sql = CONCAT(@sql,' and ap.org_no = ''',v_org_no,'''');
end if;

IF v_business_no is not null then 
set @sql = CONCAT(@sql,' and ap.business_no = ''',v_business_no,'''');
end if;

IF v_year is not null then 
set @sql = CONCAT(@sql,' and ap.year = ''',v_year,'''');
end if;

IF v_from IS NULL THEN SET v_from = 0; END IF;
IF v_pageSize IS NULL THEN SET v_pageSize = 10; END IF;

SET @SQL = CONCAT(@SQL,' Group by ap.org_no,ap.business_no limit ',v_from,',',v_pageSize);

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

参数

IN v_from int,IN v_pageSize int,IN v_org_no varchar(225),IN v_business_no varchar(225),IN v_year varchar(225)

 

参考

MySQL存储过程中使用动态行转列

Connection is read-only问题的产生原因与解决方法

转载于:https://my.oschina.net/anxiaole/blog/1573442