Ruby Forum Typo > MySQL error after SVN up to 1088...

I did a fresh checkout of r1088 and ran 'rake migrate' to update.  I 
believe
that is a migration from 42 to 46.  This ran without error.  When I 
attempt
to view the site I get a message stating: Controller stack got out of
kilter!

I saw this in the log:
Processing ArchivesController#index (for 127.0.0.1 at 2006-07-08 
22:01:11)
  Blog Load (0.002360)   SELECT * FROM blogs ORDER BY id LIMIT 1
  Trigger Load (0.000713)   SELECT * FROM triggers WHERE (due_at <=
'2006-07-08 22:01:11')
  Content Load (0.000000)   Mysql::Error: 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 ''12'' at line 1: select count(*) as count,
extract(year from published_at)||' '||lpad(extract(month from
published_at),2,'0') as date from contents where type='Article' and
published = 1 and published_at < '2006-07-08 22:01:11' group by date 
order
by date desc limit '12'

Anybody seen this before?  Is the Archives Sidebar broken?

I
On Jul 8, 2006, at 11:10 PM, Steve Longdo wrote:

> <= '2006-07-08 22:01:11')
>   Content Load (0.000000)   Mysql::Error: 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 ''12'' at line 1: select  
> count(*) as count, extract(year from published_at)||' '||lpad 
> (extract(month from published_at),2,'0') as date from contents  
> where type='Article' and published = 1 and published_at <  
> '2006-07-08 22:01:11' group by date order by date desc limit '12'
>
> Anybody seen this before?  Is the Archives Sidebar broken?

I noticed that as well when I upgraded, but haven't had the time to
track it down.  But yeah, I saw the exact same thing.

--Wade

Yeah. The sidebar configuration makes the limit 12 out ot be String 
which
MySQL doesn't like.  I have removed the Archive sidebar for now and the 
site
loads correctly.  I'll see if I can patch the Archives Sidebar.

Well the fix for the MySQL error is simple enough just add "to_i" to the
count variable that is passed on line 19 of the archives controller.  It
still doesn't render correctly though:

Archives

   - Master Archive <http://localhost:3000/articles/archives>
   - 1 () <http://localhost:3000/articles/find_by_date?month=0&year=1>

Can you give 1089 a try?


Scott

If that doesn't work, then can you run this query and send me the 
results?

select count(*) as count, extract(year from published_at)||'
'||lpad(extract(month from published_at),2,'0')  as date from contents
where type='Article'  group by date order by date desc limit 5;


Scott

1089 resolves the SQL error, but the archives sidebar still renders as 
1().

Here is the result of that query:

mysql> select count(*) as count, extract(year from published_at)||'
    '> '||lpad(extract(month from published_at),2,'0')  as date from
contents
    -> where type='Article'  group by date order by date desc limit 5;
+-------+------+
| count | date |
+-------+------+
|    71 |    1 |
+-------+------+
1 row in set (0.01 sec)

That's... bizarre.

Can you play with it a bit?  Does extract(month from published_at) and
extract(year from published_at) work right?


Scott

Seems to be something with the || concat.  I switched to the function:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14-standard

mysql> select count(*) as count, concat(extract(year from
published_at),lpad(extract(month from published_at),2,'0'))  as date 
from
contents where type='Article'  group by date order by date desc limit 5;
+-------+--------+
| count | date   |
+-------+--------+
|     1 | 200607 |
|     8 | 200606 |
|     7 | 200605 |
|     5 | 200604 |
|    14 | 200603 |
+-------+--------+
5 rows in set (0.03 sec)

Is it maybe just an issue on MySQL prior to version 5?  Most of the 
shared
hosts haven't upgraded to 5 yet...

Can you try r1096?


Scott

Been following this, as I gave up on the archives a while ago, just
didn't have time to dig into it.
I just tried r1096, and now I see the archives again, though it comes
across as a linked 1 with 460 articles in it. Clicking the link /
articles/find_by_date?month=0&year=1 gives an argument out of range
error

This is mysql?

Okay, try the current one.  I stopped concatenating things inside of the 
DB,
that should be a lot more portable.


Scott

Now I get this, which is certainly an improvement, the generated links 
look
good:
Archives

   - Master Archive <http://localhost:3000/articles/archives>
   - 2006 7 () <http://localhost:3000/articles/2006/7>
   - 2006 6 () <http://localhost:3000/articles/2006/6>
   - 2006 5 () <http://localhost:3000/articles/2006/5>
   - 2006 4 () <http://localhost:3000/articles/2006/4>
   - 2006 3 () <http://localhost:3000/articles/2006/3>
   - 2006 2 () <http://localhost:3000/articles/2006/2>
   - 2006 1 () <http://localhost:3000/articles/2006/1>
   - 2005 12 () <http://localhost:3000/articles/2005/12>
   - 2005 11 () <http://localhost:3000/articles/2005/11>
   - 2005 10 () <http://localhost:3000/articles/2005/10>

Processing ArchivesController#index (for 127.0.0.1 at 2006-07-09 
13:08:47)
  Blog Load (0.001680)   SELECT * FROM blogs ORDER BY id LIMIT 1
  Trigger Load (0.000905)   SELECT * FROM triggers WHERE (due_at <=
'2006-07-09 13:08:47')
  Content Load (0.026332)   select count(*) as count, extract(year from
published_at) as year,extract(month from published_at) as month from
contents where type='Article' and published = 1 and published_at <
'2006-07-09 13:08:47' group by year,month order by year desc,month desc
limit 12
  Content Columns (0.000892)   SHOW FIELDS FROM contents
Rendering plugins/sidebars/archives/content

mysql> select count(*) as count, extract(year from published_at) as
year,extract(month from published_at) as month from contents where
type='Article' and published = 1 and published_at < '2006-07-09 
13:08:47'
group by year,month order by year desc,month desc limit 12
    -> ;
+-------+------+-------+
| count | year | month |
+-------+------+-------+
|     1 | 2006 |     7 |
|     8 | 2006 |     6 |
|     7 | 2006 |     5 |
|     5 | 2006 |     4 |
|    14 | 2006 |     3 |
|     9 | 2006 |     2 |
|     5 | 2006 |     1 |
|     7 | 2005 |    12 |
|     9 | 2005 |    11 |
|     5 | 2005 |    10 |
+-------+------+-------+
10 rows in set (0.03 sec)

Hmm.  It's closer at least :-)

I suspect this will easier to fix, but I'm away from my computer right
now, and it'll be a few hours before I can fix it.


Scott

Two things, my theme overrode plugin/sidebars/archives/content.rhtml. 
So
that is part of the weirdness.  The default archives displays the
aricle_count but the months were still numeric.  I changed
line 23 of archives controller to:
        :name => "#{entry.year} #{Date::MONTHNAMES[entry.month.to_i]}",

I'm not sure if this is consider the best/most efficient way to get the
Month name mapped to the int value.  Should I have used something like
strftime('%m',entry.month.to_i) instead?

Okay, I fixed the monthname problem.  Is anything else still broken?


Scott

Looks good to me Scott!  :-)

Hi guys.
I'm on the latest SVN trunk (1123) and using mysql 5.0.19.

I'm not sure what is going on.. as I just moved hosting providers and
not if it is that or typo.

regards
Ian

ActiveRecord::StatementInvalid (Mysql::Error: Lost connection to
MySQL server during query: UPDATE sessions SET `sessid` =
'6b08673361b2d7cd5780f3593d3e592a', `data` =
'BAh7CDoOcmV0dXJuX3RvMDoJdXNlcm86CVVzZXIGOhBAYXR0cmlidXRlc3sQ
\nIhZub3RpZnlfdmlhX2phYmJlciIGMCIJbmFtZSIQSWFuIEhvbHNtYW4iHW5v
\ndGlmeV93YXRjaF9teV9hcnRpY2xlczAiFW5vdGlmeV92aWFfZW1haWwiBjAi
\nB2lkIgYyIgtqYWJiZXIiFmtyeXRvbkBqYWJiZXIub3JnIhdub3RpZnlfb25f
\nY29tbWVudHMiBjAiDXBhc3N3b3JkIi1hYTU5ZjMzMTUwZTRmMzhkMjM5ZDE0
\nY2MwNjA4NzRmNWE2ZWI3NDQ3Igpsb2dpbiIJaWFuaCIbbm90aWZ5X29uX25l
\nd19hcnRpY2xlcyIGMCIKZW1haWwiACIKZmxhc2hJQzonQWN0aW9uQ29udHJv
\nbGxlcjo6Rmxhc2g6OkZsYXNoSGFzaHsABjoKQHVzZWR7AA==\n', `updated_at` =
'2006-07-11 21:35:40' WHERE id = 64708):
     /vendor/rails/activerecord/lib/active_record/connection_adapters/
abstract_adapter.rb:120:in `log'
     /vendor/rails/activerecord/lib/active_record/connection_adapters/
mysql_adapter.rb:184:in `execute'
     /vendor/rails/activerecord/lib/active_record/connection_adapters/
mysql_adapter.rb:199:in `update'
     /vendor/rails/activerecord/lib/active_record/base.rb:1723:in
`update_without_lock'
     /vendor/rails/activerecord/lib/active_record/locking.rb:33:in
`update_without_callbacks'
     /vendor/rails/activerecord/lib/active_record/callbacks.rb:274:in
`update_without_timestamps'
     /vendor/rails/activerecord/lib/active_record/timestamp.rb:39:in
`update'
     /vendor/rails/activerecord/lib/active_record/base.rb:1718:in
`create_or_update_without_callbacks'
     /vendor/rails/activerecord/lib/active_record/callbacks.rb:249:in
`create_or_update'
     /vendor/rails/activerecord/lib/active_record/base.rb:1392:in
`save_without_validation'
     /vendor/rails/activerecord/lib/active_record/validations.rb:
724:in `save_without_transactions'
     /vendor/rails/activerecord/lib/active_record/transactions.rb:
126:in `save'
     /vendor/rails/activerecord/lib/active_record/connection_adapters/
abstract/database_statements.rb:51:in `transaction'
     /vendor/rails/activerecord/lib/active_record/transactions.rb:
91:in `transaction'
     /vendor/rails/activerecord/lib/active_record/transactions.rb:
118:in `transaction'
     /vendor/rails/activerecord/lib/active_record/transactions.rb:
126:in `save'
     /vendor/rails/actionpack/lib/action_controller/session/
active_record_store.rb:307:in `update'
     /vendor/rails/activerecord/lib/active_record/base.rb:794:in
`silence'
     /vendor/rails/actionpack/lib/action_controller/session/
active_record_store.rb:307:in `update'
     /vendor/rails/actionpack/lib/action_controller/session/
active_record_store.rb:314:in `close'
     /usr/local/lib/ruby/1.8/cgi/session.rb:330:in `close'
     /vendor/rails/actionpack/lib/action_controller/base.rb:984:in
`close_session'
     /vendor/rails/actionpack/lib/action_controller/base.rb:1026:in
`process_cleanup_without_flash'
     /vendor/rails/actionpack/lib/action_controller/flash.rb:147:in
`process_cleanup_without_filters'
     /vendor/rails/actionpack/lib/action_controller/filters.rb:439:in
`process_cleanup_without_session_management_support'
     /vendor/rails/actionpack/lib/action_controller/
session_management.rb:126:in `process_cleanup_without_components'
     /vendor/rails/actionpack/lib/action_controller/components.rb:
182:in `process_cleanup'
     /vendor/rails/actionpack/lib/action_controller/base.rb:385:in
`process_without_filters'
     /vendor/rails/actionpack/lib/action_controller/filters.rb:377:in
`process_without_session_management_support'
     /vendor/rails/actionpack/lib/action_controller/
session_management.rb:117:in `process'
     /vendor/rails/railties/lib/dispatcher.rb:38:in `dispatch'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/lib/mongrel/
rails.rb:73:in `process'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/lib/
mongrel.rb:551:in `process_client'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/lib/
mongrel.rb:550:in `process_client'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/lib/
mongrel.rb:636:in `run'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/lib/
mongrel.rb:636:in `run'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/lib/
mongrel.rb:625:in `run'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/lib/
mongrel.rb:956:in `run'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/lib/
mongrel.rb:955:in `run'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/bin/
mongrel_rails:127:in `run'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/lib/mongrel/
command.rb:199:in `run'
     /usr/local/lib/ruby/gems/1.8/gems/mongrel-0.3.13.3/bin/
mongrel_rails:235
     /usr/local/bin/mongrel_rails:18

Do you have ActiveRecord MySQL timeout setup for mongrel?

http://mongrel.rubyforge.org/faq.html

>From the FAQ:
Q: Mongrel stops working if it's left alone for a long time.

If you find that Mongrel stops working after a long idle time and you're
using MySQL then you're hitting a bug in the MySQL driver that doesn't
properly timeout connections. What happens is the MySQL *server* side of 
the
connection times out and closes, but the MySQL *client* doesn't detect 
this
and just sits there.

What you have to do is set:

ActiveRecord::Base.verification_timeout = 14400

Or to any value that is lower than the MySQL server's
*interactive_timeout*setting. This will make sure that ActiveRecord
checks the connection often
enough to reset the connection.

nope.
where do I put that ?

I would guess in the environment.rb, add the line above: ActionMailer::
Base.default_charset = 'utf-8'

Actually anywhere you put it in there it should get picked up.  You 
might
cross check with the mongrel mailing list to see what they recommend.

-Steve
http://www.stevelongdo.com

Hmm.  Is this something that we should put into Typo by default?


Scott

ok.. I added it,
but I still have the same problem.
I don't think it is a timeout issue, as it happens straight after I
launch mongrel (or WEBBrick).
i'm thinking its a SQL issue.

Which MySQL driver are you using?  The one built into Rails or the
.gem one?  Try swapping for the other one.


Scott

I was using the one built into rails.
I installed the one via gem install and my blog is working again

Thanks Scott & Steve.