CODEWITHSUNDEEP
mysqlvariables
Omar Juvera
Omar Juvera

Reputation: 12297

MYSQL: How to define or get LONG string variables

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?

Upvotes: 8

Views: 5433

Answers (3)

Omar Juvera
Omar Juvera

Reputation: 12297

SET GLOBAL group_concat_max_len = (7*1024);
SET GLOBAL max_allowed_packet = (50*1024*1024);

The root of the problem was the function GROUP_CONCAT. the documentation says GROUP_CONCAT's group_concat_max_len IS LIMITED BY max_allowed_packet: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

50% to @echo_me and 50% to @barmar. It has to be done together, otherwise it wont work.

Full code:

SET GLOBAL group_concat_max_len = (7*1024);
SET GLOBAL max_allowed_packet = (50*1024*1024);

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;

Upvotes: 1

Barmar
Barmar

Reputation: 780974

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

SET group_concat_max_len = 10000

Upvotes: 5

echo_Me
echo_Me

Reputation: 37233

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

Upvotes: 2

Related Questions