| Bug #107394 | mysqlbing|mysql recover data limit max transaction limit 1G | ||
|---|---|---|---|
| Submitted: | 25 May 2022 10:09 | Modified: | 25 May 2022 13:52 |
| Reporter: | peng gao | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.28 | OS: | Any |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[25 May 2022 10:38]
MySQL Verification Team
Hi, It is not a bug. max_allowed_packet can be changed "per session" also and 1G is some default we chose based on our experience as best general case value. What is important is that you can change it (even per session) when you need it to be bigger. Kind regards
[25 May 2022 11:00]
peng gao
mysql> set max_allowed_packet= 2*1024*1024*1024; ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value mysql> set global max_allowed_packet= 2*1024*1024*1024; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect max_allowed_packet value: '2147483648' | +---------+------+------------------------------------------------------------+ 1 row in set (0.02 sec) Only set global not session and max values is 1G, but mysqlbinlog|mysql can't recover transaction have more than 1G(Maximum Value 1073741824) event. I think this is very important scene, use backup and binlog recover database to any point. If big transaction can't recovery ,this is very troubling. Thanks!!
[25 May 2022 11:09]
MySQL Verification Team
Hi, Yes, you are right, our documentation there is faulty, it does not work for session, only global, but you can change it in realtime when needed (not ideal as in-session as documented would be better). thanks for the report
[25 May 2022 12:22]
Ye Jinrong
The key problem is max_allowed_packet can not exceed 1GB, so when a large transaction exceeds 1GB, it cannot be recovered by mysqlbin binlog.xx | mysql
[25 May 2022 13:49]
peng gao
Hi ：
I think documentation is correct like:
Command-Line Format --max-allowed-packet=#
System Variable max_allowed_packet
Scope Global, Session
Dynamic Yes
SET_VAR Hint Applies No
Type Integer
Default Value 67108864
Minimum Value 1024
Maximum Value 1073741824
in master-slave one event is net packet. see dump thread code(5.7).
Log_event::read_log_event
if (data_len < LOG_EVENT_MINIMAL_HEADER_LEN ||
data_len > max(current_thd->variables.max_allowed_packet,
opt_binlog_rows_event_max_size + MAX_LOG_EVENT_HEADER))
so one event large than 1G ,this situation is rare.
But mysql|mysqlbinlog one transaction all event is a mysqld read net packet,this not like master-slave ,when net packet large than max limit of max_allowed_packet(1G), this situation is very common.
I think "can change it in realtime when needed" is net_realloc function(5.7) .
net_read_packet
->net_realloc
length >= net->max_packet_size
but net->max_packet_size max limit is 1024 * 1024 * 1024, so "realtime change" upper limit is 1G.
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(4096 * 1024),
BLOCK_SIZE(1024), NO_MUTEX_GUARD, NOT_IN_BINLOG,
ON_CHECK(check_max_allowed_packet));
In my issue, i change VALID_RANGE(1024, 1024 * 1024 * 1024) to VALID_RANGE(1024, 8l * 1024 * 1024 * 1024) , max limit change to 8G
and set global max_allowed_packet=8*1024*1024*1024 it work fine.
we add some log output in net_realloc function like :
2022-05-25T13:44:21.773569Z 5 [Note] net packet size 16777215(init value is 16M)
2022-05-25T13:44:21.916363Z 5 [Note] net packet size 33554430
...
2022-05-25T13:46:20.902361Z 5 [Note] net packet size 1006632900
2022-05-25T13:46:21.286737Z 5 [Note] net packet size 1023410115
2022-05-25T13:46:21.743261Z 5 [Note] net packet size 1040187330
2022-05-25T13:46:22.187788Z 5 [Note] net packet size 1056964545
2022-05-25T13:46:22.630998Z 5 [Note] net packet size 1073741760(max value is 1G)
2022-05-25T13:46:22.728740Z 5 [Note] Aborted connection 5 to db: 'unconnected' user: 'dbatest' host: '192.168.1.66' (Got a packet bigger than 'max_allowed_packet' bytes)
find realtime change upper limit is 1G, but this transaction all event is larger than 1G, so error encounter
Thanks very much to recheck detail..
[25 May 2022 13:52]
peng gao
"when net packet large than max limit of max_allowed_packet(1G)" change to "when net packet large than max limit of max_allowed_packet(1G) error encounter."
Description: Hi ： When we use mysqlbinlog| mysql to recovery data,if one transaction binlog event larger than 1G (max_allowed_packet max value limit is 1G) ,will get error -rw-r----- 1 mysql mysql 1.2G May 24 16:10 binlog.000012 mysqlbinlog binlog.000012 | mysql -f sbtest errlog: ERROR 1153 (08S01) at line 297718: Got a packet bigger than 'max_allowed_packet' bytes mysql batch mode store all transaction binlog event in batch buffer then transport to mysqld,mysqld use net->buff to save all transaction binlog event. When larger than max values off max_allowed_packet(1G) will get this error. Is this a bug ? sometime must use mysqlbinlog| mysql to recovery data. We change max limit of max_allowed_packet to 4G ,this work, but i think there must be some reason for limit this parameter to 1G. How to repeat: set global max_allowed_packet=1024*1024*1024; mysql> show variables like '%max_allowed_packet%'; +----------------------------+------------+ | Variable_name | Value | +----------------------------+------------+ | max_allowed_packet | 1073741824 | | mysqlx_max_allowed_packet | 67108864 | | replica_max_allowed_packet | 1073741824 | | slave_max_allowed_packet | 1073741824 | +----------------------------+------------+ 1、create table test(id int,name text); 2、insert into test values(1,repeat('a',2000)); 3、repeat insert into select command. util ibdata larger than 2G mysql> insert into test select * from test; Records: 524288 Duplicates: 0 Warnings: 0 4、flush binary logs; 5、delete from test; check new binlog larger than 1G.for example binlog.000012 6、mysqlbinlog binlog.000012 | mysql Suggested fix: no suggested.We change max limit of max_allowed_packet to 4G ,this work, but i think there must be some reason for limit this parameter to 1G.