Error Code: 1054. Unknown column 'abcdef' in 'field list' in stored procedure

Question

I have written the following stored procedure. It was working quite fine and was giving correct results. But after some addition of attributes it shows the error. If I change the name of last retrieved attribute to a four letters word then it runs correctly. If I run it as a stored procedure then it shows the error: "Error Code: 1054. Unknown column 'fiel' in 'field list'"

If run as independent statements then shows the errors: "0 row(s) affected, 1 warning(s): 1260 Row 69 was cut by GROUP_CONCAT()" "Error Code: 1054. Unknown column 'fiel' in 'field list'"

    CREATE DEFINER=`root`@`localhost` PROCEDURE `getCases`()
      BEGIN
        SET @sql = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
          information_schema.columns WHERE table_schema = 'xyz_data_base' 
            AND table_name = 'table1' 
            AND column_name NOT IN ('col_one', 'col_last')), 
            ', table2.Name from xyz_data_base.table1, xyz_data_base.table2 
              where table1.col_last = table2.Id');  

       PREPARE stmt1 FROM @sql;

       EXECUTE stmt1;
    END

see https://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length — etsa, Aug 22 '17 at 10:25

score 0 · Answer 1 · answered Aug 22 '17 at 13:03

Before you run group_concat you must change the system parameter group_concat_max_len to prevent results cut.

    CREATE DEFINER=`root`@`localhost` PROCEDURE `getCases`()
      BEGIN

        SET @@session.group_concat_max_len = 10000;

        SET @sql = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
          information_schema.columns WHERE table_schema = 'xyz_data_base' 
            AND table_name = 'table1' 
            AND column_name NOT IN ('col_one', 'col_last')), 
            ', table2.Name from xyz_data_base.table1, xyz_data_base.table2 
              where table1.col_last = table2.Id');  

       PREPARE stmt1 FROM @sql;

       EXECUTE stmt1;
    END

Error Code: 1054. Unknown column 'abcdef' in 'field list' in stored procedure

1 Answers1