MYSQL not getting disconnected (at consumer side)

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

MYSQL not getting disconnected (at consumer side)

reddylast
This post was updated on .
Hi,

I've recently started to work with RabbitMQ. I'm using rabbitmq to fulfill cronjob like mechanism. I have a jobs table (in mysql) which have fields : id, status, category, start_time and end_time. The producer script reads all the records from jobs table which have status='queued' and then push them to multiple rabbitmq queues (according to the category). At consumer site my script read those jobs from the rabbitmq queues and do some processing and updates status='in-progress' and start_time=CURRENT_TIME() in DB. Once consumer script finish all processing then jobs table is updated with status='completed', end_time=CURRENT_TIME and also sends acknowledgement.

So far everything works fine, but, one thing I've noticed is that consumer script never kills mysql threads/processes, even if I close mysql connection after running each query. In this way it generates  "too many connections" mysql error.

Any Idea, where exactly the problem exits ?

Thanks
Reddy
Reply | Threaded
Open this post in threaded view
|

Re: MYSQL not getting disconnected Consumer

Tim Watson-6
Hi,

On 20 Mar 2013, at 19:39, reddylast wrote:
> So far everything works fine, but, one thing I've noticed is that consumer
> script never kills mysql threads/processes, even if I close mysql connection
> after executing each query. In this way it generate "too many connections"
> error.
>
> In Idea ? where is the exact problem exits ?
>

I can hazard a guess that the problem is in the consumer script and has something to do with not releasing MySQL connections properly. I can't imagine that you'll find what you're after on the RabbitMQ mailing list though - I'd suggest trying the MySQL support forums or Stack Overflow instead.

Best of luck!

Cheers,
Tim
_______________________________________________
rabbitmq-discuss mailing list
[hidden email]
https://lists.rabbitmq.com/cgi-bin/mailman/listinfo/rabbitmq-discuss
Reply | Threaded
Open this post in threaded view
|

Re: MYSQL not getting disconnected Consumer

reddylast
This post was updated on .
Hi Tim,

Thanks for your reply.

I posted this issues also on MySQL support forums and Stack Overflow, but, didn’t get any solution. Please see the sample code of consumer script, hope this will help you to get an idea on this issue :

$connection = new AMQPConnection(HOST, PORT, USER, PASS, VHOST);
$channel = $connection->channel();
$channel->queue_declare($queue, false, true, false, false);
$channel->exchange_declare('myexchange', 'direct', true, true, false);
$channel->queue_bind('myqueue', $exchange, $queue);
$callback = function($msg, $db) {

        $data = json_decode($msg->body, true); // on producer script I've send data in json format
        $db->mysqlConnect();
        $db->mysqlQuery("update jobstable set status='in-progress' where id=".$data['id']);
        $db->mysqlDisconnect();

        processData($data); // this will process data

        $db->mysqlConnect();
        $db->mysqlQuery("update jobstable set status='complete' where id=".$data['id']);
        $db->mysqlDisconnect();

};

$channel->basic_qos(null, 1, null);
$channel->basic_consume('myqueue', 'myConsumerTag', false, false, false, false, $callback);

while (count($channel->callbacks)) {
        $channel->wait();
}

$channel->close();
$connection->close();



function processData(){
        /* I'm also running some mysql queries. */
}



Thanks,
Reddy










 
Reply | Threaded
Open this post in threaded view
|

Re: MYSQL not getting disconnected Consumer

Simon MacMullen-2
On 25/03/13 13:15, reddylast wrote:
> I posted this issues also on MySQL support forums and Stack Overflow, but,
> didn’t get any solution.

I'm sorry you didn't get a response elsewhere, but this is the RabbitMQ
list, and you have a MySQL problem. We try to be helpful, really, but we
can't help you.

Cheers, Simon

--
Simon MacMullen
RabbitMQ, VMware
_______________________________________________
rabbitmq-discuss mailing list
[hidden email]
https://lists.rabbitmq.com/cgi-bin/mailman/listinfo/rabbitmq-discuss
Reply | Threaded
Open this post in threaded view
|

Re: MYSQL not getting disconnected Consumer

Tim Watson-6
In reply to this post by reddylast
Hi,

On 25 Mar 2013, at 13:15, reddylast wrote:
> Thanks for you reply.
>

You're welcome.

> I posted this issues also on MySQL support forums and Stack Overflow, but,
> didn’t get any solution. Please see the samle code of consumer script which
> have right now, hope this will help you to get an idea on this issue :
>

Perhaps I wasn't clear. I advised you to try out the MySQL forums and SO because those are the right places for you to get help with MySQL issues. The RabbitMQ mailing list is not the right place to address this problem. If there was something going on with your RabbitMQ connection/queue/channel/etc then this *would* be a good place to come and get help - and we'd be happy to give it! - but your issue is afaict with MySQL connection handling/cleanup and that is nothing to do with RabbitMQ, ergo this is not the right mailing list to ask for help, hence my suggestion to go somewhere more appropriate.

If you've not heard back from the support forums or SO then I can only suggest that you either continue to wait for a reply, or poke the question(s) again and ask if anyone's able to help. You may find that rephrasing your question or adding additional details helps - the canonical advise given to those seeking help on the internet is http://www.catb.org/esr/faqs/smart-questions.html#intro and is vital reading if you're not already familiar with it.

> $db->mysqlConnect();
> $db->mysqlQuery(...);
> $db->mysqlDisconnect();
>
> processData($data); // this will process data
>
> $db->mysqlConnect();
> $db->mysqlQuery(...);
> $db->mysqlDisconnect();
>
> };

That section of code looks rather suspicious to me - but I know next to nothing about MySQL. I do *not* want to discourage you from asking for help here, because when it comes to questions about RabbitMQ, we're always happy to assist users both on this mailing list and on the #rabbitmq IRC channel on freenode. But if your question has nothing to do with RabbitMQ, this is not the right forum. If you suspect that RabbitMQ is at fault, then why not rewrite the script to execute the MySQL code in a tight loop using some pre-populated array of test json data and take a look if the connection cleanup is still failing without RabbitMQ being involved? If so, then you know it's nothing to do with rabbit. If the problem goes away, then it's possible that some aspect of the client library you're using (such as, for example, running callbacks in different threads or error handling mechanisms) is interacting with the MySQL code in an unhelpful manner. If that (latter situation) can be proved, then you might try contacting the author of your rabbitmq client library and asking them for help. That *is* something you can use this list for, although there's no guarantee that all client library authors follow this list, and mailing the author directly or using the project issue tracker (for the client library) might be a better approach.

HTH - as I said, *please* don't be discouraged from approaching this list for help, once you're certain that your issue actually has something to do with RabbitMQ (rather than MySQL or some other unrelated technology).

Cheers,
Tim


_______________________________________________
rabbitmq-discuss mailing list
[hidden email]
https://lists.rabbitmq.com/cgi-bin/mailman/listinfo/rabbitmq-discuss