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?

see [this question](http://stackoverflow.com/questions/12294955/storage-limits-of-mysql-user-defined-variables) on Storage limits of MySQL user-defined variables — km6zla, May 23 '13 at 19:51
The answer there says that strings can hold 16MB, so it doesn't explain why he's getting cut off at 1K. — Barmar, May 23 '13 at 19:57
What happens if you just do `SELECT @sql`? Maybe the limit is on the size of prepared statements, not the size of the string — Barmar, May 23 '13 at 20:00
@Barmar Yes and no. The limit is on the size of the variable. A string with anything longer than 1024 characters (into @cols) gets trimmed. — Omar, May 23 '13 at 20:41
@Barmar I was wrong. The limit was being set by GROUP_CONCAT. http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat — Omar, May 23 '13 at 22:38
Why are you telling me that? I wrote it almost 2 hours ago in my answer. — Barmar, May 23 '13 at 22:55
possible duplicate of [result truncated,a MYSQL bug?](http://stackoverflow.com/questions/996872/result-truncated-a-mysql-bug) — Barmar, May 23 '13 at 22:58

score 4 · Answer 1 · answered May 23 '13 at 21:11

4

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

SET group_concat_max_len = 10000

answered May 23 '13 at 21:11

Barmar

741,623
53
500
612

echo_Me · Answer 2 · 2013-05-23T20:37:17.510

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

edited May 23 '13 at 20:37

answered May 23 '13 at 20:07

echo_Me

37,078
5
58
78

I am not using php. I am using MySQL/Workbench/Etc, – Omar May 23 '13 at 20:34
I tryed it. I even found out you can do `SET GLOBAL max_allowed_packet=52428800;` (http://dev.mysql.com/doc/refman/5.0/en/program-variables.html). But it still did not work. – Omar May 23 '13 at 21:03

score 1 · Accepted Answer · answered May 23 '13 at 22:37

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;

Note that (this was my case at least) you need to close the current mysql connection / client and start another one for this to work. — Eduard Luca, Jun 13 '16 at 10:27

score -1 · Answer 4 · edited Feb 09 '17 at 18:43

-1

Just use above solution:

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

Then, click in Database; Connect to Database; and create another connection. Run code in this newly created connection.

edited Feb 09 '17 at 18:43

Shawn

3,583
8
46
63

answered Feb 09 '17 at 14:55

Alberto Campos Silva

36
2

1

Welcome to Stack Overflow! Though we thank you for your answer, it would be better if it provided additional value on top of the other answers. In this case, your answer does not provide additional value, since another user already posted that solution. If a previous answer was helpful to you, you should vote it up instead of repeating the same information. – Toby Speight Feb 09 '17 at 15:08

MYSQL: How to define or get LONG string variables

Variables

Sample

4 Answers4

Linked