Mysql – Why is `max_allowed_packet` shows 2048 even though it’s set as 16M in thesqld.cnf

MySQL

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.

Best Answer

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

Related Solutions

Mysql – What max_allowed_packet is big enough, and why do I need to change it

It's OK to max out the max_allowed_packet to 1G. Whenever a MySQL Packet is constructed, it will not jump to 1G from the start. Why?

First you need to know what a MySQL Packet. Page 99 of the Book

Understanding MySQL Internals

explains it in paragraphs 1-3 as follows:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

Compare that with the MySQL Documentation on max_allowed_packet:

The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function. The default is 4MB as of MySQL 5.6.6, 1MB before that.

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

When you change the message buffer size by changing the value of the max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. On the client side, max_allowed_packet has a default of 1GB. Some programs such as mysql and mysqldump enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.

Given this information, you should be glad MySQL will expand and contract the MySQL Packet as needed. Therefore, go ahead and

set max_allowed_packet to 1G on both Master and Slave
set net_buffer_length to its max value of 1M on both Master and Slave

Master and Slave should match in terms of who they transmit data, especially BLOB data.

UPDATE 2013-07-04 07:03 EDT

From your messages concerning the relay log, it looks like you have the following

a corrupt relay log
a good master log

SUGGESTION

SHOW SLAVE STATUS\G
STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='(Relay_Master_Log_File from SHOW SLAVE STATUS\G)',
MASTER_LOG_POS=(Exec_Master_Log_Pos from SHOW SLAVE STATUS\G);
START SLAVE;

Running CHANGE MASTER TO clears all relay logs and starts with a new one. You will be replicating from the Last Master BinLog Event (BinLog,Position) that executed on the Slave.

Give it a Try !!!

MySQL temporary column in SELECT statement

Based on your current code, it appears that a product can have at most one match in either customer_price_increment or general_price_increment. With that fact in mind, I would probably try a different approach.

First, I would rewrite the base query returning all the prices like this:

SELECT
  p.id AS product_number,
  p.name AS product_name,
  p.redemption_price,
  COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
  COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
  p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
FROM product AS p
LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id

To filter on sale_price, I would just use the above query as a derived table, so that I could reference the sale_price alias and avoid repeating the entire expression in the WHERE clause:

SELECT *
FROM (
    SELECT
      p.id AS product_number,
      p.name AS product_name,
      p.redemption_price,
      COALESCE(p.redemption_price * cpi.increment_percentage / 100, 0) AS customer_price_increment,
      COALESCE(p.redemption_price * gpi.increment_percentage / 100, 0) AS general_price_increment,
      p.redemption_price * (1 + COALESCE(cpi.increment_percentage, gpi.increment_percentage, 0) / 100) AS sale_price
    FROM product AS p
    LEFT JOIN customer_price_increment AS cpi ON cpi.id = p.id AND cpi.customer_id = 4
    LEFT JOIN general_price_increment AS gpi ON gpi.id = p.id
) AS s
WHERE sale_price > 250
;

I believe you could also make the first query a view and then just select from it filtering the results as necessary:

SELECT *
FROM sale_prices_view
WHERE sale_price > 250
;