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

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标签里的所有空格再手打即可