Your username here MUST MATCH your XenForo username (connected to your XF license).

    Once you have registered here, then you need to start a conversation at xenforo.com w/Bob and provide the following:
    1. Your XenForo License Validation Token
    2. The Domain Name associated with the License
    NOTE: Your account will be validated once ALL requirements are verified/met. Thank you for your patience.

MySQL queries getting piled up

Discussion in 'Showcase Support' started by Nobitakung, Apr 29, 2017.

Thread Status:
Not open for further replies.
  1. Nobitakung

    Nobitakung New Member Showcase

    I got the below error on my site:
    An exception occurred: User mysite_guy already has more than 'max_user_connections' active connections in /home/mysite/public_html/library/Zend/Db/Adapter/Mysqli.php on line 333

    1. Zend_Db_Adapter_Mysqli->_connect() in Zend/Db/Adapter/Abstract.php at line 315
    2. Zend_Db_Adapter_Abstract->getConnection() in XenForo/Application.php at line 730
    3. XenForo_Application->loadDb()
    4. call_user_func_array() in XenForo/Application.php at line 973
    5. XenForo_Application->lazyLoad() in XenForo/Application.php at line 1004
    6. XenForo_Application::get() in XenForo/Application.php at line 1609
    7. XenForo_Application::getDb() in XenForo/Model.php at line 161
    8. XenForo_Model->_getDb() in XenForo/Model/DataRegistry.php at line 138
    9. XenForo_Model_DataRegistry->_getMultiFromDb() in XenForo/Model/DataRegistry.php at line 97
    10. XenForo_Model_DataRegistry->getMulti() in XenForo/Dependencies/Abstract.php at line 147
    11. XenForo_Dependencies_Abstract->preLoadData() in XenForo/FrontController.php at line 127
    12. XenForo_FrontController->run() in /home/mysite/public_html/index.php at line 13
    (I replace the real account/directory name with mysite)

    When I asking my host to check it out, I got their response as below:

    The MySQL queries with respect to the database "mysite_guy" was getting piled up which was the reason for the connections to be getting completely used

    | 66726 | mysite_guy | localhost | mysite_xen | Execute | 35816 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view
    | 70099 | mysite_guy | localhost | mysite_xen | Execute | 35754 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view
    | 77440 | mysite_guy | localhost | mysite_xen | Execute | 35665 | Creating delayed handler | INSERT DELAYED INTO xf_nflj_showcase_item_view
    | 77894 | mysite_guy | localhost | mysite_xen | Execute | 35658 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view
    | 79755 | mysite_guy | localhost | mysite_xen | Execute | 35628 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view

    I've killed the queries now and the site is loading now, please contact the developer of the site and investigate why this was happening so as to avoid further issues.

    It's look like there is some problem in the attachment uploading of the showcase addon.

    I currently use showcase 2.5.4, just not sure if upgrading will solved the problem or not as I can't find any thread mention about 'max_user_connections'.

    If this is already addressed somewhere, please point me to that page. Thanks.
  2. Bob

    Bob Developer Staff Member

    That is an issue with your SERVER aka HOST. My guess is that you are on a shared server or limited resources server.

    Yes, those queries were not being run because of issues with your HOST.

    Those queries are processing VIEW STATISTICS. They are using MySQL Delayed Insert queries.

    These 4 SQL Queries are processing ATTACHMENT Views (the number of times a specific attachment has been viewed)

    This one is processing a Showcase Item views (the number if times specific showcase items have been viewed).


    This is not even remotely TRUE. You are making a baseless accusation because you saw one of the queries with the word showcase it in. The queries are not even the problem, your server configuration is!

    This is an issue with your HOST due to the server configuration (max user connections). You need a less restrictive server.

    You might want to consider disabling DELAYED INSERTS. This is a core XenForo Option setting.


    XenForo and Showcase are NOT the issue. Max User Connections has absolutely NOTHING to do with Showcase or XenForo. Its that your server configuration is not setup to handle the amount of processing that your site requires.
  3. Nobitakung

    Nobitakung New Member Showcase

    Thank you for your suggestion. I have disabled the 'insert delayed' as you suggested.

    So, did you mean those sql queries in queue has nothing to do with uploading attachment? it's just viewing?
  4. Bob

    Bob Developer Staff Member

    That is what I said. Just look at the QUERY.

     INSERT DELAYED INTO xf_attachment_view
    INSERT INTO TABLE NAME. INSERT means that a new record is being added to the table. The table in this case is xf_attachment_view. That table stores the VIEW COUNTS for when each time a user views an attachment in the xf attachment system.

    The ISSUE you are having is because of all the VIEW TABLES having records created via delayed queries. Those delayed queries are attempting to run and your HOST is preventing that because of how they configured your server.

    Until THEY fix your server, its going to continue happening.
    Nobitakung likes this.
  5. Nobitakung

    Nobitakung New Member Showcase

    Thanks again for explanation. I know the sql but I just don't clearly understand what the xf_attachment_view is used for. I checked the table in my database and found that there are only 8 records there, while I have thousands or records in xf_attachment. And there is a view_count field in the xf_attachment already. So, I wonder what activities actually lead to the INSERT query to the xf_attachment_view. It must be very rarely-happen activity according to the nature of my site.

    As I have already disable the INSERT DELAYED in XF setting, I think this is unlikely to happen again, but I'd like to make some test first.

    Note: my xf_attachment_view table has only 1 field; attachment_id, no view_count. I'm using XF 1.5.6
    Last edited: May 1, 2017
  6. Bob

    Bob Developer Staff Member

    As for how it works, its pretty basic level stuff.

    The xf_attachment_view is a MEMORY table that temporarily stores attachment ID's each time that an attachment is viewed. Then once every 30 minutes a CRON runs which updates the View Counts for Attachments. It does this by querying the xf_attachment_view table and getting the counts for each attachment_id. The process then updates the view_count field in the xf_attachments table for each attachment_id that has new view counts. The xf_attachment_view table is then truncated (emptied) and the process starts over again.

    If you have further questions about the core xenforo attachments system and how it works, you need to ask those questions at xenforo.com.
    Nobitakung likes this.
  7. Nobitakung

    Nobitakung New Member Showcase

    That means the INSERT into xf_attachment_view occurs almost every time people visit my site. And after disable the INSERT DELAYED, there is no any issue yet. So, it's very possible that the problem has been solved.

    Thank you very much for your help.
  8. Bob

    Bob Developer Staff Member

    Yes, and the same goes with THREADS. Each time a thread is viewed, the thread_id is written to the xf_thread_view table and once every 30 minutes a CRON does the same thing as it does for attachments.

    If you have XenForo Media Gallery, it too does this for both albums and media.

    Showcase does it as well for Showcase Item views.

    btw, turning off delayed inserts won't fix anything with your HOST. Ignoring the problem (YOUR HOST) is the wrong thing to do. You will continue to have problems until you find a better host with a better server configuration that matchs the needs of your site.
  9. Nobitakung

    Nobitakung New Member Showcase

    From what had happened, it seems that the server has problem in processing the INSERT DELAYED, so, disabling that query type should fix the issue, doesn't it? Or you meant that that 'max_user_connections' limit may still be a problem if my traffic increase in the future?
    Last edited: May 2, 2017
  10. Bob

    Bob Developer Staff Member

    I will make it Large Bold Red text so that maybe this time you will READ it.

Thread Status:
Not open for further replies.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.