Tuesday, July 29, 2008

why are there empty strings in my not null column?

So you're looking at your database and wondering wtf are those default values doing in there?
mysql> create table foo(title char(10) not null);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into foo(title) values (NULL);
ERROR 1048 (23000): Column 'title' cannot be null

that was expected...

mysql> insert into foo(title) values (NULL),("OH SNAP");
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'title' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)
Hmm, not quite what I was expecting...error became a warning..

mysql> select * from foo;
+-----------+
| title
+-----------+
| asdf
|
| OH SNAP
+-----------+
3 rows in set (0.00 sec)
we get the default empty string inserted for us...how nice

mysql> set sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into foo(title) values (NULL),("asdf");
ERROR 1048 (23000): Column 'title' cannot be null
woot, stricter now..

A

Monday, February 18, 2008

A Couple Of Quick Mongrel Tips

timeout mongrel connections

There are two time based arguments for your pleasure:

-o, --timeout X - Time to wait before killing a stalled thread
-t, --throttle X - Time to pause between accepting clients

Both conveniently begin with the letter 't'. Mistaking -t for the timeout can lead to some interesting behavior like for example causing your site to not accept new clients for a bit.

If you're looking to timeout your connections after X amount of time you can use -o X, where X is the time to wait in seconds:

mongrel_rails start -d -e production -o 30 -p 5555 -P mongrel.pid


stopping a mongrel

Mongrels dont always like to stop when you tell them to stop, they will just sit there happily forever not doing anything productive:

% mongrel_rails stop -P mongrel.pid
Sending TERM to Mongrel at PID 25073...Done.
% ps aux | grep '25073'
25073 Feb17 5:51 mongrel_rails [5555/0/8288]: idle
But I said stop! :(

You can use --force to send a kill -9, but this isn't too graceful. You want to allow any queued clients to exit before doing this.

To deal with this you can also provide a wait argument which attempt to do a graceful stop, but if after X amount of time it can't, it will bring out the BFG and kill -9 the process:

mongrel_rails stop --force -w 30 -P mongrel.pid


monitoring a mongrel

If you're planning to restart mongrel processes using God by checking the http status code, make sure you check for more than N consecutive failures as you can(and will) get random timeouts/refused connections.

restart.condition(:http_response_code) do |c|
c.timeout = 15.seconds
c.code_is_not = 200
c.times = [3, 3]
end
This for example, will check for 3 consecutive http response failures before restarting.