FB refuses to create unique index on col with no dups [CORE400] #744

firebird-automations · 2005-04-14T03:00:00Z

Submitted by: jamincat (jamincat)

Attachments:
PROB.FBK

SFID: 1183051#⁠
Submitted By: jamincat

Cannont create unique index. Database running fine with
unique index on column. While restoring a backup the
following error was logged.

gbak: ERROR: attempt to store duplicate value (visible
to active transactions) in unique index
"SMS_GATEWAY_TRANS_NO".

I put together some java code to find duplicate values
but found none. I also used IBExpert to make the index
inactive and then active on a copy of the original
database. The same error was reported when i tried to
make the index active.

Windows XP SP2.
Firebird Super Server versions 1.5.0.4290 and 1.5.2.4731

I have attached the backup database. I have removed all
tables and columns except the one that causes the problem.

Output from restore operation.

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

IBE: Starting restore. Current time: 13:37:45
gbak: opened file C:\Documents and Settings\Jamin\My
Documents\work\firebird problem\PROB.FBK
gbak: transportable backup -- data in XDR format
gbak: backup file is compressed
gbak: created database C:\Documents and
Settings\Jamin\My Documents\work\firebird
problem\PROB.FDB, page_size 4096 bytes
gbak: started transaction
gbak: restoring domain RDB$216
gbak: restoring domain RDB$217
gbak: restoring domain RDB$228
gbak: restoring domain RDB$229
gbak: restoring table SMS_GATEWAY_TRANS_DETAILS
gbak: restoring column TRANS_NO
gbak: restoring column ID
gbak: committing metadata for table
SMS_GATEWAY_TRANS_DETAILS
gbak: restoring generator SMS_GATEWAY_TRANS_DETAILS_ID
value: 5346
gbak: restoring stored procedure
SMS_GATEWAY_TRANS_DETAILS_ID
gbak: restoring parameter NUMBERTOALLOCATE for stored
procedure
gbak: restoring parameter NEWID for stored procedure
gbak: committing metadata
gbak: restoring index RDB$PRIMARY21
gbak: restoring index SMS_GATEWAY_TRANS_NO
gbak: restoring data for table SMS_GATEWAY_TRANS_DETAILS
gbak: committing data for table SMS_GATEWAY_TRANS_DETAILS
gbak: 5346 records restored
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user PUBLIC
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: creating indexes
gbak: restoring index RDB$PRIMARY21
gbak: cannot commit index SMS_GATEWAY_TRANS_NO
gbak: ERROR: attempt to store duplicate value (visible
to active transactions) in unique index
"SMS_GATEWAY_TRANS_NO"
gbak: restoring index SMS_GATEWAY_TRANS_NO
gbak: finishing, closing, and going home

IBE: Restore completed. Current time: 13:37:49. Elapsed
time: 00:00:0

Java test class

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

import java.sql.*;

public class DBTest
{
public static void main(String[] args)
{
try
{
Class.forName("org.firebirdsql.jdbc.FBDriver");

        final Connection connection =

DriverManager.getConnection("jdbc:firebirdsql:localhost:C:/PROB.FDB",
"SYSDBA", "masterkey");

        try
            \{
            searchForDuplicates\(connection\);
            \}
        finally
            \{
            connection\.close\(\);
            \}

        \}
    catch \(ClassNotFoundException e\)
        \{
        e\.printStackTrace\(\);
        \}
    catch \(SQLException e\)
        \{
        e\.printStackTrace\(\);
        \}
    \}


public static void searchForDuplicates\(Connection

connection) throws SQLException
{
final Statement statement =
connection.createStatement();
final PreparedStatement preparedStatement =
connection.prepareStatement("SELECT * FROM
SMS_GATEWAY_TRANS_DETAILS WHERE TRANS_NO = ?");

    final ResultSet results =

statement.executeQuery("SELECT * FROM
SMS_GATEWAY_TRANS_DETAILS");

    try
        \{
        int recordCount = 0;
        int duplicates = 0;

        while \(results\.next\(\) == true\)
            \{
            recordCount\+\+;

            final byte\[\] bytes =

results.getBytes("TRANS_NO");

            preparedStatement\.clearParameters\(\);
            preparedStatement\.setBytes\(1, bytes\);

            final int matching =

recordsMatchingCurrent(preparedStatement);

            if \( matching \> 1 \)
                duplicates \+= \(matching \-1\);

            \}

        System\.out\.println\("Scanned " \+ recordCount

+ " records");
System.out.println("Found " + duplicates +
" duplicate records");
}
finally
{
results.close();
}
}

public static int

recordsMatchingCurrent(PreparedStatement
preparedStatement) throws SQLException
{
final ResultSet resultSet =
preparedStatement.executeQuery();
try
{
int count = 0;

        while \(resultSet\.next\(\)\)
            count\+\+;

        return count;
        \}
    finally
        \{
        resultSet\.close\(\);
        \}
    \}

\}

The text was updated successfully, but these errors were encountered:

firebird-automations · 2006-06-14T12:39:03Z

Commented by: Alice F. Bird (firebirds)

Date: 2005-04-17 19:48
Sender: seanleyne
Logged In: YES
user_id=71163

In order for a GUID to be stored in the database in a CHAR
(16) form, the field/column must be defined the the Charset
OCTET.

Please post a message to the support list to discuss this
further.

firebird-automations · 2006-06-14T12:39:03Z

Commented by: Alice F. Bird (firebirds)

Date: 2005-04-15 19:38
Sender: jamincat
Logged In: YES
user_id=1259309

Hi.

This column is of type CHAR(16). The values are binary
represenations of GUID's( These are our own GUID type and
the initial bytes are zero so interpreted as a NULL
terminated string may appear to be empty - but they are not!
). These values where actually inserted with a unique index
enabled on this column without error( only after disabling
and reenabling the index or doing a backup and restore does
firebird present this error ). The values are all unique
GUID's. As far as I undertsand Firebird has no BINARY data
type and the CHAR datatype is intended to be used binary
data also. You seem to be implying that firbird will
interpret these values as NULL terminated string so if this
is the case could you please explain how to store binary
data in a column - as this is the method I have used up till
now(several years) without encoutering such problems.

firebird-automations · 2006-06-14T12:39:03Z

Commented by: Alice F. Bird (firebirds)

Date: 2005-04-15 19:03
Sender: seanleyne
Logged In: YES
user_id=71163

The TRANS_NO column contains empty strings (i.e data) not
NULLs. Accordingly, all of the records contain the same
values, which is by definition not unique!

firebird-automations · 2006-06-14T12:39:03Z

Commented by: Alice F. Bird (firebirds)

Date: 2005-04-14 15:54
Sender: jamincat
Logged In: YES
user_id=1259309

Adding database file.

firebird-automations · 2006-06-19T12:57:55Z

Modified by: Alice F. Bird (firebirds)

description: SFID: 1183051#⁠
Submitted By: jamincat

Cannont create unique index. Database running fine with
unique index on column. While restoring a backup the
following error was logged.

gbak: ERROR: attempt to store duplicate value (visible
to active transactions) in unique index
"SMS_GATEWAY_TRANS_NO".

I put together some java code to find duplicate values
but found none. I also used IBExpert to make the index
inactive and then active on a copy of the original
database. The same error was reported when i tried to
make the index active.

Windows XP SP2.
Firebird Super Server versions 1.5.0.4290 and 1.5.2.4731

I have attached the backup database. I have removed all
tables and columns except the one that causes the problem.

Output from restore operation.

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

IBE: Starting restore. Current time: 13:37:45
gbak: opened file C:\Documents and Settings\Jamin\My
Documents\work\firebird problem\PROB.FBK
gbak: transportable backup -- data in XDR format
gbak: backup file is compressed
gbak: created database C:\Documents and
Settings\Jamin\My Documents\work\firebird
problem\PROB.FDB, page_size 4096 bytes
gbak: started transaction
gbak: restoring domain RDB$216
gbak: restoring domain RDB$217
gbak: restoring domain RDB$228
gbak: restoring domain RDB$229
gbak: restoring table SMS_GATEWAY_TRANS_DETAILS
gbak: restoring column TRANS_NO
gbak: restoring column ID
gbak: committing metadata for table
SMS_GATEWAY_TRANS_DETAILS
gbak: restoring generator SMS_GATEWAY_TRANS_DETAILS_ID
value: 5346
gbak: restoring stored procedure
SMS_GATEWAY_TRANS_DETAILS_ID
gbak: restoring parameter NUMBERTOALLOCATE for stored
procedure
gbak: restoring parameter NEWID for stored procedure
gbak: committing metadata
gbak: restoring index RDB$PRIMARY21
gbak: restoring index SMS_GATEWAY_TRANS_NO
gbak: restoring data for table SMS_GATEWAY_TRANS_DETAILS
gbak: committing data for table SMS_GATEWAY_TRANS_DETAILS
gbak: 5346 records restored
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user PUBLIC
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: creating indexes
gbak: restoring index RDB$PRIMARY21
gbak: cannot commit index SMS_GATEWAY_TRANS_NO
gbak: ERROR: attempt to store duplicate value (visible
to active transactions) in unique index
"SMS_GATEWAY_TRANS_NO"
gbak: restoring index SMS_GATEWAY_TRANS_NO
gbak: finishing, closing, and going home

IBE: Restore completed. Current time: 13:37:49. Elapsed
time: 00:00:0

Java test class

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

import java.sql.*;

public class DBTest
{
public static void main(String[] args)
{
try
{
Class.forName("org.firebirdsql.jdbc.FBDriver");

        final Connection connection =

DriverManager.getConnection("jdbc:firebirdsql:localhost:C:/PROB.FDB",
"SYSDBA", "masterkey");

        try
            \{
            searchForDuplicates\(connection\);
            \}
        finally
            \{
            connection\.close\(\);
            \}

        \}
    catch \(ClassNotFoundException e\)
        \{
        e\.printStackTrace\(\);
        \}
    catch \(SQLException e\)
        \{
        e\.printStackTrace\(\);
        \}
    \}


public static void searchForDuplicates\(Connection

connection) throws SQLException
{
final Statement statement =
connection.createStatement();
final PreparedStatement preparedStatement =
connection.prepareStatement("SELECT * FROM
SMS_GATEWAY_TRANS_DETAILS WHERE TRANS_NO = ?");

    final ResultSet results =

statement.executeQuery("SELECT * FROM
SMS_GATEWAY_TRANS_DETAILS");

    try
        \{
        int recordCount = 0;
        int duplicates = 0;

        while \(results\.next\(\) == true\)
            \{
            recordCount\+\+;

            final byte\[\] bytes =

results.getBytes("TRANS_NO");

            preparedStatement\.clearParameters\(\);
            preparedStatement\.setBytes\(1, bytes\);

            final int matching =

recordsMatchingCurrent(preparedStatement);

            if \( matching &gt; 1 \)
                duplicates \+= \(matching \-1\);

            \}

        System\.out\.println\("Scanned " \+ recordCount

+ " records");
System.out.println("Found " + duplicates +
" duplicate records");
}
finally
{
results.close();
}
}

public static int

recordsMatchingCurrent(PreparedStatement
preparedStatement) throws SQLException
{
final ResultSet resultSet =
preparedStatement.executeQuery();
try
{
int count = 0;

        while \(resultSet\.next\(\)\)
            count\+\+;

        return count;
        \}
    finally
        \{
        resultSet\.close\(\);
        \}
    \}

\}

=>

SFID: 1183051#⁠
Submitted By: jamincat

Cannont create unique index. Database running fine with
unique index on column. While restoring a backup the
following error was logged.

gbak: ERROR: attempt to store duplicate value (visible
to active transactions) in unique index
"SMS_GATEWAY_TRANS_NO".

I put together some java code to find duplicate values
but found none. I also used IBExpert to make the index
inactive and then active on a copy of the original
database. The same error was reported when i tried to
make the index active.

Windows XP SP2.
Firebird Super Server versions 1.5.0.4290 and 1.5.2.4731

I have attached the backup database. I have removed all
tables and columns except the one that causes the problem.

Output from restore operation.

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

IBE: Starting restore. Current time: 13:37:45
gbak: opened file C:\Documents and Settings\Jamin\My
Documents\work\firebird problem\PROB.FBK
gbak: transportable backup -- data in XDR format
gbak: backup file is compressed
gbak: created database C:\Documents and
Settings\Jamin\My Documents\work\firebird
problem\PROB.FDB, page_size 4096 bytes
gbak: started transaction
gbak: restoring domain RDB$216
gbak: restoring domain RDB$217
gbak: restoring domain RDB$228
gbak: restoring domain RDB$229
gbak: restoring table SMS_GATEWAY_TRANS_DETAILS
gbak: restoring column TRANS_NO
gbak: restoring column ID
gbak: committing metadata for table
SMS_GATEWAY_TRANS_DETAILS
gbak: restoring generator SMS_GATEWAY_TRANS_DETAILS_ID
value: 5346
gbak: restoring stored procedure
SMS_GATEWAY_TRANS_DETAILS_ID
gbak: restoring parameter NUMBERTOALLOCATE for stored
procedure
gbak: restoring parameter NEWID for stored procedure
gbak: committing metadata
gbak: restoring index RDB$PRIMARY21
gbak: restoring index SMS_GATEWAY_TRANS_NO
gbak: restoring data for table SMS_GATEWAY_TRANS_DETAILS
gbak: committing data for table SMS_GATEWAY_TRANS_DETAILS
gbak: 5346 records restored
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user PUBLIC
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: restoring privilege for user SYSDBA
gbak: creating indexes
gbak: restoring index RDB$PRIMARY21
gbak: cannot commit index SMS_GATEWAY_TRANS_NO
gbak: ERROR: attempt to store duplicate value (visible
to active transactions) in unique index
"SMS_GATEWAY_TRANS_NO"
gbak: restoring index SMS_GATEWAY_TRANS_NO
gbak: finishing, closing, and going home

IBE: Restore completed. Current time: 13:37:49. Elapsed
time: 00:00:0

Java test class

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

import java.sql.*;

public class DBTest
{
public static void main(String[] args)
{
try
{
Class.forName("org.firebirdsql.jdbc.FBDriver");

        final Connection connection =

DriverManager.getConnection("jdbc:firebirdsql:localhost:C:/PROB.FDB",
"SYSDBA", "masterkey");

        try
            \{
            searchForDuplicates\(connection\);
            \}
        finally
            \{
            connection\.close\(\);
            \}

        \}
    catch \(ClassNotFoundException e\)
        \{
        e\.printStackTrace\(\);
        \}
    catch \(SQLException e\)
        \{
        e\.printStackTrace\(\);
        \}
    \}


public static void searchForDuplicates\(Connection

connection) throws SQLException
{
final Statement statement =
connection.createStatement();
final PreparedStatement preparedStatement =
connection.prepareStatement("SELECT * FROM
SMS_GATEWAY_TRANS_DETAILS WHERE TRANS_NO = ?");

    final ResultSet results =

statement.executeQuery("SELECT * FROM
SMS_GATEWAY_TRANS_DETAILS");

    try
        \{
        int recordCount = 0;
        int duplicates = 0;

        while \(results\.next\(\) == true\)
            \{
            recordCount\+\+;

            final byte\[\] bytes =

results.getBytes("TRANS_NO");

            preparedStatement\.clearParameters\(\);
            preparedStatement\.setBytes\(1, bytes\);

            final int matching =

recordsMatchingCurrent(preparedStatement);

            if \( matching \> 1 \)
                duplicates \+= \(matching \-1\);

            \}

        System\.out\.println\("Scanned " \+ recordCount

+ " records");
System.out.println("Found " + duplicates +
" duplicate records");
}
finally
{
results.close();
}
}

public static int

recordsMatchingCurrent(PreparedStatement
preparedStatement) throws SQLException
{
final ResultSet resultSet =
preparedStatement.executeQuery();
try
{
int count = 0;

        while \(resultSet\.next\(\)\)
            count\+\+;

        return count;
        \}
    finally
        \{
        resultSet\.close\(\);
        \}
    \}

\}

firebird-automations · 2006-06-26T21:42:09Z

Commented by: @pcisar

database file

firebird-automations · 2006-06-26T21:42:10Z

Modified by: @pcisar

Attachment: PROB.FBK [ 10036 ]

firebird-automations · 2006-08-01T03:17:47Z

Commented by: MikeStar (xsaero00)

I think I have a related issue and dont want to double up. I get the same message mentioned in this ticket when I try to manually create unique index on VarChar column in table with no duplicates. I even emptied out the table and then tried to create the index but got the same message:
Code:
ALTER TABLE REFERRAL ADD CONSTRAINT REFERRALBYREFERRALCODE UNIQUE (REFERRALCODE);
Result:
ERROR: attempt to store duplicate value (visible
to active transactions) in unique index
"REFERRALBYREFERRALCODE ".
I spent about an hour checking system tables and trying to pinpoint the issue. Then I restarted the database and tried again and all went fine. Seems like there was an active transaction that did not let me do it. I did not check outstanding transactions before restart so I dont know for sure.

firebird-automations · 2008-01-28T15:20:07Z

Modified by: @pcisar

Workflow: jira [ 10424 ] => Firebird [ 14702 ]

dyemanov · 2024-01-14T13:19:18Z

The backup attached to this ticket restores successfully on v3.0.11, v4.0.4 and v5.0.0. Shouldn't this issue be closed?

mrotteveel · 2024-01-14T13:21:11Z

@dyemanov Given the age of this ticket, shouldn't it just be closed?

firebird-automations added priority: major component: engine type: bug labels Apr 25, 2021

firebird-automations assigned hvlad Apr 25, 2021

dyemanov closed this as completed Jan 14, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FB refuses to create unique index on col with no dups [CORE400] #744

FB refuses to create unique index on col with no dups [CORE400] #744

firebird-automations commented Apr 14, 2005

firebird-automations commented Jun 14, 2006

firebird-automations commented Jun 14, 2006

firebird-automations commented Jun 14, 2006

firebird-automations commented Jun 14, 2006

firebird-automations commented Jun 19, 2006

firebird-automations commented Jun 26, 2006

firebird-automations commented Jun 26, 2006

firebird-automations commented Aug 1, 2006

firebird-automations commented Jan 28, 2008

dyemanov commented Jan 14, 2024

mrotteveel commented Jan 14, 2024

FB refuses to create unique index on col with no dups [CORE400] #744

FB refuses to create unique index on col with no dups [CORE400] #744

Comments

firebird-automations commented Apr 14, 2005

firebird-automations commented Jun 14, 2006

firebird-automations commented Jun 14, 2006

firebird-automations commented Jun 14, 2006

firebird-automations commented Jun 14, 2006

firebird-automations commented Jun 19, 2006

firebird-automations commented Jun 26, 2006

firebird-automations commented Jun 26, 2006

firebird-automations commented Aug 1, 2006

firebird-automations commented Jan 28, 2008

dyemanov commented Jan 14, 2024

mrotteveel commented Jan 14, 2024