MySQL

Querys Bug

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2008

Posts: 3

Reputation: dispy is an unknown quantity at this point

Solved Threads: 0

dispy

Offline

Newbie Poster

Querys Bug

Jun 23rd, 2008

Good day

<<== new, please excuse If I post shit

I've one query which doesn't work:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
 SELECT
      conquer.TIMESTAMP AS TIME,
      conquer.new_owner AS new_owner_id
 
      FROM de12_conquer AS conquer
      INNER JOIN de12_tribe AS conqueror ON conqueror.id=conquer.new_owner
      INNER JOIN de12_tribe AS loser ON loser.id=conquer.old_owner
 
      WHERE (conquer.timestamp>0 && conquer.timestamp<1213647029734) AND (conqueror.ally=37038 OR loser.ally=37038 )
      ORDER BY conquer.TIMESTAMP DESC LIMIT 50
 SELECT
      conquer.timestamp AS time,
      conquer.new_owner AS new_owner_id
 
      FROM de12_conquer AS conquer
      INNER JOIN de12_tribe AS conqueror ON conqueror.id=conquer.new_owner
      INNER JOIN de12_tribe AS loser ON loser.id=conquer.old_owner
 
      WHERE (conquer.timestamp>0 && conquer.timestamp<1213647029734) AND (conqueror.ally=37038 OR loser.ally=37038 )
      ORDER BY conquer.timestamp DESC LIMIT 50

This query doesn't work .... mysql server has gone away is the return or nothing... just loading

I've located the problem with a second query which works:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT
      conquer.TIMESTAMP AS TIME,
      conquer.new_owner AS new_owner_id
 
      FROM de12_conquer AS conquer
      INNER JOIN de12_tribe AS conqueror ON conqueror.id=conquer.new_owner
 
      WHERE (conquer.timestamp>0 && conquer.timestamp<1213647029734) AND (conqueror.ally=37038  )
      ORDER BY conquer.TIMESTAMP DESC LIMIT 50
SELECT
      conquer.timestamp AS time,
      conquer.new_owner AS new_owner_id
 
      FROM de12_conquer AS conquer
      INNER JOIN de12_tribe AS conqueror ON conqueror.id=conquer.new_owner
 
      WHERE (conquer.timestamp>0 && conquer.timestamp<1213647029734) AND (conqueror.ally=37038  )
      ORDER BY conquer.timestamp DESC LIMIT 50

As you can see the both query have only 1 different INNER JOIN (ignore the additional where-condition):

Help with Code Tags

MySQL Syntax (Toggle Plain Text)

INNER JOIN de12_tribe AS loser ON loser.id=conquer.old_owner

This line is "crashing" MySQl... but why ????

Structures of the tables:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
CREATE TABLE IF NOT EXISTS `de12_tribe` (
`id` INT(11) NOT NULL,
`name` VARCHAR(50) NOT NULL DEFAULT '',
`ally` INT(11) NOT NULL,
`villages` INT(5) NOT NULL,
`points` INT(11) NOT NULL,
`rank` INT(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `de12_tribe` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL default '',
`ally` int(11) NOT NULL,
`villages` int(5) NOT NULL,
`points` int(11) NOT NULL,
`rank` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
CREATE TABLE IF NOT EXISTS `de12_conquer` (
`villageid` INT(11) NOT NULL,
`TIMESTAMP` INT(11) NOT NULL,
`new_owner` INT(11) NOT NULL,
`old_owner` INT(11) NOT NULL,
KEY `villageid` (`villageid`),
KEY `TIMESTAMP` (`TIMESTAMP`),
KEY `new_owner` (`new_owner`),
KEY `old_owner` (`old_owner`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `de12_conquer` (
`villageid` int(11) NOT NULL,
`timestamp` int(11) NOT NULL,
`new_owner` int(11) NOT NULL,
`old_owner` int(11) NOT NULL,
KEY `villageid` (`villageid`),
KEY `timestamp` (`timestamp`),
KEY `new_owner` (`new_owner`),
KEY `old_owner` (`old_owner`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I hope you can help me, thanks !!

dispy

Last edited by dispy; Jun 23rd, 2008 at 3:46 pm.

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: Querys Bug

Jun 23rd, 2008

Hi

What exactly do you mean by:
"This query doesn't work .... mysql server has gone away is the return or nothing... just loading" ?

Do you get any error message?
Does the query never ends?
Is the result set empty? (because of improper data you want to join together. for example: if old_owner of table del2_conquer and id of del2_tribe do not have same values, your result set would be empty.)

How large are both tables, current number of rows per table? Depending on size and features needed sometimes innodb is more useful than myisam.

Second table does not have primary key, also no foreign keys (sorry, fk not possible for myisam). I am trying to figure out which relationship exists between both tables, and I have got the feeling that the table's structure wouldn't that be optimal. As for example villages of del2_tribe could appear several times for same tribe. On the other hand there is villageid of second table, how is it related to first table (via villages)?

ok, maybe above will get you some ideas to proceed solving your problem.

krs,
tesu

•

Join Date: Jun 2008

Posts: 79

Reputation: varmadba is an unknown quantity at this point

Solved Threads: 8

varmadba

Offline

Junior Poster in Training

Re: Querys Bug

Jun 24th, 2008

if you run a explain on your query it shows

"Impossible WHERE noticed after reading const tables"

that's because

[code] WHERE conquer.timestamp between 0 and 1213647029734 [\code]

In table de12_conquer ,timestamp columns is a integer data type and this number cant be inserted into it

The value you are using (1213647029734) is worng,it not a UNIXTIME or a timestamp value

let me know if it helps....

•

Join Date: Jun 2008

Posts: 3

Reputation: dispy is an unknown quantity at this point

Solved Threads: 0

dispy

Offline

Newbie Poster

Re: Querys Bug

Jun 24th, 2008

•

"This query doesn't work .... mysql server has gone away is the return or nothing... just loading" ?

Do you get any error message?
Does the query never ends?

One time testing it I waited ~5 minutes and then I got the "mysql server has gone away"

•

Is the result set empty? (because of improper data you want to join together. for example: if old_owner of table del2_conquer and id of del2_tribe do not have same values, your result set would be empty.)

Possible, but there are records passing the conditions, I'm sure ^^
I've tried LEFT JOIN instead of INNER JOIN -that didn't work too.

•

Second table does not have primary key, also no foreign keys (sorry, fk not possible for myisam). I am trying to figure out which relationship exists between both tables, and I have got the feeling that the table's structure wouldn't that be optimal. As for example villages of del2_tribe could appear several times for same tribe. On the other hand there is villageid of second table, how is it related to first table (via villages)?

de12_conquer can't have a primary key - there isn't an id existing just one time

de12_tribe contains the tribe -there is a primary key cause the id of every player exists only one time.
A tribe can have multiple conquers logged in the table de12_conquer - new_owner and old_owner are the fields which contain the id of the tribe ( one the looser and one the winner).
It's for a tool for the browsergame tribalwars (to understand the relations ).

the villageid contains the ID (primary key) of the village (in de12_villages- for this problem not necessary).

Don't worry about the structure of the relations -they're correct

•

f you run a explain on your query it shows

"Impossible WHERE noticed after reading const tables"

that's because

[code] WHERE conquer.timestamp between 0 and 1213647029734 [\code]

Wrong -I already tried explain and I didn't get an error.
Here the explain-output:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	loser 	ALL 	PRIMARY 	NULL 	NULL 	NULL 	7616 	USING temporary; USING filesort
1 	SIMPLE 	conquer 	ref 	TIMESTAMP,new_owner,old_owner 	old_owner 	4 	dispy.loser.id 	8 	USING WHERE
1 	SIMPLE 	conqueror 	ALL 	PRIMARY 	NULL 	NULL 	NULL 	7616 	Range checked for each record (index map: 0x1)
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	loser 	ALL 	PRIMARY 	NULL 	NULL 	NULL 	7616 	Using temporary; Using filesort
1 	SIMPLE 	conquer 	ref 	timestamp,new_owner,old_owner 	old_owner 	4 	dispy.loser.id 	8 	Using where
1 	SIMPLE 	conqueror 	ALL 	PRIMARY 	NULL 	NULL 	NULL 	7616 	Range checked for each record (index map: 0x1)

•

In table de12_conquer ,timestamp columns is a integer data type and this number cant be inserted into it

The value you are using (1213647029734) is worng,it not a UNIXTIME or a timestamp value

You're right: I'll fix that.

Thanks for trying to help me