On 11/27/17 17:17, Jiří Činčura wrote:
Hi,
I have a weird behavior here. Database after b&r, no errors. I try to
insert non-unique value into table with unique constraint and it fails
as it should. I encrypt it (AES128 from IBPhoenix) and the same
statement does not fail. Only the next one will throw unique violation.
Here's a snap from console:
$ .\isql.exe
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect 'localhost:C:\Users\Jiri\Downloads\aaa\aaa\aaa.FDB' user
'sysdba' password 'masterkey';
Database: 'localhost:C:\Users\Jiri\Downloads\aaa\aaa\aaa.FDB', User:
SYSDBA
SQL> insert into product2suppdoc(productid, supportingdocumentid) values
(17,1);
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "UNQ_PRODUCT2SUPPDOC" on
table "PRODUCT2SUPPDOC"
-Problematic key value is ("PRODUCTID" = 17, "SUPPORTINGDOCUMENTID" = 1)
SQL> alter database encrypt with aes128;
SQL> commit;
SQL> exit;
$ .\isql.exe
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect 'localhost:C:\Users\Jiri\Downloads\aaa\aaa\aaa.FDB' user
'sysdba' password 'masterkey';
Database: 'localhost:C:\Users\Jiri\Downloads\aaa\aaa\aaa.FDB', User:
SYSDBA
SQL> insert into product2suppdoc(productid, supportingdocumentid) values
(17,1);
SQL> insert into product2suppdoc(productid, supportingdocumentid) values
(17,1);
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "UNQ_PRODUCT2SUPPDOC" on
table "PRODUCT2SUPPDOC"
-Problematic key value is ("PRODUCTID" = 17, "SUPPORTINGDOCUMENTID" = 1)
SQL>
Any ideas what could be wrong and where to look at?
Wierd - I could not reproduce it with ............. plugin.
localhost bin # ./gbak -b employee e1
localhost bin # ./gbak -c e1 e1.fdb
localhost bin # ./isql e1.fdb
Database: e1.fdb, User: SYSDBA
SQL> insert into country values('Russia', 'Rub');
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "INTEG_2" on table "COUNTRY"
-Problematic key value is ("COUNTRY" = 'Russia')
SQL> alter database encrypt with "DbCrypt";
Statement failed, SQLSTATE = HY000
Key not set
SQL> alter database encrypt with "DbCrypt" key Red;
SQL> commit;
SQL>
localhost bin # ./isql e1.fdb
Database: e1.fdb, User: SYSDBA
SQL> insert into country values('Russia', 'Rub');
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "INTEG_2" on table "COUNTRY"
-Problematic key value is ("COUNTRY" = 'Russia')
SQL>
But mostly wierd is that even in theory crypt plugin can not affect
integrity constraints - encryption works a few logical levels deeper
than constraints violation, that's absolutely different parts of code.
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
