| Bug #42195 | max_allowed_packet correctly set - still max_allowed_packet-error | ||
|---|---|---|---|
| Submitted: | 19 Jan 2009 10:21 | Modified: | 22 Feb 2009 23:15 |
| Reporter: | Werner Popken | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S2 (Serious) |
| Version: | 5.0.67 | OS: | Linux (2.4.36 SMP) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | error, max_allowed_packet | ||
[19 Jan 2009 10:21]
Werner Popken
[19 Jan 2009 10:38]
Valeriy Kravchuk
Thank you for the problem report. Please, send that entire /etc/my.cnf. Are you sure there is no other my.cnf with [client] and/or [mysql] section that redefines max_allowed_packet?
[19 Jan 2009 10:51]
Werner Popken
In order to exclude the possiblility that something inside the statement itself would cause the problem, I constructed a very simple example:
MySQL pz1>desc test\G
*************************** 1. row ***************************
Field: a
Type: int(10) unsigned
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
*************************** 2. row ***************************
Field: b
Type: enum('a','b','d','c')
Null: NO
Key:
Default: b
Extra:
*************************** 3. row ***************************
Field: c
Type: mediumtext
Null: YES
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: tmstmp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra:
4 rows in set (0.00 sec)
insert_statement2.sql is more than 5MB with a very simple statement:
INSERT INTO `test` (`a`, `b`, `c`, `tmstmp`) VALUES ('', 'b', 'aaaaaaaaaaaa
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
[...] etc. etc. up to 5 MB
', NOW('CURRENT_TIMESTAMP'));
pz1:~# mysql pferdezeitung_db1 < insert_statement2.sql
Logging to file '/root/mysql_session.log'
ERROR 1153 (08S01) at line 1: Got a packet bigger than 'max_allowed_packet' bytes
mysql: 0 files and 1 streams is left open
[19 Jan 2009 10:57]
Sveta Smirnova
Thank you for the feedback. Please send output of mysql pferdezeitung_db1 -e "show variables like 'max_allowed_packet'" so we can be sure you have no other configuration files and max_allowed_packet for client set correct.
[19 Jan 2009 11:17]
Werner Popken
Hi Valeriy & Sveta! Thanks for the quick response. I looked for other my.cnfs to no avail. It's a SuSE standard installation with ServControl. There is a mysql batch in /etc/rc.d which does not feed any parameters at all. I asked the provider for ruther information. In order to check this hypothesis I restarted the MySQL server: before: MySQL pz1>show variables like 'max_allowed_packet'; +--------------------+------------+ | Variable_name | Value | +--------------------+------------+ | max_allowed_packet | 1073741824 | +--------------------+------------+ 1 row in set (0.00 sec) after: MySQL pz1>show variables like 'max_allowed_packet'; +--------------------+------------+ | Variable_name | Value | +--------------------+------------+ | max_allowed_packet | 1073741824 | +--------------------+------------+ 1 row in set (0.00 sec) Something is wrong here, though; when I checked after some time, the value was set back to 1M: +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 1048576 | +--------------------+---------+ Resetting to 1 G doesn't solve the problem, though. The server shows the right value, but the client crashes nevertheless. Afterwards, the setting still is 1G so nothing has interfered in the meantime. Here's the cnf: # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /home/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] password = # commented out here #password = your_password # wp 19:44 09.01.2009 port = 3306 socket = /var/lib/mysql/mysql.sock max_allowed_packet = 1073741824 #_allowed_packet = 1024M #_allowed_packet = 1G # wp 01:34 19.01.2009 #_allowed_packet = 1024*1024*1024 # doesn't seem to work here # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 16M #_allowed_packet = 1G # doesn't work anymore # wp 01:34 19.01.2009 max_allowed_packet = 1073741824 #_allowed_packet = 1024M #_allowed_packet = 1024*1024*1024 #_allowed_packet = 1M # wp 19:44 09.01.2009 character_set_server=latin1 # wp 17:33 10.01.2009 #table_cache = 1500 # wp 19:45 09.01.2009 query_cache_size=52428800 # wp 19:46 09.01.2009 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M log-error log-slow-queries set-variable=long_query_time=1 max_connections=500 max_connect_errors=10000 join_buffer_size=8M # wp 19:47 09.01.2009 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Disable Federated by default skip-federated # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 2 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = <hostname> # # The username the slave will use for authentication when connecting # to the master - required #master-user = <username> # # The password the slave will authenticate with when connecting to # the master - required #master-password = <password> # # The port the master is listening on. # optional - defaults to 3306 #master-port = <port> # # binary logging - not required for slaves, but recommended #log-bin=mysql-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 10000 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /home/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /home/mysql/ #innodb_log_arch_dir = /home/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 # wp 00:25 14.01.2009 sync_binlog=1 # wp 00:25 14.01.2009 #innodb_lock_wait_timeout = 50 #innodb_force_recovery = 4 # wp 19:51 09.01.2009 # The safe_mysqld script [safe_mysqld] err-log=/home/mysql/mysqld.log [mysqldump] #quick opt quote-names # wp 19:51 09.01.2009 [mysql] prompt=MySQL pz1> tee= /root/mysql_session.log #wp 19:52 09.01.2009 no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
[19 Jan 2009 19:21]
Werner Popken
Another test: MySQL pz1>show variables like 'max_allowed_packet'; +--------------------+------------+ | Variable_name | Value | +--------------------+------------+ | max_allowed_packet | 1073741824 | +--------------------+------------+ 1 row in set (0.00 sec) pz1:~# mysql -e "show variables like 'max_allowed_packet'" Logging to file '/root/mysql_session.log' +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 1048576 | +--------------------+---------+ mysql: 0 files and 1 streams is left open MySQL pz1>show variables like 'max_allowed_packet'; +--------------------+------------+ | Variable_name | Value | +--------------------+------------+ | max_allowed_packet | 1073741824 | +--------------------+------------+ 1 row in set (0.00 sec) What's this??? From within the client, we get 1G, from without, 1M!!! Let's try to force it: pz1:~# mysql --max_allowed_packet=1073741824 pferdezeitung_db1 < insert_statement2.sql Logging to file '/root/mysql_session.log' ERROR 1153 (08S01) at line 1: Got a packet bigger than 'max_allowed_packet' bytes mysql: 0 files and 1 streams is left open And check back if it is the server: MySQL pz1>show variables like 'max_allowed_packet'; +--------------------+------------+ | Variable_name | Value | +--------------------+------------+ | max_allowed_packet | 1073741824 | +--------------------+------------+ 1 row in set (0.01 sec) Funny indeed.
[22 Jan 2009 23:15]
Sveta Smirnova
Thank you for the feedback.
Please apply this patch and recompile client:
$diff -u mysys/default.c mysys/default.c.new
--- mysys/default.c 2008-12-18 18:19:51.000000000 +0100
+++ mysys/default.c.new 2009-01-23 00:13:11.000000000 +0100
@@ -649,6 +649,7 @@
}
}
#endif
+fprintf(stderr, "Name: %s\n", name);
if (!(fp= my_fopen(name, O_RDONLY, MYF(0))))
return 1; /* Ignore wrong files */
mysql command line client will print paths to configuration files it reads. Please check its output and examine if one of these files redefines max_allowed_packet.
[23 Feb 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
