[#CORE-997] Database-Update 1.5 to 2.0 fails - Firebird RDBMS Issue Tracker

Firebird Core

Database-Update 1.5 to 2.0 fails

Created: 12/Nov/06 01:15 PM Updated: 26/Jan/09 08:08 AM

Component/s:

None

Affects Version/s:

None

Fix Version/s:

2.0.1, 2.1 Alpha 1

Time Tracking:

Not Specified

File Attachments:

BUG2.zip (43 kB)

Environment:

« Hide

"Old" Server: Windows XP SP2 german, Firebird 1.5.3
"New" Server: Windows XP SP2 german, Firebird 2.0

Issue Links:

Duplicate

This issue is duplicated by:

~~CORE-1049~~ Server crash on create index

Relate

This issue relate to:

~~QA-20~~ Test for CORE-997

Description

« Hide

On the "old" system the database has been backed up with gbak without any errors. The attempt to restore the database on the "new " system failed with an error "index size too big". A restore without activating the indices worked without warnings. Here is the DDL of the table without the index:

/******************************************************************************/
/**** Generated by IBExpert 14.11.2006 09:40:33 ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES WIN1252;

/******************************************************************************/
/**** Tables ****/
/******************************************************************************/

CREATE TABLE VSSCHNIT (
    VSS_ART VARCHAR(10) NOT NULL COLLATE PXW_INTL,
    VSS_BEMERKUNG VARCHAR(30) default null COLLATE PXW_INTL,
    VSS_PROGNAM VARCHAR(30) default null COLLATE PXW_INTL,
    VSS_WODAT VARCHAR(100) default null COLLATE PXW_INTL,
    VSS_GLVOBNR INTEGER default null,
    VSS_SCHREIBW VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_ANREDE VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_STRASSE_ORT VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_HDE_TAB VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_HDE_TAB_PRO VARCHAR(8) default null COLLATE PXW_INTL,
    VSS_HDE_TRENN VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_HDE_OERE INTEGER default null,
    VSS_HDE_PRIV INTEGER default null,
    VSS_HDE_ALLG INTEGER default null,
    VSS_SB_NR INTEGER default null,
    VSS_VOA_VB INTEGER default null,
    VSS_VOA_VB_WDV SMALLINT default null,
    VSS_DRU_VB_BESCHEID VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_VOA_AHE INTEGER default null,
    VSS_VOA_AHE_WDV SMALLINT default null,
    VSS_DRU_AHE_BESCHEID VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_AHE_AUTO_GLAU VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_POS_8_SUM VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_UEAZ_AUTO VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_MAHNGEB_SOLL VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_SAUM_SOLL VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_SAUM_WEITER VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_ZAHL_ZURUECK VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_ZAHL_RU_DTA VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_ZAHL_RU_ART SMALLINT default null,
    VSS_ZAHL_D_UEBER VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_ZAHL_DU_STATIS VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_ZAHL_DU_VB VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_SZ_DATUM_JN VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_SZ_BIS_DATUM SMALLINT default null,
    VSS_VOA_PRIV INTEGER default null,
    VSS_VOA_PRIV_WDV SMALLINT default null,
    VSS_DRU_PRIV_BESCHEID VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_MAHN_GEBTAB SMALLINT default null,
    VSS_MAHN_ERMIT VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_SZ_NEU VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_MG_DATUM_JN VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_VOA_VB_NURNF INTEGER default null,
    VSS_VOA_VB_NURNF_WDV SMALLINT default null,
    VSS_DRU_VB_NURNF_BESCH VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_VOA_AHE_NURNF INTEGER default null,
    VSS_VOA_AHE_NURNF_WDV SMALLINT default null,
    VSS_DRU_AHE_NURNF_BESCH VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_VOA_PRIV_NURNF INTEGER default null,
    VSS_VOA_PRIV_NURNF_WDV SMALLINT default null,
    VSS_DRU_PRIV_NURNF_BESCH VARCHAR(1) default null COLLATE PXW_INTL,
    VSS_SBZUO SMALLINT default null,
    VSS_SOLLSTNF_VAR VARCHAR(20) default null COLLATE PXW_INTL
);

It is impossible to create an unique index on the field VSS_ART if the data in this field contains some german "Umlaute".

If I start with an empty database in Firebird 2.0, create the table with this DDL and fill it with data. I can create an unique index on VSS_ART without any errors.

Description

On the "old" system the database has been backed up with gbak without any errors. The attempt to restore the database on the "new " system failed with an error "index size too big". A restore without activating the indices worked without warnings. Here is the DDL of the table without the index: /******************************************************************************/ /**** Generated by IBExpert 14.11.2006 09:40:33 ****/ /******************************************************************************/ SET SQL DIALECT 3; SET NAMES WIN1252; /******************************************************************************/ /**** Tables ****/ /******************************************************************************/ CREATE TABLE VSSCHNIT ( VSS_ART VARCHAR(10) NOT NULL COLLATE PXW_INTL, VSS_BEMERKUNG VARCHAR(30) default null COLLATE PXW_INTL, VSS_PROGNAM VARCHAR(30) default null COLLATE PXW_INTL, VSS_WODAT VARCHAR(100) default null COLLATE PXW_INTL, VSS_GLVOBNR INTEGER default null, VSS_SCHREIBW VARCHAR(1) default null COLLATE PXW_INTL, VSS_ANREDE VARCHAR(1) default null COLLATE PXW_INTL, VSS_STRASSE_ORT VARCHAR(1) default null COLLATE PXW_INTL, VSS_HDE_TAB VARCHAR(1) default null COLLATE PXW_INTL, VSS_HDE_TAB_PRO VARCHAR(8) default null COLLATE PXW_INTL, VSS_HDE_TRENN VARCHAR(1) default null COLLATE PXW_INTL, VSS_HDE_OERE INTEGER default null, VSS_HDE_PRIV INTEGER default null, VSS_HDE_ALLG INTEGER default null, VSS_SB_NR INTEGER default null, VSS_VOA_VB INTEGER default null, VSS_VOA_VB_WDV SMALLINT default null, VSS_DRU_VB_BESCHEID VARCHAR(1) default null COLLATE PXW_INTL, VSS_VOA_AHE INTEGER default null, VSS_VOA_AHE_WDV SMALLINT default null, VSS_DRU_AHE_BESCHEID VARCHAR(1) default null COLLATE PXW_INTL, VSS_AHE_AUTO_GLAU VARCHAR(1) default null COLLATE PXW_INTL, VSS_POS_8_SUM VARCHAR(1) default null COLLATE PXW_INTL, VSS_UEAZ_AUTO VARCHAR(1) default null COLLATE PXW_INTL, VSS_MAHNGEB_SOLL VARCHAR(1) default null COLLATE PXW_INTL, VSS_SAUM_SOLL VARCHAR(1) default null COLLATE PXW_INTL, VSS_SAUM_WEITER VARCHAR(1) default null COLLATE PXW_INTL, VSS_ZAHL_ZURUECK VARCHAR(1) default null COLLATE PXW_INTL, VSS_ZAHL_RU_DTA VARCHAR(1) default null COLLATE PXW_INTL, VSS_ZAHL_RU_ART SMALLINT default null, VSS_ZAHL_D_UEBER VARCHAR(1) default null COLLATE PXW_INTL, VSS_ZAHL_DU_STATIS VARCHAR(1) default null COLLATE PXW_INTL, VSS_ZAHL_DU_VB VARCHAR(1) default null COLLATE PXW_INTL, VSS_SZ_DATUM_JN VARCHAR(1) default null COLLATE PXW_INTL, VSS_SZ_BIS_DATUM SMALLINT default null, VSS_VOA_PRIV INTEGER default null, VSS_VOA_PRIV_WDV SMALLINT default null, VSS_DRU_PRIV_BESCHEID VARCHAR(1) default null COLLATE PXW_INTL, VSS_MAHN_GEBTAB SMALLINT default null, VSS_MAHN_ERMIT VARCHAR(1) default null COLLATE PXW_INTL, VSS_SZ_NEU VARCHAR(1) default null COLLATE PXW_INTL, VSS_MG_DATUM_JN VARCHAR(1) default null COLLATE PXW_INTL, VSS_VOA_VB_NURNF INTEGER default null, VSS_VOA_VB_NURNF_WDV SMALLINT default null, VSS_DRU_VB_NURNF_BESCH VARCHAR(1) default null COLLATE PXW_INTL, VSS_VOA_AHE_NURNF INTEGER default null, VSS_VOA_AHE_NURNF_WDV SMALLINT default null, VSS_DRU_AHE_NURNF_BESCH VARCHAR(1) default null COLLATE PXW_INTL, VSS_VOA_PRIV_NURNF INTEGER default null, VSS_VOA_PRIV_NURNF_WDV SMALLINT default null, VSS_DRU_PRIV_NURNF_BESCH VARCHAR(1) default null COLLATE PXW_INTL, VSS_SBZUO SMALLINT default null, VSS_SOLLSTNF_VAR VARCHAR(20) default null COLLATE PXW_INTL ); It is impossible to create an unique index on the field VSS_ART if the data in this field contains some german "Umlaute". If I start with an empty database in Firebird 2.0, create the table with this DDL and fill it with data. I can create an unique index on VSS_ART without any errors.

Show »

All

Comments

Work Log

Change History

Version Control

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Vlad Khorsun added a comment - 17/Nov/06 12:43 AM

If you can create your index on new database but can't restore existing backup then i guess you have created old database with small page size.
Try change page size at restore using gbak's command line switch : -page_size 4096 ...

[ Permalink | « Hide ]

Ulrich Wagner added a comment - 18/Nov/06 12:39 PM

This is a zipped Firebird 2.0 database. It has been created by an gbak operation from an Firebird 1.5 database. I can't create an unique index on VSS_ART as long as german "Umlaute" in this column.

[ Permalink | « Hide ]

Ulrich Wagner added a comment - 18/Nov/06 12:46 PM

The Firebird 1.5 database had an page size of 8192. I have restored the database with an page size of 8192.

[ Permalink | « Hide ]

Adriano dos Santos Fernandes added a comment - 18/Nov/06 05:10 PM

An old bug become more visible.

Try this in 1.5.3:

create table t (c varchar(4) character set win1252 collate pxw_intl);
insert into t values ('????');
commit;
create unique index t_c on t (c);

Statement failed, SQLCODE = -902

internal gds software consistency check (index key too big (174))
Statement failed, SQLCODE = -902

internal gds software consistency check (can't continue after bugcheck)

[ Permalink | « Hide ]

Pavel Cisar added a comment - 26/Apr/07 11:41 AM

Reopened to update ticket information.