MySql Question...

Hello, when I visit my Member List I noticed the following error:

Unfortunately a query of 924,657 bytes is too big for the current MySQL max_allowed_packet setting of 1,048,576 bytes [INSERT INTO cms_cache (cached_for, dependencies, lang, identifier, the_theme, staff_status, the_member, groups, is_bot, timezone, the_value, date_and_time) VALUES ('main_members', 'cns_member_directory:critical_error:global:chat:cns:search:mail:points:galleries:cns_special_cpf!ajax:ajax_people_lists…]

I thought this was just a matter of editing /etc/my.cnf 

I edited the file, rebooted my server but I am still getting the error…. Are there multiple locations for this file? 

This may help:

How to find out the location of currently used MySQL configuration file in linux - Stack Overflow

Also sometimes MySQL options are passed directly into PHP by e.g. /etc/init.d/mysql, although I very much doubt this is the cause in this case.

Hrrmmm I have verified that /etc/my.cnf is the file MySql is using. (I made a change and it failed to start the service, so I reverted back) I've added:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
wait_timeout = 31536000
max_allowed_packet=16M

service mysqld stop
service mysqld start

However, I am still getting the following error… Not sure if it is critical or not? (When looking at the Members List)

Unfortunately a query of 916,905 bytes is too big for the current MySQL max_allowed_packet setting of 1,048,576 bytes [INSERT INTO cms_cache (cached_for, dependencies, lang, identifier, the_theme, staff_status, the_member, groups, is_bot, timezone, the_value, date_and_time) VALUES ('main_members', 'cns_member_directory:critical_error:global:cns:search:galleries:points:cns_special_cpf!ajax:ajax_people_lists!cns_membe…]

Weird.

This MySQL manual page implies maybe actually what I said about a startup parameter could be true:
https://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html

I just checked the code and we set it explicitly in our code too.
sources/database/mysqli.php:

What happens if you change this line to:

(based on what I saw in 2nd highest voted comment on php - how to check and set max_allowed_packet mysql variable - Stack Overflow)

And this topic implies our code is probably fine, just our code is setting the client-side setting, but the server-side setting has to be raised to:
mysql - Can you increase max_allowed_packet from the client? - Stack Overflow

Definitely check for MySQL startup parameters. I think if you do ps -Af | grep mysqld you'll see the startup params for MySQL.

Hrrmmm I made the change to sources/database/mysqli.ph  No effect… I didnt see any wierd startup :

[root@basslinecartel database]# ps -Af | grep mysqld
root      4563     1  0 12:45 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe –datadir=/var/lib/mysql –socket=/var/lib/mysql/mysql.sock –pid-file=/var/run/mysqld/mysqld.pid –basedir=/usr –user=mysql
mysql     4806  4563  0 12:45 pts/0    00:00:00 /usr/libexec/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/var/log/mysqld.log –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/lib/mysql/mysql.sock

I'm going to continue fiddling with this and I will let you know if I find a fix / solution!

If you'd like my help debugging on server send me an email to chris@ocproducts.com. I'd need to have root SSH access though.

Ultimately we may be updating the hosting requirements tutorial, so in any event I'd like to find out what is causing this.

Hi,

I've learned a lot looking at this. MySQL's documentation is very poor, so in the 2nd part of my reply I'm posting some new documentation I've written, which will go into the hosting requirements tutorial.

I'll also address the specific situation here with some context, so anyone else reading this topic can follow with definitive answers.

The code in Composr to set the limit didn't work, which I was not aware of (I'll remove it). So this did need setting at a server level. Usually a webhost will do this for you, but you're either on a VPS or a dedicated, so it has fallen to you. Sorry I didn't realise this setting needed to be done!

My mysqli.php change did actually work. It only worked because you're running your site under the root MySQL user (you only have one site on the server, so that's safe enough).

That said I've reverted the mysqli.php change because your my.cnf also did work.

I'm not sure why your testing indicated this wasn't working, so there must have been some confusion somewhere.

Configuring MySQL (advanced)

Server variables may be set in the MySQL config – either the main config file (e.g. /etc/my.cnf), or included files (e.g. from /etc/my.cnf.d) – or in the startup parameters – or via setting the server variable at run-time. If they are not set anywhere then MySQL will use hard-coded defaults.

To find which config file(s) MySQL uses, run this command (Linux and MacOS):

In the config file it will look something like this (example for setting max_allowed_packet, which is probably what you want):

To find if a setting is set in startup parameters (Linux and MacOS) run:
If it is, you'll see it in the command. This may be set in an init file such as /etc/init.d/mysqld, but it varies considerably by Linux distribution.

If you are changing a config file, or a startup parameter, you naturally need to reset MySQL for it to take effect, e.g. with:
or:
Again it varies a lot by distribution. On Windows you'd use the Services application to restart the service.

When a MySQL session is started, certain server variables are cloned into the session (e.g. max_allowed_packet). These variables may then be read and written as either server or session variables (actually max_allowed_packet is read-only as a session variable – an exception described further below). If you set a global variable then the current session is not affected, but new sessions will be. Sessions are thrown out when connections are closed, e.g. when a Composr page is fully served.

To find the values of a variable you can use these MySQL queries (example for max_allowed_packet):

There are lots of equivalent terms and syntaxes in MySQL which can make it confusing when trying to understand the documentation and third party solutions. Here is a guide:

• Types of variable:
  • "Server" = "Global"
  • "Session" = "Local" = "Connection"
• Commands for setting variables:
  • Server variables, all equivalent:
    • SET @@global.whatever=something
    • SET GLOBAL whatever=something
  • Session variables, all equivalent:
    • SET whatever=something
    • SET @@whatever=something
    • SET @@session.whatever=something
    • SET SESSION whatever=something
    • SET @@local.whatever=something
    • SET LOCAL whatever=something
• Getting variables:
  • SHOW VARIABLES = SHOW LOCAL VARIABLES = SHOW SESSION VARIABLES
  • (SHOW GLOBAL VARIABLES has no equivalencies [SHOW SERVER VARIABLES doesn't exist – which is inconsistent])

The rest of this section will deal with the specifics of the max_allowed_packet setting, which is the only variable that a server administrator usually needs to set.

max_allowed_packet exists as separate server-side and client-side settings; this is totally different from the concept of server and session variables (which are both server-side) and should not be confused.

max_allowed_packet defaults to 1MB server-side if not configured at all (i.e. this is the hard-coded default). Most web hosts will have a higher default value and Composr defines a minimum requirement of 16MB (Tempcode for dense blocks such as main_members may use a couple of MB, but we define an even higher limit in case of producing very long Comcode pages).
max_allowed_packet's session variable exists but is read-only. Attempts to change it will give an error message. You therefore must configure the server variable correctly.
Only MySQL users with the SUPER privilege may set the server variable. Typically only the root user has this.

max_allowed_packet defaults to 1GB client-side (e.g. PHP), except in official client applications like mysql (16MB) and mysqldump (24MB) (which may be configured in the MySQL configuration, the client configuration sections).
Therefore for the Composr application the client-side setting is irrelevant, but you may come up with it when doing MySQL dumps for example.

max_allowed_packet can be set with "M" syntax, not just with exact bytes. So you can set it to 16M or 16777216, that's your choice. Once parsed MySQL will show it in bytes.

The absolute maximum max_allowed_packet setting (both server-side and client-side, as it relates to the protocol implementation) is 1GB.

Well thanks for the assistance on this! I was running out of ideas, will note this next time I run into this issue. I'll perform your recommendations, as It is giving me that error again at BasslineCartel.com is for sale | HugeDomains  .  What I just noticed is, if a user is not logged in I recieve the error. Once I log into the site, the error goes away. Just an FYI

Thanks Chris have a great day.
 

It's still happening ? I just took a look on your server again, and the setting seems to have gone up to 200MB now, and that's not configured anywhere.

Is it possible something else is changing the setting around after MySQL starts I wonder?

Regardless, I've taken a different approach - I've just optimised the member directory to use less cache data.
I've made that change on your site and it will be in the next patch release also.

The problem could still happen in some other places, especially if you have large pages, but that's the only default case I know of.