动态地将内存分配给列的MySQL查询

Question

I have this query:

SELECT DISTINCT(po.mark) franquicia, COUNT(po.id) failures, GROUP_CONCAT(po.log_transaction SEPARATOR ';') logs, DATE(po.created) fecha
FROM pagos_onlines AS po
INNER JOIN usuarios_tokens AS ut ON po.usuario_token = ut.id
WHERE status = 'REJECTED'
AND created > '2017-10-03 11:00:01'
GROUP BY fecha, franquicia;

The problem is that log_transaction is a type BLOB in the table and it stores JSON data, so the field "logs" should be a huge string with concatenated JSONs. When I run this query only one and a half JSON appeared in the field "logs", so my guess is that the memory for that field runs out of space.

Is there a way to allocate memory on the fly for a specific column when running a MySQL SELECT query?

Answer 1

This query almost certainly throws a warning about the data being cut by GROUP_CONCAT. If you are using a client or library that ignores warnings, you can run the query SHOW WARNINGS; immediately after running your query, and confirm that this warning is present.

To resolve this, you need to increase the value of the system variable @@GROUP_CONCAT_MAX_LEN to get longer results without truncation.

The default value is only 1024 (bytes, not characters, according to the documentation). You can change this in global config or change it in your session before running this query.