Page 1 of 1

[SOLVED] Query work on production but not on testing server

Posted: Mon Jul 31, 2006 3:22 am
by Stryks
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I know, I know .... it's probrably a difference in mySQL version (5.0.20 on testing and 4.1.20 on production), either that or just plain old bad SQL (which I'm quite good at) - (making bad SQL not just good at SQL) - but I'm interested to hear any feedback on why this is, and if it should be done a different way to be compatible with both.

My query is:

[syntax="sql"]
SELECT `a1`.`VI_PK` , `a2`.`name` , `b2`.`VRI_PK`
FROM `tbl_vendor_recipe` `b1`
INNER JOIN `tbl_vendor_recipe_ingredients` `b2` ON ( `b1`.`VR_PK` = `b2`.`VR_ID` )
RIGHT OUTER JOIN `tbl_vendor_product` `a1` ON ( `b2`.`VI_ID` = `a1`.`VI_PK` )
INNER JOIN `tbl_master_items` `a2` ON ( `a1`.`MI_ID` = `a2`.`MI_PK` )
WHERE (
`a1`.`V_ID` = '8'
)
AND (
`a2`.`MT_ID` = '1'
)
AND (
`b1`.`VR_PK` = '286'
)
ORDER BY `a2`.`name` 
Messy with the aliases I know, but i'm not the best with SQL anyhow, so anything to help me see how it works is always handy.

Anyhow ... on the production server, it returns a list of items:[/syntax]

Code: Select all

211  	Bacon  	NULL
212 	Chicken 	NULL
213 	Ham 	508
214 	Hot Salami 	NULL
215 	Mild Salami 	NULL
But on the testing server it returns only:

Code: Select all

213 	Ham 	508

It's probrably worth nothing that the desired output is that of the production server.

I guess at the end of the day I am just wondering if its a bad query which the older version is just allowing while the new version blocks the NULL values, or is the query OK and some other issue is at fault.

Either way, all assistance appreciated. Cheers


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Mon Jul 31, 2006 1:39 pm
by RobertGonzalez
Are both tables set up the same (InnoDB vs MyISAM) with all fields the same type (int, not null) etc. Please give some details of each table.

Posted: Mon Jul 31, 2006 7:52 pm
by Stryks
My table structure is as follows:

Code: Select all

-- ----------------------------
-- Table structure for tbl_vendor_recipe
-- ----------------------------
CREATE TABLE `tbl_vendor_recipe` (
  `VR_PK` int(11) NOT NULL default '0',
  `std_menu_id` tinyint(2) default NULL,
  `recipe_name` varchar(32) default NULL,
  `description` blob NOT NULL,
  `image` varchar(16) NOT NULL default '',
  `V_ID` int(11) NOT NULL default '0',
  `U_ID` int(11) default NULL,
  `last_update` int(11) default NULL,
  `RES_ID` tinyint(2) default NULL,
  PRIMARY KEY  (`VR_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for tbl_vendor_recipe_ingredients
-- ----------------------------
CREATE TABLE `tbl_vendor_recipe_ingredients` (
  `VRI_PK` int(11) NOT NULL auto_increment,
  `VR_ID` int(11) default NULL,
  `VI_ID` int(11) default NULL,
  `I_TYPE` int(2) default NULL,
  PRIMARY KEY  (`VRI_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for tbl_vendor_product
-- ----------------------------
CREATE TABLE `tbl_vendor_product` (
  `VI_PK` int(11) NOT NULL auto_increment,
  `V_ID` int(11) NOT NULL default '0',
  `MI_ID` int(11) NOT NULL default '0',
  `TT_ID` tinyint(2) NOT NULL default '0',
  PRIMARY KEY  (`VI_PK`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB; InnoDB free: 4096 kB; InnoDB free: 409';

-- ----------------------------
-- Table structure for tbl_master_items
-- ----------------------------
CREATE TABLE `tbl_master_items` (
  `MI_PK` int(11) NOT NULL auto_increment,
  `MT_ID` tinyint(2) NOT NULL default '0',
  `name` varchar(32) NOT NULL default '',
  `V_ID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`MI_PK`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I was a bit concerened about the different engines, but I have been told that it shouldn't be an issue. Is there a reason it would cause problems?

Posted: Mon Jul 31, 2006 10:18 pm
by RobertGonzalez
Is that a structure of the development or production server? What the info on the other one? I only ask because I think there is a difference in handling things in 5+ from 4.1 and even 4.0-. Just throwing out ideas.

Posted: Mon Jul 31, 2006 11:33 pm
by Stryks
It's the same on both. I wanted to test it so I made a new database and inserted an SQL dump from the other one.

Apart from the version of mySQL, the structure and data is idientical.

I'm at a bit of a loss. I can leave the query as is and it will work in production, but if my host ever upgrades ... my live site will start showing these incomplete data sets.

Thanks for the help on this. If anything else comes to you I'd love to hear it.

Posted: Mon Jul 31, 2006 11:47 pm
by Benjamin
Try it this way and see if it makes a difference.

Code: Select all

SELECT `a1`.`VI_PK` , `a2`.`name` , `b2`.`VRI_PK` 
FROM `tbl_vendor_recipe` `b1` 
INNER JOIN `tbl_vendor_recipe_ingredients` `b2` ON `b1`.`VR_PK` = `b2`.`VR_ID` 
RIGHT OUTER JOIN `tbl_vendor_product` `a1` ON `b2`.`VI_ID` = `a1`.`VI_PK` 
INNER JOIN `tbl_master_items` `a2` ON `a1`.`MI_ID` = `a2`.`MI_PK` 
WHERE `a1`.`V_ID` = '8' 
AND `a2`.`MT_ID` = '1' 
AND `b1`.`VR_PK` = '286' 
ORDER BY `a2`.`name` 

Posted: Tue Aug 01, 2006 2:59 am
by Stryks
Damn .... no change.

Thanks for the suggestion though.

Anything else anyone would like to add?

Posted: Tue Aug 01, 2006 4:47 am
by Stryks
I just got the hot tip .... and finally it works.

In case it can help anyone else out:

Code: Select all

select a1.VI_PK 
     , a2.name 
     , b2.VRI_PK 
  from tbl_vendor_product a1
inner 
  join tbl_master_items a2 
    on a2.MI_PK = a1.MI_ID 
   and a2.MT_ID = '1'
left outer
  join tbl_vendor_recipe_ingredients b2  
    on b2.VI_ID = a1.VI_PK  
   and b2.VR_ID = '286' 
 where a1.V_ID = '8'  
order 
    by a2.name
Cheers :D