Alpha 6 Mysql warnings.

[dhughes]

I run with php option mysql.trace_mode=On and get the following warnings:

Warning: mysql_query() [http://www.mysql.com/doc]: Your query requires a full tablescan (table rights, 3 rows affected). Use EXPLAIN to optimize your query. in /www/zabbix/include/db.inc.php on line 63

Warning: Unknown: 1 result set(s) not freed. Use mysql_free_result to free result sets which were requested using mysql_query() in Unknown on line 0

This one I usually get when I bail out of a function and forget the close and disconnect. On a production server it eventually results in a significant memory hit.

This appear immediately after login and on most (all?) following pages.

[cadbury]

i have the same problem on beta 7 after an update of mysql

did you solved it?

thanks

[cadbury]

for those who have this problem,
i solved it by turning off the trace mode of mysql,
in php.ini,

mysql.trace_mode = Off

then restart apache, and it's gone

[Onno]

Had exactly the same problem with some software I wrote a while back. But instead of ignoring the debug output, I have attempted to isolate the root cause of the problem within my code. It might be different for other environments, but this is what I found:
1) Using PHP, MySQL and Apache -> software upgrade/downgrade no result/change
2) Checking the computer and found it is dead slow (PIII/700Mhz) -> no hardware available at the moment, so I had to keep it like it was
3) Checking the PHP code and after many hours found the cause of the
problem for my situation.

problem: It appeared to be related to loop conditions which included HTML elements as output.

1) retrieve massive amount of data from mysql
2) create a loop condition (if or while) to present this data into <td>$data</td> fields into a large table (approx 100 fields within one record)

Now I solved it by adding a small delay within the loop condition (3000uS in my example) and all errors where gone and dusted. The moment I lower the delay, the problem occurs again.

Since I am not an expert in this area there might be more root causes, but I am suspecting the following to occur;

1) The HTML output buffer isn't that happy with very aggresive (table based) html sequences and the loop is generating way to much output for the HTML buffer to store before presenting.
2) Or the mysql process is suffering from starvation due to the very high and long cpu utilization. I saw before the small adjustment cpu hitting the roof for over 60 seconds. After the adjustment the cpu doesn't spike at all.

The latter is to me most logical. The loop claims all cpu resources, mysql isn't receiving proper attention and start complaining about it.

Now the interesting thing here is that installing the same code on a faster laptop does solve the problem...almost.... I can tune the delay to approx 1000uS and it remains stable.

Somehow a process is not happy with the high data volume and associated cpu and memory claim.

Hopefully the true experts can chime in and provide answers to this misterious problem.

Yours Sincerely,
Onno

[Alexei]

Warning: mysql_query() [http://www.mysql.com/doc]: Your query requires a full tablescan (table rights, 3 rows affected). Use EXPLAIN to optimize your query. in /www/zabbix/include/db.inc.php on line 63

IMHO MySQL is stupid here. How could it complain about the full tablescan if the table contains only 3 rows??? Full table scan will ALWAYS work faster than a scan with some indexes used.

[Wolfgang]

IMHO MySQL is stupid here. How could it complain about the full tablescan if the table contains only 3 rows??? Full table scan will ALWAYS work faster than a scan with some indexes used.

I agree that in case of just 3 rows, a full scan will be faster.

If the table contains thousands of records, an index will be better, given that the index matches the search criteria.