Home > Archive > PHP SQL > September 2005 > get results only sometimes
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
get results only sometimes
|
|
| Bob Bedford 2005-09-25, 7:56 am |
| Sometimes, WITH EXACLY THE SAME QUERY AND THE SAME RECORDS IN MY DATABASE, i
get results, sometimes I get no results.
I don't change the query, and when doing a query refresh in phpmyadmin, then
sometimes it fails, sometimes not.
Here is the simplest query that fails sometimes:
SELECT *
FROM type
INNER JOIN mod ON type.modcode = mod.modcode
INNER JOIN mak ON mod.makecode = mak.makecode
this query works always.
SELECT *
FROM type
INNER JOIN mod ON type.modcode = mod.modcode
So it seems that as soon as you put 2 inner joins with a table named "type"
then it fails: sometimes I get records, sometimes not.
with query results, the result count is 0-0 (1 total) (in phpmyadmin)
so even when it works, mysql server doesn't return the correct number of
records with the first query. The second query is ok
any idea ?
| |
| Stefan Rybacki 2005-09-25, 7:56 am |
| Bob Bedford wrote:
> Sometimes, WITH EXACLY THE SAME QUERY AND THE SAME RECORDS IN MY
> DATABASE, i get results, sometimes I get no results.
> I don't change the query, and when doing a query refresh in phpmyadmin,
> then sometimes it fails, sometimes not.
> Here is the simplest query that fails sometimes:
>
> SELECT *
> FROM type
> INNER JOIN mod ON type.modcode = mod.modcode
> INNER JOIN mak ON mod.makecode = mak.makecode
>
> this query works always.
> SELECT *
> FROM type
> INNER JOIN mod ON type.modcode = mod.modcode
>
> So it seems that as soon as you put 2 inner joins with a table named "type"
> then it fails: sometimes I get records, sometimes not.
> with query results, the result count is 0-0 (1 total) (in phpmyadmin)
> so even when it works, mysql server doesn't return the correct number of
> records with the first query. The second query is ok
>
> any idea ?
>
Did you tried to send this query from the console or another application like mysqlcc?
Maybe its because of phpmyadmin.
Stefan
| |
| Bob Bedford 2005-09-25, 7:56 am |
| "Stefan Rybacki" <stefan.rybacki@gmx.net> a écrit dans le message de news:
3pnd47Fb7mtiU1@individual.net...
> Bob Bedford wrote:
>
> Did you tried to send this query from the console or another application
> like mysqlcc? Maybe its because of phpmyadmin.
Hi Stefan, thanks for the reply-
Actually we got the error from the php code in the site.
the query on the dev machine (XP) doesn't get this error. It actually works
on the server (Linux) I can't test on the console, and it fails, in our PHP
code and in the phpmyadmin code.
So I can't test like you said.
Bob
| |
| Stefan Rybacki 2005-09-25, 6:57 pm |
| Bob Bedford wrote:
> "Stefan Rybacki" <stefan.rybacki@gmx.net> a écrit dans le message de
> news: 3pnd47Fb7mtiU1@individual.net...
>
>
>
> Hi Stefan, thanks for the reply-
> Actually we got the error from the php code in the site.
> the query on the dev machine (XP) doesn't get this error. It actually works
> on the server (Linux) I can't test on the console, and it fails, in our
> PHP code and in the phpmyadmin code.
>
> So I can't test like you said.
>
Do you get any errors, in the case there are no results?
Stefan
> Bob
>
| |
| Bob Bedford 2005-09-25, 6:57 pm |
| >> Hi Stefan, thanks for the reply-
>
> Do you get any errors, in the case there are no results?
>
> Stefan
that's the strangest thing: there is no error at all. Not in phpmyadmin nor
in my php code, then I test the mysql_query, I get no error and the code
continue (as I always chech the result of the mysql_query).
If I got any error it would be far easier
Bob
| |
| Neil Trigger 2005-09-25, 6:57 pm |
| $query_1='INNER JOIN mod ON type.modcode = mod.modcode';
$query_2='INNER JOIN mak ON mod.makecode = mak.makecode';
SELECT * FROM type ($query_1)&&($query_2)
Would that work?
--
Neil Trigger
Magic2k Managing Director
http://www.magic2k.com
http://www.magic2k.co.uk
| |
| Stefan Rybacki 2005-09-25, 6:57 pm |
| Bob Bedford wrote:
>
>
> that's the strangest thing: there is no error at all. Not in phpmyadmin
> nor in my php code, then I test the mysql_query, I get no error and the
> code continue (as I always chech the result of the mysql_query).
>
> If I got any error it would be far easier
Well did you query like this?
mysql_query("...") or die(mysql_error());
means do you check for a successful query or do you just query without any boolean check.
Stefan
> Bob
>
| |
| Bob Bedford 2005-09-26, 3:56 am |
| > Well did you query like this?
>
> mysql_query("...") or die(mysql_error());
>
> means do you check for a successful query or do you just query without any
> boolean check.
Yes of course I did. I've no mysql_error at all !!!! anyway even on
phpmyadmin I would get the error, isn't
| |
| Bob Bedford 2005-09-26, 8:00 am |
| "Neil Trigger" <nt018a9036@blueyonder.co.uk> a écrit dans le message de
news: _7BZe.6310$fl6.3231@fe2.news.blueyonder.co.uk...
> $query_1='INNER JOIN mod ON type.modcode = mod.modcode';
> $query_2='INNER JOIN mak ON mod.makecode = mak.makecode';
>
> SELECT * FROM type ($query_1)&&($query_2)
>
I've created an other table copying the type into tbltype.
syntax:
drop table if exists tbltype;
CREATE TABLE IF NOT EXISTS tbltype DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci(SELECT * FROM type);
Then I've changed the query and now it does work always.
What's strange anyway is that the type table is 10.1MB and the new tbltype
is 8.9, with exactly the same number of records, same character set and co.
Only thing, there is no key defined for the new table.
I won't change all my php code, I would preffer to fix the problem on the
type table instead of creating a new one and then to change my php code
Bob
| |
| Stefan Rybacki 2005-09-26, 8:00 am |
| Bob Bedford wrote:
> "Neil Trigger" <nt018a9036@blueyonder.co.uk> a écrit dans le message de
> news: _7BZe.6310$fl6.3231@fe2.news.blueyonder.co.uk...
>
>
> I've created an other table copying the type into tbltype.
>
> syntax:
> drop table if exists tbltype;
> CREATE TABLE IF NOT EXISTS tbltype DEFAULT CHARACTER SET latin1 COLLATE
> latin1_swedish_ci(SELECT * FROM type);
>
> Then I've changed the query and now it does work always.
> What's strange anyway is that the type table is 10.1MB and the new
> tbltype is 8.9, with exactly the same number of records, same character
> set and co.
> Only thing, there is no key defined for the new table.
>
> I won't change all my php code, I would preffer to fix the problem on
> the type table instead of creating a new one and then to change my php code
>
Did you try to optimize, repair, check or analyze table type? Don't worry different sizes
of tables on same data, this could happen because of fragmentation because I guess you
already worked on the type table but didn't on the new tbltype table.
Regards
Stefan
> Bob
>
| |
| Bob Bedford 2005-09-26, 8:00 am |
| > Did you try to optimize, repair, check or analyze table type? Don't worry
> different sizes of tables on same data, this could happen because of
> fragmentation because I guess you already worked on the type table but
> didn't on the new tbltype table.
>
Hello Stefan,
thanks for your help.
I've tried everything. Now, I've dropped indexes from the tbltype (the copy
of type table) and the error doesn't occur anymore.
As soon as I add 2 indexes, then sometimes the error appear again.
Why can't I add more than a Primary key and one index ?
Bob
| |
| Stefan Rybacki 2005-09-26, 6:57 pm |
| Bob Bedford wrote:
> Hello Stefan,
>
> thanks for your help.
>
> I've tried everything. Now, I've dropped indexes from the tbltype (the
> copy of type table) and the error doesn't occur anymore.
> As soon as I add 2 indexes, then sometimes the error appear again.
>
> Why can't I add more than a Primary key and one index ?
Not sure about that. What kind of index was it? Are makecode and and modcode primary keys?
If yes you don't need indices on them. But consider mysql can only use one index per
table, so if you're often seraching or connecting by a non primary key attribute a
combined index makes sense.
But your problem is really odd and shouldn't appear. What are you specs (cpu, ram, mysql
version, os, hdd, configuration)?
It seems there is something wrong with your configuration since you told me its working
fine under winxp (same specs besides linux?).
Regards
Stefan
>
> Bob
>
| |
| Bob Bedford 2005-09-26, 6:57 pm |
| > Not sure about that. What kind of index was it? Are makecode and and
> modcode primary keys?
I've several fields for linked tables. I've created indexes for such fields
to accelerate results.
Modcode is an index. not makecode.
> If yes you don't need indices on them. But consider mysql can only use one
> index per table, so if you're often seraching or connecting by a non
> primary key attribute a combined index makes sense.
I've 50 tables with about 10 connected tables at every query. This is the
only one that fails. Other query are bigger and more time consuming and they
don't fail.
> But your problem is really odd and shouldn't appear. What are you specs
> (cpu, ram, mysql version, os, hdd, configuration)?
Don't know the web server, but they are quite good on hosting, they are
recommended for bigger sites than our.
> It seems there is something wrong with your configuration since you told
> me its working fine under winxp (same specs besides linux?).
My XP takes more time to execute queries than on the server. So the problem
seems not the machine.
Regars Stefan and thanks for your help.
I finally fixed it, probably database structure (I'm not a professional
database guy, I'm a php programmer).
Cheers.
bob
| |
| Stefan Rybacki 2005-09-26, 6:57 pm |
| Bob Bedford wrote:
>
> I've several fields for linked tables. I've created indexes for such
> fields to accelerate results.
> Modcode is an index. not makecode.
>
>
> I've 50 tables with about 10 connected tables at every query. This is
> the only one that fails. Other query are bigger and more time consuming
> and they don't fail.
>
>
> Don't know the web server, but they are quite good on hosting, they are
> recommended for bigger sites than our.
>
>
> My XP takes more time to execute queries than on the server. So the
> problem seems not the machine.
>
> Regars Stefan and thanks for your help.
> I finally fixed it, probably database structure (I'm not a professional
> database guy, I'm a php programmer).
Ok whatever the reason was, hopefully you won't get such a behaviour again.
Good luck.
Stefan
>
> Cheers.
>
> bob
>
|
|
|
|
|