MySQL - Server has gone away while long Polling

Question

I have long poll based chat app. Server looks like this:

<?php
header('Content-Type: application/json');

if (!isset($_GET['lastId'])) {
    exit(json_encode(array('status' => 'error_lastIdNotSet')));
}

//DIBI LIBRARY
//insert dibi lib code here, no space for it
//more about it here: http://www.dibiphp.com/cs/quick-start

$lastDownloadedId = intval($_GET['lastId']);

$counter = 290;
while ($counter > 0) {
    if ($lastDownloadedId !== intval(file_get_contents('./lastMessageId.txt'))) {

        $arr_output = array(
            'status' => 'new',
            'messages' => array()
        );

        if ($_SERVER['REMOTE_ADDR'] == '127.0.0.1') {
            dibi::connect(array(
                'driver'   => 'mysql',
                'host'     => 'localhost',
                'username' => 'root',
                'password' => '',
                'database' => 'chat',
                'charset'  => 'utf8',
            ));
        } else {
            dibi::connect(array(
                'driver'   => 'mysql',
                'host'     => 'mysql.moxo.cz',
                'username' => 'u570204589_blog',
                'password' => '*************',
                'database' => 'u570204589_blog',
                'charset'  => 'utf8',
            ));
        }
        dibi::query('SET NAMES utf8');

        foreach(dibi::query('SELECT `id`, `time`, `unsafe_from`, `unsafe_messageText` FROM `messages` WHERE id>' . $lastDownloadedId)->fetchAll() as $row) {
            $arr_output['messages'][] = array(
                'id' => $row->id,
                'time' => $row->time->format('U'),
                'from' => $row->unsafe_from,
                'messageText' => $row->unsafe_messageText
            );
        }
        exit(json_encode($arr_output));
    }
    $counter--;
    usleep(100000);
}

echo json_encode(array('status' => 'timeout'));

If new message is sent in first like 10 seconds after opening connection, everything is OK, but when it gets sent later, Mysql server has gone away error gets fired instead.

Script is currently at http://anagmate.moxo.cz/projectsDir/chat/longPollServer.php. For it to work, ?lastId= and last message id must be appended (can be found at http://anagmate.moxo.cz/projectsDir/chat/lastMessageId.txt). Then you can try writing some message to http://anagmate.moxo.cz/projectsDir/chat and looking back to response.

Where could be the problem?

Error looks like this:

Fatal error: Uncaught exception 'DibiDriverException' with message 'MySQL server has gone away' in /home/u570204589/public_html/projectsDir/chat/longPollServer.php:9
Stack trace:
#0 /home/u570204589/public_html/projectsDir/chat/longPollServer.php(9): DibiMySqlDriver->query('SELECT `id`, `t...')
#1 /home/u570204589/public_html/projectsDir/chat/longPollServer.php(9): DibiConnection->nativeQuery('SELECT `id`, `t...')
#2 /home/u570204589/public_html/projectsDir/chat/longPollServer.php(9): DibiConnection->query(Array)
#3 /home/u570204589/public_html/projectsDir/chat/longPollServer.php(42): dibi::query('SELECT `id`, `t...')
#4 {main}
SQL: SELECT `id`, `time`, `unsafe_from`, `unsafe_messageText` FROM `messages` WHERE id>55
  thrown in /home/u570204589/public_html/projectsDir/chat/longPollServer.php on line 9

http://stackoverflow.com/questions/1644432/mysql-server-has-gone-away-in-exactly-60-seconds — ɹɐqʞɐ zoɹǝɟ, Apr 15 '14 at 17:39
if the server is going to be idle for 10 seconds, there is no need to keep the database connection for that long. You could close it and reconnect in your loop. — Ibu, Apr 15 '14 at 17:42
Server Gone away is common error, in case either server connection is close, or server crash itself. You should immediately close connection. If connection timeout error will be different, it seems your server is crashing from my experience. — Sumit Gupta, Apr 15 '14 at 17:50
Thats how it works... I use dibi::connect after I know that there is new message, just before the query and then I immediately end the script and close connection... — onlyMe, Apr 15 '14 at 17:51

score 0 · Answer 1 · answered Apr 15 '14 at 19:15

foreach(dibi::query('SELECT `id`, `time`, `unsafe_from`, `unsafe_messageText` FROM `messages` WHERE id>' . $lastDownloadedId)->fetchAll() as $row) {
        $arr_output['messages'][] = array(
            'id' => $row->id,
            'time' => $row->time->format('U'),
            'from' => $row->unsafe_from,
            'messageText' => $row->unsafe_messageText
        );
    }

Emm, am I right understand dibi::query('SELECT id, time, unsafe_from, unsafe_messageText FROM messages WHERE id>' . $lastDownloadedId)->fetchAll() mean you do new request to database on each iteration?

Can you share your dibi class?

Try to use:

$result = dibi::query('SELECT `id`, `time`, `unsafe_from`, `unsafe_messageText` FROM `messages` WHERE id>' . $lastDownloadedId)->fetchAll();
foreach($result as $row) {
            $arr_output['messages'][] = array(
                'id' => $row->id,
                'time' => $row->time->format('U'),
                'from' => $row->unsafe_from,
                'messageText' => $row->unsafe_messageText
            );
        }

score 0 · Answer 2 · answered Apr 15 '14 at 19:56

0

The problem was in opening mysql connection at the start of file, but using it later. I fixed it on localhost, but forgot to upload it to server.

answered Apr 15 '14 at 19:56

onlyMe

127
9

MySQL - Server has gone away while long Polling

2 Answers2