Why is `max_allowed_packet` shows 2048 even though it's set as 16M in mysqld.cnf

Question

I've got the error related to max_allowed_packet:

[S1000] Packet for query is too large (2,127 > 2,048). You can change this value on the server by setting the ‘max_allowed_packet’ variable.

So the limit is 2048. I've checked /etc/mysql/mysql.conf.d/mysqld.cnf and the value there is 16Mb:

max_allowed_packet = 16M

I've ran the query:

SHOW VARIABLES LIKE 'max_allowed_packet';

Which gave me 2,048. So the values are different. Is my assumption correct that the value in mysqld.cnf specifies limit for server while the query gave me the limit for client side? If so, where do I configure the value for the client side?

By running this query:

SET GLOBAL max_allowed_packet=16777216;

I only set the variable for the current session and as I understand it'll be reset after I restart the database. What's also interesting that:

SHOW VARIABLES LIKE 'max_allowed_packet';

still gives me 2,048m but:

SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';

gives me 16777216.

I need to set it in the client configuration file. But is there such a thing? I've done some reading but it's still vague.

Answer 1

Settings in my.cnf (etc) are seen only when mysqld starts up.

Settings changed by SET GLOBAL... are not seen until someone logs in. But, such settings go away when mysqld is restarted. (This item probably explains what you encountered.)

Settings changed for the "session" last only for that one connection, and only until disconnecting.

(MySQL 8.0 has some more situations, due to being about the "persist" changes across restarts.)

(Not all settings have a GLOBAL/SESSION split as implied above.)