From: michael libby <x@...>
Date: 2002-10-26T10:06:43+09:00
Subject: Re: problem with threading mysql queries in ruby

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Friday 25 October 2002 16:48, stan wrote:
> It seems that once a thread containing a mysql query gets some cpu time
> it won't let go until the query is finished. 

In the database work I've done, the client locks up while the query is 
running, so you've got to fork before that call (and I don't know if it 
can be fixed with Ruby and the mysql module, but threading doesn't seem to 
be enough with the different ways I tried-- fork does work, though)... so 
I wouldn't expect anything back from that process until the query is done. 
So goes life.

> So does the mysql module just not play nice with threads, or is there
> something I'm doing wrong here? Any suggestions for alternative ways of
> getting periodic feedback on the status of a long mysql query?

I can't think of anything you're doing wrong. Short of logging into MySQL 
as an admin and checking the running processes, you can't really ask a 
query how it's doing. But here's one possible solution as a way to get you 
started: (and I've probably got this backwards, you may want to fork the 
query itself and have it send the results back via socket or temp file)

<code>
#!/usr/bin/ruby -w
require 'mysql'
@dbConn = Mysql.new("host", "user", "pass", "datbase")

def threadTest()
  timer = fork {
    while (1)
      puts "ticktock = " + Time.now.to_s
      sleep 1
    end
  }
  puts "Timer PID is " + timer.to_s

  call = Thread.new {
    puts "sleeping"
    sleep 3
    puts "done sleeping"
    @connRes = @dbConn.query("select * from table;")
    puts "query finished"
    sleep 2
    puts "all done"
  }

  while (1)
    break if call.status == false
  end
  call.join

  puts "cleaning up timer"
  system("kill #{timer}")
  puts "timer cleaned. waiting."
  Process.wait

  puts "done waiting, exit threadTest"
end

threadTest()
</code>

<output>
ichimunki@greyhound:~/ruby-misc$ ruby mysql-threads.rb 
Timer PID is 7641
sleeping
ticktock = Fri Oct 25 19:53:56 CDT 2002
ticktock = Fri Oct 25 19:53:57 CDT 2002
ticktock = Fri Oct 25 19:53:58 CDT 2002
done sleeping
ticktock = Fri Oct 25 19:53:59 CDT 2002
ticktock = Fri Oct 25 19:54:00 CDT 2002
ticktock = Fri Oct 25 19:54:01 CDT 2002
ticktock = Fri Oct 25 19:54:02 CDT 2002
ticktock = Fri Oct 25 19:54:03 CDT 2002
ticktock = Fri Oct 25 19:54:04 CDT 2002
ticktock = Fri Oct 25 19:54:05 CDT 2002
ticktock = Fri Oct 25 19:54:06 CDT 2002
query finished
ticktock = Fri Oct 25 19:54:07 CDT 2002
ticktock = Fri Oct 25 19:54:08 CDT 2002
all done
cleaning up timer
timer cleaned. waiting.
done waiting, exit threadTest
ichimunki@greyhound:~/ruby-misc$ 
</output>

 -michael
 
<signature>
  <name>Michael C. Libby</name>
  <email>x@ichimunki.com</email>
  <web-site>http://www.ichimunki.com/</web-site>
  <public-key>http://www.ichimunki.com/public_key.txt</public-key>
</signature>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE9uepz4ClW9KMwqnMRAsQNAJsHxCa/xyh9HljnuiCUst3xSeRXvgCgm7nY
J/B3RrKFd4GBIJOThYAysAA=
=nDCB
-----END PGP SIGNATURE-----