I'm in a situation where I need to make a call to a stored procedure from Rails. I can do it, but it either breaks the MySQL connection, or is a pseudo hack that requires weird changes to the stored procs. Plus the pseudo hack can't return large sets of data.
我正处于需要从Rails调用存储过程的情况。我可以做到这一点,但它要么打破MySQL连接,要么是伪hack,需要对存储过程进行奇怪的更改。加上伪hack无法返回大量数据。
Right now my solution is to use system() and call the mysql command line directly. I'm thinking that a less sad solution would be to open my own MySQL connection independent of Active Record's connection.
现在我的解决方案是使用system()并直接调用mysql命令行。我认为一个不太令人悲伤的解决方案是打开我自己的MySQL连接,而不依赖于Active Record的连接。
I don't know of any reasons why this would be bad. But I also don't know the innards of the MySQL well enough to know it's 100% safe.
我不知道为什么这会是坏事。但我也不清楚MySQL的内部结构是否足以让它知道它是100%安全的。
It would solve my problem neatly, in that with the controller actions that need to call a stored proc would open a fresh database connection, make the call and close it. I might sacrifice some performance, but if it works that's good enough. It also solves the issue of multiple users in the same process (we use mongrel, currently) in edge rails where it's now finally thread safe, as the hack requires two sql queries and I don't think I can guarantee I'm using the same database connection via Active Record.
它可以很好地解决我的问题,因为需要调用存储过程的控制器操作将打开一个新的数据库连接,进行调用并关闭它。我可能会牺牲一些表现,但如果它的效果足够好。它还解决了同一进程中的多个用户的问题(我们目前使用mongrel)它现在最终是线程安全的,因为hack需要两个sql查询,我不认为我可以保证我正在使用通过Active Record进行相同的数据库连接
So, is this a bad idea and/or dangerous?
那么,这是一个坏主意和/或危险吗?
3 个解决方案
#1
Ruby on Rails generally eschews stored procedures, or implementing any other business logic in the database. One might say that you're not following "the Rails way" to be calling a stored proc in the first place.
Ruby on Rails通常会避开存储过程,或者在数据库中实现任何其他业务逻辑。有人可能会说你没有遵循“Rails方式”来首先调用存储过程。
But if you must call the stored proc, IMO opening a second connection from Ruby must be preferable to shelling out with system(). The latter method would open a second connection to MySQL anyway, plus it would incur the overhead of forking a process to run the mysql client.
但是如果你必须调用存储过程,那么从Ruby打开第二个连接的IMO必须比用system()进行外壳更好。后一种方法无论如何都会打开与MySQL的第二个连接,另外它会产生分支进程运行mysql客户端的开销。
You should check out "Enterprise Recipes with Ruby and Rails" by Maik Schmidt. It has a chapter on calling stored procedures from Rails.
您应该查看Maik Schmidt撰写的“使用Ruby和Rails的企业食谱”。它有一章从Rails调用存储过程。
MySQL can handle more than one connection per request, though it will increase the load on the database server. You should open the second connection in a 'lazy' manner, only when you are sure you need it on a given request.
MySQL可以为每个请求处理多个连接,但它会增加数据库服务器上的负载。只有当您确定在给定请求中需要它时,才应以“懒惰”方式打开第二个连接。
Anyway, if performance were important in this application, you wouldn't be using Rails! >:-)
无论如何,如果性能在这个应用程序中很重要,那么你就不会使用Rails了! > :-)
(joking!)
#2
Considering how firmly RoR is intertwined with its own view of dbms usage, you probably should open a second connection to the database for any interaction it doesn't manage for you, just for SoC purposes if nothing else. It sounds from your description like it's the simplest approach as well, which is usually a strong positive sign.
考虑到RoR与其自己的dbms使用视图的紧密联系,您可能应该为数据库打开第二个连接,以便进行任何不为您管理的交互,仅用于SoC目的(如果没有别的话)。它听起来像你的描述,因为它是最简单的方法,这通常是一个强烈的积极迹象。
Applications from other languages (esp. e.g. PHP) open multiple connections regularly (which doesn't make it desirable, but at least it demonstrates that mysql won't object.)
来自其他语言的应用程序(尤其是PHP)定期打开多个连接(这不是理想的,但至少它证明了mysql不会反对。)
#3
We've since tried the latest mysql gem from github and even that doesn't solve the problem.
我们从github尝试了最新的mysql gem,甚至没有解决问题。
We've patched the mysql adapter in Rails and that actually does work. All it does is make sure the MySQL connection has no more results before continuing on.
我们在Rails中修补了mysql适配器,实际上它确实有效。它所做的就是确保MySQL连接没有更多结果,然后再继续。
I'm not accepting this answer, yet, because I don't feel 100% that the fix is a good one. We haven't done quite enough testing. But I wanted to put it out there for anyone else looking at this question.
我不接受这个答案,但是,因为我不觉得100%的修复是好的。我们还没有做足够的测试。但是我想把它放在那里看其他人看这个问题。
#1
Ruby on Rails generally eschews stored procedures, or implementing any other business logic in the database. One might say that you're not following "the Rails way" to be calling a stored proc in the first place.
Ruby on Rails通常会避开存储过程,或者在数据库中实现任何其他业务逻辑。有人可能会说你没有遵循“Rails方式”来首先调用存储过程。
But if you must call the stored proc, IMO opening a second connection from Ruby must be preferable to shelling out with system(). The latter method would open a second connection to MySQL anyway, plus it would incur the overhead of forking a process to run the mysql client.
但是如果你必须调用存储过程,那么从Ruby打开第二个连接的IMO必须比用system()进行外壳更好。后一种方法无论如何都会打开与MySQL的第二个连接,另外它会产生分支进程运行mysql客户端的开销。
You should check out "Enterprise Recipes with Ruby and Rails" by Maik Schmidt. It has a chapter on calling stored procedures from Rails.
您应该查看Maik Schmidt撰写的“使用Ruby和Rails的企业食谱”。它有一章从Rails调用存储过程。
MySQL can handle more than one connection per request, though it will increase the load on the database server. You should open the second connection in a 'lazy' manner, only when you are sure you need it on a given request.
MySQL可以为每个请求处理多个连接,但它会增加数据库服务器上的负载。只有当您确定在给定请求中需要它时,才应以“懒惰”方式打开第二个连接。
Anyway, if performance were important in this application, you wouldn't be using Rails! >:-)
无论如何,如果性能在这个应用程序中很重要,那么你就不会使用Rails了! > :-)
(joking!)
#2
Considering how firmly RoR is intertwined with its own view of dbms usage, you probably should open a second connection to the database for any interaction it doesn't manage for you, just for SoC purposes if nothing else. It sounds from your description like it's the simplest approach as well, which is usually a strong positive sign.
考虑到RoR与其自己的dbms使用视图的紧密联系,您可能应该为数据库打开第二个连接,以便进行任何不为您管理的交互,仅用于SoC目的(如果没有别的话)。它听起来像你的描述,因为它是最简单的方法,这通常是一个强烈的积极迹象。
Applications from other languages (esp. e.g. PHP) open multiple connections regularly (which doesn't make it desirable, but at least it demonstrates that mysql won't object.)
来自其他语言的应用程序(尤其是PHP)定期打开多个连接(这不是理想的,但至少它证明了mysql不会反对。)
#3
We've since tried the latest mysql gem from github and even that doesn't solve the problem.
我们从github尝试了最新的mysql gem,甚至没有解决问题。
We've patched the mysql adapter in Rails and that actually does work. All it does is make sure the MySQL connection has no more results before continuing on.
我们在Rails中修补了mysql适配器,实际上它确实有效。它所做的就是确保MySQL连接没有更多结果,然后再继续。
I'm not accepting this answer, yet, because I don't feel 100% that the fix is a good one. We haven't done quite enough testing. But I wanted to put it out there for anyone else looking at this question.
我不接受这个答案,但是,因为我不觉得100%的修复是好的。我们还没有做足够的测试。但是我想把它放在那里看其他人看这个问题。