[Bucardo-general] bucardo fails to replicate when the column name contains german umlauts

[Christian Ritter]

I have switched the query log on, and it looks like bucardo has 
problems with reading the data from the source database.


my insert into database testa:
2013-09-02 11:08:28 CEST LOG:  statement: INSERT INTO testtable VALUES 
(1, '192.168.1.1', 'foobar');

what bucardo tries to insert into database testb:
2013-09-02 11:08:29 CEST LOG:  execute dbdpg_p14740_6: INSERT INTO 
public.testtable (id, host_ip,"prüfsumme") VALUES ($1,$2,$3)
2013-09-02 11:08:29 CEST DETAIL:  parameters: $1 = '1', $2 = 
'192.168.1.1', $3 = NULL
2013-09-02 11:08:29 CEST ERROR:  null value in column "prüfsumme" 
violates not-null constraint
2013-09-02 11:08:29 CEST STATEMENT:  INSERT INTO public.testtable (id, 
host_ip,"prüfsumme") VALUES ($1,$2,$3)

prüfsumme is quoted with "" which looks ok.

client encoding is set to utf8:

testa=# SHOW client_encoding;
  client_encoding
-----------------
  UTF8
(1 row)


Cheers

Christian



On 2013-09-02 10:55, Alex Balashov wrote:
> Yes, but this works:
>
> CREATE TABLE x (
>         übergang        varchar(32) PRIMARY KEY NOT NULL
> );
>
> evariste=> INSERT INTO x (übergang) VALUES('Ausgezeichnet!');
> INSERT 0 1
>
> -- Alex
>
> On 09/02/2013 04:53 AM, Michelle Sullivan wrote:
>
>> Alex Balashov wrote:
>>> That's really odd.  Are Perl strings not 8-bit clean by default?  I
>>> wouldn't have thought...
>>
>> I would suggest this is more likely to be an issue with PostgreSQL
>> rather than perl (though I am guessing.)
>>
>> You get similar problems with using capital letters for column 
>> names...
>> PostgreSQL will ignore the case unless the column name is enclosed 
>> in
>> quotes...
>>
>> CREATE TABLE testtable
>> (
>>    id integer primary key,
>>    host_ip inet NOT NULL,
>>    Prufsumme character varying(255) NOT NULL
>> );
>>
>> INSERT INTO testtable VALUES (1, '192.168.1.1', 'foobar');
>>
>> Will succeed.
>>
>> INSERT INTO testtable (id, host_ip, "Prufsumme") VALUES (1,
>> '192.168.1.1', 'foobar');
>>
>> Will also succeed.
>>
>> INSERT INTO testtable (id, host_ip, Prufsumme) VALUES (1, 
>> '192.168.1.1',
>> 'foobar');
>>
>> Will fail.
>>
>> INSERT INTO testtable (id, host_ip, prufsumme) VALUES (1, 
>> '192.168.1.1',
>> 'foobar');
>>
>> Will succeed on *some* versions of PostgreSQL (but should fail on 
>> all).
>>
>> Michelle
>>
>>>
>>> On 09/02/2013 04:33 AM, Christian Ritter wrote:
>>>
>>>> Hello,
>>>>
>>>> i have a really strange problem with bucardo and german umlauts.
>>>> If you have a table where a column name contains german umlauts 
>>>> bucardo
>>>> is unable to replicate the data.
>>>>
>>>> Bucardo version is 4.5.0 and postgres version is 9.1.9 or 9.2.1
>>>>
>>>> If you create a table like that on both databases:
>>>>
>>>> CREATE TABLE testtable
>>>> (
>>>>      id integer primary key,
>>>>       host_ip inet NOT NULL,
>>>>      prüfsumme character varying(255) NOT NULL
>>>> );
>>>>
>>>>
>>>> and then add some data to that table, like:
>>>>
>>>> INSERT INTO testtable VALUES (1, '192.168.1.1', 'foobar');
>>>>
>>>>
>>>> bucardo fails to replicate it and you get an error:
>>>>
>>>> KID [1/1] public.testtable INSERT source to target pk 1
>>>> KID Warning! Aborting due to exception for public.testtable.id: 1 
>>>> Error
>>>> was DBD::Pg::st execute failed: ERROR:  null value in column 
>>>> "prüfsumme"
>>>> violates not-null constraint at /root/perl5/lib/perl5/Bucardo.pm 
>>>> line
>>>> 5769.
>>>> KID Final database backend PID is 10758
>>>>
>>>> It looks like bucardo tries to insert NULL and not foobar into the
>>>> column called "prüfsumme" which of course breaks the NOT NULL
>>>> constraint.
>>>>
>>>>
>>>>
>>>> complete log of db creation and bucardo install is here:
>>>> http://pastebin.com/Q8KV06jA
>>>>
>>>> log.bucardo could be found here:
>>>> http://pastebin.com/TyCbn8mz
>>>>
>>>> Cheers
>>>>
>>>> Christian
>>>> _______________________________________________
>>>> Bucardo-general mailing list
>>>> Bucardo-general at bucardo.org
>>>> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>>>
>>>
>>
>>