| Subject | duplicates in a table with PK |
|---|---|
| Author | Puigsegur, Jordi |
| Post date | 2007-05-31T13:11:40Z |
Hi all,
We have a very strange situation with a FireBird database: a table with
a PrimaryKey has duplicate values.
The table is defined as:
SQL> CONNECT D:\FIREBIRD\CORRUPT.FDB USER SYSDBA PASSWORD masterkey;
Database: D:\FIREBIRD\CORRUPT.FDB, User: SYSDBA
SQL> SHOW TABLE CCP_ARBOLPRECIOS;
IDCATPRO NUMERIC(18, 0) Not Null
CLAVE VARCHAR(100) Not Null
FECHASALIDA DATE Not Null
PRECIOS BLOB segment 80, subtype TEXT Nullable
CONSTRAINT INTEG_9:
Primary key (IDCATPRO, CLAVE, FECHASALIDA)
SQL> SHOW INDICES;
RDB$PRIMARY3 UNIQUE INDEX ON CCP_ARBOLPRECIOS(IDCATPRO, CLAVE,
FECHASALIDA)
RDB$PRIMARY2 UNIQUE INDEX ON CCP_ARBOLPRODUCTOS(IDCATPRO)
But id does have duplicates:
SQL> SELECT IDCATPRO, CLAVE, FECHASALIDA, COUNT(*) AS N
CON> FROM CCP_ARBOLPRECIOS
CON> GROUP BY IDCATPRO, CLAVE, FECHASALIDA
CON> HAVING COUNT(*) > 3
CON> ORDER BY 4 DESC;
IDCATPRO CLAVE
FECHASALIDA N
===================== =================================
= =========== ============
-8117087563480258264 D83,,7
2007-06-01 23
7233994484437242593 D83,,7
2007-05-28 21
7233994484437242593 D83,,7
2007-05-29 21
7233994484437242593 D83,,7
2007-06-01 21
7233994484437242593 D83,,7
2007-06-02 21
7233994484437242593 D83,,7
2007-05-26 20
-2027657964566560373 D83,,7
2007-06-01 20
-5829823685140873563 D83,,7
2007-05-28 20
-5829823685140873563 D83,,7
2007-05-29 20
-4008117318041927914 D83,,7
2007-05-28 20
-4008117318041927914 D83,,7
2007-05-29 20
4773902079884594999 D83,,7
2007-05-28 20
4773902079884594999 D83,,7
2007-05-29 20
-5829823685140873563 D83,,7
2007-05-26 19
-4008117318041927914 D83,,7
2007-05-26 19
4773902079884594999 D83,,7
2007-05-26 19
-8117087563480258264 D83,,7
2007-06-05 8
-8117087563480258264 D83,,7
2007-06-08 8
-8117087563480258264 D83,,7
2007-06-12 8
7233994484437242593 D83,,7
2007-06-11 7
(many more cases .......)
SQL> SELECT * FROM CCP_ARBOLPRECIOS
CON> WHERE IDCATPRO= -8117087563480258264
CON> AND CLAVE='D83,,7'
CON> AND FECHASALIDA='2007-06-01';
IDCATPRO CLAVE
FECHASALIDA PRECIOS
=====================
======================================================
= =========== =================
-8117087563480258264 D83,,7
2007-06-01 82:e8c23
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsd="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:6a1640
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:90128e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:ab1e16
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bb6d1e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bc054d
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bc134e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bc43da
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bd4764
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bd4fcb
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:be7222
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:be84d0
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:be959e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bf689c
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bfd3f7
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bfd993
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c1b2e8
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c1cdfb
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c1d751
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c1e90e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
IDCATPRO CLAVE
FECHASALIDA PRECIOS
=====================
======================================================
= =========== =================
-8117087563480258264 D83,,7
2007-06-01 82:c276f6
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c292f8
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c3e132
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
SQL>
(blob fields contain xml that we have truncated)
Any idea how could have happened ?
We kept a copy of the database, so if you want more information we can
provide it.
Since the database contains only cache information, we started with a
new database and everything worked. This situation has not happen again.
Jordi.
Jordi Puigsegur Figueras
jordi.puigsegur@...
<mailto:jordi.puigsegur@...> (+34) 971 788 206 ext. 1852
(+34) 971 788 208 fax
www.infomallorca.es <http://www.infomallorca.es/>
[Non-text portions of this message have been removed]
We have a very strange situation with a FireBird database: a table with
a PrimaryKey has duplicate values.
The table is defined as:
SQL> CONNECT D:\FIREBIRD\CORRUPT.FDB USER SYSDBA PASSWORD masterkey;
Database: D:\FIREBIRD\CORRUPT.FDB, User: SYSDBA
SQL> SHOW TABLE CCP_ARBOLPRECIOS;
IDCATPRO NUMERIC(18, 0) Not Null
CLAVE VARCHAR(100) Not Null
FECHASALIDA DATE Not Null
PRECIOS BLOB segment 80, subtype TEXT Nullable
CONSTRAINT INTEG_9:
Primary key (IDCATPRO, CLAVE, FECHASALIDA)
SQL> SHOW INDICES;
RDB$PRIMARY3 UNIQUE INDEX ON CCP_ARBOLPRECIOS(IDCATPRO, CLAVE,
FECHASALIDA)
RDB$PRIMARY2 UNIQUE INDEX ON CCP_ARBOLPRODUCTOS(IDCATPRO)
But id does have duplicates:
SQL> SELECT IDCATPRO, CLAVE, FECHASALIDA, COUNT(*) AS N
CON> FROM CCP_ARBOLPRECIOS
CON> GROUP BY IDCATPRO, CLAVE, FECHASALIDA
CON> HAVING COUNT(*) > 3
CON> ORDER BY 4 DESC;
IDCATPRO CLAVE
FECHASALIDA N
===================== =================================
= =========== ============
-8117087563480258264 D83,,7
2007-06-01 23
7233994484437242593 D83,,7
2007-05-28 21
7233994484437242593 D83,,7
2007-05-29 21
7233994484437242593 D83,,7
2007-06-01 21
7233994484437242593 D83,,7
2007-06-02 21
7233994484437242593 D83,,7
2007-05-26 20
-2027657964566560373 D83,,7
2007-06-01 20
-5829823685140873563 D83,,7
2007-05-28 20
-5829823685140873563 D83,,7
2007-05-29 20
-4008117318041927914 D83,,7
2007-05-28 20
-4008117318041927914 D83,,7
2007-05-29 20
4773902079884594999 D83,,7
2007-05-28 20
4773902079884594999 D83,,7
2007-05-29 20
-5829823685140873563 D83,,7
2007-05-26 19
-4008117318041927914 D83,,7
2007-05-26 19
4773902079884594999 D83,,7
2007-05-26 19
-8117087563480258264 D83,,7
2007-06-05 8
-8117087563480258264 D83,,7
2007-06-08 8
-8117087563480258264 D83,,7
2007-06-12 8
7233994484437242593 D83,,7
2007-06-11 7
(many more cases .......)
SQL> SELECT * FROM CCP_ARBOLPRECIOS
CON> WHERE IDCATPRO= -8117087563480258264
CON> AND CLAVE='D83,,7'
CON> AND FECHASALIDA='2007-06-01';
IDCATPRO CLAVE
FECHASALIDA PRECIOS
=====================
======================================================
= =========== =================
-8117087563480258264 D83,,7
2007-06-01 82:e8c23
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsd="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:6a1640
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:90128e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:ab1e16
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bb6d1e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bc054d
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bc134e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bc43da
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bd4764
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bd4fcb
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:be7222
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:be84d0
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:be959e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bf689c
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bfd3f7
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:bfd993
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c1b2e8
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c1cdfb
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c1d751
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c1e90e
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
IDCATPRO CLAVE
FECHASALIDA PRECIOS
=====================
======================================================
= =========== =================
-8117087563480258264 D83,,7
2007-06-01 82:c276f6
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c292f8
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
-8117087563480258264 D83,,7
2007-06-01 82:c3e132
========================================================================
====
PRECIOS:
<?xml version="1.0" encoding="utf-16"?><R001ArbolDePrecios
xmlns:xsi="http:/
[...]
========================================================================
====
SQL>
(blob fields contain xml that we have truncated)
Any idea how could have happened ?
We kept a copy of the database, so if you want more information we can
provide it.
Since the database contains only cache information, we started with a
new database and everything worked. This situation has not happen again.
Jordi.
Jordi Puigsegur Figueras
jordi.puigsegur@...
<mailto:jordi.puigsegur@...> (+34) 971 788 206 ext. 1852
(+34) 971 788 208 fax
www.infomallorca.es <http://www.infomallorca.es/>
[Non-text portions of this message have been removed]