Description:
Hi,
The stored procedure is running during work hours every day. And the issue generating from it happens about every one month. I think it is the INSERT portion generates this issue.
Stored procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERTSOTRAN`(IN whID TINYINT(1), IN timeVal TINYINT(1))
BEGIN
DECLARE msg VARCHAR(50) DEFAULT NULL;
INSERT INTO so_detail
(so_num, cust_num, part_num, descrip, disc, cost, price, qtyord, qtyshp, extprice, ord_date, req_date, dealer_type, part_cls, location, sostat, discls, wh_id)
SELECT s.sono, s.custno, s.item, s.descrip, s.disc, s.cost, s.price, s.qtyord, s.qtyshp, s.extprice, CONCAT(s.ordate, ' ', time(now())), CONCAT(s.rqdate, ' ', time(now())),
s.terr, s.class, s.seq, s.sostat, s.disclass, whID
FROM SBT.SOTRAN s
LEFT JOIN so_detail d ON s.sono = d.so_num AND s.item = d.part_num
WHERE d.so_num IS NULL;
drop table if exists temp;
create table temp (id int, sono varchar(8), custno varchar(6), item varchar(15), descrip varchar(35), disc DOUBLE, cost DOUBLE, price DOUBLE,qtyord DOUBLE, qtyshp DOUBLE, extprice DOUBLE, ordate DATE, rqdate DATE, shipdate DATE, terr varchar(2), class varchar(2), seq varchar(4), sostat varchar(1), disclass varchar(2));
set @row := 0;
insert into temp ( sono, custno, item, descrip, disc, cost, price,qtyord, qtyshp, extprice, ordate, rqdate, shipdate, terr, class, seq, sostat, disclass, id)
select *, @row:=@row+1 as id
from SBT.SOTRAN;
update so_detail d
join (
select sono, item, sostat, disc, price, qtyord, qtyshp, extprice
from
(
select MAX(id) as rowID
from temp
group by sono, item
) AS t2
inner join temp AS t1
on t2.rowID = t1.id
) AS t3
on d.so_num= t3.sono and d.part_num= t3.item
set
d.sostat= t3.sostat,
d.disc = t3.disc,
d.price = t3.price,
d.qtyord = t3.qtyord,
d.qtyshp = t3.qtyshp,
d.extprice = t3.extprice
where d.wh_id=1;
END
Tables involved:
CREATE TABLE `so_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`so_num` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`cust_num` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`part_num` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`descrip` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL,
`disc` decimal(10,3) DEFAULT NULL,
`cost` decimal(10,3) DEFAULT NULL,
`price` decimal(10,3) DEFAULT NULL,
`qtyord` int(11) DEFAULT NULL,
`qtyshp` int(11) DEFAULT NULL,
`extprice` decimal(10,3) DEFAULT NULL,
`ord_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`req_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`shp_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`dealer_type` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`part_cls` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`location` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
`sostat` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`discls` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`wh_id` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `so_num` (`so_num`),
KEY `cust_num` (`cust_num`),
KEY `part_num` (`part_num`),
KEY `descrip` (`descrip`),
KEY `disc` (`disc`),
KEY `cost` (`cost`),
KEY `price` (`price`),
KEY `qtyord` (`qtyord`),
KEY `qtyshp` (`qtyshp`),
KEY `ord_date` (`ord_date`),
KEY `req_date` (`req_date`),
KEY `shp_date` (`shp_date`),
KEY `part_cls` (`part_cls`),
KEY `location` (`location`),
KEY `sostat` (`sostat`),
KEY `discls` (`discls`),
KEY `wh_id` (`wh_id`),
KEY `dealer_type` (`dealer_type`),
KEY `extprice` (`extprice`)
) ENGINE=InnoDB AUTO_INCREMENT=827181 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `SOTRAN` (
`sono` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
`custno` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
`item` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`descrip` varchar(35) COLLATE utf8_unicode_ci NOT NULL,
`disc` double NOT NULL,
`cost` double NOT NULL,
`price` double NOT NULL,
`qtyord` double NOT NULL,
`qtyshp` double NOT NULL,
`extprice` double NOT NULL,
`ordate` date NOT NULL,
`rqdate` date NOT NULL,
`shipdate` date NOT NULL,
`terr` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`class` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`seq` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
`sostat` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
`disclass` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
KEY `sono` (`sono`),
KEY `item` (`item`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
The client we use is mysql workbench 6.3
How to repeat:
It generates duplicates every one month or less than that.
The generated duplicated records are consecutive and every time it happens, it has one extra line which is exact the same except the id is one number greater, below is the result of the issue happened lately:
# id, so_num, cust_num, part_num, descrip, disc, cost, price, qtyord, qtyshp, extprice, ord_date, req_date, shp_date, dealer_type, part_cls, location, sostat, discls, wh_id
'826830', '370457', '***', '52562-06130', 'RETAINER, RR BUMPER,', '25.000', '4.216', '8.260', '2', '0', '12.390', '2016-09-12 13:47:49', '2016-09-12 13:42:18', '0000-00-00 00:00:00', 'TY', 'A0', '311D', '', '', '1'
'826831', '370457', '***', '52562-06130', 'RETAINER, RR BUMPER,', '25.000', '4.216', '8.260', '2', '0', '12.390', '2016-09-12 13:47:49', '2016-09-12 13:42:18', '0000-00-00 00:00:00', 'TY', 'A0', '311D', '', '', '1'
Thank you !
Description: Hi, The stored procedure is running during work hours every day. And the issue generating from it happens about every one month. I think it is the INSERT portion generates this issue. Stored procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERTSOTRAN`(IN whID TINYINT(1), IN timeVal TINYINT(1)) BEGIN DECLARE msg VARCHAR(50) DEFAULT NULL; INSERT INTO so_detail (so_num, cust_num, part_num, descrip, disc, cost, price, qtyord, qtyshp, extprice, ord_date, req_date, dealer_type, part_cls, location, sostat, discls, wh_id) SELECT s.sono, s.custno, s.item, s.descrip, s.disc, s.cost, s.price, s.qtyord, s.qtyshp, s.extprice, CONCAT(s.ordate, ' ', time(now())), CONCAT(s.rqdate, ' ', time(now())), s.terr, s.class, s.seq, s.sostat, s.disclass, whID FROM SBT.SOTRAN s LEFT JOIN so_detail d ON s.sono = d.so_num AND s.item = d.part_num WHERE d.so_num IS NULL; drop table if exists temp; create table temp (id int, sono varchar(8), custno varchar(6), item varchar(15), descrip varchar(35), disc DOUBLE, cost DOUBLE, price DOUBLE,qtyord DOUBLE, qtyshp DOUBLE, extprice DOUBLE, ordate DATE, rqdate DATE, shipdate DATE, terr varchar(2), class varchar(2), seq varchar(4), sostat varchar(1), disclass varchar(2)); set @row := 0; insert into temp ( sono, custno, item, descrip, disc, cost, price,qtyord, qtyshp, extprice, ordate, rqdate, shipdate, terr, class, seq, sostat, disclass, id) select *, @row:=@row+1 as id from SBT.SOTRAN; update so_detail d join ( select sono, item, sostat, disc, price, qtyord, qtyshp, extprice from ( select MAX(id) as rowID from temp group by sono, item ) AS t2 inner join temp AS t1 on t2.rowID = t1.id ) AS t3 on d.so_num= t3.sono and d.part_num= t3.item set d.sostat= t3.sostat, d.disc = t3.disc, d.price = t3.price, d.qtyord = t3.qtyord, d.qtyshp = t3.qtyshp, d.extprice = t3.extprice where d.wh_id=1; END Tables involved: CREATE TABLE `so_detail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `so_num` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL, `cust_num` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL, `part_num` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `descrip` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL, `disc` decimal(10,3) DEFAULT NULL, `cost` decimal(10,3) DEFAULT NULL, `price` decimal(10,3) DEFAULT NULL, `qtyord` int(11) DEFAULT NULL, `qtyshp` int(11) DEFAULT NULL, `extprice` decimal(10,3) DEFAULT NULL, `ord_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `req_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `shp_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `dealer_type` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL, `part_cls` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL, `location` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL, `sostat` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, `discls` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL, `wh_id` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `so_num` (`so_num`), KEY `cust_num` (`cust_num`), KEY `part_num` (`part_num`), KEY `descrip` (`descrip`), KEY `disc` (`disc`), KEY `cost` (`cost`), KEY `price` (`price`), KEY `qtyord` (`qtyord`), KEY `qtyshp` (`qtyshp`), KEY `ord_date` (`ord_date`), KEY `req_date` (`req_date`), KEY `shp_date` (`shp_date`), KEY `part_cls` (`part_cls`), KEY `location` (`location`), KEY `sostat` (`sostat`), KEY `discls` (`discls`), KEY `wh_id` (`wh_id`), KEY `dealer_type` (`dealer_type`), KEY `extprice` (`extprice`) ) ENGINE=InnoDB AUTO_INCREMENT=827181 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `SOTRAN` ( `sono` varchar(8) COLLATE utf8_unicode_ci NOT NULL, `custno` varchar(6) COLLATE utf8_unicode_ci NOT NULL, `item` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `descrip` varchar(35) COLLATE utf8_unicode_ci NOT NULL, `disc` double NOT NULL, `cost` double NOT NULL, `price` double NOT NULL, `qtyord` double NOT NULL, `qtyshp` double NOT NULL, `extprice` double NOT NULL, `ordate` date NOT NULL, `rqdate` date NOT NULL, `shipdate` date NOT NULL, `terr` varchar(2) COLLATE utf8_unicode_ci NOT NULL, `class` varchar(2) COLLATE utf8_unicode_ci NOT NULL, `seq` varchar(4) COLLATE utf8_unicode_ci NOT NULL, `sostat` varchar(1) COLLATE utf8_unicode_ci NOT NULL, `disclass` varchar(2) COLLATE utf8_unicode_ci NOT NULL, KEY `sono` (`sono`), KEY `item` (`item`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci The client we use is mysql workbench 6.3 How to repeat: It generates duplicates every one month or less than that. The generated duplicated records are consecutive and every time it happens, it has one extra line which is exact the same except the id is one number greater, below is the result of the issue happened lately: # id, so_num, cust_num, part_num, descrip, disc, cost, price, qtyord, qtyshp, extprice, ord_date, req_date, shp_date, dealer_type, part_cls, location, sostat, discls, wh_id '826830', '370457', '***', '52562-06130', 'RETAINER, RR BUMPER,', '25.000', '4.216', '8.260', '2', '0', '12.390', '2016-09-12 13:47:49', '2016-09-12 13:42:18', '0000-00-00 00:00:00', 'TY', 'A0', '311D', '', '', '1' '826831', '370457', '***', '52562-06130', 'RETAINER, RR BUMPER,', '25.000', '4.216', '8.260', '2', '0', '12.390', '2016-09-12 13:47:49', '2016-09-12 13:42:18', '0000-00-00 00:00:00', 'TY', 'A0', '311D', '', '', '1' Thank you !