mysql connection is read-only._Connection is read-only问题的产生原因与解决方法

经过

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事务

com.hzcf.flagship.service.*

解决办法

因为我项目中事务切入点是所有的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)

参考