Difference between revisions of "X-Cart:SQL errors"

From X-Cart 4 Classic
Jump to: navigation, search
(MySQL server has gone away)
Line 24: Line 24:
  
 
This usually occurs when:
 
This usually occurs when:
 +
 
* a customer opens a store page in the browser
 
* a customer opens a store page in the browser
 
* PHP script connects to the MySQL
 
* PHP script connects to the MySQL
Line 35: Line 36:
  
 
* increase the <u>wait_timeout</u> option value (default value is 28800). More info about this option: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_wait_timeout
 
* increase the <u>wait_timeout</u> option value (default value is 28800). More info about this option: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_wait_timeout
* If it is impossible to change this option then it is recommended either to change the hosting company or apply the [[File:mysql_server_has_gone_away.txt]] patch.
+
* If it is impossible to change this option then it is recommended either to change the hosting company or apply the [[File:mysql server has gone away.txt]] patch.
  
 
After this patch is applied, X-Cart will reconnect to MySQL in case of such errors. This patch is already included to X-Cart 4.1.11 and higher.
 
After this patch is applied, X-Cart will reconnect to MySQL in case of such errors. This patch is already included to X-Cart 4.1.11 and higher.
  
 
=== SQL error when deleting products ===
 
=== SQL error when deleting products ===
 +
 +
When trying to delete a product, a SQL error appears. This problem can be reproduced on MySQL 5.x
 +
 +
Error message is as follows:
 +
 +
<pre>
 +
SQL query : SELECT IF(xcart_categories_lng.category != '', xcart_categories_lng.category, xcart_categories.category) as category FROM
 +
xcart_categories, xcart_products_categories LEFT JOIN xcart_categories_lng ON xcart_categories.categoryid = xcart_categories_lng.categoryid AND
 +
xcart_categories_lng.code = 'US' WHERE xcart_products_categories.productid = '16856' AND xcart_products_categories.categoryid =
 +
xcart_categories.categoryid AND xcart_products_categories.main = 'Y'
 +
 +
Error code : 1054
 +
Description : Unknown column 'xcart_categories.categoryid' in 'on clause'
 +
Request URI: /admin/process_product.php?mode=delete&keep_https=yes
 +
Backtrace:
 +
/include/func/func.db.php:186
 +
/include/func/func.db.php:112
 +
/include/func/func.db.php:357
 +
/include/process_product.php:202
 +
/admin/process_product.php:43
 +
</pre>
 +
 +
In order to fix this issue, please apply the [[File:product_deletion_error_in_4_1_11.txt]] patch.
 +
 +
This issue is fixed in the X-Cart 4.1.12 and higher.
  
 
=== The size of the data package being transmitted is greater than maximum allowed by the server ===
 
=== The size of the data package being transmitted is greater than maximum allowed by the server ===
 +
 +
  
 
=== Unknown column 'xcart products.productid' in 'on clause' ===
 
=== Unknown column 'xcart products.productid' in 'on clause' ===

Revision as of 16:17, 3 February 2010

Got error 28 from storage engine

This error appeared due to the lack of free space in the file system on the hard disc drive where MySQL is installed. The solution is rather simple - to clean up some disc space.

Lost connection to MySQL server during query

This error message means that when executing the query the conenction with MySQL server was lost

Mysql connect(): Too many connections

The limit of simultaneous connections to MySQL server is exceeded. This limit is set by the max_connections MySQL option - see http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html. The problem can be resolved in the following ways:

  • Increase the max_connections option value
  • if the images are stored in the database(are called via image.php), move them to the file system
  • change the hosting company

MySQL server has gone away

When you get a number of MySQL server has gone away errors in logs, this means that the MySQl server aborted the connection to PHP script but the script is still trying to execute a certain query.

This usually occurs when:

  • a customer opens a store page in the browser
  • PHP script connects to the MySQL
  • for some reason the script stops for a while, during this pause MySQL server timed out and the connection was closed
  • the script finishes its work and tries to execute the MySQL query again
  • since the connection is already lost, the error message is shown

Additional info on the matter can be found here: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

There are two possible solutions in this case:

After this patch is applied, X-Cart will reconnect to MySQL in case of such errors. This patch is already included to X-Cart 4.1.11 and higher.

SQL error when deleting products

When trying to delete a product, a SQL error appears. This problem can be reproduced on MySQL 5.x

Error message is as follows:

SQL query : SELECT IF(xcart_categories_lng.category != '', xcart_categories_lng.category, xcart_categories.category) as category FROM
xcart_categories, xcart_products_categories LEFT JOIN xcart_categories_lng ON xcart_categories.categoryid = xcart_categories_lng.categoryid AND
xcart_categories_lng.code = 'US' WHERE xcart_products_categories.productid = '16856' AND xcart_products_categories.categoryid =
xcart_categories.categoryid AND xcart_products_categories.main = 'Y'

Error code : 1054
Description : Unknown column 'xcart_categories.categoryid' in 'on clause'
Request URI: /admin/process_product.php?mode=delete&keep_https=yes
Backtrace:
/include/func/func.db.php:186
/include/func/func.db.php:112
/include/func/func.db.php:357
/include/process_product.php:202
/admin/process_product.php:43

In order to fix this issue, please apply the File:Product deletion error in 4 1 11.txt patch.

This issue is fixed in the X-Cart 4.1.12 and higher.

The size of the data package being transmitted is greater than maximum allowed by the server

Unknown column 'xcart products.productid' in 'on clause'

User 'mysqluser' has exceeded the 'max questions' resource (current value: 50000)

User [name] has already more than 'max_user_connections' active connections

Warning: mysql_connect(): User [name] has already more than 'max_user_connections' active connections
in /home/[path_to_xcart_dir]/include/func.php on line 43

Could not connect to SQL db

This means that your mysql user has exceeded the max_user_connections limit (mysql configurable parameter). For example, when several users browse your site, several instances of x-cart scripts are called and the scripts try to connect to the mysql database. If the number of those scripts instances is higher than the max_user_connections limit defined on your server, user will see such error message.

You should contact your hosting administrators and ask them to increase the limit number of mysql connections per mysql user, i.e. 'max_user_connections' limit.