Query that worked in MySQL 3+ no longer works with MySQL 5?

[Mateo1041]

Hi everyone,

I recently upgraded a database from MySQL 3+ to version 5. However, the following query no longer works and gives a syntax error in PHP:


ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Dual ON Dual.MemberID = Member.MemberID WHERE Member.Active = 1 .

The database query failed! Query was 'SELECT DISTINCT * FROM Member LEFT JOIN Dual ON Dual.MemberID = Member.MemberID WHERE Member.Active = 1 AND Member.Deceased = 0 AND Member.Resigned = 0 AND (Member.PondID = 3005 OR Dual.PondID = 3005) GROUP BY Member.MemberID'


I'm very confused as to why this doesn't work. Why would there be a syntax error with a simple LEFT JOIN? It worked just fine before. And I couldn't find any documentation where the syntax would have changed between versions.

I even tried shorning the query to simply be:


ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Dual ON Dual.MemberID = Member.MemberID' at line 1.

The database query failed! Query was 'SELECT * FROM Member LEFT JOIN Dual ON Dual.MemberID = Member.MemberID'


Any ideas would really be appreciated. This is so frustrating. Thanks.

- Matt.

 

[swampBoogie]

Seems like Mysql has added DUAL as a reserved word. Change the name or enclose it in backticks i.e. `DUAL`

 

[Mateo1041]

Thanks! This was it exactly. I did have trouble as I tried a normal apostrophy (') at first when a backtick (`) was needed, but eventually got it right.

I've now changed all instances where the "Dual" table is mentioned and all is working well.