Bug #107395 max-allowed-packet has only global scope (no session scope)
Submitted: 25 May 11:12 Modified: 31 May 21:14
Reporter: Bogdan Kecman Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[25 May 11:12] Bogdan Kecman
Description:
In documentation:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_pa...

it is stated that max_allowed_packet has session scope too, it does not.

How to repeat:
mysql [localhost:8027] {msandbox} ((none)) > set session max_allowed_packet=77108864;
ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value

Suggested fix:
fix the documentation

- Scope 	Global, Session
+ Scope 	Global
[31 May 21:14] Jon Stephens
This variable is a bit odd and has separate global and session scopes, the session scope being read-only. From sys_vars.cc:

static bool check_max_allowed_packet(sys_var *self, THD *thd, set_var *var) {
  longlong val;
  if (session_readonly(self, thd, var)) return true;

  val = var->save_result.ulonglong_value;
  if (val < (longlong)global_system_variables.net_buffer_length) {
    push_warning_printf(thd, Sql_condition::SL_WARNING, WARN_OPTION_BELOW_LIMIT,
                        ER_THD(thd, WARN_OPTION_BELOW_LIMIT),
                        "max_allowed_packet", "net_buffer_length");
  }
  return false;
}

static Sys_var_ulong Sys_max_allowed_packet(
    "max_allowed_packet",
    "Max packet length to send to or receive from the server",
    SESSION_VAR(max_allowed_packet), CMD_LINE(REQUIRED_ARG),
    VALID_RANGE(1024, 1024 * 1024 * 1024), DEFAULT(64 * 1024 * 1024),
    BLOCK_SIZE(1024), NO_MUTEX_GUARD, NOT_IN_BINLOG,
    ON_CHECK(check_max_allowed_packet));

static Sys_var_ulong Sys_replica_max_allowed_packet(
    "replica_max_allowed_packet",
    "The maximum size of packets sent from an upstream source server to this "
    "server.",
    GLOBAL_VAR(replica_max_allowed_packet), CMD_LINE(REQUIRED_ARG),
    VALID_RANGE(1024, MAX_MAX_ALLOWED_PACKET), DEFAULT(MAX_MAX_ALLOWED_PACKET),
    BLOCK_SIZE(1024));

The cited documentation explains the reason for this: 

    The session value of this variable is read only. The client can
    receive up to as many bytes as the session value. However, the
    server does not send to the client more bytes than the current
    global max_allowed_packet value. (The global value could be less
    than the session value if the global value is changed after the
    client connects.)

So this is not a bug.

Thanks!

jon.