Cannot access Dashboard in the backend Bug

0
Hi,
I have a page with lots of data (5GB of DB) and Zoo intensive which works fine with other components, but since some months ago I cannot access Content Statistics. In another posts (Error 500) you requested access to the site but I never got a reply after that.

Can you please let me know how I can proceed to fix this issue?

Thanks!!
Responses (9)
  • Accepted Answer

    Tuesday, September 17 2019, 06:29 AM - #Permalink
    0
    hi,

    if you access phpMyAdmin, can you see how big is the content statistics table? (how many rows?)
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, September 18 2019, 10:30 AM - #Permalink
    0
    Hi Germinal,
    thank you very much for your reply.

    The table is 575.7 MB big. Zoo Items table is around 1GB and it works with no issues in the querying.

    From my little research on this I think the problem is you are using a direct query on "buildQuery" method:

    $query = ' SELECT st.*, u.name as username '
    . ' FROM #__content_statistics as st '
    . ' LEFT JOIN #__users as u ON u.id = st.user_id '
    . $where_clause
    . $orderby
    ;


    And when that code comes to the getTotal method, it totally crashes the system (too big of a result).

    Maybe simplifying it for the getTotal method, makes the query lighter and it improves performance.
    • Germinal Camps
      more than a month ago
      hmm.. it's weird that this could crash the system. getTotal is just a count on the query.
      how many rows (approx) are there in this table?
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, September 19 2019, 10:52 AM - #Permalink
    0
    It's exactly 5,486,347 rows.

    Best!!
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, September 19 2019, 11:10 AM - #Permalink
    0
    ok

    this is A LOT of rows.

    the only suggestion I have is to delete old data, so the table is not so big.

    Probably you have some very old data in there. if you want to keep it, you can make a copy of the table, but I would try to keep the main table smaller (maybe only data from the last year or last few months)

    do you know how to do that?
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, September 19 2019, 11:13 AM - #Permalink
    0
    > hmm.. it's weird that this could crash the system. getTotal is just a count on the query.

    Actually that's my point!! As you are using a direct query, the _getListCount() doesn't replace the items in the select, it just performs the full query on all the table!!

    I have just tested it placing this code just before the return of the buildQuery method and the error dissappears:


    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->select('st.*, u.name as username');
    $query->from('#__content_statistics as st');
    $query->join('left', '#__users as u ON u.id = st.user_id');
    $query->order('st.date_event', 'asc');

    return $query;


    This code allows Joomla Model to replace the fields in the select with a proper "Count" function.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, September 19 2019, 01:52 PM - #Permalink
    0
    Germinal Camps wrote:

    ok

    this is A LOT of rows.

    the only suggestion I have is to delete old data, so the table is not so big.

    Probably you have some very old data in there. if you want to keep it, you can make a copy of the table, but I would try to keep the main table smaller (maybe only data from the last year or last few months)

    do you know how to do that?


    Sorry Germinal,
    I saw your reply just after pushing mine. Can you please consider using that code in the component? I have tested it and it allows the dashboard to load properly. Actually you just need to plug the "Where" conditions there and your component will perform much better!!

    We will check on removing old data but as we cannot access the component, there is not much we can do at the moment from the component so I would rather have this fixed than keep getting these errors.

    Please let me know.
    The reply is currently minimized Show
  • Accepted Answer

    Friday, September 20 2019, 01:41 PM - #Permalink
    0
    Hi Germinal,
    did you read my messages and test my code? I also have some suggetions that will for sure improve a bit performance like:

    1. Load a range of data on first load (like only last semester)
    2. Automatically export yearly data (or any kind of periods)
    3. Allow an unatended export for the data to be able to split the process and avoid max execution time and memory issues when creating the csv file or provide the sql query needed to get the data in a reasonable clean way directly from phpMyAdmin

    Still waiting for a fix for this issue.

    Best!!
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, September 21 2019, 03:21 PM - #Permalink
    0
    hi!

    thanks, yes I read it, I will look into it.

    1. what you mean? always is never loaded "all at once"... it's always paginated...
    2. good idea!
    3. yep
    The reply is currently minimized Show
  • Accepted Answer

    Tuesday, September 24 2019, 10:02 AM - #Permalink
    0
    Regarding 1. I mean setting by default a 1 year filter so that it doesn't need to count all rows in the table. As per my case, even with my modifications, if you set the "order by" clause, the sql query is too much and the server cannot handle it.

    It's a simple way of avoiding a server overload issue to fetch data that to be honest I do not think people need to see at first glance ;)

    Do you have an ETA of when any of these changes will be available?

    Best!!
    The reply is currently minimized Show
Your Reply