You probably have to change it for both the client (you are running to do the import) AND the daemon mysqld that is running and accepting the import.
For the client, you can specify it on the command line:
mysql --max_allowed_packet=100M -u root -p database < dump.sql
Also, change the my.cnf or my.ini file (usually found in /etc/mysql/) under the mysqld section and set:
max_allowed_packet=100M
or you could run these commands in a MySQL console connected to that same server:
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
(Use a very large value for the packet size.)
You can use GROUP_CONCAT
:
SELECT person_id,
GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Ludwig stated in his comment, you can add the DISTINCT
operator to avoid duplicates:
SELECT person_id,
GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY
:
SELECT person_id,
GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:
SET group_concat_max_len = 2048;
Of course, you can change 2048
according to your needs. To calculate and assign the value:
SET group_concat_max_len = CAST(
(SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
FROM peoples_hobbies
GROUP BY person_id) AS UNSIGNED);
Best Answer
That is allowed by default on MySQL.
What is disabled by default is remote
root
access. If you want to enable that, run this SQL command locally:And then find the following line and comment it out in your
my.cnf
file, which usually lives on/etc/mysql/my.cnf
on Unix/OSX systems. In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf).If it's a Windows system, you can find it in the MySQL installation directory, usually something like
C:\Program Files\MySQL\MySQL Server 5.5\
and the filename will bemy.ini
.Change line
to
And restart the MySQL server (Unix/OSX, and Windows) for the changes to take effect.