MySQL reconnect support

[sonots]

With #17, focuslight now uses Sequel, but still remains some issues for mysql support.

Currently, focuslight keeps connection to MySQL in a process. However, it does not reconnect gets connection error once, then reconnect when the connection is killed by some reasons such as mysql restart. So, it requires to restart focuslight itself.

I made sure how GrowthForecast works. One option is to follow the GrowthForecast way.

SQLite: keep to open
MySQL: connect everytime on each request

[sonots]

@sonots says

And, Let me write additional notes here: GrowthForecast(Plack) respawns child processes after seving 100 requests as default. So, Even if it opens files everytime it will not address big issues. I think this is nice mechanism, in terms of reducing memory issues too, and this can be done using unicorn-worker-killer gem

[sonots]

@koichiro says

Supporting Sequel's native connection pool would be better.
http://rdoc.info/github/evanfarrar/opensprints/Sequel/ConnectionPool

And, Using ConnectionValidator extension would enable reconnecting when database gets down.
https://raw.github.com/jeremyevans/sequel/master/lib/sequel/extensions/connection_validator.rb

[sonots]

@tagomoris @sorah Any opinions? > keep connection

[tagomoris]

Sequel::ConnectionPool and Sequel::ConnectionValidator look good. I think that we should not solve such problems in focuslight code (for more databases that anyone wants).

[sonots]

It seems Sequel::ConnectionPool is not necessary because we are not using threads internaly.

I will check ConnectionValidator, but it looks it just checks connection every 1 hour. So, it would not be suitable for our purpose because what we want to do is to reconnect everytime when disconnection is detected.

the validation checks are only run if the connection has
been idle for longer than a certain threshold. By default,
that threshold is 3600 seconds (1 hour), but it can be

[sonots]

I examined ConnectionValidator.

1. The default behavior (without connection validator)

We get InternalServerError once. At 2nd request, the connection is recovered.

140223 13:02:52     2 Connect   root@localhost on focuslight
                    2 Query     SET @@wait_timeout = 2147483
                    2 Query     SET SQL_AUTO_IS_NULL=0
                    2 Query     SELECT * FROM `graphs` ORDER BY `service_name`
                    2 Query     SELECT * FROM `complex_graphs` ORDER BY `service_name`
/usr/libexec/mysqld, Version: 5.5.33-log (MySQL Community Server (GPL) by Remi). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Here, we get InternalServerError once
140223 13:03:02     1 Connect   UNKNOWN_MYSQL_USER@localhost as  on
                    1 Quit
# Then, recovers
140223 13:03:11     2 Connect   root@localhost on focuslight
                    2 Query     SET @@wait_timeout = 2147483
                    2 Query     SET SQL_AUTO_IS_NULL=0
                    2 Query     SELECT * FROM `graphs` ORDER BY `service_name`
                    2 Query     SELECT * FROM `complex_graphs` ORDER BY `service_name`

2. Connection validator with timeout = -1 (which means check everytime)

DB.extension(:connection_validator)
DB.pool.connection_validation_timeout = -1

This issues queries like below and looks stupid, SELECT NULL.

140223 13:01:05     4 Query     SELECT NULL
                    4 Query     SELECT * FROM `graphs` ORDER BY `service_name`
                    4 Query     SELECT NULL
                    4 Query     SELECT * FROM `complex_graphs` ORDER BY `service_name`
140223 13:01:07     4 Query     SELECT NULL
                    4 Query     SELECT * FROM `graphs` ORDER BY `service_name`
                    4 Query     SELECT NULL
                    4 Query     SELECT * FROM `complex_graphs` ORDER BY `service_name`

3. Connection validator with timeout = 20

If we wait 20 seconds, the connection is recovered without seeing IntervalServerError.
SELECT NULL query is issued if 20 seconds passes. It looks dirty for me.

140223 13:10:17     2 Connect   root@localhost on focuslight
                    2 Query     SET @@wait_timeout = 2147483
                    2 Query     SET SQL_AUTO_IS_NULL=0
                    2 Query     SELECT * FROM `graphs` ORDER BY `service_name`
                    2 Query     SELECT * FROM `complex_graphs` ORDER BY `service_name`
140223 13:10:22     2 Query     SELECT * FROM `graphs` ORDER BY `service_name`
                    2 Query     SELECT * FROM `complex_graphs` ORDER BY `service_name`
/usr/libexec/mysqld, Version: 5.5.33-log (MySQL Community Server (GPL) by Remi). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
140223 13:10:31     1 Connect   UNKNOWN_MYSQL_USER@localhost as  on
                    1 Quit
140223 13:10:38     2 Connect   root@localhost on focuslight
                    2 Query     SET @@wait_timeout = 2147483
                    2 Query     SET SQL_AUTO_IS_NULL=0
                    2 Query     SELECT * FROM `graphs` ORDER BY `service_name`
                    2 Query     SELECT * FROM `complex_graphs` ORDER BY `service_name`

After all, I do not think ConnectionValidator is useful.