在存储过程中,这个语句会Lost connection to MySQL server during query错误:
delete from YHQHS where YHQTM=instr1;
yhqtm和instr1都是varchar类型。
如果把where后面的条件换成不是varchar类型的,就不会出错,这是为什么呢?
delete from YHQHS where YHQTM=instr1;
yhqtm和instr1都是varchar类型。
如果把where后面的条件换成不是varchar类型的,就不会出错,这是为什么呢?
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
CREATE TABLE `YHQHS` (
`YHQTM` varchar(20) NOT NULL,
`SPDM` varchar(20) DEFAULT NULL,
`SPTM` varchar(13) DEFAULT NULL,
`YHJE` decimal(12,2) DEFAULT '0.00',
`TPMC` varchar(50) DEFAULT NULL,
`KSSJ` datetime DEFAULT NULL,
`JSSJ` datetime DEFAULT NULL,
`SYSCDM` varchar(4) DEFAULT NULL,
`SYMDH` varchar(10) DEFAULT NULL,
`SYXPH` varchar(20) DEFAULT NULL,
`SYSYJH` varchar(10) DEFAULT NULL,
`SCSJ` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`BZ1` decimal(12,2) DEFAULT NULL,
`BZ2` decimal(12,2) DEFAULT NULL,
`BY1` varchar(30) DEFAULT NULL,
`BY2` varchar(30) DEFAULT NULL,
`BY3` varchar(30) DEFAULT NULL,
`SPMC` varchar(80) DEFAULT NULL,
`SFDZ` double DEFAULT '0',
`DZZT` varchar(20) DEFAULT NULL,
`DZSJ` datetime DEFAULT NULL,
`SFTH` varchar(10) DEFAULT '0',
`THYHQTM` varchar(20) DEFAULT '0',
`YQSPXX` varchar(500) DEFAULT NULL,
PRIMARY KEY (`YHQTM`),
KEY `IDX_YHQHS_3` (`SYXPH`,`SYSYJH`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;存储过程
begin
DECLARE vscdm VARCHAR(4);
DECLARE errcode INTEGER ;
DECLARE err VARCHAR(200);
DECLARE ERR_BG INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND set ERR_BG=1;
begin
set ret =1;
set ret2=1;
set rmsg = 'success' ;
select csz into vscdm from `XTCS` where csm='SCDM';
delete from YHQHS where syscdm=vscdm and symdh=vmdh and syxph=vxph and sysyjh=vsyjh;
delete from `XSXPB` where scdm=vscdm and mdh=vmdh and xph=vxph and syjh=vsyjh and sfsc=0 and sfyx=1;
end;
IF ERR_BG =1 THEN
set ret =1;
set ret2=0;
Set errcode=1116;
set rmsg = 'error ??' ;
set err:='????????';
insert into `SYSLOG` (scdm,mdh,xph,syjh,procname,errmsg,errcode) values(vscdm,vmdh,vxph,vsyjh,'P_XSXP_DEL',err,errcode);
end if ;
commit;
end调用:
const char* str_sql = "call P_XSXP_DEL('1323','20100126245','9',@a,@b,@c);";
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_XSXP_DEL`(IN VMDH VARCHAR(20), IN VXPH VARCHAR(20), IN VSYJH VARCHAR(20), OUT ret INTEGER, OUT ret2 INTEGER, OUT rmsg VARCHAR(20))
DETERMINISTIC
begin DECLARE vscdm VARCHAR(4); DECLARE errcode INTEGER ; DECLARE err VARCHAR(200); DECLARE ERR_BG INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND set ERR_BG=1; begin set ret =1; set ret2=1; set rmsg = 'success' ; select csz into vscdm from `XTCS` where csm='SCDM'; -- update `YHQMXB` set syscdm='',symdh ='' , syxph='' , sysyjh='' , sfsy=0 where syscdm=vscdm and symdh=vmdh and sysyjh=vsyjh and syxph=vxph; delete from YHQHS where syscdm=vscdm and symdh=vmdh and syxph=vxph and sysyjh=vsyjh; /*delete from `XSXPB` where scdm=vscdm and mdh=vmdh and xph=vxph and syjh=vsyjh and sfsc=0 and sfyx=1;*/ end; IF ERR_BG =1 THEN set ret =1; set ret2=0; Set errcode=1116; set rmsg = 'error ??' ; set err:='????????'; insert into `SYSLOG` (scdm,mdh,xph,syjh,procname,errmsg,errcode) values(vscdm,vmdh,vxph,vsyjh,'P_XSXP_DEL',err,errcode); end if ; commit;end;
ERROR 2013 : Lost connection to MySQL server during query
这是在mysql中执行的结果
Query OK, 0 rows affected (0.06 sec)mysql> select @A//
+------+
| @A |
+------+
| 1 |
+------+
1 row in set (0.00 sec)mysql> select @A,@B,@C//
+------+------+----------+
| @A | @B | @C |
+------+------+----------+
| 1 | 0 | error ?? |
+------+------+----------+
1 row in set (0.00 sec)mysql>
delete from YHQHS where syscdm=vscdm and symdh=vmdh and syxph=vxph and sysyjh=vsyjh;这一句符合条件的有多少条?是不是符合条件的过多或者你的表中数据很多查询时间过长?
你可以试一下select * from YHQHS where syscdm=vscdm and symdh=vmdh and syxph=vxph and sysyjh=vsyjh;(把变量换成实际值)看看需要多少时间?再检查一下这些变量。
mysql> show variables like '%timeout%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+----------------------------+-------+
10 rows in set (0.00 sec)mysql>
INSERT INTO `YHQHS` VALUES ('20100602633300000012', '4201005280284', null, '2.00', null, '2010-05-31 06:00:00', '2010-07-10 23:59:59', '26', '1333', '20100531101', '1', '2010-05-31 08:49:39', null, null, null, null, null, '????', '0', null, null, '0', '0', '174219;??100???????;1;1;||');调用
call p_xsxp_del('1333','20100531101','1',@A,@B,@C)
, '2.00', null, '2010-05-31 06:00:00', '2010-07-10 23:59:59', '26', '1333', '201
00531101', '1', '2010-05-31 08:49:39', null, null, null, null, null, '????', '0'
, null, null, '0', '0', '174219;??100???????;1;1;||');
Query OK, 1 row affected (0.00 sec)mysql> call p_xsxp_del('1333','20100531101','1',@A,@B,@C)
-> ;
Query OK, 0 rows affected (0.00 sec)mysql> select @A,@B,@C;
+------+------+----------+
| @A | @B | @C |
+------+------+----------+
| 1 | 0 | error ?? |
+------+------+----------+
1 row in set (0.00 sec)mysql>
| Variable_name | Value |
+--------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+--------------------------+-------+
9 rows in set数据只有一条,应该不是超时的问题。
-- ----------------------------
-- Table structure for XTCS
-- ----------------------------
CREATE TABLE `XTCS` (
`CSM` varchar(10) default NULL,
`CSZ` varchar(200) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `XTCS` VALUES ('GSDM', '91');
INSERT INTO `XTCS` VALUES ('SCDM', '26');
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO `XTCS` VALUES ('SCDM', '26');
Query OK, 1 row affected (0.00 sec)mysql> call p_xsxp_del('1333','20100531101','1',@A,@B,@C);
Query OK, 0 rows affected (0.01 sec)mysql> select @A,@B,@C;
+------+------+---------+
| @A | @B | @C |
+------+------+---------+
| 1 | 1 | success |
+------+------+---------+
1 row in set (0.00 sec)mysql>