Rails - Jun 2005 - MySQL 3.23.58 Syntax Error (does it work with Rails?)

Hi. I''m trying to get an app running on a Red Hat server, running  
Apache 2 and MySQL 3.23.58... I''m having particular problems with  
this line of code:


     @pages = Page.find_by_sql(["select pages.* from pages join  
categories on pages.category_id = categories.id join websites on  
categories.website_id = websites.id where websites.id = ? order by  
visible DESC, category_id, title", current_website?])


It works fine on all other servers I''ve tried this code on, but  
they''re all running some version of MySQL 4, and I''m wondering
if
there''s some obvious thing I''m trying here that''s not
going to work
with MySQL 3. The error that shows up in my production log:


     Page Load (0.000000)   You have an error in your SQL syntax near  
''on pages.category_id = categories.id join websites on  
categories.website_id = we'' at line 1: select pages.* from pages join  
categories on pages.category_id = categories.id join websites on  
categories.website_id = websites.id where websites.id = 1 order by  
visible DESC, category_id, title


...which, while not particularly enlightening, does at least narrow  
it down to this SQL query.

Any ideas what I might be doing wrong here?


Thanks very much,
Raymond

You need mysql4+ 

On 6/6/05, Raymond Brigleb
<ray-THGPwszTed5CpjqP0VxSwUEOCMrvLtNR@public.gmane.org>
wrote:
> Hi. I''m trying to get an app running on a Red Hat server, running
> Apache 2 and MySQL 3.23.58... I''m having particular problems with
> this line of code:
> 
> 
>      @pages = Page.find_by_sql(["select pages.* from pages join
> categories on pages.category_id = categories.id join websites on
> categories.website_id = websites.id where websites.id = ? order by
> visible DESC, category_id, title", current_website?])
> 
> 
> It works fine on all other servers I''ve tried this code on, but
> they''re all running some version of MySQL 4, and I''m
wondering if
> there''s some obvious thing I''m trying here
that''s not going to work
> with MySQL 3. The error that shows up in my production log:
> 
> 
>      Page Load (0.000000)   You have an error in your SQL syntax near
> ''on pages.category_id = categories.id join websites on
> categories.website_id = we'' at line 1: select pages.* from pages
join
> categories on pages.category_id = categories.id join websites on
> categories.website_id = websites.id where websites.id = 1 order by
> visible DESC, category_id, title
> 
> 
> ...which, while not particularly enlightening, does at least narrow
> it down to this SQL query.
> 
> Any ideas what I might be doing wrong here?
> 
> 
> Thanks very much,
> Raymond
> 
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

-- 
Tobi
http://www.snowdevil.ca - Snowboards that don''t suck
http://typo.leetsoft.com - Open source weblog engine
http://blog.leetsoft.com - Technical weblog

Strictly speaking, you can achieve that query in mysql 3.23.58,
although I''m not sure you can use that syntax (I''ve never
tried it).
This, however, should accomplish the same effect:

@pages = Page.find_by_sql(["select pages.* from pages, categories,
websites where pages.category_id = categories.id and
categories.website_id = websites.id and websites.id = ? order by
visible DESC, category_id, title", current_website?])

I''m running a few rails apps (including typo) on mysql 3.23.  They
work fine, except transactions aren''t rolled back if you have an error
during processing, so you tend to end up with turds all over the
database during development.

Tyler

> I''m running a few rails apps (including typo) on mysql 3.23.  They
> work fine, except transactions aren''t rolled back if you have an
error
> during processing, so you tend to end up with turds all over the
> database during development.
I understand what you''re saying here, but that''s a way
different
interpretation than many have for "works fine"...  =)

> I understand what you''re saying here, but that''s a way
different
> interpretation than many have for "works fine"...  =)
Hmm, good point.  Sometimes, I get so used to suboptimal environments
that I forget they''re suboptimal. :p

In some circumstances, upgrading to mysql4 isn''t an option;
I''m
dealing with two servers like that right now.  Even with a vintage
mysql, I still prefer Rails to php.  So right now, I''m developing on
mysql4.0, and deploying on 3.23.  That setup works quite well, given
the circumstances.

I just thought it would be good to point out the fact that Rails on
3.23 is doable, and I guess I worded it too strongly.

Tyler