Geeklog Forums

MySQL server has gone away... Since I have 70.000 stories, the above clause exceeds the MYSQL default max_allowed_packet = 1megabyte.

When I click in the author name, i calls users.php?mode=profile... that creates a list (in memory) that try to include 70.000 SIC CODES. When the list reachs near 1 megabyte, the system crash e prints in the error.log more than 1 megabyte of code.... A little sample

SQL in question: SELECT sid,title,pid,type,UNIX_TIMESTAMP(date) AS unixdate FROM gl_comments WHERE (uid = 7251) HAVING sid in ('70021031223937404', '20045236152770177', '20019221455116612', '20001012956341057',... (more than 1 mega) ... '20046923595171593', 'conhecimento') ORDER BY unixdate DESC LIMIT 10

The last SIC is note "numeric", since the memory overflowed and the error starts loging.

What could I do?

I read the documentation of MYSQL, and they suggest to improve the MYSQL default max_allowed_packet. My provider refuses to make it in the server. It is possible to setup it in a LOCAL SESSION, like that:

DB_query("SET SESSION max_allowed_packet = 4 * 1024 * 1024");

But I don´t know:

- where to include a statment like that (i tried, without success, in the lib-commum, near fisrt delete...), since it runs once a time;

- if the syntax is correct (i am not technical)

The other approch would be to cut the SIC lists in the queries, since they eat a lot of memory and CPU (see users.php?mode=profile...). I really don´t know what would be the consequences of searchin a person using only its UID code: fast, easy, sharp. But the comments in the program are telling that the LIST improves performance. May be I am the only crazy person that has 70.000 histories... I presume the author knows very well what was done and why.

Who can really help me? The systems run perfectly with low number of histories...

Please,

Thanks in Advance

Notivaga

I discovered what is the problem.

When you have many histories - like in my case - there is a list that is "biffered" with all SID codes - as many as many histories. This list - if too big - overflow PHP ou SQL capacities...

When you ask for a person profile, you already know his UID. So - there is no need to put inside the list all SIDs - buffering the list with SIDs from that UID. I made this little adjust in the respective query (users;php / profile function) and everything is working well - even with 70.000 histories. Fast and realible.

Can you post your patch, please? We may want to include that in future versions ...

Thanks.

bye, Dirk

At users.php, near LINE 121

ORIGINAL
// $result = DB_query("SELECT username,fullname,regdate,homepage,about,pgpkey,photo FROM {$_TABLES['userinfo']},{$_TABLES["users"]} WHERE {$_TABLES['userinfo']}.uid = {$_TABLES['users']}.uid AND {$_TABLES['users']}.uid = $user");

NEW (works FINE!)

$result = DB_query("SELECT
nomereal,username,fullname,regdate,homepage,about,pgpkey,photo FROM {$_TABLES['userinfo']},{$_TABLES["users"]} WHERE {$_TABLES['userinfo']}.uid = {$_TABLES['users']}.uid AND {$_TABLES['users']}.uid = $user");

Hmm, this doesn't look right. The only difference between the two lines is the "nomereal" - and that isn't a valid field name in Geeklog ...

bye, Dirk

Yes.

The "upgrade I´ve introduced cutted all "last 10 top comments" in the profile page...


In fact, this SQl ask for all stories SIDs. With many histories, buffer overflows.

Since in my site there is much LESS comments than histories, I joined stories and comments dbs in a new sql. The list has only SIDs that has comments. It works very well.

FILE: users.php
LINE: near 195

ORIGINAL:
$sql = "SELECT sid FROM {$_TABLES['stories']} WHERE (draft_flag = 0) AND (date
NEW:
$sql = "SELECT {$_TABLES['stories']}.sid FROM {$_TABLES['stories']} LEFT JOIN {$_TABLES['comments']} ON {$_TABLES['comments']}.sid={$_TABLES['stories']}.sid WHERE ({$_TABLES['stories']}.tid IN ($topics)) AND ({$_TABLES['comments']}.sid IS NOT NULL)"

It is not necessary to filter draft_flag or date in the WHERE CLAUSE, since comments are only made based on already published histories. Just by joining with DB COMMENTS, you has this filter by default...

Thanks again.