Timeout copying tmp table

[aderijke]

Hi,

I have a problem with Phoca Gallery. I have a site with 20.000 pictures and everything worked ok for a while. Since a couple of weeks however I can't go to "images" (in the backend). I get a script time-out error:

Fatal error: Maximum execution time of 60 seconds exceeded in C:\Inetpub\vhosts\turen.nl\httpdocs\libraries\joomla\database\database\mysql.php on line 223
Fatal error: Maximum execution time of 60 seconds exceeded in C:\Inetpub\vhosts\turen.nl\httpdocs\libraries\joomla\session\storage\database.php on line 82
Fatal error: Maximum execution time of 60 seconds exceeded in C:\Inetpub\vhosts\turen.nl\httpdocs\libraries\joomla\session\storage\database.php on line 51

The timeout occurs at different places every time.

When i look at processes in my MySQL DB it seems it hangs on the following line

351613 webaw_*** localhost:2116 webawere_joomla_*** Query 24 Copying to tmp table SELECT a.*, cc.title AS category, cc.owner_id AS o...

When i kill this process the backend will show a partial page with some error's. On this page I can set the filter to show only unpublished images (I have no images unpublished). When I do this, the images page will load correctly. This is however a workaround i'd rather not use, because it is quite a lot of work.

Does anybode know what might be wrong?

Best,
Arthur

p.s. I have Joomla 1.5.2 (windows host) and Phoca Gallery 2.7.4

[Jan]

Hi, sorry, no experiences with problem "copying to tmp table"

How many categories do you have. There is no important how many images you have because images use pagination. But for categories a tree needs to be created and all categories needs to be loaded from database. So if there is e.g. more then 700 categories this can be a time issue on some servers.

Jan

[aderijke]

Hi Jan,

Thank you for your reply!

At the moment I have 271 categories. The company that hosts my site took a look at the database query's and found that this query takes over 30 seconds to return results:
SELECT a . * , cc.title AS category, cc.owner_id AS ownerid, u.name AS editor, v.average AS ratingavg, ua.username AS usercatname
FROM jos_phocagallery AS a
LEFT JOIN jos_phocagallery_categories AS cc ON cc.id = a.catid
LEFT JOIN jos_phocagallery_img_votes_statistics AS v ON v.imgid = a.id
LEFT JOIN jos_users AS u ON u.id = a.checked_out
LEFT JOIN jos_users AS ua ON ua.id = cc.owner_id
ORDER BY category, a.ordering

The error is:
Fatal error: Maximum execution time of 60 seconds exceeded in C:\Inetpub\vhosts\turen.nl\httpdocs\libraries\joomla\database\database\mysql.php on line 223

When they removed "ORDER BY category, a.ordering" the query only takes 0.0017 seconds.

They tested the query in phpMyAdmin.

Do you know how I can solve this?

Best,
Arthur

[Jan]

Hi, thank you for this info, I will do some tests with this query.

Jan

[aderijke]

Hi Jan,

Thank you very much. If i can be of any help by sending some more information, please let me know.

Arthur

[aderijke]

Hi Jan,

Did you manage to find the time to look into this problem yet?

Arthur

[Jan]

Hi, I am testing it now.

The default sql query if I test 500 categories and subcategories (with cca 6 levels):

Application afterLoad: 0.007 seconds, 0.53 MB
Application afterInitialise: 0.156 seconds, 3.03 MB
Application afterRoute: 0.156 seconds, 3.03 MB
Application afterDispatch: 4.508 seconds, 5.35 MB
Application afterRender: 4.662 seconds, 7.30 MB

If I remove "ORDER BY category, a.ordering":

Application afterLoad: 0.006 seconds, 0.53 MB
Application afterInitialise: 0.126 seconds, 3.03 MB
Application afterRoute: 0.126 seconds, 3.03 MB
Application afterDispatch: 4.456 seconds, 5.36 MB
Application afterRender: 4.616 seconds, 7.32 MB

and have no idea how can removing of ordering make such difference (I think, mysql needs to order the data as default, so if you remove the ordering, it is still ordered by mysql itself, so there should be not any diffrence )


Maybe this is more problem with thumbnail creating where the script needs to know which images don't have any thumbnails, but this can be solved by enabaling "Pagination Thumbnail Creation" parameter, see:
https://www.phoca.cz/documents/2-phoca-g ... -menu-item

Jan

[aderijke]

Hi Jan,

It is a strange problem....

I upload my images AND thumbnails via FTP, so Phoca doesn't need to create thumbnails (Enable Thumbnails Creation is set to NO)
Maybe this is a workaround for me: is it possible te set the "select state" filter in the images page to show only unpublished images by default?
When i click on "Images" I tested the page will load in under a second when i set the "select state" filter to unpublished, but I don't know where in the PHP code I can change this so that it will load the page with the filter set to unpublished by default (I'm a .NET c# programmer and don't know PHP unfortunatly).

Arthur

[Jan]

Hi, for image list view:

administrator\components\com_phocagallery\views\phocagallerys\view.html.php

Code: Select all

$filter_state		= $mainframe->getUserStateFromRequest( $this->_context.'.filter_state',	'filter_state', '',	'word' );

Jan