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_key2、BadSqlGrammarException: Error querying database.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.COMMENTS' in 'field list'3、java.io.FileNotFoundException: C:Prosoftapache-maven3.6.3repocomgithubjunrarjunrar3.1.0commons-vfs2-2.2.jar (系统找不到指定的文件。)4、java.lang.NumberFormatException: For input string: "1.000000000000000000000000000000"5、Out of range value for column '字段' at row 16、MySQLSyntaxErrorException: FUNCTIONdd_etl_rest.fun_checktree_action does not exist7、MySQLSyntaxErrorException: Every derived table must have its own alias8、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_keyCause: 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的
3、java.io.FileNotFoundException: C:Prosoftapache-maven3.6.3repocomgithubjunrarjunrar3.1.0commons-vfs2-2.2.jar (系统找不到指定的文件。)解决方法：采用COLUMN_COMMENT
java.io.FileNotFoundException: C:Prosoftapache-maven-3.6.3repocomgithubjunrarjunrar3.1.0commons-vfs2-2.2.jar (系统找不到指定的文件。) at java.util.zip.ZipFile.open(Native Method) ~[na:1.8.0_111]
原因：可能是github网络不好，没有完成下载
4、java.lang.NumberFormatException: For input string: “1.000000000000000000000000000000”解决方法：到maven远程仓库下载jar包，按照提示位置，复制到本地仓库文件夹https://mvnrepository.com/artifact/org.apache.commons/commons-vfs2/2.2
[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
5、Out of range value for column ‘字段’ at row 1解决方法：将字段属性从decimal改为int
原因：字段的值超过其可输入的范围
解决方法：找到字段所在表，然后修改字段大小
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
8、MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds解决方法：切换到mysql方言即可
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标签里的所有空格再手打即可
