X-Cart:SQL errors

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

• if such errors are numerous, most likely the reason is the same as described here: https://kb.x-cart.com/article/mysql-server-has-gone-away
• if it is a one time error, then most likely it was caused by the MySQL server restart
• also the problem may be caused by the low value of the max_allowed_packet option: https://kb.x-cart.com/article/the-size-of-the-data-package-being-transmitted-is-greater-than-maximum-allowed-by-the-server

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 https://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: https://dev.mysql.com/doc/refman/5.0/en/gone-away.html

A possible solutions in this case - increase the wait_timeout option value (default value is 28800). Please contact your hosting regarding the matter. More info about this option: https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_wait_timeout

As a quick workaround solution, you can 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.

If the error still persists and it is impossible to change the wait_timeout option value then it is recommended to change the hosting company.

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

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

The above error is caused by the fact that there was an attempt to transmit via MySQL the data package, which size was greater than the 'max_allowed_packet' MySQL option value.

The detailed description can be found here: https://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html

In order to fix this issue it is required to change the max_allowed_packet to a larger value

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

X-Cart 4.0or above

X-Cart 4.1.1

This issue may be caused by several facts:

• the hosting has mySQL 5.x installed
• X-Cart 4.0.x is used, which does not support mySQL 5.x. (the issue was also found in X-Cart 4.1.1) ( MySQL 5.x is not supported since there were numerous changes in MySQL 5.0.12)
• X-Cart 4.0.x had been released before the MySQL 5.0.12, that's why such errors occur.

The problem can be resolved using the File:MYSQL5 comp for 4.0.x.txt patch.

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

You receive the following SQL errors:

INVALID SQL: 1226 : User 'mysqluser' has exceeded the 'max_questions' resource (current value: 50000)

MySQL allows to limit any user's activity. The max_questions option contains a number of queries of 'SELECT' type which a user can execute per hour.

If such an error is received, this means that X-cart executes a lot of queries and exceeds the limit. You can find more info about this option here: https://dev.mysql.com/doc/refman/5.1/en/user-resources.html

The problem can be resolved in the following ways:

• increase the max_questions value
• the following workaround can be used: it is required to create a few MySQL users, realize in the config.php a random choice of user from the list. Since every user has a limit of max_questions, X-Cart will be able to fulfill the following number of queries: max_questions * the number of users in the config.php
• move the images to the file system, disable the unnecessary modules, disable the statistics, which will decrease page load time
• change the hosting provider

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.

Can't create/write to file

If you get an error of the "Can't create/write to file" type for some queries, it means that MySQL cannot create a temporary file for the result set in the temporary directory. The probable reasons are:

• no writable permissions set for the temporary directory;
• the temporary directory is defined incorrectly in MySQL configuration file;
• problems with the storage device (not enough free disk space);
• anti-virus or other software is blocking MySQL from writing to the temporary directory.

Contact your server administrators and ask them to look into and fix the problem.

For more details about the "Can't create/write to file" error please refer to: https://dev.mysql.com/doc/refman/5.0/en/cannot-create.html

If you have McAfee On-Access Scanner running on your server, add the temporary directory or the MySQL filetypes (MYD, MYI) into On-Access Scanner's exclude list. See: How to manage file and folder exclusions in VirusScan Enterprise 8.x

Table '%table_name%' is marked as crashed and should be repaired

If you get an error like:

Table '%table_name%' is marked as crashed and should be repaired

It means that the MySQL table '%table_name%' has been crashed in the database. Occasionally, under circumstances involving high load on the server, multiple INSERTs and UPDATEs, coupled with many SELECTs, or hardware failure, your database server may corrupt a table. According to the MySQL manual, you can get corrupted tables if some of the following things happens:

• The mysqld process being killed in the middle of a write.
• Unexpected shutdown of the computer (for example, if the computer is turned off).
• A hardware error.
• You are using an external program (like myisamchk) on a live table.
• A software bug in the MySQL or MyISAM code.

In order to fix this issue try to connect to your store MySQL database and executed the following MySQL query:

REPAIR TABLE '%table_name%';