mysql 1038,错误“ 1038内存不足，请考虑增加排序缓冲区大小

In symfony2, doctrine2, I have a query which triggers an error :

Error "1038 Out of sort memory, consider increasing server sort buffer size

The query :

$queryBuilder = $this

->createQueryBuilder('object')

->leftJoin('object.objectCategory', 'c')

->leftJoin('object.medias', 'm')

->leftJoin('object.recipients', 'r')

->leftJoin('object.answers', 'a')

->leftJoin('object.tags', 't')

->leftJoin('object.user', 'u')

->leftJoin('object.votes', 'v')

->leftJoin('object.comments', 'comments')

->leftJoin('v.user', 'vuser')

->addSelect('c, t, v, u')

->groupBy('object, c, t, v, u')

->where('object.isVisible = :isVisible')

->orderBy('object.createdAt', 'DESC')

->setParameter('isVisible', true)

->addSelect('SUM(v.value) AS HIDDEN vote_value')

->orderBy('vote_value', 'DESC')

;

If I omit the group by, it runs just fine.

If I add select and group by less elements, it runs fine also but then I have more subqueries launched in my twig templates.

How can I either optimize this query to not have the error OR get rid of the error by allocating more memory (ideally just for this query) ?

解决方案

You probably need to increase the buffer size of mysql in the /etc/mysql/my.cnf , set

sort_buffer_size to 256K