0

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.

Improve this question
5
  • Do you have another cnf file perhaps that you are reading from? Alternatively could someone else have changed it manually using SET GLOBAL max_allowed_packet = 2147483648;
    – IGGt
    Jan 20, 2020 at 16:26
  • What's also interesting that If session variable differs from global one - look additional init commands in connection properties.
    – Akina
    Jan 20, 2020 at 16:51
  • @Akina, @IGGt it seems that after I've restarted mysql the values of the session and global variables are the same and the error is now gone, but I can't seem to figure out how running SET GLOBAL... helped. Do you maybe have a guess where did the command make the change and why it persisted over database restart?
    – Max Koretskyi
    Jan 20, 2020 at 17:05
  • In general it is an effect of additional connection init commands on the CLIENT side.
    – Akina
    Jan 20, 2020 at 17:11
  • @Akina, thanks, not sure that's the case here. Anyways, thanks. It's working now.
    – Max Koretskyi
    Jan 20, 2020 at 17:20

1 Answer 1

Reset to default
2

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.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.