Characters are garbled when replicating fields with type BLOB SUB_TYPE TEXT if the character set of the connection and the field are different. Tested by Firebird 4.0.4 and 5.0.0.
Metadata:
SET NAMES UTF8;
CREATE DATABASE 'inet://localhost:3055/d:\fbdata\5.0\test.fdb'
USER SYSDBA PASSWORD 'masterkey' DEFAULT CHARACTER SET UTF8;
CREATE TABLE T (
ID BIGINT NOT NULL,
V VARCHAR(30) CHARACTER SET UTF8,
B BLOB SUB_TYPE TEXT CHARACTER SET UTF8,
CONSTRAINT PK_T PRIMARY KEY(ID)
);
ALTER DATABASE
INCLUDE ALL TO PUBLICATION;
EXIT;
OK. Copy TEST.FDB to RTEST.FDB and transfer RTEST.FDB to replica mode.
gfix -user SYSDBA -password masterkey -replica READ_ONLY inet://localhost:3055/d:\fbdata\5.0\RTEST.FDB
terminal 1. Master
chcp 1251
isql -ch WIN1251
connect 'inet://localhost:3055/d:\fbdata\5.0\test.fdb' USER SYSDBA PASSWORD 'masterkey';
alter database enable publication;
SQL> show database;
Database: inet://localhost:3055/d:\fbdata\5.0\test.fdb
Owner: SYSDBA
PAGE_SIZE 8192
Number of DB pages allocated = 240
Number of DB pages used = 232
Number of DB pages free = 8
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 20
Transaction - oldest active = 21
Transaction - oldest snapshot = 21
Transaction - Next = 24
ODS = 13.1
Database not encrypted
Wire crypt plugin: ChaCha64
Creation date: Jan 17, 2024 9:44:18
Replica mode: NONE
Protocol version = 18
Default Character set: UTF8
Publication: Enabled
SQL> insert into t(id, v, b) values(1, 'привет', null);
SQL> commit;
SQL> insert into t(id, v, b) values(2, '', 'мир');
SQL> commit;
SQL> select * from t where id = 1;
ID V B
===================== ============================== =================
1 привет <null>
SQL> select * from t where id = 2;
ID V B
===================== ============================== =================
2 0:3
==============================================================================
B:
мир
==============================================================================
SQL> select octet_length(b) from t where id = 2;
OCTET_LENGTH
=====================
6
terminal 2. Replica
chcp 1251
isql -ch WIN1251
SQL> connect 'inet://localhost:3055/d:\fbdata\5.0\rtest.fdb' USER SYSDBA PASSWORD 'masterkey';
Database: 'inet://localhost:3055/d:\fbdata\5.0\rtest.fdb', User: SYSDBA
SQL> select * from t where id = 1;
ID V B
===================== ============================== =================
1 привет <null>
SQL> select * from t where id = 2;
ID V B
===================== ============================== =================
Statement failed, SQLSTATE = 22018
Cannot transliterate character between character sets
SQL> select octet_length(b) from t where id = 2;
OCTET_LENGTH
=====================
3
The study showed that, unlike fields of the VARCHAR/CHAR type, fields with the BLOB SUB_TYPE TEXT type fall into replication segments not in the storage character set, but in the character set of the current connection. This becomes clear when we compare octet_length(b) on the replica and the master.
Characters are garbled when replicating fields with type BLOB SUB_TYPE TEXT if the character set of the connection and the field are different. Tested by Firebird 4.0.4 and 5.0.0.
Metadata:
OK. Copy
TEST.FDBtoRTEST.FDBand transferRTEST.FDBto replica mode.terminal 1. Master
terminal 2. Replica
The study showed that, unlike fields of the VARCHAR/CHAR type, fields with the BLOB SUB_TYPE TEXT type fall into replication segments not in the storage character set, but in the character set of the current connection. This becomes clear when we compare
octet_length(b)on the replica and the master.