Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > linux.debian.bugs.dist > #1015342

Bug#963731: firebird3.0-server-core: duplicate primary key values after an exception and a rollback

Show all headers | View raw

Package: firebird3.0-server-core
Version: 3.0.1.32609.ds4-14
Severity: important
Tags: upstream patch fixed-upstream

Control: forwarded -1 http://tracker.firebirdsql.org/browse/CORE-6343

It is possible to get duplicate values in a column declared as a primary key 
when a transaction that had inserted some new rows is rolled back after an 
exception.

Attached is the example from the upstream bug report. Transcript follows:

% FIREBIRD_LOCK=. isql-fb -user sysdba -e -i fb-core-6343.sql
Use CONNECT or CREATE DATABASE to specify a database
drop database './test.fdb';
Command error: drop database './test.fdb'
create database './test.fdb';

CREATE GLOBAL TEMPORARY TABLE GTT_TABLE (
    ID INTEGER NOT NULL
) ON COMMIT DELETE ROWS;

CREATE TABLE TEST_TABLE (
    ID INTEGER NOT NULL PRIMARY KEY
);

set term ^ ;
CREATE OR ALTER PROCEDURE TEST_PROC
RETURNS (
    ID1 INTEGER)
AS
DECLARE VARIABLE ID2 INTEGER;
BEGIN
   INSERT INTO GTT_TABLE VALUES(1);
   INSERT INTO GTT_TABLE VALUES(2);
   INSERT INTO GTT_TABLE VALUES(3);
   FOR SELECT ID FROM GTT_TABLE
   INTO :ID1 DO
              BEGIN
                    INSERT INTO TEST_TABLE (ID) VALUES (:ID1);

                    FOR SELECT 1 FROM RDB$DATABASE INTO :ID2 DO
                       IF (:ID1=3) THEN ID1 = 1/0; --in production there is EXCEPTION EX_NAME instead

                    SUSPEND;

                    DELETE FROM TEST_TABLE;
               END
END ^
set term ; ^

select * from test_proc;

         ID1
============
           1
           2
Statement failed, SQLSTATE = 22012
arithmetic exception, numeric overflow, or string truncation
-Integer divide by zero.  The code attempted to divide an integer value by an integer divisor of zero.
-At procedure 'TEST_PROC' line: 16, col: 41
After line 35 in file fb-core-6343.sql

rollback;
-- should result in no rows
select * from test_table;

          ID
============
           2
           3



select * from test_proc;

         ID1
============
           1
           2
Statement failed, SQLSTATE = 22012
arithmetic exception, numeric overflow, or string truncation
-Integer divide by zero.  The code attempted to divide an integer value by an integer divisor of zero.
-At procedure 'TEST_PROC' line: 16, col: 41
After line 40 in file fb-core-6343.sql

rollback;
-- now see the duplicates
select * from test_table;

          ID
============
           2
           3
           2
           3


quit;
------------------------------------------

Upstream commits 8b328f86ded51f4caa06fe56478788b597a34922 and 
8a837bd7fdb75e8206d8f4878a0f789f9b4fe530 have a fix which is reported 
to work. I'll confirm this is the case for the Debian package and 
upload a fixed version shortly.

<https://github.com/FirebirdSQL/firebird/commit/8b328f86ded51f4caa06fe56478788b597a34922>
<https://github.com/FirebirdSQL/firebird/commit/8a837bd7fdb75e8206d8f4878a0f789f9b4fe530>

This deserves a stable upload, because ensuring uniqueness of primary keys is a
critical function of a database system.

According to the upstream bug report version 3.0.1 (found in oldstable) is also
affected.


-- Damyan


-- System Information:
Debian Release: bullseye/sid
  APT prefers unstable-debug
  APT policy: (500, 'unstable-debug'), (500, 'unstable'), (500, 'stable'), (1, 'experimental')
Architecture: amd64 (x86_64)

Kernel: Linux 5.6.0-2-amd64 (SMP w/4 CPU cores)
Kernel taint flags: TAINT_OOT_MODULE
Locale: LANG=bg_BG.UTF-8, LC_CTYPE=bg_BG.UTF-8 (charmap=UTF-8), LANGUAGE=bg_BG.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: systemd (via /run/systemd/system)

Versions of packages firebird3.0-server-core depends on:
ii  firebird3.0-common      3.0.5.33220.ds4-2
ii  firebird3.0-common-doc  3.0.5.33220.ds4-2
ii  libc6                   2.30-8
ii  libfbclient2            3.0.5.33220.ds4-2
ii  libgcc-s1               10.1.0-4
ii  libib-util              3.0.5.33220.ds4-2
ii  libicu67                67.1-2
ii  libstdc++6              10.1.0-4

Versions of packages firebird3.0-server-core recommends:
ii  firebird3.0-utils  3.0.5.33220.ds4-2

Versions of packages firebird3.0-server-core suggests:
ii  firebird3.0-server  3.0.5.33220.ds4-2

-- no debconf information

Back to linux.debian.bugs.dist | Previous | Next | Find similar | Unroll thread

Thread

Bug#963731: firebird3.0-server-core: duplicate primary key values after an exception and a rollback Damyan Ivanov <dmn@debian.org> - 2020-06-26 10:00 +0200

csiph-web