[TYPO3-dev] Fatal Query

[Dan Osipov]

 > If you run the query in phpmyadmin is there a way to reproduce the 
problem?

Again, problem is random, so most times it works fine, so I was unable 
to reproduce it in phpMyAdmin. Also, several people may be using the 
NewsAdmin at the same time, causing several queries to go into 
statistics simultaneously - which might be the cause of the issue.

 > Slow query log is activeted?

Yes, query never makes it to the slow log, since it locks up the DB.

 > What is the size for temporary tables in Mysql?

1024M

 > How many different values for sys_language_uid do you have?

We're only using one language.

 >
 > Better for performance would be count(uid) but that a different story
 > (i.e. CORE problem)

AFAIK, count(*) and count(uid) are equivalent, since uid is a primary key.

Dan Osipov
Calkins Media
http://danosipov.com/blog/

Peter Russ wrote:
> --- Original Nachricht ---
> Absender:   Dan Osipov
> Datum:       12.01.2009 21:48:
>> Hello all,
>>
>> We have a serious issue on our hand, and I'm hoping someone will be 
>> able to provide some workarounds.
>>
>> Basic issue is that the mySQL DB is freezing due to a query from Typo3 
>> Core.
>>
>> Here is the set up. We're using the News Admin from the tt_news 3.0.0. 
>> At one point, the module is calling the function 
>> setTotalItems($queryParts) from class.db_list (line 485 in Typo3 
>> 4.2.3). The function runs a Count function, for example:
>> SELECT count(*)
>>             FROM tt_news LEFT JOIN tt_news_cat_mm AS mm1 ON 
>> tt_news.uid=mm1.uid_local
>>             WHERE
>>                 pid=15 AND 1=1 AND tt_news.pid > 0 AND 
>> tt_news.deleted=0  AND tt_news.sys_language_uid=0  AND mm1.uid_foreign 
>> IN (15,16,17,...)
>>
>> I truncated the list of categories - but there are many. Normally the 
>> query runs fine, returning result in the thousands. But sometimes, the 
>> query goes into "statistics" state, and locks the tt_news table. The 
>> query hangs, preventing all other queries from exectuing, and requires 
>> a manual restart of mySQL.
>>
>> I can hack tt_news module, but before I do - is there anything else 
>> someone can recommend?
>>
> 
> If you run the query in phpmyadmin is there a way to reproduce the problem?
> Slow query log is activeted?
> What is the size for temporary tables in Mysql?
> How many different values for sys_language_uid do you have?
> 
> Better for performance would be count(uid) but that a different story 
> (i.e. CORE problem)
> 
> Regs. Peter
> 
>