3

I'm having an issue with placing orders for a store that has taken thousands of orders. I am using Enterprise One Page Checkout with Authorize.net Direct Post

Without anything having changed to my knowledge yesterday Place Order started hanging and generating the following error:

URL: []/authorizenet/directpost_payment/place/form_key/.../
IP Address: ...
Time: 2014-01-31 14:44:16 GMT

Error:
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Trace:
0 /var/www/.../lib/Zend/Db/Adapter/Pdo/Abstract.php(305): PDO->beginTransaction()
1 /var/www/.../lib/Zend/Db/Adapter/Abstract.php(495): Zend_Db_Adapter_Pdo_Abstract->_beginTransaction()
2 /var/www/.../lib/Varien/Db/Adapter/Pdo/Mysql.php(219): Zend_Db_Adapter_Abstract->beginTransaction()
3 /var/www/.../app/code/core/Mage/Core/Model/Resource/Abstract.php(76): Varien_Db_Adapter_Pdo_Mysql->beginTransaction()
4 /var/www/.../app/code/core/Mage/Core/Model/Abstract.php(313): Mage_Core_Model_Resource_Abstract->beginTransaction()
5 /var/www/.../app/code/core/Mage/Sales/Model/Quote.php(1966): Mage_Core_Model_Abstract->save()
6 /var/www/.../app/code/core/Mage/Checkout/controllers/OnepageController.php(624): Mage_Sales_Model_Quote->save()
7 /var/www/.../app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Checkout_OnepageController->saveOrderAction()
8 /var/www/.../app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('saveOrder')
9 /var/www/.../app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
10 /var/www/.../app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
11 /var/www/.../app/Mage.php(683): Mage_Core_Model_App->run(Array)
12 /var/www/.../index.php(87): Mage::run('default', 'store')
13 {main}
`

I have read and been told by the hosting company that this could be related to mysql configuration so here is the my.cnf for the db:

    #this is mostly auto generated by a form at perconas web site.

[mysqld]
bind-address=0.0.0.0

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 32M
max-connect-errors             = 1000000

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

query_cache_type=1
query_cache_size=128M
query_cache_limit=24M
#query_alloc_block_size=128
#query_cache_min_res_unit=128

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 26G



# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

innodb_data_file_path=ibdata1:1G;ibdata2:512M:autoextend
innodb_autoextend_increment=512

#max_connections=110
wait_timeout=120
interactive_timeout=120

# modify isolation method for faster inserting.
# Do not uncomment the line below unless you understand what this does.
# transaction-isolation = READ-COMMITTED
# do not reverse lookup clients
skip-name-resolve

#long_query_time=6
#log_slow_queries=/var/log/mysqld-slow.log
#log_queries_not_using_indexes=On
#log_slow_admin_statements=On

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Any suggestions or information to help me debug this issue would be greatly appreciated. Thanks.

  • Can you connect to your database from console and run some command directly? – oleksii.svarychevskyi Jan 31 '14 at 15:40
  • Yes, we can connect to the db from console and run commands and from the web front end. All other queries including other steps of checkout are running properly. Even when checkout hangs on place order is save into a Pending Payment Status. – Howie Ross Jan 31 '14 at 15:44
  • Is there something in mysql log files? – oleksii.svarychevskyi Jan 31 '14 at 15:48
  • There is nothing logged during this issue in the mysql.d log – Howie Ross Jan 31 '14 at 16:23
1

I think that's because your max-allowed-packet value is too small.

Try to increase it

max-allowed-packet = 64M

Also I am wondering why your config uses max-allowed-packet and not max_allowed_packet.

So if it will not work try

max_allowed_packet = 64M

Do not forget to restart server. Maybe you will need more than 64M.

  • We had actually already updated max-allowed-packet to 32M and it cleared up some unrelated errors we were seeing in the logs. I updated the my.cnf in the question to reflect that. I think the dash syntax is ok, but I'll double check. – Howie Ross Jan 31 '14 at 16:45
  • 2006 MySQL server has gone away Error is definitely connected with max-allowed-packet. – oleksii.svarychevskyi Jan 31 '14 at 16:47
  • we increased max-allowed-packet to 128MB. I restarted mysql and confirmed the setting from the mysql command line but the issue still persists. – Howie Ross Jan 31 '14 at 17:01
  • Can you run this sql statement from mysql console show variables like 'max_allowed_packet%' ;? And give me the result. – oleksii.svarychevskyi Jan 31 '14 at 17:15
  • mysql> show variables like 'max_allowed_packet%' ; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | max_allowed_packet | 134217728 | +--------------------+-----------+ 1 row in set (0.00 sec) which is shown in Bytes and = 128MB – Howie Ross Jan 31 '14 at 17:20
0

You may also look into index table size when using shared hosting. It may take much space because of that also you may get "mysql server gone away".

0

id suggest you to remove duplicates from your mysql config, and increase all possible limits and timeouts:

max_allowed_packet = 150M
wait_timeout = 1800
net_read_timeout = 120
net_write_timeout = 300

and check these variables:

innodb_read_io_threads
innodb_write_io_threads
innodb_buffer_pool_instances

you can double threads, and if innodb_buffer_pool_instances is not 8, you can set it then.

and make sure you have upped php limits too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.