CODEWITHSUNDEEP
phpmysqlpdo
Paul Ishak
Paul Ishak

Reputation: 1093

Mysql stored function truncating at 65535 chars in length

I am new to stored functions in mysql, and find myself wondering why my result is getting truncated at 65535 chars in length. I feel it has something to do with the concat function, but an unable to fix this problem.

I have over 10k rows in my test table, but this function only assembled 1936 rows into my generated table and I know it was truncated because my closing table tag is missing...Any insight would be greatly appreciated!

Here is my stored procedure:

CREATE DEFINER=`root`@`localhost` FUNCTION `hotdog`() RETURNS text CHARSET utf8
BEGIN
DECLARE str TEXT DEFAULT "<table>";
DECLARE done INT DEFAULT 0;
DECLARE var1 INT;
DECLARE var2 INT;
DECLARE curs CURSOR FOR  SELECT * from `test`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN curs;
    SET done = 0;
    REPEAT
        FETCH curs INTO var1, var2;
        SET str = concat(str, "<tr><td>");
        SET str = concat(str, var1);
        SET str = concat(str, "</td><td>");
        SET str = concat(str, var2);
        SET str = concat(str, "</td></tr>");
    UNTIL done END REPEAT;
SET str = concat(str, '</table>');
RETURN str;
END

Here is my php that is calling it...

<?
    include 'includes/config.inc.php';
    $q = $db->prepare("select hotdog() as hotdog1");
    $q->execute();
    $row = $q->fetch(PDO::FETCH_ASSOC);
    echo $row['hotdog1'];
    // for($i=1; $i<500000; $i++)
    // {
        // $qs = "INSERT INTO `test`(`id`, `test`) VALUES (Null,$i)";
        // $q = $db->prepare($qs);
        // $q->execute();
    // }
?>

Upvotes: 1

Views: 268

Answers (1)

sg-
sg-

Reputation: 2176

That's the size of a TEXT column.

http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

You are declaring str as TEXT. You need MEDIUMTEXT or LONGTEXT if you want to store more than that.

Upvotes: 2

Related Questions