1
gruessle
SQL error: query requires a full tablescan
  • 2006/6/17 3:13

  • gruessle

  • Friend of XOOPS

  • Posts: 348

  • Since: 2003/9/20


Warning: mysql_query(): Your query requires a full tablescan (table xxxx_config, 242 rows affected). Use EXPLAIN to optimize your query. in /home/vmc/public_html/x/modules/protector/class/protector.php on line 44

**********************************************
Hello

What does "Use EXPLAIN to optimize your query" mean? Is that something I can add to some query to get better debug info?

I don't know if this is protector related but sure seams to be MySQL related. I went in to phpmyadmin to check the config table.
Table maintenance: Check table & Analyze table return no errors or tasks to do.

I don't seam to beable to turn protector off. I went in to modules and replaced the 1 with a 0 in the isactive field.

Do you know what t do?

Thanks
Grüssle
......

http://www.vmclinks.com

2
gruessle
Re: SQL error: query requires a full tablescan
  • 2006/6/17 13:51

  • gruessle

  • Friend of XOOPS

  • Posts: 348

  • Since: 2003/9/20


I am still looking for a solution on this in case.

You can see the error at

http://www.vmclinks.com/x/

Thank you
Grüssle
......

http://www.vmclinks.com

3
Dave_L
Re: SQL error: query requires a full tablescan
  • 2006/6/17 15:23

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


Here's how to use EXPLAIN:http://dev.mysql.com/doc/refman/4.1/en/explain.html

I don't know why you got the original error message. I've never seen that.

4
gruessle
Re: SQL error: query requires a full tablescan
  • 2006/6/17 17:59

  • gruessle

  • Friend of XOOPS

  • Posts: 348

  • Since: 2003/9/20


When I add EXPLAIN after SELECT the error becomes like this:
Warningmysql_query(): You have an error in your SQL syntaxCheck the manual that corresponds to your MySQL server version for the right syntax to use near 'EXPLAIN conf_name,conf_value FROM ty_config WHERE conf_title li in /home/vmc/public_html/x/modules/protector/class/protector.php on line 44

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/vmc/public_html/x/modules/protector/class/protector.php on line 45



When I run the QUERY in phpmyadmin I get no errors and it works fine.
SELECT *  FROM `ty_configWHERE `conf_titleLIKE '_MI_PROTECTOR%'



You can check the error at
http://www.vmclinks.com/

Thank you

Gruessle
Grüssle
......

http://www.vmclinks.com

5
Dave_L
Re: SQL error: query requires a full tablescan
  • 2006/6/17 18:06

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


EXPLAIN goes before SELECT, not after it.

But you can't just add EXPLAIN to a query in XOOPS, since the query will output diagnostics, not the needed data from the database.

You would run the EXPLAIN query using phpMyAdmin, outside of XOOPS>

6
gruessle
Re: SQL error: query requires a full tablescan
  • 2006/6/17 18:15

  • gruessle

  • Friend of XOOPS

  • Posts: 348

  • Since: 2003/9/20


I also followed the blank screen instractions from the FAQ

I can login now athttp://www.vmclinks.com/x/user.php

But when I go to the admin page I get
Quote:

This is your first time to enter the administration section. Press the button below to proceed."



Any ideas?
Grüssle
......

http://www.vmclinks.com

7
gruessle
Re: SQL error: query requires a full tablescan
  • 2006/6/17 18:25

  • gruessle

  • Friend of XOOPS

  • Posts: 348

  • Since: 2003/9/20


Quote:

Dave_L wrote:
EXPLAIN goes before SELECT, not after it.

But you can't just add EXPLAIN to a query in XOOPS, since the query will output diagnostics, not the needed data from the database.

You would run the EXPLAIN query using phpMyAdmin, outside of XOOPS>


OK this is what I get

SQL query
EXPLAIN SELECT *
FROM `ty_config`
WHERE `conf_titleLIKE '_MI_PROTECTOR%'
 
[Edit] [Skip Explain SQL] [Create PHP Code]



table     type     possible_keys     key     key_len     ref     rows     Extra
ty_config     ALL     NULL     NULL     NULL     NULL     242     Using where
Grüssle
......

http://www.vmclinks.com

8
gruessle
Re: SQL error: query requires a full tablescan
  • 2006/6/17 18:31

  • gruessle

  • Friend of XOOPS

  • Posts: 348

  • Since: 2003/9/20


I got in to the admin site like I said it shows me that green message that this is my first time in the admin area. Like on a new install.

When I clicked cancel it let me in to the admin area and I disabled the "Xoops Protector".
Now the site shows up in the user area.
Still have that Warning on top
Warningmysql_query(): Your query requires a full tablescan (table ty_config242 rows affected). Use EXPLAIN to optimize your queryin /home/vmc/public_html/x/modules/protector/class/protector.php on line 44


When I log out and back in I get that green "First time in admin area message"

Some thing is very wrong
Grüssle
......

http://www.vmclinks.com

9
gruessle
Re: SQL error: query requires a full tablescan
  • 2006/6/17 18:51

  • gruessle

  • Friend of XOOPS

  • Posts: 348

  • Since: 2003/9/20


OK

Disabled the protector and removed the two protector lines from mainfile.php.

Site is back up without protector now.

What do I need to do to get protector working again?
Grüssle
......

http://www.vmclinks.com

10
Dave_L
Re: SQL error: query requires a full tablescan
  • 2006/6/17 20:54

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


I don't know, but I saw that you posted the question athttp://www.peak.ne.jp/xoops/md/xhnewbb/viewtopic.php?topic_id=1509
You'll probably get help there.

Login

Who's Online

188 user(s) are online (138 user(s) are browsing Support Forums)


Members: 0


Guests: 188


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits