At work, we are redesigning one of our websites and we couldn’t completely get rid of our old data since other websites/cms still use it. On the new site, we standardized our database structure, used transactions and started using views once we converted from mysql 4 to mysql 5. The change went pretty smooth until we realized one of the changes in mysql 5, caused a lot of problems with our old code. Luckily it was on our development server and were able to make the changes before we went live with mysql 5.
The code was originally written around 1999 and contained Comma Joins.
SELECT * FROM users as u, user_details as d WHERE u.id = d.user_id
A comma separated joins are not as efficient nor are they the norm in web development. The “as” in “users as u” is not required, but that is my style.
If you have any of those joins and you move to mysql 5, you will run into some issues. You will want to convert those to INNER JOINS or LEFT JOINS, etc…
SELECT * FROM users as u INNER JOIN user_details as d ON u.id = d.user_id
Hope this helps!
*Thanks Matt for noticing the missing FROM
6 comments
1 ping
Skip to comment form ↓
Matt
August 2, 2008 at 7:52 pm (UTC -5) Link to this comment
That isn’t true. MySQL 5 supports both Theta and ANSI joins.
Timothy Haroutunian
August 3, 2008 at 3:49 pm (UTC -5) Link to this comment
As soon as we made those changes to the query, it worked and there weren’t any errors. What about the query would have caused the problem?
Matt
August 5, 2008 at 7:09 am (UTC -5) Link to this comment
The first one looks fine but the second one is missing the “FROM” keyword.
Zach
August 6, 2008 at 7:59 am (UTC -5) Link to this comment
I’m with Matt on this. Both of those queries are valid in MySQL 5. If it wasn’t working, there was something else wrong.
al
August 6, 2008 at 7:58 pm (UTC -5) Link to this comment
I have to agree with Matt and Zach.
Timothy Haroutunian
August 6, 2008 at 8:27 pm (UTC -5) Link to this comment
I will try to find out from the code, and post it. Thanks for the help
MySQL 5 Does Not Work With â??Comma Joinsâ??
August 2, 2008 at 4:53 pm (UTC -5) Link to this comment
[…] Original Carbon Tax Center […]