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

[Michelle Sullivan]

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..

-- 
Michelle Sullivan
http://www.mhix.org/