Rails如何构建MySQL语句？ [英] How does Rails build a MySQL statement?

问题描述

我有以下代码在间歇失败的控制器内的heroku上运行。这是一个不容错过的，它应该适用于我，但我必须失去一些东西。

@artist = Artist.find（params [：

$ p




$ b $ p $参数散列如下所示：

{utf8=> ，
authenticity_token=>XXXXXXXXXXXXXXX，
password=>[FILTERED] ，
commit=>下载，
动作=>显示，
控制器=>专辑，
artist_id =>62，
id=>157}


我得到的错误如下所示：

  ActiveRecord :: StatementInvalid：Mysql :: Error :: SELECT`artists`。* FROM`artists` where'artists`.`id` =？限制1 
  

注意 WHERE 艺术家。 id =？声明的一部分？它试图找到问号的ID。含义Rails没有传入 params [：artist_id] ，这显然在params散列中。我在完全损失。

在试图以类似方式选择记录的不同页面上出现相同的错误。

我的环境：Heroku上的Cedar Stack（这个仅发生在Heroku上），Ruby 1.9.3，Rails 3.2.8，托管在Amazon S3上的文件（尽管我怀疑它很重要） ，使用 mysql gem（不是 mysql2 ，根本不起作用），ClearDB MySQL数据库。 p>

以下是完整跟踪。

任何帮助都会被极大地赞赏。

试试 sql？

如果仅仅是这一条语句，并且导致了生产问题，那么现在可以省略查询生成器吗？换句话说，就短期而言，只需自己编写SQL即可。

 ＃全部在一行上：
 Artist.find_by_sql 
 SELECT`artists`。* FROM`artists` 
 WHERE`artists`.`id` =＃{params [：artist_id] .to_i} LIMIT 1
  pre> 
 
 
 ARel / MySQL解释？
 
 
 
 Rails可以帮助解释MySQL正在尝试做什么：
  Artist.find（params [：artist_id]）。explain 
 
 http://weblog.rubyonrails.org / 2011/12/6 / what-s-new-in-edge-rails-explain / 
  
也许你可以发现成功与失败的查询之间存在某种差异，例如 explain 使用索引或优化。
 
 
 
 mysql2 gem？
 
 
 
你可以尝试从mysql gem更改为mysql2 gem吗？当你切换到mysql2 gem时会遇到什么故障？
 
 
波动率
 
 
 
也许还有别的改变params在运行时散列，所以当你打印它时你会看到它，但是它在查询运行时改变了吗？
 
 
 
尽快为您分配变量params：
 
 
  artist_id = params [：artist_id] 
 ...这里的任何代码... 
 @artist = Artist.find（artist_id）
  
 
 
 
不是params哈希？
 
 
 
你写了意义Rails没有传入params [：artist_id]，这显然在params散列中。我不认为这是问题 - 我希望你看到这个，因为Rails使用？作为准备好的语句的占位符。
 
 
 
为了弄清楚，运行@Mori建议的命令并比较它们;他们应该是相同的。
  Article.find（42）.to_sql 
 Article.find（params [：artist_id ]）。to_sql 
  
 
 
 
准备好的语句？
 
 
 
可能是一个准备好的语句缓存问题，当查询被实际执行时。
 
 
 
这是失败的代码 - 并且有一个很大的警告。
 
 $ b $ 
  begin 
 stmt.execute（* binds.map {| col，val | type_cast（val，col）}）
救援Mysql :: Error => e 
＃老版本的MySQL在发生错误时将准备好的语句放在错误的
＃位置。为了支持较老的mysql版本，我们
＃需要关闭语句并从
＃缓存中删除语句。 
 stmt.close 
 @ statements.delete sql 
 raise e 
 end 
  
尝试配置您的数据库以关闭预准备语句，以查看是否有所作为。
 
 
 
在 ./ config /database.yml  file：
 制作：
适配器：mysql 
 prepared_statements：false 
 ... 
  
 
 
 
准备好的语句错误？
 
 
 
 Rails忽略此设置可能存在问题。如果您想了解更多信息，请参阅Jeremey Cole和Aaron的讨论和错误修复： https://github.com/rails/rails/pull/7042  
 
 
 
 Heroku可能会忽略该设置。您可以通过修改prepared_statements设置来尝试覆盖Heroku： https://github.com/rails / rails / issues / 5297  
 
 
 
删除查询缓存？
 
 
 
尝试删除ActiveRecord QueryCache看看是否有所作为：
  config.middleware.delete ActiveRecord :: QueryCache 
 
 http ：//edgeguides.rubyonrails.org/configuring.html#configuring-middle 
  
 
 
 
尝试postgres？
 
 
 
如果您可以尝试Postgres，那也可以清除它。这对你来说可能不是一个长期的解决方案，但它会将问题隔离到MySQL。
 
I have the following code that run on heroku inside a controller that intermittently fails. It's a no-brainer that it should work to me, but I must be missing something.

@artist = Artist.find(params[:artist_id])

The parameters hash looks like this:
{"utf8"=>"������",
 "authenticity_token"=>"XXXXXXXXXXXXXXX",
 "password"=>"[FILTERED]",
 "commit"=>"Download",
 "action"=>"show",
 "controller"=>"albums",
 "artist_id"=>"62",
 "id"=>"157"}
The error I get looks like this:
ActiveRecord::StatementInvalid: Mysql::Error: : SELECT `artists`.* FROM `artists` WHERE `artists`.`id` = ? LIMIT 1
notice the WHEREartists.id= ? part of the statement? It's trying to find an ID of QUESTION MARK. Meaning Rails is not passing in the params[:artist_id] which is obviously in the params hash. I'm at complete loss.

I get the same error on different pages trying to select the record in a similar fashion.

My environment: Cedar Stack on Heroku (this only happens on Heroku), Ruby 1.9.3, Rails 3.2.8, files being hosted on Amazon S3 (though I doubt it matters), using the mysql gem (not mysql2, which doesn't work at all), ClearDB MySQL database.

Here's the full trace.

Any help would be tremendously appreciated.
解决方案
try sql?

If it's just this one statement, and it's causing production problems, can you omit the query generator just for now? In other words, for very short term, just write the SQL yourself. This will buy you a bit of time.
# All on one line:
Artist.find_by_sql
  "SELECT `artists`.* FROM `artists` 
   WHERE `artists`.`id` = #{params[:artist_id].to_i} LIMIT 1"


ARel/MySQL explain?

Rails can help explain what MySQL is trying to do:
Artist.find(params[:artist_id]).explain

http://weblog.rubyonrails.org/2011/12/6/what-s-new-in-edge-rails-explain/
Perhaps you can discover some kind of difference between the queries that are succeeding vs. failing, such as how the explain uses indexes or optimizations.

mysql2 gem?

Can you try changing from the mysql gem to the mysql2 gem? What failure do you get when you switch to the mysql2 gem?

volatility?

Perhaps there's something else changing the params hash on the fly, so you see it when you print it, but it's changed by the time the query runs?

Try assigning the variable as soon as you receive the params:
artist_id = params[:artist_id]
... whatever code here...
@artist = Artist.find(artist_id)


not the params hash?

You wrote "Meaning Rails is not passing in the params[:artist_id] which is obviously in the params hash."  I don't think that's the problem-- I expect that you're seeing this because Rails is using the "?" as a placeholder for a prepared statement.

To find out, run the commands suggested by @Mori and compare them; they should be the same.
Article.find(42).to_sql
Article.find(params[:artist_id]).to_sql


prepared statements?

Could be a prepared statement cache problem, when the query is actually executed.

Here's the code that is failing-- and there's a big fat warning.
begin
  stmt.execute(*binds.map { |col, val| type_cast(val, col) })
rescue Mysql::Error => e
  # Older versions of MySQL leave the prepared statement in a bad
  # place when an error occurs. To support older mysql versions, we
  # need to close the statement and delete the statement from the
  # cache.
  stmt.close
  @statements.delete sql
  raise e
end
Try configuring your database to turn off prepared statements, to see if that makes a difference.

In your ./config/database.yml file:
production:
   adapter: mysql
   prepared_statements: false
   ...


bugs with prepared statements?

There may be a problem with Rails ignoring this setting. If you want to know a lot more about it, see this discussion and bug fix by Jeremey Cole and Aaron: https://github.com/rails/rails/pull/7042

Heroku may ignore the setting. Here's a way you can try overriding Heroku by patching the prepared_statements setup: https://github.com/rails/rails/issues/5297

remove the query cache?

Try removing the ActiveRecord QueryCache to see if that makes a difference:
config.middleware.delete ActiveRecord::QueryCache

http://edgeguides.rubyonrails.org/configuring.html#configuring-middle


try postgres?

If you can try Postgres, that could clear it up too. That may not be a long term solution for you, but it would isolate the problem to MySQL.

                        
这篇关于Rails如何构建MySQL语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！