max_user_connections

[chadlly2003]

For some reason, I started getting an error in my log –

“already has more than 'max_user_connections' active connections in.”
I contacted my hosting server and their response was below. This is causing my server to lose connection status. Can someone assist me or lead me in the right direction on solving this issue.

Hello,

After reviewing your server, you are hitting MySQL connection limits as MySQL is taking too long to process incoming requests. After investigating this issue I found the issue is due to the following query on the server:

select distinct p.products_id FROM products p
LEFT JOIN manufacturers m USING(manufacturers_id)
LEFT JOIN products_description pd on p.products_id = pd.products_id
JOIN products_to_categories p2c on p.products_id = p2c.products_id
JOIN categories c on p2c.categories_id = c.categories_id
LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1
WHERE (p.products_status = 1
AND pd.language_id = 1
AND ((pd.products_name LIKE '%822312%'
OR p.products_model LIKE '%822312%'
OR m.manufacturers_name LIKE '%822312%'
OR (mtpd.metatags_keywords LIKE '%822312%'
AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%winderosa%'
OR p.products_model LIKE '%winderosa%'
OR m.manufacturers_name LIKE '%winderosa%'
OR (mtpd.metatags_keywords LIKE '%winderosa%'
AND mtpd.metatags_keywords !='')) ))
GROUP BY p.products_id

This query is forcing MySQL to read every single row for the table products_description as products_description has no key that can be used.
+----+-------------+-------+--------+---------------------------------+---------+---------+-----------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+---------+---------+-----------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | pd | ALL | PRIMARY | NULL | NULL | NULL | 62505 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY,idx_products_status_zen | PRIMARY | 4 | inverter_154.pd.products_id | 1 | Using where |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 4 | inverter_154.p.manufacturers_id | 1 | Distinct |
| 1 | SIMPLE | p2c | ref | PRIMARY,idx_cat_prod_id_zen | PRIMARY | 4 | inverter_154.pd.products_id | 1 | Using index; Distinct |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | inverter_154.p2c.categories_id | 1 | Using index; Distinct |
| 1 | SIMPLE | mtpd | eq_ref | PRIMARY | PRIMARY | 8 | inverter_154.pd.products_id,const | 1 | Using where; Distinct |
+----+-------------+-------+--------+---------------------------------+---------+---------+-----------------------------------+-------+----------------------------------------------+
6 rows in set (1.58 sec)

I have attempted to resolve this by adding a key to the products_description table for the column products_id however this did not resolve the issue. You may need to contact a developer regarding setting up proper keys for the products_description
table or optimize the query to reduce the amount of time MySQL spends on these queries.

The only other solution I can provide is to block the following useragnet as it appears to be making the most queries to the server causing this load:
Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:52.0) Gecko/20100101 Firefox/52.0

Sample of query seen:
34.229.17.231 - - [19/Aug/2018:05:00:39 -0400] "GET /index.php?main_page=advanced_search_result&search_in_description=1&keyword=71004 2B%20Winderosa HTTP/1.1" 302 22 "-" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:52.0) Gecko/20100101 Firefox/52.0"
107.20.130.165 - - [19/Aug/2018:05:00:39 -0400] "GET /index.php?main_page=advanced_search_result&search_in_description=1&keyword=82301 0%20Winderosa HTTP/1.1" 302 22 "-" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:52.0) Gecko/20100101 Firefox/52.0"
18.212.95.191 - - [19/Aug/2018:05:00:35 -0400] "GET /index.php?main_page=advanced_search_result&search_in_description=1&keyword=82303 4%20Winderosa HTTP/1.1" 302 22 "-" "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:52.0) Gecko/20100101 Firefox/52.0"

Let us know how you wish to proceed or if you have any questions.


Matthew C.

[chadlly2003]

this is one of the original errors in my log

[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64
[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64
[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64
[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64
[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64
[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64
[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64
[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64
[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64
[19-Aug-2018 01:52:00 America/New_York] Request URI: /index.php?main_page=advanced_search_result&search_in_description=1&keyword=25-2058%20All%20Balls, IP address: 34.227.71.232
#1 mysqli_connect() called at [/home/inverter/public_html/includes/classes/db/mysql/query_factory.php:64]
#2 queryFactory->connect() called at [/home/inverter/public_html/includes/init_includes/init_database.php:23]
#3 require(/home/inverter/public_html/includes/init_includes/init_database.php) called at [/home/inverter/public_html/includes/autoload_func.php:48]
#4 require(/home/inverter/public_html/includes/autoload_func.php) called at [/home/inverter/public_html/includes/application_top.php:170]
#5 require(/home/inverter/public_html/includes/application_top.php) called at [/home/inverter/public_html/index.php:26]

[19-Aug-2018 01:52:00 America/New_York] PHP Warning: mysqli_connect(): (HY000/1203): User inverter already has more than 'max_user_connections' active connections in /home/inverter/public_html/includes/classes/db/mysql/query_factory.php on line 64

[lat9]

Are you on a shared server? Who is your web-host?

[chadlly2003]

I am using Hostgator -- VPS

Snappy 4000
4 GB RAM
2 Cores CPU
165 GB Disk Space
2 TB Bandwidth

[lat9]

What plugin(s) do you have installed that modify the advanced_search_results page? The query that you posted is not "standard" Zen Cart.

[chadlly2003]

You are correct it is not standard. The plugin I am using is

Zen cart Dynamic filters
https://www.zen-cart.com/downloads.php?do=file&id=1361

[lat9]

I haven't looked at the plugin's file-set, but if you modify the code so that instead of

select distinct p.products_id FROM products p
LEFT JOIN manufacturers m USING(manufacturers_id)
LEFT JOIN products_description pd on p.products_id = pd.products_id
JOIN products_to_categories p2c on p.products_id = p2c.products_id
JOIN categories c on p2c.categories_id = c.categories_id
LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1
WHERE (p.products_status = 1
AND pd.language_id = 1
AND ((pd.products_name LIKE '%822312%'
OR p.products_model LIKE '%822312%'
OR m.manufacturers_name LIKE '%822312%'
OR (mtpd.metatags_keywords LIKE '%822312%'
AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%winderosa%'
OR p.products_model LIKE '%winderosa%'
OR m.manufacturers_name LIKE '%winderosa%'
OR (mtpd.metatags_keywords LIKE '%winderosa%'
AND mtpd.metatags_keywords !='')) ))
GROUP BY p.products_id

the query looks like (removing the AND-condition under the WHERE condition as well):

select distinct p.products_id FROM products p
LEFT JOIN manufacturers m USING(manufacturers_id)
LEFT JOIN products_description pd on p.products_id = pd.products_id AND pd.languages_id = 1
JOIN products_to_categories p2c on p.products_id = p2c.products_id
JOIN categories c on p2c.categories_id = c.categories_id
LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1
WHERE (p.products_status = 1
AND ((pd.products_name LIKE '%822312%'
OR p.products_model LIKE '%822312%'
OR m.manufacturers_name LIKE '%822312%'
OR (mtpd.metatags_keywords LIKE '%822312%'
AND mtpd.metatags_keywords !='')) and (pd.products_name LIKE '%winderosa%'
OR p.products_model LIKE '%winderosa%'
OR m.manufacturers_name LIKE '%winderosa%'
OR (mtpd.metatags_keywords LIKE '%winderosa%'
AND mtpd.metatags_keywords !='')) ))
GROUP BY p.products_id

[chadlly2003]

I lost ya a little bit. Where would I change that information.

In includes\modules\pages\advanced_search_result\header.php

I found this piece of code. I am not sure if I am on the right track here. I figured more information is better than no information

$where_str = " WHERE (p.products_status = 1
AND pd.language_id = :languagesID ";

I guess what I am trying to say is can you let me know how to change that

[chadlly2003]

anyone have any ideas that could assist me i would greatly appreciate it

[lat9]

Perhaps posting in the DPU plugin's support thread would get you a quicker response. I've not used the plugin.