Dec 15 19:51:51 hobbes sqlgrey: dbaccess: warning: couldn't do
query: UPDATE from_awl SET last_seen = NOW(), first_seen =
first_seen WHERE sender_name = 'schuhb?ckoubut' AND
Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (utf8_general_ci,COERCIBLE) for operation '=',
reconnecting to DB
Okay, here's some more detail.
Fresh log entry:
-----------------------------------------------------------------------
Apr 2 12:48:42 hobbes sqlgrey: warning: Use of uninitialized value in
concatenation (.) or string at /usr/sbin/sqlgrey line 1139.
Apr 2 12:48:42 hobbes sqlgrey: dbaccess: error: couldn't access
from_awl table:
Apr 2 12:48:42 hobbes sqlgrey: grey: from awl match: updating
212.56.148.57(212.56.148.57),
il<E4>***@fastmail.fm(il<E4>***@fastmail.fm)
Apr 2 12:48:42 hobbes sqlgrey: dbaccess: warning: couldn't do query:
UPDATE from_awl SET last_seen = NOW(), first_seen = first_seen WHERE
sender_name = 'il<E4>nderkebym' AND sender_domain = 'fastmail.fm' AND
src = '212.56.148.57': Illegal mix of collations
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for
operation '=', reconnecting to DB
-----------------------------------------------------------------------
MySQL information:
-----------------------------------------------------------------------
mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
-----------------------------------------------------------------------
So in general, my system is setup to use UTF-8. Keep in mind that
client and connection are referring to the mysql command line client here.
-----------------------------------------------------------------------
mysql> SHOW CHARACTER SET LIKE 'latin1';
+---------+----------------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+----------------------+-------------------+--------+
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
+---------+----------------------+-------------------+--------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------
This shows that latin1 causes the latin1_swedish_ci collation to be set
as default.
-----------------------------------------------------------------------
mysql> SHOW FULL COLUMNS FROM from_awl ;
+---------------------------------------------+
| Field: sender_name |
| Type: varchar(64) |
| Collation: latin1_swedish_ci |
| Null: NO |
| Key: PRI |
| Default: NULL |
| Extra: |
| Privileges: select,insert,update,references |
| Comment: |
+---------------------------------------------+
| Field: sender_domain |
| Type: varchar(255) |
| Collation: latin1_swedish_ci |
| Null: NO |
| Key: PRI |
| Default: NULL |
| Extra: |
| Privileges: select,insert,update,references |
| Comment: |
+---------------------------------------------+
| Field: src |
| Type: varchar(39) |
| Collation: latin1_swedish_ci |
| Null: NO |
| Key: PRI |
| Default: NULL |
| Extra: |
| Privileges: select,insert,update,references |
| Comment: |
+---------------------------------------------+
| Field: first_seen |
| Type: timestamp |
| Collation: NULL |
| Null: NO |
| Key: |
| Default: CURRENT_TIMESTAMP |
| Extra: |
| Privileges: select,insert,update,references |
| Comment: |
+---------------------------------------------+
| Field: last_seen |
| Type: timestamp |
| Collation: NULL |
| Null: NO |
| Key: MUL |
| Default: 0000-00-00 00:00:00 |
| Extra: |
| Privileges: select,insert,update,references |
| Comment: |
+---------------------------------------------+
5 rows in set (0.01 sec)
-----------------------------------------------------------------------
[I reformatted that output so it wouldn't have to wrap.]
So the Swedish collation is on all the text fields....
And here are a few of the relevant lines from an mysqldump of my
sqlgrey database:
-----------------------------------------------------------------------
--
-- Table structure for table `from_awl`
--
DROP TABLE IF EXISTS `from_awl`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `from_awl` (
`sender_name` varchar(64) NOT NULL,
`sender_domain` varchar(255) NOT NULL,
`src` varchar(39) NOT NULL,
`first_seen` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`last_seen` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`src`,`sender_domain`,`sender_name`),
KEY `from_awl_lseen` (`last_seen`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
-----------------------------------------------------------------------
2nd line from the bottom has "DEFAULT CHARSET=latin1;" which shows that
I followed the HOWTO's instructions when creating the databases. (I
checked another database and it has "DEFAULT CHARSET=utf8" like I'd
expect if I didn't set something explicitly.)
So I have two questions:
1.) What's wrong?
2.) How can I fix it?
Right now, my leaning is that the connection from sqlgrey is UTF-8
since that's my system's default and that's causing the error about utf8
and swedish fighting.
Now I know next to nothing about non-ASCII encodings, MySQL's deep dark
secrets, and Perl. But I did find this page about setting the
connection's encoding for mysql:
http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
--
.----------. ,----------== Peep ==----------,
/ .-. .-. \ `-== (Philippe Chaintreuil) ==-`
/ | | | | \
\ `-' `-' _/ FidoNet Netmail Address:
/\ .--. / | 1:2613/118
\ | / / / / InterNet Address:
/ | `--' /\ \ ***@parallaxshift.com
/`-------' \ \ --PiCTuRe By aN uNKNoWN aSCii aRTiST--
"We're just two lost souls swimming in a fish bowl..."