Error 'Data truncated for column ...' while calling a MySql stored proc, with SET datatype and PDO

Question

MySQL table 'item' (simplified):

DROP TABLE IF EXISTS item;
CREATE TABLE item (
    item_id INTEGER(6) NOT NULL, 
    item_name VARCHAR(40) NOT NULL, 
    item_consumption SET ('racine','fruit','feuille','graine','tubercule','bulbe','fleur','tige'),
    CONSTRAINT pk_item 
            PRIMARY KEY (item_id)
 );

MySQL table 'species'

CREATE TABLE species (
    spec_id INTEGER(6) NOT NULL,   
    spec_name VARCHAR(40) NOT NULL, 
    CONSTRAINT pk_species 
        PRIMARY KEY (spec_id),
    CONSTRAINT fk_species_item 
        FOREIGN KEY (spec_id) REFERENCES item  (item_id)
);

MySQL stored proc 'addSpecies' :

DROP PROCEDURE IF EXISTS addSpecies;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE addSpecies (
    IN p_item_name VARCHAR(40),
    IN p_item_consumption SET('racine','fruit','feuille','graine','tubercule','bulbe','fleur','tige'),
    IN p_spec_name VARCHAR(40),
)
BEGIN

    DECLARE errno INTEGER;
    DECLARE text VARCHAR(200);
    DECLARE item_id INTEGER DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    -- GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
    -- SELECT errno AS MYSQL_ERROR;
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
     @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
    SELECT @full_error;
    ROLLBACK;
    END;

    START TRANSACTION;

    INSERT INTO item 
        (item_name, item_consumption)
        VALUES
        (p_item_name,p_item_consumption);

    SELECT LAST_INSERT_ID() INTO item_id;

    INSERT INTO species (spec_id,spec_name) 
        VALUES (item_id,p_spec_name);

    SELECT item_id;

    COMMIT WORK;

END$$
DELIMITER ;

When I run this stored proc in command mode, it works, but when I call it using PDO, I have got an error :

SQL command :

CALL addSpecies(:item_name,:item_consumption,:spec_name)

Bind array :

Array ( [:item_name] => test  [:item_consumption] => racine,feuille,tubercule,fleur  [:spec_name] => test)

error message :

erreur SQLSTATE[01000]: Warning: 1265 Data truncated for column 'p_item_consumption' at row 1

I tried using VARCHAR(100) for parameter, but the message is now for the database column.

Thanks for help

EDIT 1

PDO does support only numeric and string parameter binds.

Data type SET is MySQL specific.

Options :

use a mixed PDO parameter binds for numeric and string parameters, and value for the set type. ( MySQL add values to SET element )
convert SET datatype to VARCHAR(255) (or less)
use 2 tables to manage the n-n relationship between 'item' and 'consumption'

@Strawberry comment '...abomination...' : SET datatype converts this set to an integer. If you change the order of items in the fields description, your data becomes incoherent ! I will abandon the datatype SET

Use a string with values separator need needs code to check values (a trigger could assure that it is ok).

@Shadow comment : '...3NF...' : 'consumption' and 'consumptionItem' : each update needs DELETE consumptionItem for the item, and INSERT new consumptionItem

These 8 values should never change...but if they change from french to english, for example, nice work to do ! I will test the last 2 solutions and tell which I keep.

EDIT 2

From comments and thoughts, here is the choice I did : thanks for answering ! That works fine !

1) create a new table to store the 8 words (parts of plants that can be eated)

DROP TABLE IF EXISTS consumption;
CREATE TABLE consumption (
    cons_name VARCHAR(12) NOT NULL COMMENT "Intitulé/Intitulé du mode de consommation",  
    CONSTRAINT pk_consumption
        PRIMARY KEY (cons_name)
)ENGINE = InnoDB, DEFAULT CHARACTER SET utf8, COMMENT "Modes/Modes de consommation";

INSERT INTO consumption (cons_name)
VALUES 
("racine"),
("fruit"),
("feuille"),
("graine"),
("tubercule"),
("bulbe"),
("fleur"),
("tige");

2) create a table to link items to consumptions (for each plant, which part can be eated)

DROP TABLE IF EXISTS consumptionveg;
CREATE TABLE consumptionveg (
    veg_id INTEGER(6) NOT NULL, 
    cons_name VARCHAR(12) NOT NULL,
    CONSTRAINT pk_consumptionVeg
            PRIMARY KEY (cons_name, veg_id), 
    CONSTRAINT fk_consumptionveg_vegetal 
            FOREIGN KEY (veg_id) REFERENCES vegetal (veg_id)
                ON DELETE CASCADE,
    CONSTRAINT fk_consumptionveg_consumption 
            FOREIGN KEY (cons_name) REFERENCES consumption (cons_name)
                ON UPDATE CASCADE 
);

3) remove the SET datatype field

4) use a function to split a string : from Equivalent of explode() to work with strings in MySQL

DROP FUNCTION IF EXISTS split;
DELIMITER $$
CREATE FUNCTION split(
  ch VARCHAR(255),
  delim VARCHAR(4),
  pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(ch, delim, pos),
       LENGTH(SUBSTRING_INDEX(ch, delim, pos -1)) + 1),
       delim, '');
END$$
DELIMITER ;

5) create a stored procedure to create 'consumptionveg' from a string (using 'split' function)

DROP PROCEDURE IF EXISTS replaceVegConsumption;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE replaceVegConsumption (
    IN p_item_id INTEGER(6),
    IN p_veg_consumption VARCHAR(255)
)
BEGIN

    DECLARE errno INTEGER;
    DECLARE text VARCHAR(255);

    DECLARE ct  INT;
    DECLARE str  VARCHAR(255);
    DECLARE res  VARCHAR(255);
    DECLARE sep  VARCHAR(1);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    SHOW ERRORS;
    ROLLBACK;
    END;
    SET @enabled = TRUE;

  call debug_msg(@enabled, 'avant delete');

    DELETE FROM consumptionveg WHERE veg_id = p_item_id;
  call debug_msg(@enabled, 'apres delete');

    SET ct = 1;
    SET str =  '';
    SET res =  '';
    SET sep =  '';

    loop_label:  LOOP
        IF  ct > 10 THEN 
            LEAVE  loop_label;
        END  IF;
       SET res = trim(split(p_veg_consumption,',',ct));
       if res <> "" then
           if str NOT LIKE CONCAT('%',res,'%') THEN
            SET  str = CONCAT(str,sep,res);
            SET sep = ",";

            INSERT INTO consumptionveg (cons_name, veg_id)
                VALUES (res, p_item_id);

           END IF;
       end if;
        SET  ct = ct + 1;

    END LOOP;

END$$
DELIMITER ;

and call it in the main stored proc (simplified) :

DROP PROCEDURE IF EXISTS addSpecies;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE addSpecies (
... params
    IN p_veg_consumption VARCHAR(255),
... params
)
BEGIN

    DECLARE errno INTEGER;
    DECLARE text VARCHAR(255);
    DECLARE item_id INTEGER DEFAULT 0;


    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    SHOW ERRORS;
    ROLLBACK;
    END;

    START TRANSACTION;

... insert ......
SELECT LAST_INSERT_ID() INTO item_id;
... inserts ......


    CALL replaceVegConsumption(item_id, p_veg_consumption);

    SELECT item_id;

    COMMIT WORK;

END$$
DELIMITER ;

6) create a stored function that retreive item consumptions as a string :

DROP FUNCTION IF EXISTS getVegConsumption;
DELIMITER $$
CREATE FUNCTION getVegConsumption(
    p_veg_id INT
) RETURNS CHAR(255)  reads sql data
BEGIN
        DECLARE done INT DEFAULT FALSE;
        declare i_myfield int;
        declare iter int;
  DECLARE sep CHAR(1);
  DECLARE str CHAR(255);
  DECLARE cursor_VAL CHAR(255);
  DECLARE cursor_i CURSOR FOR SELECT cons_name 
    FROM consumptionVeg WHERE veg_id = p_veg_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   SET sep = " ";
  SET str = " ";
  SET done = 0;
  OPEN cursor_i;
  read_loop: LOOP
    FETCH NEXT FROM cursor_i INTO cursor_VAL; -- cursor_ID, cursor_VAL;
     IF done THEN
      LEAVE read_loop;
     END IF;
     SET str =  CONCAT(trim(str),sep, coalesce(trim(cursor_VAL)," "));
    SET sep = ",";
  END LOOP;
  CLOSE cursor_i;
  return str;
END;
$$
DELIMITER ;

7) use this function to retreive data, convert to checkbox, then to string and submit to stored procedure !

I suppose that my initial problem is due to PDO limitation. (I didn't check for the moment).

The column is too small for the data you are trying to insert. If you have long strings you can use TEXT as column type to make room. — colburton, Dec 07 '19 at 07:56
But I use SET datatype which is converted into integer value by MySQL engine — Patrick Dezecache, Dec 07 '19 at 08:00
I would normalise your data structure and use separate tables instead of the set data type. — Shadow, Dec 07 '19 at 08:02
Yes I know, but it is really a fixed length set of values. If there is no solution, that's what I will do. — Patrick Dezecache, Dec 07 '19 at 08:10
Even if there is a solution, it's still what I'd do. The SET data type is an abomination. — Strawberry, Dec 07 '19 at 08:16
Incidentally, note that the '6' in 'int(6)' doesn't actually do anything useful — Strawberry, Dec 07 '19 at 08:17
yes I will remove these values. (I use this 6 value to check integer value up to 999999) — Patrick Dezecache, Dec 07 '19 at 11:22

Error 'Data truncated for column ...' while calling a MySql stored proc, with SET datatype and PDO

EDIT 1

EDIT 2

0 Answers0