3月10日 Bug
- 1、MySQLSyntaxErrorException:SELECT t1.COLUMN_NAME AS fieldName, t1.DATA_TYPE jdbcType, t1.COMMENTS remark FROM information_schema.COLUMNS t1, WHERE t1.TABLE_NAME = ? ORDER BY t1.column_key
- 2、BadSqlGrammarException: Error querying database.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.COMMENTS' in 'field list'
- 3、java.io.FileNotFoundException: C:\Prosoft\apache-maven3.6.3\repo\com\github\junrar\junrar\3.1.0\commons-vfs2-2.2.jar (系统找不到指定的文件。)
- 4、java.lang.NumberFormatException: For input string: "1.000000000000000000000000000000"
- 5、Out of range value for column '字段' at row 1
- 6、MySQLSyntaxErrorException: FUNCTIONdd_etl_rest.fun_checktree_action does not exist
- 7、MySQLSyntaxErrorException: Every derived table must have its own alias
- 8、MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds
1、MySQLSyntaxErrorException:SELECT t1.COLUMN_NAME AS fieldName, t1.DATA_TYPE jdbcType, t1.COMMENTS remark FROM information_schema.COLUMNS t1, WHERE t1.TABLE_NAME = ? ORDER BY t1.column_key
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE t1.TABLE_NAME = 'fisp_interface_conf'
ORDER BY t1.colu' at line 5
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE t1.TABLE_NAME = 'fisp_interface_conf'
ORDER BY t1.colu' at line 5
原因:where前面多了个,
解决方法:去掉,
2、BadSqlGrammarException: Error querying database.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘t1.COMMENTS’ in ‘field list’
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.COMMENTS' in 'field list'
### The error may exist in com/datadriver/web/trace/dao/TraceMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT t1.COLUMN_NAME AS fieldName, t1.DATA_TYPE jdbcType, t1.COMMENTS remark FROM information_schema.COLUMNS t1 WHERE t1.TABLE_NAME = ? ORDER BY t1.column_key
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.COMMENTS' in 'field list'
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.COMMENTS' in 'field list'
原因:mysql系统表字段里面没有COMMENTS,这是oracle的
解决方法:采用COLUMN_COMMENT
3、java.io.FileNotFoundException: C:\Prosoft\apache-maven3.6.3\repo\com\github\junrar\junrar\3.1.0\commons-vfs2-2.2.jar (系统找不到指定的文件。)
java.io.FileNotFoundException: C:\Prosoft\apache-maven-3.6.3\repo\com\github\junrar\junrar\3.1.0\commons-vfs2-2.2.jar (系统找不到指定的文件。)
at java.util.zip.ZipFile.open(Native Method) ~[na:1.8.0_111]
原因:可能是github网络不好,没有完成下载
解决方法:到maven远程仓库下载jar包,按照提示位置,复制到本地仓库文件夹https://mvnrepository.com/artifact/org.apache.commons/commons-vfs2/2.2
4、java.lang.NumberFormatException: For input string: “1.000000000000000000000000000000”
[ERROR] 2022-03-10 13:23:57 UnitedLogger@(UnitedLogger.java:43):For input string: "1.000000000000000000000000000000"
java.lang.NumberFormatException: For input string: "1.000000000000000000000000000000"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
原因:oracle转到mysql自动转换会把int转化为decimal
解决方法:将字段属性从decimal改为int
5、Out of range value for column ‘字段’ at row 1
原因:字段的值超过其可输入的范围
解决方法:找到字段所在表,然后修改字段大小
SELECT
table_name
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'dd_etl_rest'
AND COLUMN_NAME = 'LAST_CHECKIN_TIME'
6、MySQLSyntaxErrorException: FUNCTIONdd_etl_rest.fun_checktree_action does not exist
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: FUNCTION dd_etl_rest.fun_checktree_action does not exist
### The error may exist in com/datadriver/web/system/dao/SystemrPermissionActionMapper.xml
### The error may involve com.datadriver.web.system.dao.SystemrPermissionActionMapper.selectByUserId-Inline
原因:没有对应函数
解决方法:找到对应函数逻辑,重写函数
7、MySQLSyntaxErrorException: Every derived table must have its own alias
SQL: select * from ( select tmp_page.*, rownum row_id from ( select u.vc_username as userName, (select p.vc_deptname from SYS_DEPT p where p.f_deptid=u.f_companyid) as companyName, DATE_FORMAT(t.d_logintime,'%Y-%m-%d %k:%i:%s') as loginTime, t.vc_login_status as loginStatus, IF(t.vc_login_status='1','登录成功','登录失败') as loginStatusName, t.vc_login_msg as loginMsg, t.vc_login_ip as loginIp from sys_log_login t left join sys_user u on (t.f_userid=u.f_userid) where 1=1 and ( t.f_userid=? or exists ( select 1 from sys_user_role sur,sys_role sr where sr.vc_status='0' and sur.f_roleid=sr.f_roleid and sr.vc_rolecode='MANAGER_SYS' and sur.f_userid=?) or exists ( select 1 from sys_user_role sur,sys_role sr,sys_user su where sr.vc_status='0' and sur.f_roleid=sr.f_roleid and sr.vc_rolecode='MANAGER_COMPANY' and sur.f_userid=? and sur.f_userid=su.f_userid and su.f_companyid=u.f_companyid) ) order by loginTime desc ) tmp_page ) where row_id <= ? and row_id > ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Every derived table must have its own alias
原因:pageHelper插件使用了oracle方言row_num
解决方法:切换到mysql方言即可
8、MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' F_ACTIONID as actionid
,F_ACTIONPID ' at line 3
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' F_ACTIONID as actionid
,F_ACTIONPID ' at line 3
原因:mapper文件产生了不可见非空格字符
解决方法:删除所有sql标签里的所有空格再手打即可