Query works in MySQL 4 but not v5

daymobrew

I have just moved a client site to a new server and it has MySQL 5. The old server had v4.

SELECT T.lineage, B.name AS country, A.name AS location
FROM location_tree AS T, locations AS B
INNER JOIN locations AS A ON A.location_id = T.location_id
INNER JOIN location_tree AS U ON B.location_id = U.location_id
WHERE T.node ='2273' AND U.depth ='2'
AND FIND_IN_SET( B.location_id, CONCAT(T.lineage,T.node))

This works in MySQL v4 and returns 3 fields, something like:

1,3,789,799,2159,  - USA - Charlotte Harbor Area

In MySQL v5.051a it generates an error:

#1054 - Unknown column 'T.location_id' in 'on clause'

I did notice a warning in the phpMyAdmin interface about the PHP MySQL library version:

Your PHP MySQL library version 4.1.21 differs from your MySQL server version 5.0.51a. This may cause unpredictable behavior.

The server is PHP 5.2.5, but the MySQL library is only 4.1.21 - is this a red flag?

Edit: I am asking here before contacting the hosting support because I am not confident that they will know the answer.

E39MSport

Hi. don't mean to be simplifying this too much but it appears that you are using 2 separate databases and that they may not be synchronised in terms of data. Error states that a column you referenced is invalid. Have you checked the views/tables?

daymobrew

E39MSport wrote: »

Hi. don't mean to be simplifying this too much but it appears that you are using 2 separate databases and that they may not be synchronised in terms of data. Error states that a column you referenced is invalid. Have you checked the views/tables?

There is no harm in asking the basics.

Yes, the dbs are on different machines. I copied the data via phpMyAdmin Export to SQL as file and import on the other side. No errors reported.
I have run other scripts against this table without an issue.
I have checked the table structures and they are the same (except for collation on 1 column on v5 db).

E39MSport

Hiya.
I had a quick bit of googlage and found this. Looks like your not the only one to have similar issues.
http://www.oscommerce.com/community/contributions,4654

daymobrew

E39MSport wrote: »

Hiya.
I had a quick bit of googlage and found this. Looks like your not the only one to have similar issues.
http://www.oscommerce.com/community/contributions,4654

Thanks for this. While the site is not osCommerce, that page gave me the idea to search the MySQL bug db. I found a bug which told me to put round brackets around parts of my query and this fixed it. Apparently MySQL was changed to make it more compliant with SQL:2003.

daymobrew

Here are my changes, in case it is of use to someone else.

I added brackets around the FROM and ON parameters.

SELECT T.lineage, B.name AS country, A.name AS location
FROM [COLOR="Red"]([/COLOR]location_tree AS T, locations AS B[COLOR="Red"])[/COLOR]
INNER JOIN locations AS A ON [COLOR="Red"]([/COLOR]A.location_id = T.location_id[COLOR="Red"])[/COLOR]
INNER JOIN location_tree AS U ON [COLOR="Red"]([/COLOR]B.location_id = U.location_id[COLOR="Red"])[/COLOR]
WHERE T.node ='2273' AND U.depth ='2'
AND FIND_IN_SET( B.location_id, CONCAT(T.lineage,T.node))

Civilian_Target

Interesting, I would have never guessed that. Thanks for the info.