#10 warning: couldn't do query

open-accepted
database (9)
5
2007-08-05
2007-01-12
theklap
No

I am running SQLGrey 1.7.4 on Gentoo. I get emails every now and then that sqlgrey lost DB connection then recovers.

Here are just a few of the errors in the mail log.

Jan 11 09:28:57 gentoo sqlgrey: dbaccess: error: couldn't access from_awl table:
Jan 11 09:28:57 gentoo sqlgrey: dbaccess: warning: couldn't do query: UPDATE from_awl SET last_seen = NOW(), first_seen = first_seen WHERE sender_name = 'upbeatadiós' AND sender_domain = 'acdi-cida.gc.ca' AND src = '68.36.17.213': Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=', reconnecting to DB
Jan 11 09:28:57 gentoo sqlgrey: dbaccess: error: couldn't access from_awl table:
Jan 11 09:28:57 gentoo sqlgrey: dbaccess: warning: couldn't do query: UPDATE from_awl SET last_seen = NOW(), first_seen = first_seen WHERE sender_name = 'upbeatadiós' AND sender_domain = 'acdi-cida.gc.ca' AND src = '68.36.17.213': Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=', reconnecting to DB
Jan 11 12:51:44 gentoo sqlgrey: dbaccess: error: couldn't access from_awl table:

Looks like it does not like the special characters in the sender_name. Is there a way around this?

Discussion

  • theklap

    theklap - 2007-01-13

    Logged In: YES
    user_id=1687717
    Originator: YES

    I think I fixed this. By setting the character set after connecting to the DB to latin1.

    # Global DB Init code
    sub initdb($) {
    my $self = shift;
    $self->connectdb();
    $self->update_dbnow();
    $self->database_setup();
    $self->do("SET CHARACTER SET latin1") or $self->mydie('initdb set charset error','Couldn\'t set charset');
    }

     
  • Philippe Chaintreuil

    Logged In: YES
    user_id=348972
    Originator: NO

    Yeah, I see this same thing and I'm also on Gentoo.

    =====================================================================
    mysql> use sqlgrey;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed

    mysql> show variables like "character_set_database";
    +------------------------+--------+
    | Variable_name | Value |
    +------------------------+--------+
    | character_set_database | latin1 |
    +------------------------+--------+
    1 row in set (0.00 sec)

    mysql> show variables like "collation_database";
    +--------------------+-------------------+
    | Variable_name | Value |
    +--------------------+-------------------+
    | collation_database | latin1_swedish_ci |
    +--------------------+-------------------+
    1 row in set (0.00 sec)
    =====================================================================

    Gentoo's ebuild outputs an instructional message when installing sqlgrey:

    =====================================================================

    If you are using MySQL >= 4.1 use "latin1" as charset for
    the SQLgrey db
    =====================================================================

    Which is done above. Should this also be done for the collation?

     
  • Lionel Bouton

    Lionel Bouton - 2007-08-05

    Logged In: YES
    user_id=89899
    Originator: NO

    Are accentuated characters allowed in email address now? I believed it was not the case (so maybe we should had a filter on the email address to verify that they are pure ASCII before accessing the database).

     
  • Lionel Bouton

    Lionel Bouton - 2007-08-05
    • status: open --> open-accepted
     
  • Lionel Bouton

    Lionel Bouton - 2007-08-05
    • assigned_to: nobody --> gyver
     
  • alec

    alec - 2011-02-16

    The cody by theklap does realy fix the problem in a way.
    Still this problem should be solved at a different point.

    Without the workaround it is possible to send an email with special crafted chars in the local part of the email and thereby bypass greylisting due to the fact the it crashes.
    With the workaround the tripple goes into greylisting but is not valid against RFC 2822 and therefore should not be greylisted at all