MySQL hangs on windows.

[ghost]

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]

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

[ghost]

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]

Same issue. Confirmed working on Ruby 1.8.7 though

[rdp]

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:

https://github.com/rdp/mysql2/commit/7206cfb1cce

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]

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

[luislavena]

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

[Auxx]

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]

awesome thank you!

[ghazel]

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

[luislavena]

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

[ghazel]

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]

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

[luislavena]

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]

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]

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

[rdp]

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]

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]

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

[ghazel]

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]

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

[ghazel]

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]

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

[rdp]

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

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

[ghazel]

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]

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]

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]

@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]

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]

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

[ghazel]

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]

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]

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]

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]

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: https://github.com/brianmario/mysql2/issues/142#comment_1340103

[rdp]

I think I remember overcoming something like this by (kludgely) forcing -lws2_32 to come first in rev builds: https://github.com/tarcieri/cool.io/commit/1c22e33b3ff9119e0cbd859280f5cb11e9b1db6e 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]

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]

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: https://github.com/ged/ruby-pg/commit/1c3c5028f12da82b040d19bd13288f2e50d0b128

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

[brianmario]

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]

Ah, I didn't know about 83278afe56d666d45d0289b98cf3543d99ccfa06. 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]

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]

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]

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