Phoca Forum

We have phoca download running in Joomla on a private in-house network for a hospital .. the IT person in charge of the server wrote us this the other day and asked me to investigate what is going on.

That Phoca download component of Joomla is still causing issues... something in the code asks mysql to create a tmp table that fills the entire hard drive and takes up resources on the server while it waits. Today I got a notification that some user apparently got impatient and kept clicking... there were 3 of these errant processes hosing the intranet server that I had to kill.

Can you look and see if there has been a new release of that product since you did the install? Maybe a newer version has that issue patched.

Anyone have any ideas what's going on here? I don't see anything in settings or any other issues posted online that give me any leads on this. Any help / advice is appreciated. If it's something that's been fixed in a recent update, we'll take steps to upgrade the component and see if that solves it.

Hi, Phoca Download never created a temporary table and has never confirmed security problem, so for now I cannot say what exactly is a problem on that server.

Ask your IT person what (which part) exactly creates the temporary tables, but Phoca Download does not do it. Will bet good for you to get some concrete information.

Jan

Thanks for the reply - I didnt' get any more info from that and he couldn't tell what was happening other than saying that 'when repeatedly clicked on something' it brought his server to it's knees.

Here is some more information from the IT person:


Here is the mysql "show processlist" command output during one of these scenarios. It shows part of the SQL statement, but unfortunately it is cut off... You'll notice there are two queries creating tmp tables... those are the ones that fill the hard drive.



mysql> show processlist;
+---------+--------------+-----------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+--------------+-----------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 1296410 | cvmcintranet | localhost | intranet | Query | 2351 | Sorting result | SELECT a.id, a.title, a.description as `text`, CONCAT('index.php?option=com_phocadownload&view=categ |
| 1296462 | cvmcintranet | localhost | intranet | Query | 2298 | Copying to tmp table | SELECT a.id, a.title, a.description as `text`, CONCAT('index.php?option=com_phocadownload&view=categ |
| 1296467 | cvmcintranet | localhost | intranet | Query | 186 | Locked | UPDATE jos_phocadownload SET hits = ( hits + 1 ) WHERE id='2737' |
| 1296470 | cvmcintranet | localhost | intranet | Query | 186 | Locked | UPDATE jos_phocadownload SET hits = ( hits + 1 ) WHERE id='2737' |
| 1296473 | cvmcintranet | localhost | intranet | Query | 2287 | Copying to tmp table | SELECT a.id, a.title, a.description as `text`, CONCAT('index.php?option=com_phocadownload&view=categ |
| 1296494 | cvmcintranet | localhost | intranet | Query | 177 | Locked | UPDATE jos_phocadownload SET hits = ( hits + 1 ) WHERE id='1011' |
| 1296538 | cvmcintranet | localhost | intranet | Query | 188 | Locked | SELECT c.*, cc.id AS categoryid, cc.title AS categorytitle, cc.alias AS categoryalias, cc.access as |
| 1296627 | cvmcintranet | localhost | intranet | Query | 188 | Locked | SELECT c.*, cc.id AS categoryid, cc.title AS categorytitle, cc.alias AS categoryalias, cc.access as |
| 1296631 | cvmcintranet | localhost | intranet | Query | 188 | Locked | SELECT a.id, a.title, a.alias, a.filename_play, a.filename_preview, a.link_external, c.id as catid, |
| 1296665 | cvmcintranet | localhost | intranet | Query | 168 | Locked | SELECT COUNT(a.id) AS count FROM jos_phocadownload AS a WHERE approved = 0 LIMIT 0, 1 |
| 1296726 | cvmcintranet | localhost | intranet | Query | 171 | Locked | SELECT c.*, cc.id AS categoryid, cc.title AS categorytitle, cc.alias AS categoryalias, cc.access as |
| 1296727 | cvmcintranet | localhost | intranet | Query | 175 | Locked | SELECT c.*, cc.id AS categoryid, cc.title AS categorytitle, cc.alias AS categoryalias, cc.access as |
| 1296740 | cvmcintranet | localhost | intranet | Query | 173 | Locked | SELECT COUNT(a.id) AS count FROM jos_phocadownload AS a WHERE approved = 0 LIMIT 0, 1 |
| 1296743 | cvmcintranet | localhost | intranet | Query | 174 | Locked | SELECT c.*, cc.id AS categoryid, cc.title AS categorytitle, cc.alias AS categoryalias, cc.access as |
| 1297230 | cvmcintranet | localhost | intranet | Query | 175 | Locked | SELECT c.*, cc.id AS categoryid, cc.title AS categorytitle, cc.alias AS categoryalias, cc.access as |
| 1297494 | cvmcintranet | localhost | intranet | Query | 113 | Locked | SELECT c.*, cc.id AS categoryid, cc.title AS categorytitle, cc.alias AS categoryalias, cc.access as |
| 1297560 | cvmcintranet | localhost | intranet | Query | 103 | Locked | SELECT a.id, a.title, a.alias, a.filename_play, a.filename_preview, a.link_external, c.id as catid, |
| 1297601 | cvmcintranet | localhost | intranet | Query | 40 | Locked | SELECT a.*, cc.title AS categorytitle, s.title AS sectiontitle, u.name AS editor, g.name AS groupnam |
| 1297611 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+---------+--------------+-----------+----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
19 rows in set (0.00 sec)

Hi, I see not query with CREATE

Anyway such queries:
do not exist in Phoca Download, so check your code if someone has no modified it.

Jan

Jan wrote:Hi, I see not query with CREATE

Meaning what?

You have written (exaclty you have quoted): "You'll notice there are two queries creating tmp tables"

But I don't see any CREATE query which creates the tmp tables

Here's some more information on this problem:

From IT:

Here are a few example queries pulled from the file with phocadownload and order by/group by keywords. The first runs only 4 minutes, where as the others seem to run for an eternity for some reason.

# User@Host: cvmcintranet[cvmcintranet] @ localhost []
# Query_time: 265 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT a.id, a.title, a.description as `text`, CONCAT('index.php?option=com_phocadownload&view=category&id=',a.catid,':',b.alias,'&download=',a.id,':',a.alias) AS `href`, '2' AS browsernav, CONCAT('Documents/', c.title, '/', b.title) AS `section`, a.date as `created` FROM jos_phocadownload a, jos_phocadownload_categories b, jos_phocadownload_sections c WHERE ( a.unaccessible_file=1 OR a.access <= 0) AND b.access <= 0 AND c.access <= 0 AND a.catid = b.id AND a.published = 1 AND b.published = 1 AND c.id = b.section AND c.published = 1 AND (a.title LIKE '%pto%' OR a.filename LIKE '%pto%' OR a.description LIKE '%pto%') OR (a.title LIKE '%cash-in%' OR a.filename LIKE '%cash-in%' OR a.description LIKE '%cash-in%') OR (a.title LIKE '%policy%' OR a.filename LIKE '%policy%' OR a.description LIKE '%policy%') ORDER BY a.date DESC LIMIT 0, 50;

# Time: 110714 9:41:37
# User@Host: cvmcintranet[cvmcintranet] @ localhost []
# Query_time: 4294967295 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT a.id, a.title, a.alias, a.filename_play, a.filename_preview, a.link_external, c.id as catid, a.confirm_license, c.title as cattitle, c.alias as catalias, s.id as sectionid, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as catslug FROM jos_phocadownload AS a LEFT JOIN jos_phocadownload_categories AS c ON a.catid = c.id LEFT JOIN jos_phocadownload_sections AS s ON c.section = s.id WHERE a.id = 3707;


# User@Host: cvmcintranet[cvmcintranet] @ localhost []
# Query_time: 4294967295 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT g1.id, g1.name, COUNT(g2.name) AS level FROM jos_core_acl_aro_groups AS g1 INNER JOIN jos_core_acl_aro_groups AS g2 ON g1.lft BETWEEN g2.lft AND g2.rgt WHERE g1.lft > 3 AND g1.lft <12 GROUP BY g1.name ORDER BY g1.lft;

Also, this link has information on the process of SQL creating TMP tables - http://www.dbtuna.com/article.php?id=55

Please get back to me with your thoughts..

Hi,

can you get an information from which files the queries are comming?

This seems, it is not a Phoca Download component but some search plugin

Which version of Phoca Download you are using?
Which extensions for Phoca Download did you install?

Jan wrote:Hi,

can you get an information from which files the queries are comming?

This seems, it is not a Phoca Download component but some search plugin

Which version of Phoca Download you are using?
Which extensions for Phoca Download did you install?

We are using version 1.3.5 of Phoca Download - the only plugins we're using that didn't come with Phoca is one called Search - Phoca Download, version 0.9 by Joomla-R-Us April 21, 2009. This allows the standard Joomla search function to also include Phoca documents in search results and adds a checkbox to our standard search results for documents.