MySQL hangs on windows. #142

Closed
birkettm opened this Issue Apr 1, 2011 · 51 comments

Projects

None yet
@birkettm
birkettm commented Apr 1, 2011

Hello! I'm trying to use Mysql2 to connect to a MySQL database, I can install the gem like so:

gem install mysql2 -- --with-mysql-lib=c:\mysql\lib --with-mysql-include=c:\mysql\include

However when I do rake db:migrate --trace it hangs at "Execute db:migrate". Logging onto the mysql server and doing a "show processlist;" shows that a connection is opened to the correct database and then the "Sleep" command passed.

Ruby 192
MySQL2 0.2.7

Hopefully I'm doing something really basic wrong as I'm new to all this, but any help would be appreciated...

Thanks.

@Loupi
Loupi commented Apr 9, 2011

I'm having the same issuer here.
Running MySql 5.5, on Win 7 Ultimate x64.
Ruby 1.9.2.
I've built mysql2 gem with MySql x86 libs.
libmysql.dll (x86) is in my ruby bin directory.

I tried with mysql2 0.2.6 and everything works fine.

@growfisher

The same issue for me when using mysql2 0.2.7.
Tested in:

  • Windows 7 64bit + MySQL 5.5 64bit
  • Windows 7 32bit + MySQL 5.1 32bit
  • Windows XP 32bit + MySQL 5.1 32bit
@birkettm

I've ended up using the MySQL gem with an older version of MySQL to get it to work....

@axelarge

I have the same problem.
Windows 7, ruby1.9.2, mysql2-0.2.7.
Surprisingly it works fine with ruby1.8.7 though

@Ritesh-Kumar

Hi all,

I am also facing the rails server hanging issue and many others too who have posted the comments asking for solutions on "http://rorguide.blogspot.com/2011/03/installing-mysql2-gem-on-ruby-192-and.html". Rake task is also hanging with mysql2.

Thanks,

@scanferla

Same problem here.

@aaronchi
aaronchi commented May 5, 2011

Same issue. Confirmed working on Ruby 1.8.7 though

@rdp
Contributor
rdp commented May 12, 2011

reproduced it here, too--appears stuck on select.
reproducible via:

client = Mysql2::Client.new(:host => "localhost", :username => "root")
client.query("create DATABASE yo;")

This commit appears to fix it perhaps:

rdp@7206cfb

Also note that you can build a mysql2 gem from source by using

C:> gem install mysql2 -- --with-mysql-dir=c:\installs\mysql-5.5-11

@aaronchi

Confirmed working on windows with ruby 1.9.2 with the above commit. :) Now someone just needs to patch the repo

@rdp
Contributor
rdp commented May 26, 2011

published the rdp-mysql2 forked gem with this fix if anybody will find it useful.

@luislavena
Collaborator

I'm in the middle of an OS upgrade and stuff but will take care of this.

@luislavena luislavena was assigned May 26, 2011
@Auxx
Auxx commented May 29, 2011

For some reason rdp's git version fails while bundler install. Any progress on merging in brian's repo?

@aaronchi

on windows you still need to build the gem manually by specifying a path to your local mysql install

@brianmario
Owner

awesome thank you!

@ghazel
Collaborator
ghazel commented Jun 8, 2011

I don't think that patch is correct. Can you please explain the rationale?

@luislavena
Collaborator

@ghazel under 1.9, rb_thread_select just hangs, did you try Ruby 1.9.2 on Windows?

@ghazel
Collaborator
ghazel commented Jun 8, 2011

I would expect that it would hang, and I would not expect that the define check from the patch would prevent that. Did it?

@ghazel
Collaborator
ghazel commented Jun 8, 2011

See http://www.ruby-forum.com/topic/869239#975350 for my previous analysis of the situation on 1.9.

@luislavena
Collaborator

So that clearly states that the code in mysql2, for Windows is not compatible with Ruby 1.9. That is correct?

Can this be solve so it works across both versions without depending on the code in ruby-trunk?

@ghazel
Collaborator
ghazel commented Jun 8, 2011

Right, that is my belief from reading the code.

I believe the only way to correctly write this for 1.9 would be to use rb_thread_blocking_region with native select when available. Eventmachine does this properly:
https://github.com/eventmachine/eventmachine/blob/d326bb323aae552f662a2b8db0964691b743ae51/ext/em.cpp#L812

HAVE_TBR is like HAVE_RB_THREAD_BLOCKING_REGION:
https://github.com/eventmachine/eventmachine/blob/d326bb323aae552f662a2b8db0964691b743ae51/ext/extconf.rb#L68

EmSelect is defined as rb_thread_select:
https://github.com/eventmachine/eventmachine/blob/d326bb323aae552f662a2b8db0964691b743ae51/ext/em.h#L25

So the code basically says:

if (ruby_ver == 1.9) {
  rb_thread_blocking_region(select);
} else {
  rb_thread_select();
}
@luislavena
Collaborator

Thank you for the notes. Will take a look to this during the weekend (not enough OSS slots to deal with all).

@luislavena luislavena reopened this Jun 8, 2011
@rdp
Contributor
rdp commented Jun 8, 2011

EM doesn't actually use native select since it requires ruby.h first.
But that doesn't mean the patch is correct I didn't look into it too much. It does prevent the hanging, however.

@ghazel
Collaborator
ghazel commented Jun 8, 2011

I believe EM does use the native select on 1.9. The select function is only re-defined by headers on Windows in Ruby 1.8, to my knowledge. Is there some other place in 1.9 which redefines select?

It is interesting that it prevented the hang. I would appreciate it if someone could look in to how the handle makes it to select() in win32.c

@rdp
Contributor
rdp commented Jun 8, 2011

you may be right I haven't looked into it closely

@ghazel
Collaborator
ghazel commented Jun 9, 2011

I had time to dig in to this, here are my findings:

FD_SET is redefined by include/ruby-1.9.1/ruby/win32.h, which means the fd is not actually being set correctly for rb_thread_select when the CRT fd code is ifdef'd out. Instead, it puts -1 in the fdset since a CRT fd is not found, which rb_w32_select filters out, so it essentially returns immediately. This is why the patch appears to work, but it also means selecting isn't really working and mysql2 will probably eat 100% CPU tight-looping as it waits for readability.

rdp was right that EventMachine fails to call native select(), which ruby goes to great lengths to replace even outside of the headers by providing the symbol to the linker before ws2_32 is linked. Unfortunate, because that will make the fix more difficult. One way would be to grab a pointer to select() from ws2_32.dll directly... Anyone have a better idea?

@rdp
Contributor
rdp commented Jun 9, 2011

Hmm. So why does the current code (without my patch) not work in 1.9 is my question...

@ghazel
Collaborator
ghazel commented Jun 9, 2011

For the reason I stated on ruby-forum. rb_thead_select (and the rest of the 1.9 socket code) calls is_socket, which checks to see if the handle is in the internal socklist. If it's not, it's filtered out and considered a pipe, which does not work. Unfortunately I don't see a way to add the libmysql socket to socklist ourselves.

@brianmario
Owner

Hey guys, just wanted to say thanks a ton for looking into this. Really appreciate it!

@rdp
Contributor
rdp commented Jun 9, 2011

I don't see a way to add the libmysql socket to socklist ourselves.

IO.for_fd maybe? (I've never used it...)

@ghazel
Collaborator
ghazel commented Jun 9, 2011

Doesn't look like it. The only places I see anything inserted into socklist are when a new socket handle is created in win32.c. So, socket, socketpair, and accept. fcntl also inserts, but first checks to make sure it's already in socklist and seems to just change some flags.

@rdp
Contributor
rdp commented Jun 10, 2011

mysqlplus used to use IO.new

https://github.com/oldmoe/mysqlplus/blob/master/lib/mysqlplus.rb#L13
I can't remember who put that in you could ask them about it :)

@ghazel
Collaborator
ghazel commented Jun 10, 2011

I can't get mysqlplus to compile, but I don't see any indication that IO.new would add the socket to the socklist in win32.c. I would imagine mysqlplus has the same behavior of eating 100% CPU while waiting.

@brianmario
Owner

@ghazel this is untested but, what do you think about something along these lines?

diff --git a/ext/mysql2/client.c b/ext/mysql2/client.c
index ca1edee..65010db 100644
--- a/ext/mysql2/client.c
+++ b/ext/mysql2/client.c
@@ -20,6 +20,12 @@ static ID intern_merge, intern_error_number_eql, intern_sql_state_eql;
   mysql_client_wrapper *wrapper; \
   Data_Get_Struct(self, mysql_client_wrapper, wrapper)

+#ifdef _WIN32
+#undef FD_SET
+#undef select
+#endif
+
 /*
  * used to pass all arguments to mysql_real_connect while inside
  * rb_thread_blocking_region
@@ -380,25 +386,13 @@ static VALUE rb_mysql_client_query(int argc, VALUE * argv, VALUE self) {
     for(;;) {
       int fd_set_fd = fd;

-#if defined(_WIN32) && !defined(HAVE_RB_THREAD_BLOCKING_REGION)
-      WSAPROTOCOL_INFO wsa_pi;
-      // dupicate the SOCKET from libmysql
-      int r = WSADuplicateSocket(fd, GetCurrentProcessId(), &wsa_pi);
-      SOCKET s = WSASocket(wsa_pi.iAddressFamily, wsa_pi.iSocketType, wsa_pi.iProtocol, &wsa_pi, 0, 0);
-      // create the CRT fd so ruby can get back to the SOCKET
-      fd_set_fd = _open_osfhandle(s, O_RDWR|O_BINARY);
-#endif
-
       FD_ZERO(&fdset);
       FD_SET(fd_set_fd, &fdset);

+#ifdef _WIN32
+      retval = select(fd_set_fd + 1, &fdset, NULL, NULL, tvp);
+#else
       retval = rb_thread_select(fd_set_fd + 1, &fdset, NULL, NULL, tvp);
-
-#if defined(_WIN32) && !defined(HAVE_RB_THREAD_BLOCKING_REGION)
-      // cleanup the CRT fd
-      _close(fd_set_fd);
-      // cleanup the duplicated SOCKET
-      closesocket(s);
 #endif

       if (retval == 0) {

We'd be sacrificing the use of rb_thread_select on win32 which kinda sucks for 1.8 users, and we possibly need to also wrap the native select call in a rb_thread_blocking_region call on 1.9 but would something like this work?

edit: I removed the #undef FD_ZERO cause I don't think Ruby is changing that at all

@ghazel
Collaborator
ghazel commented Jun 10, 2011

Unfortunately that won't work. Ruby goes to great lengths to replace select(). It actually links to rb_thread_select, so there's no way to call native select. This is awful.

One way around it would be to grab a pointer to select manually:

LPFN_SELECT select_ptr = GetProcAddress(GetModuleHandleA("ws2_32.dll"), "select");
(*select_ptr)(fd_set_fd + 1, &fdset, NULL, NULL, tvp);

Using that on 1.9 instead a rb_thread_blocking_region and using the old rb_thread_select code on 1.8 should work great, even though it's a terrible hack.

@rdp
Contributor
rdp commented Jun 10, 2011

@ghazel maybe ask core? Have you experimented with IO.new?

@ghazel
Collaborator
ghazel commented Jun 10, 2011

I have asked ruby-core before: http://www.ruby-forum.com/topic/869239#975350

I haven't written anything to use IO.new, but I have read the code in ruby 1.9.2 and I don't see any reason it would add the socket to socklist.

@brianmario
Owner

I'm thinking I might just skip the async mysql_send_query stuff entirely for 1.9 on win32 and instead just use the blocking query call wrapped with TBR. I'd rather sacrifice the :async API than have it not work at all.

@brianmario
Owner

This also means that I'll probably have to make Mysql2::Client#socket raise in 1.9 on win32, and the :async => true option will be a noop. This win32 socket stuff is way too ridiculous to support IMO.

@ghazel
Collaborator
ghazel commented Jun 10, 2011

Yeah, ruby made a huge error in the way they tried to implement this - they actually made Windows sockets much more difficult to use than they would be otherwise. It would probably be helpful to the community if you provided this feedback to ruby-core; I don't think they understand how terrible it is.

@brianmario
Owner

I definitely will. Again, really appreciate your help on this.

On Jun 9, 2011, at 11:48 PM, ghazelreply@reply.github.com wrote:

Yeah, ruby made a huge error in the way they tried to implement this - they actually made Windows sockets much more difficult to use than they would be otherwise. It would probably be helpful to the community if you provided this feedback to ruby-core; I don't think they understand how terrible it is.

Reply to this email directly or view it on GitHub:
#142 (comment)

@rdp
Contributor
rdp commented Jun 10, 2011

I think I remember overcoming something like this by (kludgely) forcing -lws2_32 to come first in rev builds: tarcieri/cool.io@1c22e33 IO.dup might maybe help too, just be sure to only call at most once per socket as otherwise mingw builds can hang sometimes: http://redmine.ruby-lang.org/issues/show/373 though I haven't experimented with it recently it might work well now.

@brianmario
Owner

Just wanted to note that the postgres gem doesn't appear to be doing anything fancy with file descriptors coming from libpq: https://github.com/ged/ruby-pg/blob/master/ext/pg.c#L799-812. What's even more interesting is matz is one of the authors...

When I was doing that in mysql2 I kept getting exceptions from ruby saying Invalid File Descriptor or something until I wrapped it as a CRT.

@ghazel
Collaborator
ghazel commented Jun 12, 2011

pg does something nearly identical to the CRT code in mysql2:
https://github.com/ged/ruby-pg/blob/master/ext/pg.c#L2101

because I added it:
ged/ruby-pg@1c3c502

And of course, it suffers from the exact same issues on ruby 1.9 on Windows.

@brianmario
Owner

Oh I missed that part, but was specifically talking about returning the "raw" fd from it's #socket method (like I used to do). If I tried to use that socket in Ruby land with something like IO.for_fd it blew up. I wonder if it works for pg?

@ghazel
Collaborator
ghazel commented Jun 12, 2011

Ah, I didn't know about 83278af. It's depressing that you needed to do that. Probably pg suffers from the same issue if anyone does something with the socket in ruby land.

As a side note, since you already create the CRT in advance it would be possible to remove the creation of another around rb_thread_select, but it doesn't matter much. Also I think since you created but never close the duplicate you have a socket leak, but that also probably doesn't matter much.

@brianmario
Owner

Yeah I never felt that great about that commit... Was trying to think of a clean way to close it but nothing has come to me yet.

Unfortunately I think the best thing to do (for now) is to just disallow any async stuff on win32. I'll do my best to get to that today.

@surfnext

Tried to get it to work with Shoes and Windows 7 64bit (using 32 bit libraries). Doesn't.

http://blog.ideaday.de/max/2012/03/ruby-shoes-and-the-mysql2-gem-on-windows-7/

After reading the comments here, I'm giving up on this. Is there an easier solution than recompiling the gem?

@Auxx
Auxx commented Mar 12, 2012

surfnext, you should have Ruby installed from http://rubyinstaller.org/ with DevKit and also have MySQL sources. Correct the path to your MySQL sources and run:
gem install mysql2 -v 0.2.11 --no-ri --no-rdoc --platform=ruby -- --with-mysql-lib=C:\soft\mysql\lib\opt --with-mysql-include=C:\soft\mysql\include

@surfnext

Thank you for your help, Auxx.

I still have some trouble correctly providing the lib and the include of MySQL - I used XAMPP for Windows, where they are not shipped, if I am not mistaken. I tried obtaining them from MySQL central (at least compatible versions), but I am stuck - DevKit does not find mysql.h.

On the plus side, I now have all my favourite commands from the bash directly in my Windows shell. Now I can type ls -alh :-)

C:\Windows\system32>gem install mysql2 -v 0.2.11 --no-ri --no-rdoc --platform=ruby -- --wi
th-mysql-lib=D:\Resources\xampp\mysql\opt-6.0.2 --with-mysql-include=D:\Resources\xampp\my
sql\source-5.5.21\include
Temporarily enhancing PATH to include DevKit...
Building native extensions. This could take a while...
ERROR: Error installing mysql2:
ERROR: Failed to build gem native extension.

    C:/Ruby193/bin/ruby.exe extconf.rb --with-mysql-lib=D:\Resources\xampp\mysql\opt-6

.0.2 --with-mysql-include=D:\Resources\xampp\mysql\source-5.5.21\include
checking for rb_thread_blocking_region()... yes
checking for main() in -llibmysql... yes
checking for mysql.h... no

checking for mysql/mysql.h... no

mysql.h is missing. please check your installation of mysql and try again.

*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers. Check the mkmf.log file for more
details. You may need configuration options.

Provided configuration options:
--with-opt-dir
--without-opt-dir
--with-opt-include
--without-opt-include=${opt-dir}/include
--with-opt-lib
--without-opt-lib=${opt-dir}/lib
--with-make-prog
--without-make-prog
--srcdir=.
--curdir
--ruby=C:/Ruby193/bin/ruby
--with-mysql-dir
--without-mysql-dir
--with-mysql-include=${mysql-dir}/include
--with-mysql-lib=${mysql-dir}/lib
--with-libmysqllib
--without-libmysqllib

Gem files will remain installed in C:/Ruby193/lib/ruby/gems/1.9.1/gems/mysql2-0.2.11 for i
nspection.
Results logged to C:/Ruby193/lib/ruby/gems/1.9.1/gems/mysql2-0.2.11/ext/mysql2/gem_make.ou
t

@Auxx
Auxx commented Mar 28, 2012

I don't think XAMP has all the headers needed. Just go and download source package from dev.mysql.com and unzip it.

@luislavena
Collaborator

Why not follow the instructions I put on my blog long ago?

http://blog.mmediasys.com/2011/07/07/installing-mysql-on-windows-7-x64-and-using-ruby-with-it/

Use MySQL Connector/C instead, then copy libmysql.dll to Ruby's bin directory.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment