Mysterious error on one MySQL5 hoster but not another

[OsakaWebbie]

I have several installations of a PHP/MySQL application I wrote. I am gradually moving all of them to better hosters who are running newer versions of the server apps. I have two copies running successfully on hoster A, who is running MySQL 5.0.45 (the old hoster was still on 3.23 - ack!). I just now moved another installation to hoster B, who I have not used before but who also is running MySQL 5.0.45. The settings listed in the MySQL section of phpinfo() look the same for both hosters. But on hoster B I'm getting a mysterious SQL error - I can only assume it is a compatibility thing, but the error message is not helpful. Here is the query:

SELECT DISTINCT person.PersonID, FullName, Furigana, Email, CellPhone, Phone, TempAddress, postalcode. * , Address, person.Photo
FROM person, percat
LEFT JOIN household ON person.HouseholdID = household.HouseholdID
LEFT JOIN postalcode ON household.PostalCode = postalcode.PostalCode
WHERE person.PersonID = percat.PersonID
AND CategoryID =22
ORDER BY Furigana

And here is the complaint:

SQL Error 1054: Unknown column 'person.HouseholdID' in 'on clause'

I can assure you that the column does indeed exist, and other queries in my code that reference that column work fine. (Yes, there is a reason I do two joins using JOIN but a third using the WHERE clause - the query is dynamically constructed from complex criteria.) I can only assume that something is different between hoster A and hoster B in their configuration of MySQL, but can someone suggest what to look for?

 

[darrellblackhawk]

That is a weird one...

Is this: postalcode. * a typo. note there is space between the '.' and the '*'.

 

[ingresman]

Can you dump the table descriptions?
I'll have a go on my machine

 

[OsakaWebbie]

darrellblackhawk: Don't take the spacing seriously - what you see is actually formatted output from PHPMyAdmin after I tried my query there. My original has no spaces at all, so I used that output for my post because it was nice and readable.

ingresman: Sure, although I don't think the problem is SQL structure. Here goes - this is the structure export for the relevent tables:

CREATE TABLE household (
  HouseholdID int(11) unsigned NOT NULL auto_increment,
  NonJapan tinyint(4) default NULL,
  PostalCode varchar(8) default NULL,
  Address varchar(200) default NULL,
  RomajiAddress varchar(200) default NULL,
  Phone varchar(20) default NULL,
  FAX varchar(20) default NULL,
  LabelName varchar(100) default NULL,
  Photo tinyint(4) NOT NULL default '0',
  PhotoCaption varchar(100) default NULL,
  UpdDate date default NULL,
  PRIMARY KEY  (HouseholdID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE percat (
  PersonID int(11) unsigned NOT NULL default '0',
  CategoryID int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (PersonID,CategoryID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE person (
  PersonID int(11) unsigned NOT NULL auto_increment,
  FullName varchar(60) NOT NULL default '',
  Furigana varchar(60) default NULL,
  Nickname varchar(30) default NULL,
  Sex char(1) default NULL,
  HouseholdID int(11) unsigned default NULL,
  Relation varchar(6) default NULL,
  Title varchar(6) default NULL,
  CellPhone varchar(30) default NULL,
  TempAddress varchar(200) default NULL,
  AddressNote varchar(20) default NULL,
  Email varchar(70) default NULL,
  Birthdate date default NULL,
  Country varchar(30) default NULL,
  Church varchar(50) default NULL,
  Remarks text,
  Photo tinyint(4) NOT NULL default '0',
  UpdDate date default NULL,
  PRIMARY KEY  (PersonID),
  KEY Furigana (Furigana)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE postalcode (
  PostalCode varchar(8) NOT NULL default '',
  Prefecture varchar(12) default NULL,
  ShiKuCho varchar(54) default NULL,
  Romaji varchar(200) default NULL,
  PRIMARY KEY  (PostalCode)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

[ingresman]

No, I'm sure it's ok, I just want to try it on my machine.
Just had a quick go and under 5.0.51b-community-nt it fails in the same way you describe, but under it works ok. So it looks like a bug but let's have a play with it first.

 

[ingresman]

Very odd, I thought it might be do with case of the names but not.
Hopefully someone might spot it as I can't, sorry.

 

[guelphdad]

your tables are out of scope. Get rid of the comma join syntax entirely and the problem will go away.

At this point in your query:

FROM person, percat
LEFT JOIN household ON person.HouseholdID = household.HouseholdID

only percat and household tables are in scope so trying to join on person.HouseholdID throws the error.

change person, percat to an INNER JOIN ... ON type join and stay away from comma join syntax.

 

[ingresman]

Could you presebt the query for us in this syntax mode ?, I'm a little confused by what you say.
Also did the rules change since version 3 and 4 ?

 

[OsakaWebbie]

Because of the way my PHP code constructs that query, it would be complicated to do the connection to percat before the word WHERE. I'll write more code to do it if I have to, but first I'd like to understand what the reasoning is. I don't know what "out of scope" means - can you explain, guelphdad?

As for when MySQL started getting more sensitive about this syntax, in my experience it would appear to have happened between when 5.0.45 was downloaded by one hoster and when 5.0.45 was downloaded by another hoster - for a development group that I'm sure has careful version control, that's spooky! Or, is there a setting in MySQL to select a more strict vs. less strict interpretation of statements? If there is, perhaps one of my hosters has it set to more strict than the other...?

 

[OsakaWebbie]

Mystery solved! The problem was a combination of poor memory on my part and a lack of careful version control. Just now I decided to go ahead and rewrite my PHP code to make it construct the query differently so that it can use the cleaner syntax (INNER JOIN rather than WHERE). I went to the master copy of my code (which contains all the newest changes) and opened the relevent file. To my surprise, it had already been modified in exactly the way I was thinking it needed to be! Piecing together the evidence, it would appear that I ran into this problem when I started using hoster A, made the needed change, but failed to copy the newest version of that file to everywhere that my code is used, namely this installation, which was at the time running happily on MySQL 3.23. Even now I still don't remember troubleshooting this issue before - my husband says it must have been one of those 2 a.m. moments (when I'm coding in the middle of the night and getting bleary - it happens to me a lot). My memory is not usually this bad - I feel like I'm in the twilight zone!

 

[OsakaWebbie]

And by the way, I just now found the thread related to the first time I troubleshot this: thread436-1501565. Now that I read that thread, the issue looks familiar, but until now I didn't remember it. Weird!

Sorry to have bothered you all unnecessarily...

 

[ingresman]

I don't think anyone has been bothered !!!
It's nice to get a follow up and also to know that there are some differecnes between SQL in 4 and 5

 

[OsakaWebbie]

I always try to follow up when an issue is solved - I know how frustrating it is to search the forum, find someone else who asked your question, but never see how it ended.

 

[guelphdad]

Here is the whole join listed above:

FROM person, percat
LEFT JOIN household ON person.HouseholdID = household.HouseholdID
LEFT JOIN postalcode ON household.PostalCode = postalcode.PostalCode

using comma separated tables is an implicit INNER JOIN with the WHERE clause handling the join condition.

earlier than mysql 5, the comma took prescedence over join clauses. in mysql 5 it was changed to confirm more closely to SQL standards.

Thus in the query above:

FROM person, percat
LEFT JOIN household ON person.HouseholdID = household.HouseholdID

there are two tables in scope at this point, percat and household. You thus can't have a join condition involving the person table at this point.

My suggestion is to get rid of the comma syntax entirely and specify an INNER JOIN on the person and perecat tables like this:

SELECT DISTINCT person.PersonID, FullName, Furigana, Email, CellPhone, Phone, TempAddress, postalcode. * , Address, person.Photo
FROM person
INNER JOIN percat
ON person.PersonID = percat.PersonID
LEFT JOIN household ON person.HouseholdID = household.HouseholdID
LEFT JOIN postalcode ON household.PostalCode = postalcode.PostalCode
WHERE 
CategoryID =22
ORDER BY Furigana

 

[OsakaWebbie]

Thanks for the explanation of why the mixed-style query wouldn't work MySQL5. As far as my situation, it was solved quite a while ago, with exactly the solution you suggested.

 

[guelphdad]

I hadn't visited the site in a while. Saw the question on the "why" and thought I'd fill it out directly in this thread. as mentioned, folks want to find a solution just not that someone else had the same problem as them right? ;-)

 

[OsakaWebbie]

Yeah, maybe I wasn't clear, because my solution, as well as AdaHacker's explanation about what MySQL didn't like about the original code (order of precedence of the comma vs. the join) was in another thread that I simply referenced in this one, so it's easy to miss when skimming a long thread. AdaHacker also gave an alternative solution of putting the comma list in parentheses, which might be useful for people who can't redo the whole structure of their SQL statement (in fact, it was pretty hard for me to do it in that instance, but I had already done it before AdaHacker jumped into the discussion). For easy reference, here is the link again: thread436-1501565

Ciao! Or more appropriately for where I live, "Mata, ne!", the Japanese equivalent of something like "Later!"

 

[ingresman]

Let me add my thanks for coming back with your solution.