Re: [Sqlgrey-users] Sqlgrey-users Digest, Vol 14, Issue 1

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Philippe Chaintreuil wrote:
> sql...@li... wrote:
>  > Philippe Chaintreuil wrote:
>  >> > 	I get errors like the following about once a week:
>  >> >
>  >> > 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
>  >> > sender_domain = 'rocketmail.com' AND src = '124.106.4.237':
>  >> > 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>nde...@fa...(il<E4>nde...@fa...)
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:
  / |  `--'  /\ \        pe...@pa...
   /`-------'  \ \  --PiCTuRe By aN uNKNoWN aSCii aRTiST--
      "We're just two lost souls swimming in a fish bowl..."