[Info-ingres] When alter table goes bad...

[Martin Bowes]

Hi All,

On II 11.0.0 (a64.lnx/100) +p15330...

create table alterme (a integer4 not null not default, b ingresdate not null with default '') with nojournaling;

insert into alterme(a) values (1), (2), (3);

modify alterme to btree unique on a with page_size=8192, unique_scope=statement;

select a, b, case when b = '' then 'blank' else 'not blank' end from alterme;

alter table alterme alter column b ingresdate not null with default '19-jan-2038';

modify alterme to reconstruct;

E_QE0083 Error modifying a table.
    Associated error messages which provide more detailed information about
    the problem can be found in the error log (errlog.log)

And in the errlog we see:
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1r.c:6761           ]: Thu Sep 13 14:49:12 2018 E_DM019D_ROW_CONVERSION_FAILED    Error in converting row data to column data type.
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1r.c:6761           ]: PQuery:  modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1r.c:6761           ]: Query:   modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1r.c:6761           ]: LQuery:  alter table alterme alter column b ingresdate not null with default '19-jan-2038'
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1c.c:2191           ]: Thu Sep 13 14:49:12 2018 E_DM9445_DMR_ROW_CVT_ERROR    Row format for current version of altered table has errors.  Database test_ldbc, table alterme, owner lust.
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1c.c:2191           ]: PQuery:  modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1c.c:2191           ]: Query:   modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1c.c:2191           ]: LQuery:  alter table alterme alter column b ingresdate not null with default '19-jan-2038'
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1c.c:2193           ]: Thu Sep 13 14:49:12 2018 E_DM9450_DM1C_GET_CV_COMP Error getting a record due to either row conversion or uncompression failure. See previous errors for more details
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1c.c:2193           ]: PQuery:  modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1c.c:2193           ]: Query:   modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1c.c:2193           ]: LQuery:  alter table alterme alter column b ingresdate not null with default '19-jan-2038'
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1b.c:4709           ]: Thu Sep 13 14:49:12 2018 E_DM9261_DM1B_GET Error occurred getting a record.
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1b.c:4709           ]: PQuery:  modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1b.c:4709           ]: Query:   modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm1b.c:4709           ]: LQuery:  alter table alterme alter column b ingresdate not null with default '19-jan-2038'
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm2uuti.c:1306        ]: Thu Sep 13 14:49:12 2018 E_DM9100_LOAD_TABLE_ERROR Error occurred while loading a table for a modify, index, or load table operation.
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm2uuti.c:1306        ]: PQuery:  modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm2uuti.c:1306        ]: Query:   modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, dm2uuti.c:1306        ]: LQuery:  alter table alterme alter column b ingresdate not null with default '19-jan-2038'
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, qeus.c:2749           ]: Thu Sep 13 14:49:12 2018 E_DM0091_ERROR_MODIFYING_TABLE    Error modifying a table.
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, qeus.c:2749           ]: PQuery:  modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, qeus.c:2749           ]: Query:   modify alterme to reconstruct
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, qeus.c:2749           ]: LQuery:  alter table alterme alter column b ingresdate not null with default '19-jan-2038'
DBING1_NDPH_OX_AC_::[38763             , 30140     ,  00007f6feb6e2140, qeus.c:2749           ]: Thu Sep 13 14:49:12 2018 E_QE0083_ERROR_MODIFYING_TABLE    Error modifying a table.
Associated error messages which provide more detailed information about the problem can be found in the error log (errlog.log)

All of which was a bit of a surprise!

Martin Bowes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180913/f02aebad/attachment.html>