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

[Christian Ritter]

On 2013-09-02 13:48, Michelle Sullivan wrote:
> Christian Ritter wrote:
>> On 2013-09-02 11:49, Michelle Sullivan wrote:
>>> Christian Ritter wrote:
>>>> 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)
>>>>
>>>
>>>
>>> Now that's more like it..  I'm betting the issue therefore is in 
>>> the
>>> bucardo_delta function(s) that insert the data into the table.
>>>
>>> On the source machine try the following (assuming 4.5.x - I don't 
>>> know
>>> on 4.99.7 except this won't work):
>>>
>>> SELECT * FROM bucardo.bucardo_delta WHERE rowid = '1'::text AND 
>>> rowid2 =
>>> '192.168.1.1'::text;
>>>
>>> this will give you the rows in the bucardo_delta table and you 
>>> should be
>>> able to see if the source data is actually NULL.
>>>
>>> If it is and there are no other rows the problem will likely be in 
>>> the
>>> delta() functions.  If not the the read of the data is incorrect.  
>>> If
>>> there are multiple rows and one is null (earliest txntime) then it 
>>> may
>>> be because you changed the column/table definition after a delta 
>>> was
>>> created and not replicated - in which case you will need to remove 
>>> the
>>> rows with something like:
>>>
>>> DELETE FROM bucardo.bucardo_delta WHERE rowid = '1'::text AND 
>>> rowid2 =
>>> '192.168.1.1'::text AND rowid3 IS NULL; (if there are other tables 
>>> you
>>> will need to specify the table OID in the DELETE as well.  Table 
>>> OIDs
>>> can be obtained on the source with something like:
>>>
>>> =# select relname, oid from pg_class where relname = 'hosts2evid';
>>>   relname   |  oid
>>> ------------+-------
>>>  hosts2evid | 32596
>>> (1 row)
>>>
>>> Regards,
>>>
>>> Michelle
>>
>>
>> bucardo delta looks good to me, so it looks like bucardo is unable 
>> to
>> read the data if there are umlauts in the column name.
>>
>> testa=# SELECT * from bucardo_delta;
>>  tablename | rowid |            txntime
>> -----------+-------+-------------------------------
>>      28039 | 1     | 2013-09-02 13:23:12.344017+02
>> (1 row)
>>
>> testa=# select relname, oid from pg_class where relname = 
>> 'testtable';
>>   relname  |  oid
>> -----------+-------
>>  testtable | 28039
>>
>>
>> testa=# SELECT * from testtable;
>>  id |   host_ip   | prüfsumme
>> ----+-------------+-----------
>>   1 | 192.168.1.1 | foobar
>> (1 row)
>>
>
> Ok is your version 4.5.x?
>
> My example select was for rows with a combined key, obviously by your
> data your primary key is just 'id' (I must have been asleep to miss 
> that
> :/ )
>
> If you are not using 4.5.x then what I'm talking about here probably
> does not apply or make sense.
>
> Now assuming 4.5...
>
> bucardo uses COPY to take the source rows and pass them to the
> target...  So your logging doesn't make sense for  4.5..

bucardo version is 4.5.0 <-got that from bucardo_ctl

-- Schema for the main Bucardo database
-- Version 4.5.0

The logs are from postgres, and there is no copy in the logs, only 2 
inserts

The full log is here:

http://pastebin.com/8CxrSFmU


Cheers

Christian