0

Here's the situation: I dumped structure of my db on one machine a tried to restore it second one. But in the process of restoring db error occurres and i have no idea what's wrong with that part of sql code.

Here's the mysql error:

ERROR 1064 (42000) at line 5846: 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 'TYPE=HEAP;
     WHILE TRUE DO
        SET _paramList = TRIM(_paramList);
       ' at line 13

Need to add, i dumped the db structure on mysql 5.1.59 and trying to restore on 5.5.30.

I've already tried compatible option in mysql dump and restore, searched internet with no success.

EDIT:

here's the whole procedure:

DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `ws_core_parameters`(IN is_proc_name CHAR(64), IN is_db_name CHAR(64))
BEGIN
    DECLARE _paramList VARCHAR(800);
    DECLARE _parameter VARCHAR(100);
    SELECT
        CAST(a.param_list AS CHAR(800))
    INTO
        _paramList
    FROM mysql.proc a
    WHERE a.name = is_proc_name
        AND a.db = is_db_name
    LIMIT 1;
    CREATE TEMPORARY TABLE system_proc.__parameters ( s_parameter VARCHAR(20) ) TYPE=HEAP;
    label: WHILE TRUE DO
        SET _paramList = TRIM(_paramList);
        IF(COALESCE(_paramList, '') = '') THEN
            LEAVE label;
        END IF;
        SET _parameter = SUBSTRING_INDEX(_paramList, ',', 1);
        SET _paramList = SUBSTR(_paramList, LENGTH(_parameter)+2);
        INSERT INTO system_proc.__parameters (s_parameter)
            VALUES (
                TRIM(
                    SUBSTRING_INDEX(
                        SUBSTR(_parameter,
                            LENGTH(
                                SUBSTRING_INDEX(_parameter, ' ', 1))+2), ' ', 1)));
    END WHILE label;
    SELECT
        SUBSTR(s_parameter, 1-(
            LENGTH(s_parameter))) AS s_parameter
    FROM system_proc.__parameters WHERE s_parameter != '';
    DROP TABLE system_proc.__parameters;
END */;;
1
  • Please post your procedure code. Commented Mar 12, 2013 at 10:16

1 Answer 1

1

Change TYPE=HEAP to either, ENGINE=HEAP or ENGINE=MEMORY

Links for reference:

TYPE=HEAP not working

MYSQL documentation

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.