dobet opened a new issue, #33593: URL: https://github.com/apache/shardingsphere/issues/33593
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? 5.5.0 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? proxy ### Expected behavior import sql file to proxy success ### Actual behavior import sql file failed ### Reason analyze (If you can) ``` [INFO ] 2024-11-08 15:48:41.148 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: CREATE TABLE `sysmsg` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mid` int(11) DEFAULT NULL, `type` int(11) DEFAULT '0', `hrid` int(11) DEFAULT NULL, `state` int(11) DEFAULT '0', PRIMARY KEY (`id`), KEY `hrid` (`hrid`), KEY `sysmsg_ibfk_1` (`mid`) ) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 [INFO ] 2024-11-08 15:48:41.148 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: iadmin_sbtest ::: CREATE TABLE `sysmsg` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mid` int(11) DEFAULT NULL, `type` int(11) DEFAULT '0', `hrid` int(11) DEFAULT NULL, `state` int(11) DEFAULT '0', PRIMARY KEY (`id`), KEY `hrid` (`hrid`), KEY `sysmsg_ibfk_1` (`mid`) ) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 java.sql.SQLSyntaxErrorException: 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 ')' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960) at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1019) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.apache.shardingsphere.infra.database.mysql.metadata.data.loader.MySQLMetaDataLoader.loadViewNames(MySQLMetaDataLoader.java:96) at org.apache.shardingsphere.infra.database.mysql.metadata.data.loader.MySQLMetaDataLoader.load(MySQLMetaDataLoader.java:76) at org.apache.shardingsphere.infra.database.core.metadata.data.loader.MetaDataLoader.load(MetaDataLoader.java:88) at org.apache.shardingsphere.infra.database.core.metadata.data.loader.MetaDataLoader.lambda$load$0(MetaDataLoader.java:64) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) ``` loadViewNames failed ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. ### Example codes for reproduce this issue (such as a github link). mysql -uroot -proot -h 127.0.0.1 -P 3307 sbtest < sbtest.sql sbtest.sql is this ``` DROP TABLE IF EXISTS `sysmsg`; DROP TABLE IF EXISTS `oplog`; DROP TABLE IF EXISTS `msgcontent`; DROP TABLE IF EXISTS `menu_role`; DROP TABLE IF EXISTS `menu`; DROP TABLE IF EXISTS `hr_role`; DROP TABLE IF EXISTS `role`; DROP TABLE IF EXISTS `hr`; DROP TABLE IF EXISTS `empsalary`; DROP TABLE IF EXISTS `salary`; DROP TABLE IF EXISTS `employeetrain`; DROP TABLE IF EXISTS `employeeremove`; DROP TABLE IF EXISTS `employeeec`; DROP TABLE IF EXISTS `mail_send_log`; DROP TABLE IF EXISTS `appraise`; DROP TABLE IF EXISTS `adjustsalary`; DROP TABLE IF EXISTS `employee`; DROP TABLE IF EXISTS `department`; DROP TABLE IF EXISTS `joblevel`; DROP TABLE IF EXISTS `position`; DROP TABLE IF EXISTS `politicsstatus`; DROP TABLE IF EXISTS `nation`; DROP TABLE IF EXISTS `adjustsalary`; CREATE TABLE `department` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, `parentId` int(11) DEFAULT NULL, `depPath` varchar(255) DEFAULT NULL, `enabled` tinyint(1) DEFAULT '1', `isParent` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8; CREATE TABLE `joblevel` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, `titleLevel` enum('正高级','副高级','中级','初级','员级') DEFAULT NULL, `createDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `enabled` tinyint(1) DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8; CREATE TABLE `position` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, `createDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `enabled` tinyint(1) DEFAULT '1', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8; CREATE TABLE `nation` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=utf8; CREATE TABLE `politicsstatus` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8; CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `gender` char(4) DEFAULT NULL, `birthday` date DEFAULT NULL, `idCard` char(18) DEFAULT NULL, `wedlock` enum('已婚','未婚','离异') DEFAULT NULL, `nationId` int(8) DEFAULT NULL, `nativePlace` varchar(20) DEFAULT NULL, `politicId` int(8) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `phone` varchar(11) DEFAULT NULL, `address` varchar(64) DEFAULT NULL, `departmentId` int(11) DEFAULT NULL, `jobLevelId` int(11) DEFAULT NULL, `posId` int(11) DEFAULT NULL, `engageForm` varchar(8) DEFAULT NULL, `tiptopDegree` enum('博士','硕士','本科','大专','高中','初中','小学','其他') DEFAULT NULL, `specialty` varchar(32) DEFAULT NULL, `school` varchar(32) DEFAULT NULL, `beginDate` date DEFAULT NULL, `workState` enum('在职','离职') DEFAULT '在职', `workID` char(8) DEFAULT NULL, `contractTerm` double DEFAULT NULL, `conversionTime` date DEFAULT NULL, `notWorkDate` date DEFAULT NULL, `beginContract` date DEFAULT NULL , `endContract` date DEFAULT NULL, `workAge` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `departmentId` (`departmentId`), KEY `jobId` (`jobLevelId`), KEY `dutyId` (`posId`), KEY `nationId` (`nationId`), KEY `politicId` (`politicId`), KEY `workID_key` (`workID`) ) ENGINE=InnoDB AUTO_INCREMENT=1942 DEFAULT CHARSET=utf8; CREATE TABLE `adjustsalary` ( `id` int(11) NOT NULL AUTO_INCREMENT, `eid` int(11) DEFAULT NULL, `asDate` date DEFAULT NULL, `beforeSalary` int(11) DEFAULT NULL, `afterSalary` int(11) DEFAULT NULL, `reason` varchar(255) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`eid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `appraise` ( `id` int(11) NOT NULL AUTO_INCREMENT, `eid` int(11) DEFAULT NULL, `appDate` date DEFAULT NULL, `appResult` varchar(32) DEFAULT NULL, `appContent` varchar(255) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`eid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `mail_send_log` ( `msgId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `empId` int(11) DEFAULT NULL, `status` int(11) DEFAULT '0', `routeKey` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `exchange` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `count` int(11) DEFAULT 0, `tryTime` datetime DEFAULT NULL, `createTime` datetime DEFAULT NULL, `updateTime` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `employeeec` ( `id` int(11) NOT NULL AUTO_INCREMENT, `eid` int(11) DEFAULT NULL, `ecDate` date DEFAULT NULL, `ecReason` varchar(255) DEFAULT NULL, `ecPoint` int(11) DEFAULT NULL, `ecType` int(11) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`eid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `employeeremove` ( `id` int(11) NOT NULL AUTO_INCREMENT, `eid` int(11) DEFAULT NULL, `afterDepId` int(11) DEFAULT NULL, `afterJobId` int(11) DEFAULT NULL, `removeDate` date DEFAULT NULL, `reason` varchar(255) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`eid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `employeetrain` ( `id` int(11) NOT NULL AUTO_INCREMENT, `eid` int(11) DEFAULT NULL, `trainDate` date DEFAULT NULL, `trainContent` varchar(255) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL , PRIMARY KEY (`id`), KEY `pid` (`eid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `salary` ( `id` int(11) NOT NULL AUTO_INCREMENT, `basicSalary` int(11) DEFAULT NULL, `bonus` int(11) DEFAULT NULL, `lunchSalary` int(11) DEFAULT NULL, `trafficSalary` int(11) DEFAULT NULL, `allSalary` int(11) DEFAULT NULL, `pensionBase` int(11) DEFAULT NULL, `pensionPer` float DEFAULT NULL, `createDate` timestamp NULL DEFAULT NULL, `medicalBase` int(11) DEFAULT NULL, `medicalPer` float DEFAULT NULL, `accumulationFundBase` int(11) DEFAULT NULL, `accumulationFundPer` float DEFAULT NULL, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; CREATE TABLE `empsalary` ( `id` int(11) NOT NULL AUTO_INCREMENT, `eid` int(11) DEFAULT NULL, `sid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `eid` (`eid`), KEY `empsalary_ibfk_2` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8; CREATE TABLE `hr` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, `phone` char(11) DEFAULT NULL, `telephone` varchar(16) DEFAULT NULL, `address` varchar(64) DEFAULT NULL, `enabled` tinyint(1) DEFAULT '1', `username` varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `userface` varchar(255) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) DEFAULT NULL, `nameZh` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8; CREATE TABLE `hr_role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `hrid` int(11) DEFAULT NULL, `rid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `rid` (`rid`), KEY `hr_role_ibfk_1` (`hrid`) ) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8; CREATE TABLE `menu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `url` varchar(64) DEFAULT NULL, `path` varchar(64) DEFAULT NULL, `component` varchar(64) DEFAULT NULL, `name` varchar(64) DEFAULT NULL, `iconCls` varchar(64) DEFAULT NULL, `keepAlive` tinyint(1) DEFAULT NULL, `requireAuth` tinyint(1) DEFAULT NULL, `parentId` int(11) DEFAULT NULL, `enabled` tinyint(1) DEFAULT '1', PRIMARY KEY (`id`), KEY `parentId` (`parentId`) ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8; CREATE TABLE `menu_role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mid` int(11) DEFAULT NULL, `rid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `mid` (`mid`), KEY `rid` (`rid`) ) ENGINE=InnoDB AUTO_INCREMENT=283 DEFAULT CHARSET=utf8; CREATE TABLE `msgcontent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(64) DEFAULT NULL, `message` varchar(255) DEFAULT NULL, `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8; CREATE TABLE `oplog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `addDate` date DEFAULT NULL, `operate` varchar(255) DEFAULT NULL, `hrid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `hrid` (`hrid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `sysmsg` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mid` int(11) DEFAULT NULL, `type` int(11) DEFAULT '0', `hrid` int(11) DEFAULT NULL, `state` int(11) DEFAULT '0', PRIMARY KEY (`id`), KEY `hrid` (`hrid`), KEY `sysmsg_ibfk_1` (`mid`) ) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8; DELIMITER $$ /*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `addDep`(in depName varchar(32),in parentId int,in enabled boolean,out result int,out result2 int) begin declare did int; declare pDepPath varchar(64); insert into department set name=depName,parentId=parentId,enabled=enabled; select row_count() into result; select last_insert_id() into did; set result2=did; select depPath into pDepPath from department where id=parentId; update department set depPath=concat(pDepPath,'.',did) where id=did; update department set isParent=true where id=parentId; end */$$ DELIMITER ; DELIMITER $$ /*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteDep`(in did int,out result int) begin declare ecount int; declare pid int; declare pcount int; declare a int; select count(*) into a from department where id=did and isParent=false; if a=0 then set result=-2; else select count(*) into ecount from employee where departmentId=did; if ecount>0 then set result=-1; else select parentId into pid from department where id=did; delete from department where id=did and isParent=false; select row_count() into result; select count(*) into pcount from department where parentId=pid; if pcount=0 then update department set isParent=false where id=pid; end if; end if; end if; end */$$ DELIMITER ; ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
