mysql lookup table and utf8

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
55 messages Options
123
Reply | Threaded
Open this post in threaded view
|

mysql lookup table and utf8

Antoine Nguyen-2
Dear list,

sorry for my previous incomplete message.

I'm trying to setup a postfix 3.0 with a mysql backend to host utf8
domains. The setup is almost ready but when I make some tests, utf8
domains are not found (I see "relay access denied" errors in the logs)
and I get the same result using postmap:

> postmap -q raté.com mysql:/etc/postfix/sql-domains.cf

>

If I try with a domain containing ascii characters only, it works just fine.

What am I doing wrong ?

Thanks for your help,
Antoine Nguyen
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Wietse Venema
Antoine Nguyen:

> Dear list,
>
> sorry for my previous incomplete message.
>
> I'm trying to setup a postfix 3.0 with a mysql backend to host utf8
> domains. The setup is almost ready but when I make some tests, utf8
> domains are not found (I see "relay access denied" errors in the logs)
> and I get the same result using postmap:
>
> > postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
> >

And why should this return a result? Hint: do the sql query by hand.

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
On 12/09/2016 12:42, Wietse Venema wrote:

> Antoine Nguyen:
>> Dear list,
>>
>> sorry for my previous incomplete message.
>>
>> I'm trying to setup a postfix 3.0 with a mysql backend to host utf8
>> domains. The setup is almost ready but when I make some tests, utf8
>> domains are not found (I see "relay access denied" errors in the logs)
>> and I get the same result using postmap:
>>
>>> postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
>>>
> And why should this return a result? Hint: do the sql query by hand.
>
>
The query returns a result when I execute it manually within the mysql
shell. (sorry for the previous copy/paste, my email client does not use
the same encoding)

MariaDB [modoboa]> SELECT name FROM admin_domain WHERE name='raté.com' and enabled=1;

+---------------+

| name          |

+---------------+

| raté.com      |

+---------------+

1 row in set (0.01 sec)


Antoine
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Wietse Venema
Antoine Nguyen:

> On 12/09/2016 12:42, Wietse Venema wrote:
> > Antoine Nguyen:
> >> Dear list,
> >>
> >> sorry for my previous incomplete message.
> >>
> >> I'm trying to setup a postfix 3.0 with a mysql backend to host utf8
> >> domains. The setup is almost ready but when I make some tests, utf8
> >> domains are not found (I see "relay access denied" errors in the logs)
> >> and I get the same result using postmap:
> >>
> >>> postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
> >>>
> > And why should this return a result? Hint: do the sql query by hand.
> >
> >
> The query returns a result when I execute it manually within the mysql
> shell. (sorry for the previous copy/paste, my email client does not use
> the same encoding)
>
> MariaDB [modoboa]> SELECT name FROM admin_domain WHERE name='rat?.com' and enabled=1;

Just for test, what happens when you do:

(this is what Postfix daemons use by default)
    LC_ALL=C postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf

(this is to test what happens with an UTF-8 locale)
    LC_ALL=en_US.UTF-8 postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
On 12/09/2016 17:04, Wietse Venema wrote:

> Antoine Nguyen:
>> On 12/09/2016 12:42, Wietse Venema wrote:
>>> Antoine Nguyen:
>>>> Dear list,
>>>>
>>>> sorry for my previous incomplete message.
>>>>
>>>> I'm trying to setup a postfix 3.0 with a mysql backend to host utf8
>>>> domains. The setup is almost ready but when I make some tests, utf8
>>>> domains are not found (I see "relay access denied" errors in the logs)
>>>> and I get the same result using postmap:
>>>>
>>>>> postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
>>>>>
>>> And why should this return a result? Hint: do the sql query by hand.
>>>
>>>
>> The query returns a result when I execute it manually within the mysql
>> shell. (sorry for the previous copy/paste, my email client does not use
>> the same encoding)
>>
>> MariaDB [modoboa]> SELECT name FROM admin_domain WHERE name='rat?.com' and enabled=1;
> Just for test, what happens when you do:
>
> (this is what Postfix daemons use by default)
>      LC_ALL=C postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
>
> (this is to test what happens with an UTF-8 locale)
>      LC_ALL=en_US.UTF-8 postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
>
Still no result with both command lines.

I'm a bit lost... could it be a mysql collation issue ?

Antoine
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
In reply to this post by Wietse Venema
On 12/09/2016 17:04, Wietse Venema wrote:

>
> Just for test, what happens when you do:
>
> (this is what Postfix daemons use by default)
>      LC_ALL=C postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
>
> (this is to test what happens with an UTF-8 locale)
>      LC_ALL=en_US.UTF-8 postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
>
>
Just a stupid question but am I right to think the use case I'm trying
to implement using Postfix 3+ and a SQL backend should work ?

Thanks in advance,

Antoine Nguyen
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Wietse Venema
Antoine Nguyen:

> On 12/09/2016 17:04, Wietse Venema wrote:
> >
> > Just for test, what happens when you do:
> >
> > (this is what Postfix daemons use by default)
> >      LC_ALL=C postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
> >
> > (this is to test what happens with an UTF-8 locale)
> >      LC_ALL=en_US.UTF-8 postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
> >
> Just a stupid question but am I right to think the use case I'm trying
> to implement using Postfix 3+ and a SQL backend should work ?

Yes, UTF-8 queries are supposed to work with all Postfix lookup tables,
so the question is where things go wrong.

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
On 13/09/2016 12:43, Wietse Venema wrote:

> Antoine Nguyen:
>> On 12/09/2016 17:04, Wietse Venema wrote:
>>> Just for test, what happens when you do:
>>>
>>> (this is what Postfix daemons use by default)
>>>       LC_ALL=C postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
>>>
>>> (this is to test what happens with an UTF-8 locale)
>>>       LC_ALL=en_US.UTF-8 postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
>>>
>> Just a stupid question but am I right to think the use case I'm trying
>> to implement using Postfix 3+ and a SQL backend should work ?
> Yes, UTF-8 queries are supposed to work with all Postfix lookup tables,
> so the question is where things go wrong.
>
>
Fine. Do you have any hint/idea on what I could do to locate the issue ?

Antoine
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Administrator Beckspaced.com


Am 13.09.2016 um 14:22 schrieb Antoine Nguyen:

> On 13/09/2016 12:43, Wietse Venema wrote:
>> Antoine Nguyen:
>>> On 12/09/2016 17:04, Wietse Venema wrote:
>>>> Just for test, what happens when you do:
>>>>
>>>> (this is what Postfix daemons use by default)
>>>>       LC_ALL=C postmap -q rat?.com mysql:/etc/postfix/sql-domains.cf
>>>>
>>>> (this is to test what happens with an UTF-8 locale)
>>>>       LC_ALL=en_US.UTF-8 postmap -q rat?.com
>>>> mysql:/etc/postfix/sql-domains.cf
>>>>
>>> Just a stupid question but am I right to think the use case I'm trying
>>> to implement using Postfix 3+ and a SQL backend should work ?
>> Yes, UTF-8 queries are supposed to work with all Postfix lookup tables,
>> so the question is where things go wrong.
>>
>>
> Fine. Do you have any hint/idea on what I could do to locate the issue ?
>
> Antoine
>
>
hello ;)

not sure if this will help? but had a similar issue with mysql queries a
while back.
it actually had nothing to do with postfix and sql lookup tables ...

but ran into trouble with different mysql character sets ... causing
queries to also fail

not sure how your data made it into the mysql tables? but i had problems
with latin-1 characters set ending up in a UTF-8 character set table and
the whole thing got messed up ...

it's called mysql character set hell ;)

this link might help?

https://www.blueboxcloud.com/insight/blog-article/getting-out-of-mysql-character-set-hell

but again ... i'm not sure in your case. just an idea, perhaps it might
help?

greetings
becki

Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
On 13/09/2016 16:10, Admin Beckspaced wrote:

> hello ;)
>
> not sure if this will help? but had a similar issue with mysql queries
> a while back.
> it actually had nothing to do with postfix and sql lookup tables ...
>
> but ran into trouble with different mysql character sets ... causing
> queries to also fail
>
> not sure how your data made it into the mysql tables? but i had
> problems with latin-1 characters set ending up in a UTF-8 character
> set table and the whole thing got messed up ...
>
> it's called mysql character set hell ;)
>
> this link might help?
>
> https://www.blueboxcloud.com/insight/blog-article/getting-out-of-mysql-character-set-hell 
>
>
> but again ... i'm not sure in your case. just an idea, perhaps it
> might help?
>

Thank you for this link but, since it is a new setup, I have no data.
I've already checked mysql configuration : default character set is
utf8, default collation is utf8_general_ci and database and tables are
using those values. Also, it looks like the data inserted by Modoboa
(the tool I use to manage domains) is encoded using utf8.

I'm still stuck with this and I don't understand why yet...

Antoine
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Viktor Dukhovni
On Tue, Sep 13, 2016 at 05:30:40PM +0200, Antoine Nguyen wrote:

> Thank you for this link but, since it is a new setup, I have no data. I've
> already checked mysql configuration : default character set is utf8, default
> collation is utf8_general_ci and database and tables are using those values.
> Also, it looks like the data inserted by Modoboa (the tool I use to manage
> domains) is encoded using utf8.
>
> I'm still stuck with this and I don't understand why yet...

Make sure your shell or terminal emulator is not mangling utf-8
input or command-line arguments.  

 * Post your map.cf file content, perhaps that map file restricts
   which lookup keys are passed on to the underlying database.
 * Place the lookup key in a file.
 * Ensure that the file's content is correctly utf-8 encoded.
 * Run:

    postmap -v -q - mysql:... < input-file

Repeat with various values of LANG.  Note that Postfix applies
MySQL string quoting to input arguments, and the MySQL client
confgiration must somehow decide to use UTF-8 encoding for the
query string.

--
        Viktor.
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
On 13/09/2016 18:08, Viktor Dukhovni wrote:

> Make sure your shell or terminal emulator is not mangling utf-8
> input or command-line arguments.
>
>   * Post your map.cf file content, perhaps that map file restricts
>     which lookup keys are passed on to the underlying database.
>   * Place the lookup key in a file.
>   * Ensure that the file's content is correctly utf-8 encoded.
>   * Run:
>
>      postmap -v -q - mysql:... < input-file
>
> Repeat with various values of LANG.  Note that Postfix applies
> MySQL string quoting to input arguments, and the MySQL client
> confgiration must somehow decide to use UTF-8 encoding for the
> query string.
>
The map.cf file content is the following :

user = modoboa

password = password

dbname = modoboa

hosts = 127.0.0.1

query = SELECT name FROM admin_domain WHERE name='%s' AND type='domain' AND enabled=1

I've tried the different steps you proposed and unfortunately, it is not
better:

$ echo raté.com > test

$ file test

test: UTF-8 Unicode text

$ postmap -v -q - mysql:/etc/postfix/sql-domains.cf < test

postmap: name_mask: ipv4

postmap: inet_addr_local: configured 3 IPv4 addresses

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: user = modoboa

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: password = modoboa

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: dbname = modoboa

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: result_format = %s

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: option_file = <NULL>

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: option_group = <NULL>

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: tls_key_file = <NULL>

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: tls_cert_file = <NULL>

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: tls_CAfile = <NULL>

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: tls_CApath = <NULL>

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: tls_ciphers = <NULL>

postmap: cfg_get_bool: /etc/postfix/sql-domains.cf: tls_verify_cert = on

postmap: cfg_get_int: /etc/postfix/sql-domains.cf: expansion_limit = 0

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: query = SELECT name FROM admin_domain WHERE name='%s' AND type='domain' AND enabled=1

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: domain =

postmap: cfg_get_str: /etc/postfix/sql-domains.cf: hosts = 127.0.0.1

postmap: dict_open: mysql:/etc/postfix/sql-domains.cf

postmap: dict_mysql_get_active: attempting to connect to host 127.0.0.1

postmap: dict_mysql: successful connection to host 127.0.0.1

postmap: dict_mysql: successful query from host 127.0.0.1

postmap: dict_mysql_lookup: retrieved 0 rows


The result is always the same even if I change LANG (tried C and
en_US.UTF-8).

Antoine
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Viktor Dukhovni
On Tue, Sep 13, 2016 at 09:52:16PM +0200, Antoine Nguyen wrote:

> query = SELECT name FROM admin_domain WHERE name='%s' AND type='domain' AND enabled=1

What is not clear is how MySQL's quoting behaves given the input
UTF-8 string.

> postmap: cfg_get_str: /etc/postfix/sql-domains.cf: query = SELECT name FROM admin_domain WHERE name='%s' AND type='domain' AND enabled=1
>
> postmap: dict_mysql: successful query from host 127.0.0.1
>
> postmap: dict_mysql_lookup: retrieved 0 rows

I was hoping to see the expanded query string, but it is not output
with "-v".  Perhaps there's some sort of logging you can enable in
the MySQL server or client libraries.

Do make sure that your tests with the sql CLI really make the same
query against the same database:

    SELECT name FROM admin_domain WHERE name='<utf-8-domain>' AND type='domain' AND enabled=1

--
        Viktor.
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
On 14/09/2016 04:01, Viktor Dukhovni wrote:

> On Tue, Sep 13, 2016 at 09:52:16PM +0200, Antoine Nguyen wrote:
>
>> query = SELECT name FROM admin_domain WHERE name='%s' AND type='domain' AND enabled=1
> What is not clear is how MySQL's quoting behaves given the input
> UTF-8 string.
>
>> postmap: cfg_get_str: /etc/postfix/sql-domains.cf: query = SELECT name FROM admin_domain WHERE name='%s' AND type='domain' AND enabled=1
>>
>> postmap: dict_mysql: successful query from host 127.0.0.1
>>
>> postmap: dict_mysql_lookup: retrieved 0 rows
> I was hoping to see the expanded query string, but it is not output
> with "-v".  Perhaps there's some sort of logging you can enable in
> the MySQL server or client libraries.
>
> Do make sure that your tests with the sql CLI really make the same
> query against the same database:
>
>      SELECT name FROM admin_domain WHERE name='<utf-8-domain>' AND type='domain' AND enabled=1
>
I've enabled queries logging. Here is the result :

160914  8:24:14       57 Query    SELECT name FROM admin_domain WHERE name='gaël.com' AND type='domain' AND enabled=1

160914  8:24:31       60 Connect    modoboa@localhost as anonymous on modoboa

                       60 Query    SELECT name FROM admin_domain WHERE name='gaël.com' AND type='domain' AND enabled=1

                       60 Quit

The first query was executed using mysql shell and it returns 1 row. The
second query was executed by postmap and returns nothing whereas we can
see they are the same...

Antoine
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
On 14/09/2016 10:27, Antoine Nguyen wrote:
> The first query was executed using mysql shell and it returns 1 row.
> The second query was executed by postmap and returns nothing whereas
> we can see they are the same...
>
I've just tried with postgresql and the result is the same...

Antoine
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Wietse Venema
Antoine Nguyen:
[ Charset windows-1252 converted... ]
> On 14/09/2016 10:27, Antoine Nguyen wrote:
> > The first query was executed using mysql shell and it returns 1 row.
> > The second query was executed by postmap and returns nothing whereas
> > we can see they are the same...
> >
> I've just tried with postgresql and the result is the same...

I forgot, did the same query work for a non-UTF8 domain name?

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
On 14/09/2016 12:53, Wietse Venema wrote:

> Antoine Nguyen:
> [ Charset windows-1252 converted... ]
>> On 14/09/2016 10:27, Antoine Nguyen wrote:
>>> The first query was executed using mysql shell and it returns 1 row.
>>> The second query was executed by postmap and returns nothing whereas
>>> we can see they are the same...
>>>
>> I've just tried with postgresql and the result is the same...
> I forgot, did the same query work for a non-UTF8 domain name?
>
>
Yes, it works.

Antoine
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Viktor Dukhovni
In reply to this post by Antoine Nguyen-2
On Wed, Sep 14, 2016 at 10:27:32AM +0200, Antoine Nguyen wrote:

> >Do make sure that your tests with the sql CLI really make the same
> >query against the same database:
> >
> >     SELECT name FROM admin_domain WHERE name='<utf-8-domain>' AND type='domain' AND enabled=1
> >
> I've enabled queries logging. Here is the result :
>
> 160914  8:24:14       57 Query    SELECT name FROM admin_domain WHERE name='gaël.com' AND type='domain' AND enabled=1
> 160914  8:24:31       60 Connect    modoboa@localhost as anonymous on modoboa
>                       60 Query    SELECT name FROM admin_domain WHERE name='gaël.com' AND type='domain' AND enabled=1
>                       60 Quit
>
> The first query was executed using mysql shell and it returns 1 row. The
> second query was executed by postmap and returns nothing whereas we can see
> they are the same...

Do post the "Connect" string for *both* queries.

--
        Viktor.
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Antoine Nguyen-2
On 14/09/2016 14:17, Viktor Dukhovni wrote:

> On Wed, Sep 14, 2016 at 10:27:32AM +0200, Antoine Nguyen wrote:
>
>>> Do make sure that your tests with the sql CLI really make the same
>>> query against the same database:
>>>
>>>      SELECT name FROM admin_domain WHERE name='<utf-8-domain>' AND type='domain' AND enabled=1
>>>
>> I've enabled queries logging. Here is the result :
>>
>> 160914  8:24:14       57 Query    SELECT name FROM admin_domain WHERE name='gaël.com' AND type='domain' AND enabled=1
>> 160914  8:24:31       60 Connect    modoboa@localhost as anonymous on modoboa
>>                        60 Query    SELECT name FROM admin_domain WHERE name='gaël.com' AND type='domain' AND enabled=1
>>                        60 Quit
>>
>> The first query was executed using mysql shell and it returns 1 row. The
>> second query was executed by postmap and returns nothing whereas we can see
>> they are the same...
> Do post the "Connect" string for *both* queries.
>
Here is the missing line :

160914  8:23:57    57 Connect   root@localhost as anonymous on modoboa

I indeed tried with root but result is the same if I use modoboa user
with mysql shell: it works.

Antoine
Reply | Threaded
Open this post in threaded view
|

Re: mysql lookup table and utf8

Viktor Dukhovni
On Wed, Sep 14, 2016 at 02:33:47PM +0200, Antoine Nguyen wrote:

> >>>     SELECT name FROM admin_domain WHERE name='<utf-8-domain>' AND type='domain' AND enabled=1
> >>>
> >>I've enabled queries logging. Here is the result :
> >>
> >>160914  8:24:14       57 Query    SELECT name FROM admin_domain WHERE name='gaël.com' AND type='domain' AND enabled=1
> >>160914  8:24:31       60 Connect    modoboa@localhost as anonymous on modoboa
> >>                       60 Query    SELECT name FROM admin_domain WHERE name='gaël.com' AND type='domain' AND enabled=1
> >>                       60 Quit
> >>
> >>The first query was executed using mysql shell and it returns 1 row. The
> >>second query was executed by postmap and returns nothing whereas we can see
> >>they are the same...
> >Do post the "Connect" string for *both* queries.
> >
> Here is the missing line :
>
> 160914  8:23:57    57 Connect   root@localhost as anonymous on modoboa
>
> I indeed tried with root but result is the same if I use modoboa user with
> mysql shell: it works.

Please post a single message with two complete traces in which:

   * The Unix login users executing "postmap -q" and using the
     MySQL shell are the same.  (That is the same unix shell spawns
     both "postmap -q" and the MySQL interactive session).

   * The target MySQL database name and database user are clearly
     identified in both cases.

   * The queries and results are reported in detail.

Please look for any client-side configuration files that might be
loaded by the MySQL interactive shell that may affect connection
settings, such as perhaps the client character set.

--
        Viktor.
123