MYSQL: How to define or get LONG string variables

Question

I am running the following query:

Variables

SET @src = 'Test';
SET @tgt = 'Test2';
SET @db  = 'Test';
SET @pk  = 'ID, MyPk, etc';

SELECT CONCAT( @pk, GROUP_CONCAT(CONCAT( ", ", COLUMN_NAME) SEPARATOR "") )
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @db
AND TABLE_NAME = @src
INTO @cols;

Sample

SET @sql = CONCAT( 'INSERT INTO `', @tgt, '` (SELECT ', @cols, ' FROM `', @src, '`);' );
PREPARE stm FROM @sql;
EXECUTE stm;

It works...with small tables and I can use this @cols for multiple purposes. However, it stops working with large tables (Large amount of fields). I noticed it breaks at 1024 characters. It ignores LIMIT. Is there away to get a variable longer than 1024 characters or a way around this problem?

Answer 1

The limit is on the result of GROUP_CONCAT(). You can change this with:

SET group_concat_max_len = 10000

Answer 2

In short, you need to locate your mysql my.cnf config file and add or change the max allowed packet:

 [mysqld]

 max_allowed_packet = 50M