执行此存储过程时，Mysql 停止运行 [英] Mysql Stops running when this stored procedure is executed

问题描述

早上好，

这是这个问题的后续.mysqld.exe 不断崩溃.

This is a follow on from this question. mysqld.exe keeps crashing.

我已将崩溃范围缩小到单个存储过程，该存储过程在执行此存储过程时似乎会崩溃.

I have narrowed the crashing down to a single stored procedure which just seems to fall over when this stored procedure gets executed.

我已将其从原始状态拆分为三个存储过程.

I have split it down from its original state to three stored procedures.

spProductGroupMenu的主要程序如下:

The main procedure spProductGroupMenu is as follows:

DELIMITER $$

USE `phclothing`$$

DROP PROCEDURE IF EXISTS `spProductGroupMenu`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spProductGroupMenu`(
spGrp VARCHAR(3),
spProductType ENUM('clothing','parts')
)
BEGIN
DECLARE qryType ENUM('group','manufacturer','nothing');


IF spProductType='clothing' THEN
    SET qryType = (SELECT 
                (CASE WHEN 
                    (SELECT 
                        COUNT(DISTINCT(productGroup)) 
                    FROM 
                        tblclothingitems 
                    WHERE 
                        productGroup=spGrp)>0 
                    THEN 'group' ELSE
                (CASE WHEN 
                    (SELECT 
                        COUNT(DISTINCT(productManufacturer)) 
                    FROM 
                        tblclothingitems 
                    WHERE 
                        productManufacturer=spGrp)>0
                    THEN 'manufacturer' ELSE
                'nothing' END) END));
ELSE
    SET qryType = (SELECT 
                (CASE WHEN 
                    (SELECT 
                        COUNT(DISTINCT(productGroup)) 
                    FROM 
                        tblpartsitems 
                    WHERE 
                        productGroup=spGrp)>0 
                    THEN 'group' ELSE
                (CASE WHEN 
                    (SELECT 
                        COUNT(DISTINCT(productManufacturer)) 
                    FROM 
                        tblpartsitems 
                    WHERE 
                        productManufacturer=spGrp)>0
                    THEN 'manufacturer' ELSE
                'nothing' END) END));
END IF;



IF qryType='manufacturer' THEN

    CALL spPGMMans(spGrp,spProductType,qryType); ************

ELSEIF qryType='group' THEN

    CALL spPGMGrp(spGrp,spProductType,qryType); ************
ELSE
    -- This is not going to be used
    SELECT
        DISTINCT(productgroup) grpCode,
        (CASE WHEN spProductType='clothing' THEN
            CONCAT('/',grpSEO,'.html') 
        ELSE
            CONCAT(spProductType,'/',grpSEO,'.html') END)seoLink,
        pageH1 seoLinkTitle,
        'allGroups' entryType,
        qryType
    FROM
        tblclothingitems ci
    LEFT JOIN
        tblclothinggroups cg
    ON
        ci.productGroup = cg.grpcode
    UNION ALL
    SELECT
        DISTINCT(productManufacturer) grpCode,
        (CASE WHEN spProductType='clothing' THEN            
            cm.urlExt 
        ELSE
            CONCAT(spProductType,'/',cm.urlExt) END)seoLink,
        manTitle seoLinkTitle,
        'allMans' entryType,
        qryType
    FROM
        tblclothingitems ci
    LEFT JOIN
        tblclothingmanufacturers cm
    ON
        ci.productManufacturer = cm.manCode
    ORDER BY entryType, seoLinkTitle ASC;   
END IF;
END$$

分隔符;

这曾经由一系列查询组成，并根据传入的内容使用 union all 语句，但这引发了关于 mysql 服务器崩溃的警报.所以 ************ 是我将存储过程分成三个的地方.这些存储过程中的每一个都以自己的方式在服务器上运行，但由于某种原因，当这个存储过程被调用时，服务器就会崩溃并停止运行.

This used to consist of a series of queries with union all statements depending on what was passed in but this raised the alarm about the mysql server falling over. So where the ************ are is where i have divided the stored procedure into three. Each of these stored procedures runs on the server in its own right but for some reason when this stored procedure gets called the server just falls over and stops running.

我一直在检查日志，正是当这个存储过程运行时，与服务器的连接才开始.

I have been checking the logs throughout and it is when this stored procedure is run that the connection to the server goes.

如果有人知道为什么会发生这种情况，或者如果有人有过这种情况的经验，请告诉我.

If anybody has any clues as to why this may be happening or if anybody has had experience of this situation then please let me know.

非常感谢.

格雷厄姆

推荐答案

好吧，这显然很奇怪.根据预感，我单击了存储过程中每一行的末尾并删除了所有空格，以防万一有任何错误的不可打印字符导致了问题，看起来它似乎修复了它.

Ok well this clearly very strange. Following a hunch I clicked to the end of every line in the stored procedure and removed all white space just in case there were any erroneous non printable characters that were causing an issue, low and behold it seemed to fix it.

为了测试这个理论，我再次将存储过程反转回导致服务器崩溃的那一点.我在服务器上运行它，它也运行良好，这显然很奇怪.

To test the theory I then reversed the stored procedure again back to the point when it was causing the server to crash. I ran it on the server and it worked as well, which is clearly very odd.

不用说我完全傻眼了，为什么首先会发生这个错误，但只能假设它是存储过程中行尾的非打印字符.

Needless to say I am completely dumb-founded as to why this error occurred in the first place but can only presume that it was a non-printing character at the end of a line in the stored procedure.

如果你看过，干杯！格雷厄姆

Cheers if you took a look! Graham

这篇关于执行此存储过程时，Mysql 停止运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！