After my entry yesterday about MySQL truncating data, several people
have pointed out that MySQL 4.1 or later gives you a warning. Yes, this is true. You
can even see it in the example I gave:

Query OK, 1 row affected, 1 warning (0.00 sec)

I ignored mentioning this, but perhaps should have said something
about it. I reason I didn’t mention it was because I didn’t feel that a
warning really helped anyone. Developers have enough problems
remembering to check for errors, let along remembering to check in case
there was a warning as well. Plus, they’d then have to work out if the
warning was something serious or something they could ignore. There’s
also the question of how well the language bindings present this
information. Take for example, PHP. The mysqli extension gained support
for checking for warnings in PHP5 and gives the following
code
as an
example of getting warnings:

mysqli_query($link, $query);

if (mysqli_warning_count($link)) {
   if ($result = mysqli_query($link, "SHOW WARNINGS")) {
      $row = mysqli_fetch_row($result);
      printf("%s (%d): %sn", $row[0], $row[1], $row[2]);
      mysqli_free_result($result);
   }
}

Hardly concise code. As of 5.1.0, there is also mysqli_get_warnings(),
but is undocumented beyond noting its existence. The MySQL extension
does not support getting warning information. The PDO wrapper doesn’t
provide any way to get this information.

In perl, DBD::mysql has a mysql_warning_count()
function, but presumably would have to call "SHOW WARNINGS"
like in the PHP example. Seems Python’s MYSQLdb module will raise an
exception on warnings in certain cases. Mostly using the Cursor
object.

In java, you can set the jdbcCompliantTruncation connection
parameter to make the driver throw java.sql.DataTruncation
exceptions, as per the JDBC spec, which makes you wonder why this isn’t
set by default. Unfortunately this setting is usually outside the
programmer’s control. There is also the
java.sql.Statement.getWarnings(), but once again, you need to
check this after every statement. Not sure if ORM tools like Hibernate
check this or not.

So, yes MySQL does give you a warning, but in practice is useless.

MySQL in its standard configuration has this wonderful “feature” of
truncating your data if it can’t fit in the field.

mysql> create table foo (bar varchar(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo (bar) values ("12345");
Query OK, 1 row affected, 1 warning (0.00 sec)

In comparison, PostgeSQL does:

psql=> create table foo (bar varchar(4));
CREATE TABLE
psql=> insert into foo (bar) values ('12345');
ERROR:  value too long for type character varying(4)

You can make MySQL do the right thing by setting the SQL
Mode
option to
include STRICT_TRANS_TABLES or STRICT_ALL_TABLES. The difference is that the
former will only enable it for transactional data storage engines. As much as
I’m loathed to say it, I don’t recommend using STRICT_ALL_TABLES, as an error
during updating a non-transational table will result in a partial
update, which is probably worse than a truncated field. Setting the mode
to TRADITIONAL includes both these and a couple of related issues
(NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO) You can set the
mode using:

  • On the command line:

    --sql-mode="TRADITIONAL"
  • In /etc/mysql/my.cnf:

    sql-mode="TRADITIONAL"
  • At runtime:

    SET GLOBAL sql_mode="TRADITIONAL"
    SET SESSION sql_mode="TRADITIONAL"

Just say no to databases that happily throw away your data

Was attempting to merge a branch in one of my projects and upon
committing the merge, I kept getting this error:

mojo-jojo david% svn commit -m "merge in the maven branch"
Sending        trunk
Sending        trunk/.classpath
Sending        trunk/.project
Adding         trunk/.settings
svn: Commit failed (details follow):
svn: Server sent unexpected return value (502 Bad Gateway) in response
to COPY request for '/svn/eddie/!svn/bc/314/branches/maven/.settings'

A quick search found several other people having the same problem.
Seems it only happens for https repositories using mod_dav_svn.
The solution is to make sure that your virtual host in apache has
explicit SSL config options, even if you are using an SSL config from a
default virtual host. For example, I added the following to my
subversion vhost, which was just copied from my default vhost:

SSLEngine on
SSLCertificateFile /etc/apache2/ssl/catnip.org.uk.crt
SSLCertificateKeyFile /etc/apache2/ssl/catnip.org.uk.key

We’ve recently installed a couple of Juniper J-Series routers that
have the new JUNOS with Enhanced Services installed on them. During the
transition from our existing Linux routers, we started moving internal
subnets to the new routers, but when we moved the first subnet, we
discovered a problem with hosts that had addresses on two different
subnets. Connections would either connect for a minute and work and then
get a connection reset, or packets would come in to the server and leave
again, but then get swallowed in the ether.

I spent quite a bit of time this week reading about the security
features of the new routers, and finally came up with a solution. The
first clue was that I was getting connection reset from something on the
network, but carrying out packet sniffing on our existing routers and
the end points showed that they weren’t generating it. I eventually
found the tcp-rst option, which generates a reset packet for
any non-SYN packet that doesn’t match an existing flow session. JUNOS ES
does stateful packet inspection by creating a session when it sees an
initial SYN packet and then does filtering and routing based on that
flow session so it doesn’t have to do it for every packet. When I turned
off the tcp-rst option on the trust zone, my connection that
worked for a minute worked again only for a minute, but this time, it
just hung, rather than dying with a connection reset. This cemented the
idea that the Juniper routers were the cause.

It turned out that the problem was that there was asynchronous
routing going on. A packet was coming in to 10.0.0.1/24, but the server
was also on 10.0.1.1/24 and the default route nexthop was 10.0.1.2.
Depending on which subnet we moved depended on the resulting behavour.
If we moved 10.0.0.0/24 to the new routers, they would only see the
incoming side of the conversation. If we moved 10.0.1.0/24, they would
only see the outgoing side of the conversation. If we then think how
this would work with the session-based routing and firewalling, in the
first case, the router would see the initial SYN packet, but would never
see the returning SYN-ACK packet, and after an initial timeout, decide
the flow never established and destroy the session info, resulting in
further incoming packets to be dropped. In the second case, it would
never see the SYN packet, only the SYN-ACK. This packet wouldn’t belong
to an existing session, so would be blocked. The solution is to turn off
the SYN check, using:

[edit]
user@host# set security flow tcp-session no-syn-check

After commiting that, sessions work correctly, even without the
router seeing both sides of the connection:

user@host> show security flow session destination-prefix 10.0.0.1
Session ID: 201341, Policy name: default-permit/4, Timeout: 1798
  In: 192.168.0.1/61136 --> 10.0.0.1/22;tcp, If: ge-0/0/0.7
  Out: 10.0.0.1/22 --> 192.168.0.1/61136;tcp, If: ge-0/0/1.7

1 sessions displayed

Sadly, I couldn’t find much information on the no-syn-check
option, so hopefully people will find this explaination useful.

If you get the following error:

/etc/ldap/slapd.conf: line 127: substr index of attribute "sambaSID" disallowed

when you run slapindex, then you haven’t updated your
samba.schema to the version from Samba 3.0.23. Dapper and Edgy
had 3.0.22, so if you’ve recently upgraded to Hardy, you will see this
problem. The file should have an MD5 of
0e23b3ad05cd2b38a302fe61c921f300. I’m hoping this resolves
problems I have with samba not picking up group membership changes. I’ll
update if it does.

Update: Having installed the new schema and run slapindex, net rpc info shows I have twelve groups when previously it showed zero. This may not solve my group membership problems, but it can’t be a step backwards.

Spamassassin 3.2, which is available in Gutsy and Lenny, comes with a new feature to increase performance by
compiling its regular expressions using re2c. It’s very quick to enable.
First, you need to install the required packages:

apt-get install re2c libc6-dev gcc make

Next, edit /etc/spamassassin/v320.pre and uncomment the line
that says:

loadplugin Mail::SpamAssassin::Plugin::Rule2XSBody

Next pre-compile the regular expressions using sa-compile:

femme:/etc/logcheck# sa-compile
[18741] info: generic: base extraction starting. this can take a while...
[18741] info: generic: extracting from rules of type body_0
100% [===========================] 3293.83 rules/sec 00m00s DONE
100% [===========================] 650.12 bases/sec 00m01s DONE
[18741] info: body_0: 647 base strings extracted in 2 seconds
[snip compiler output]
make install
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /var/lib/spamassassin/compiled/3.002004/auto/Mail/SpamAssassin/CompiledRegexps/body_0/body_0.so
Installing /tmp/.spamassassin18741hDrlUQtmp/ignored/man/man3/Mail::SpamAssassin::CompiledRegexps::body_0.3pm
Writing /var/lib/spamassassin/compiled/3.002004/auto/Mail/SpamAssassin/CompiledRegexps/body_0/.packlist
Appending installation info to /var/lib/spamassassin/compiled/3.002004/perllocal.pod
cp /tmp/.spamassassin18741hDrlUQtmp/bases_body_0.pl /var/lib/spamassassin/compiled/3.002004/bases_body_0.pl
cd /
rm -rf /tmp/.spamassassin18741hDrlUQtmp

Finally, restart spamassassin, and you should find it runs faster.
You will need to run sa-compile every time you update your rules, or
they won’t take effect.

If you get the following warning:

Can't locate Mail/SpamAssassin/CompiledRegexps/body_0.pm in @INC

you forgot to run sa-compile; re-run it and the error should go
away.

Regular viewers will know that I don’t think favourably of MySQL.
Here is yet another reason. Let’s create an InnoDB table:

mysql> CREATE TABLE `User_` (
mysql> ...
mysql> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected, 1 warning (0.04 sec) 

One warning, but we’re running this as part of an import, so we’ll
fail to spot this and even if we did, we wouldn’t be able to get it back
out of mysql because SHOW WARNINGS only shows the last command.
So let’s look at the table we just created:

mysql> show create table User_G
*************************** 1. row ***************************
       Table: User_
Create Table: CREATE TABLE `User_` (
...
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Eh? what’s going on? We asked for InnoDB, but have got a MyISAM
table. Lets look at the engines available.

mysql> show engines;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | DISABLED | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO       | Supports transactions and page-level locking                   |
| BLACKHOLE  | NO       | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO       | Example storage engine                                         |
| ARCHIVE    | YES      | Archive storage engine                                         |
| CSV        | YES      | CSV storage engine                                             |
| ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | YES      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                          |
| ISAM       | NO       | Obsolete storage engine                                        |
+------------+----------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

Oh, so innodb has been disabled. We can fix that easily by removing
skip-innodb from my.cnf.

root@cmsdb01:/var/log# grep skip-innodb /etc/mysql/my.cnf
root@cmsdb01:/var/log#

But hang on a second, that’s not in the config file. What’s going on?
It turns out that the reason InnoDB is disabled is because of the
innodb_log_file_size setting not matching the files on disk.

root@cmsdb01:/var/log# grep innodb_log_file_size /etc/mysql/my.cnf
innodb_log_file_size            = 512M
root@cmsdb01:/var/log# ls -lh /var/lib/mysql/ib_logfile*
-rw-rw---- 1 mysql mysql 5.0M 2006-12-19 18:39 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M 2006-12-19 18:39 /var/lib/mysql/ib_logfile1

Rumour has it that you can just stop MySQL, delete these log files
and start MySQL again. I’m yet to try this as the server in question is
in production use. The alternative is to change the
innodb_log_file_size setting to match the file.

So in summary the problems with MySQL are:

  • Not logging warnings anywhere useful.
  • Converting engine types with a warning rather than throwing an
    error. This can be fixed by setting sql_mode to include
    NO_ENGINE_SUBSTITUTION.
  • Starting up and disabling InnoDB when there is a problem rather than
    failing to start, giving a false impression that everything is
    working.

MySQL has not impressed me this week.

Unfortunately I live in the UK, where 6 months of the year, the time
is GMT. Now is the time of year when I discover which of my servers don’t
have the right timezone configuration and show the wrong time during
daylight saving. For future reference, here’s how
to set the timezone to Europe/London rather than UTC.

root@cms01:/tmp/openssl-0.9.8g# date
Mon Mar 31 08:23:35 GMT 2008
root@cms01:/tmp/openssl-0.9.8g# tzconfig
Your current time zone is set to GMT
Do you want to change that? [n]: y

Please enter the number of the geographic area in which you live:


   1) Africa         7) Australia

   2) America        8) Europe

   3) US time zones     9) Indian Ocean

   4) Canada time zones    10) Pacific Ocean

   5) Asia           11) Use System V style time zones

   6) Atlantic Ocean    12) None of the above


Then you will be shown a list of cities which represent the time zone
in which they are located. You should choose a city in your time zone.

Number: 8

Amsterdam Andorra Athens Belfast Belgrade Berlin Bratislava Brussels
Bucharest Budapest Chisinau Copenhagen Dublin Gibraltar Guernsey Helsinki
Isle_of_Man Istanbul Jersey Kaliningrad Kiev Lisbon Ljubljana London
Luxembourg Madrid Malta Mariehamn Minsk Monaco Moscow Nicosia Oslo Paris
Podgorica Prague Riga Rome Samara San_Marino Sarajevo Simferopol Skopje
Sofia Stockholm Tallinn Tirane Tiraspol Uzhgorod Vaduz Vatican Vienna
Vilnius Volgograd Warsaw Zagreb Zaporozhye Zurich

Please enter the name of one of these cities or zones
You just need to type enough letters to resolve ambiguities
Press Enter to view all of them again
Name: [] London
Your default time zone is set to 'Europe/London'.
Local time is now:      Mon Mar 31 09:23:48 BST 2008.
Universal Time is now:  Mon Mar 31 08:23:48 UTC 2008.

More information is available in the Debian
System Administrator Manual
.

By default, warnquota sends out emails with the device name in the
message, which probably doesn’t make much sense to most non-technical
users.

Hi,

We noticed that you are in violation with the quotasystem
used on this system. We have found the following violations:


/dev/mapper/Ubuntu-home

                        Block limits               File limits
Filesystem           used    soft    hard  grace    used  soft  hard  grace
/dev/mapper/Ubuntu-home
               +- 1044404 1000000 1200000  6days    1781     0     0

You can improve this by using /etc/quotatab to assign a more
meaningful name to the partition:

/dev/mapper/Ubuntu-home:user directory
/dev/mapper/Ubuntu-shared:shared area

I’ve been running Hardy on my workstation for a while and had
recently noticed that I was failing to type a space after “I”. I was
doing it far too much for it to just be me failing to press the space
bar properly, and it wasn’t happening after any other letter. After a
little bit of experiementing, I discovered that something was eating
shift-space. What was happening was that I was failing to release the
shift key quick enough after typing “I” and before I hit the space bar,
so it wasn’t getting passed on.

Turns out that the problem was a recent update of Hardy installed
SCIM, which uses
shift-space as a keyboard shortcut. To turn it off, load the SCIM Setup
program and go to the FrontEnd Global Setup screen and remove
“Shift+Space” from the Trigger hotkey.

Caused confusion for a few minutes. 🙂