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 L. 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

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

This is mysql?

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

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

Hmm. It’s closer at least :slight_smile:

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?

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

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)

Looks good to me Scott! :slight_smile:

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

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:inexecute’
/vendor/rails/activerecord/lib/active_record/connection_adapters/
mysql_adapter.rb:199:in update' /vendor/rails/activerecord/lib/active_record/base.rb:1723:inupdate_without_lock’
/vendor/rails/activerecord/lib/active_record/locking.rb:33:in
update_without_callbacks' /vendor/rails/activerecord/lib/active_record/callbacks.rb:274:inupdate_without_timestamps’
/vendor/rails/activerecord/lib/active_record/timestamp.rb:39:in
update' /vendor/rails/activerecord/lib/active_record/base.rb:1718:increate_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:insave_without_validation’
/vendor/rails/activerecord/lib/active_record/validations.rb:
724:in save_without_transactions' /vendor/rails/activerecord/lib/active_record/transactions.rb: 126:insave’
/vendor/rails/activerecord/lib/active_record/connection_adapters/
abstract/database_statements.rb:51:in transaction' /vendor/rails/activerecord/lib/active_record/transactions.rb: 91:intransaction’
/vendor/rails/activerecord/lib/active_record/transactions.rb:
118:in transaction' /vendor/rails/activerecord/lib/active_record/transactions.rb: 126:insave’
/vendor/rails/actionpack/lib/action_controller/session/
active_record_store.rb:307:in update' /vendor/rails/activerecord/lib/active_record/base.rb:794:insilence’
/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:inclose’
/usr/local/lib/ruby/1.8/cgi/session.rb:330:in close' /vendor/rails/actionpack/lib/action_controller/base.rb:984:inclose_session’
/vendor/rails/actionpack/lib/action_controller/base.rb:1026:in
process_cleanup_without_flash' /vendor/rails/actionpack/lib/action_controller/flash.rb:147:inprocess_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:inprocess_cleanup_without_components’
/vendor/rails/actionpack/lib/action_controller/components.rb:
182:in process_cleanup' /vendor/rails/actionpack/lib/action_controller/base.rb:385:inprocess_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:inprocess’
/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:inprocess’
/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:inprocess_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:inrun’
/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:inrun’
/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:inrun’
/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_timeoutsetting. This will make sure that ActiveRecord
checks the connection often
enough to reset the connection.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs