how to check and set max allowed packet mysql variable

Question

Possible Duplicate    MySQL Error 1153 - Got a packet bigger than    max allowed packet    bytes       Hi I am getting the error     1153  Got a packet bigger than  max allowed packet bytes  but I made no changes in my source code and the hosting states that they did not made any change in server settings   I don t know what happened  But I am trying to find the reason   so  how to check max allowed packet mysql variable by php script   and is that possible to set it in source code

User · Answer

The following PHP worked for me  using mysqli extension but queries should be the same for other extensions     db   new mysqli   localhost    user    pass    dbname        to get the max allowed packet  maxp    db- gt query   SELECT   global max allowed packet   - gt fetch array    echo  maxp  0       to set the max allowed packet to 500MB  db- gt query   SET   global max allowed packet       500   1024   1024      So if you ve got a query you expect to be pretty long  you can make sure that mysql will accept it with something like    sql    some really long sql query       db- gt query   SET   global max allowed packet       strlen   sql     1024     db- gt query   sql      Notice that I added on an extra 1024 bytes to the length of the string because according to the manual       The value should be a multiple of 1024  nonmultiples are rounded down to the nearest multiple    That should hopefully set the max allowed packet size large enough to handle your query   I haven t tried this on a shared host  so the same caveat as  Glebushka applies

User · Answer

max allowed packet is set in mysql config  not on php side   mysqld  max allowed packet 16M    You can see it s curent value in mysql like this   SHOW VARIABLES LIKE  max allowed packet     You can try to change it like this  but it s unlikely this will work on shared hosting   SET GLOBAL max allowed packet 16777216    You can read about it here http   dev mysql com doc refman 5 1 en packet-too-large html  EDIT  The  mysqld  is necessary to make the max allowed packet working since at least mysql version 5 5    Recently setup an instance on AWS EC2 with Drupal and Solr Search Engine  which required 32M max allowed packet  It you set the value under  mysqld safe   which is default settings came with the mysql installation  mode in  etc my cnf  it did no work  I did not dig into the problem  But after I change it to  mysqld  and restarted the mysqld  it worked

User · Answer

goto cpanel and login as Main Admin or Super Administrator   find SSH Shell Access     you will find under the security tab of cpanel   now give the username and password of Super Administrator as root or whatyougave  note  do not give any username  cos  it needs permissions  once your into console type  type       mysql   and press enter now you find youself in  mysql gt     and type here like     mysql gt  set global net buffer length 1000000   Query OK  0 rows affected  0 00 sec   mysql gt  set global max allowed packet 1000000000   Query OK  0 rows affected  0 00 sec    Now upload and enjoy

[php] how to check and set max_allowed_packet mysql variable

Examples related to php

Examples related to mysql