create table with foreign-key, referential constraint generates tdm_arkcmp core

[Julie Thai]

Create table with foreign key constraint intermittently produces tdm_arkcmp core. Script attempts to execute many ddl statements and must be run in its entirety to reproduce issue. Issue occurs more often via sqlci.

Encountered on traf_20140606_0930, 4-node cluster.

To reproduce, extract contents of attachment:
1. ./cleanup
2. ./repro_it

from log:
>>Create table T5(O int,
+>P smallint,
+>Q largeint,
+>R numeric(9,3)not null not droppable,
+>S decimal(18,9),
+>T char(20),
+>U float(52),
+>V real,
+>W double precision,
+>X timestamp,
+>Y interval year to month ,
+>Z char (12),
+>AA time not null not droppable, primary key(R,AA));

--- SQL operation complete.
>>
>>Create table T6 (O int default null,
+> P smallint default null,
+> Q largeint default null,
+> R numeric(9,3) not null ,
+> S decimal(18,9) default null,
+> T char(20) default null,
+> U float(52) default null,
+> V real default null,
+> W double precision default null,
+> X timestamp default null,
+> Y interval year to month default null ,
+> Z char (12) default null,
+> AA time not null primary key droppable)
+> attribute extent 256;

--- SQL operation complete.
>>
>>Create table T7 (R Numeric(9,3),AA Time, FOREIGN KEY(R,AA) REFERENCES T5
+> );

*** ERROR[1389] Object T5_699928485_4973 does not exist in Trafodion.

*** ERROR[8838] Unable to receive reply from Compiler, possibly caused by internal errors when compiling SQL statements, processing DDL statements, or executing the builtin stored procedures.

*** ERROR[1029] Object TRAFODION.MYSCH.T7_898119485_4973 could not be created.

*** ERROR[1029] Object TRAFODION.MYSCH.T7 could not be created.

--- SQL operation failed with errors.

From stdout:
>>Create table T7 (R Numeric(9,3),AA Time, FOREIGN KEY(R,AA) REFERENCES T5
+> );
#
# A fatal error has been detected by the Java Runtime Environment:
#
# SIGSEGV (0xb) at pc=0x00007f8bfd94da56, pid=7030, tid=140239092416096
#
# JRE version: OpenJDK Runtime Environment (7.0_55-b13) (build 1.7.0_55-mockbuild_2014_04_09_11_51-b00)
# Java VM: OpenJDK 64-Bit Server VM (24.51-b03 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C [liboptimizer.so+0x7b4a56] createConstraintInfo(desc_struct const*, QualifiedName const&, NAColumnArray const&, CheckConstraintList&, AbstractRIConstraintList&, AbstractRIConstraintList&, NAMemory*, BindWA*)+0x233
#
# Core dump written. Default location: /home/trafodion/thaiju/core or core.7030
#
# An error report file with more information is saved as:
# /tmp/jvm-7030/hs_error.log
#
# If you would like to submit a bug report, please include
# instructions on how to reproduce the bug and visit:
# http://icedtea.classpath.org/bugzilla
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#
#
# A fatal error has been detected by the Java Runtime Environment:
#
# SIGSEGV (0xb) at pc=0x00007f7ad3456a56, pid=11356, tid=140165368122976
#
# JRE version: OpenJDK Runtime Environment (7.0_55-b13) (build 1.7.0_55-mockbuild_2014_04_09_11_51-b00)
# Java VM: OpenJDK 64-Bit Server VM (24.51-b03 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C [liboptimizer.so+0x7b4a56] createConstraintInfo(desc_struct const*, QualifiedName const&, NAColumnArray const&, CheckConstraintList&, AbstractRIConstraintList&, AbstractRIConstraintList&, NAMemory*, BindWA*)+0x233
#
# Core dump written. Default location: /home/trafodion/thaiju/core or core.11356
#
# An error report file with more information is saved as:
# /tmp/jvm-11356/hs_error.log
#
# If you would like to submit a bug report, please include
# instructions on how to reproduce the bug and visit:
# http://icedtea.classpath.org/bugzilla
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#
#
# A fatal error has been detected by the Java Runtime Environment:
#
# SIGSEGV (0xb) at pc=0x00007f21f49dea56, pid=11458, tid=139783675481696
#
# JRE version: OpenJDK Runtime Environment (7.0_55-b13) (build 1.7.0_55-mockbuild_2014_04_09_11_51-b00)
# Java VM: OpenJDK 64-Bit Server VM (24.51-b03 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C [liboptimizer.so+0x7b4a56] createConstraintInfo(desc_struct const*, QualifiedName const&, NAColumnArray const&, CheckConstraintList&, AbstractRIConstraintList&, AbstractRIConstraintList&, NAMemory*, BindWA*)+0x233
#
# Core dump written. Default location: /home/trafodion/thaiju/core or core.11458
#
# An error report file with more information is saved as:
# /tmp/jvm-11458/hs_error.log
#
# If you would like to submit a bug report, please include
# instructions on how to reproduce the bug and visit:
# http://icedtea.classpath.org/bugzilla
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#
#
# A fatal error has been detected by the Java Runtime Environment:
#
# SIGSEGV (0xb) at pc=0x00007fd3876aba56, pid=11645, tid=140546347589216
#
# JRE version: OpenJDK Runtime Environment (7.0_55-b13) (build 1.7.0_55-mockbuild_2014_04_09_11_51-b00)
# Java VM: OpenJDK 64-Bit Server VM (24.51-b03 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C [liboptimizer.so+0x7b4a56] createConstraintInfo(desc_struct const*, QualifiedName const&, NAColumnArray const&, CheckConstraintList&, AbstractRIConstraintList&, AbstractRIConstraintList&, NAMemory*, BindWA*)+0x233
#
# Core dump written. Default location: /home/trafodion/thaiju/core or core.11645
#
# An error report file with more information is saved as:
# /tmp/jvm-11645/hs_error.log
#
# If you would like to submit a bug report, please include
# instructions on how to reproduce the bug and visit:
# http://icedtea.classpath.org/bugzilla
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#

*** ERROR[1389] Object T5_699928485_4973 does not exist in Trafodion.

*** ERROR[8838] Unable to receive reply from Compiler, possibly caused by internal errors when compiling SQL statements, processing DDL statements, or executing the builtin stored procedures.

*** ERROR[1029] Object TRAFODION.MYSCH.T7_898119485_4973 could not be created.

*** ERROR[1029] Object TRAFODION.MYSCH.T7 could not be created.

--- SQL operation failed with errors.

Stacktrace from core file:
#0 0x0000003fc0832925 in raise () from /lib64/libc.so.6
#1 0x0000003fc0834105 in abort () from /lib64/libc.so.6
#2 0x00007f8bf9abe8c5 in ?? () from /usr/lib/jvm/java/jre/lib/amd64/server/libjvm.so
#3 0x00007f8bf9c2c78f in ?? () from /usr/lib/jvm/java/jre/lib/amd64/server/libjvm.so
#4 0x00007f8bf9ac3a92 in JVM_handle_linux_signal ()
   from /usr/lib/jvm/java/jre/lib/amd64/server/libjvm.so
#5 <signal handler called>
#6 0x00007f8bfd94da56 in createConstraintInfo (table_desc=0x7f8be645a5f8, tableQualName=...,
    columnArray=..., checkConstraints=..., uniqueConstraints=..., refConstraints=..., heap=
    0x7f8be6595ca0, bindWA=0x7fff19eff2d0) at ../optimizer/NATable.cpp:4063
#7 0x00007f8bfd94f8b9 in NATable::NATable (this=0x7f8be645add8, bindWA=0x7fff19eff2d0,
    corrName=..., heap=0x7f8be6595ca0, inTableDesc=0x7f8be645a5f8)
    at ../optimizer/NATable.cpp:4531
#8 0x00007f8bfd9586b9 in NATableDB::get (this=0x7f8befc7b8d0, corrName=...,
    bindWA=0x7fff19eff2d0, inTableDescStruct=0x0) at ../optimizer/NATable.cpp:6975
#9 0x00007f8bfd6c0aea in BindWA::getNATable (this=0x7fff19eff2d0, corrName=...,
    catmanCollectTableUsages=1, inTableDescStruct=0x0) at ../optimizer/BindRelExpr.cpp:1443
#10 0x00007f8c0092e300 in CmpSeabaseDDL::createSeabaseIndex (this=0x7fff19f01880,
    createIndexNode=0x7f8be65e4510, currCatName=..., currSchName=...)
    at ../sqlcomp/CmpSeabaseDDLindex.cpp:466
#11 0x00007f8c00903b62 in CmpSeabaseDDL::executeSeabaseDDL (this=0x7fff19f01880, ddlExpr=
    0x7f8be6596288, ddlNode=0x7f8be65e4510, currCatName=..., currSchName=...)
    at ../sqlcomp/CmpSeabaseDDLcommon.cpp:4174
#12 0x00007f8bfca81ce4 in CmpStatement::process (this=0x7f8be65ecc40, statement=...)
    at ../arkcmp/CmpStatement.cpp:909
#13 0x00007f8bfca6e4c5 in ExCmpMessage::actOnReceive (this=0x7fff19f02bc0)
    at ../arkcmp/CmpConnection.cpp:405
#14 0x00007f8bffe60fa5 in IpcMessageStream::internalActOnReceive (this=0x7fff19f02bc0,
    buffer=0x211d0f0, connection=0xf2c940) at ../common/Ipc.cpp:3553
#15 0x00007f8bffe5eecb in IpcMessageBuffer::callReceiveCallback (this=0x211d0f0, conn=0xf2c940)
    at ../common/Ipc.cpp:2427
#16 0x00007f8bffe72fb4 in GuaConnectionToClient::acceptBuffer (this=0xf2c940,
    buffer=0x211d0f0, receivedDataLength=472) at ../common/IpcGuardian.cpp:2467
#17 0x00007f8bffe749cb in GuaReceiveControlConnection::wait (this=0xf1ab80, timeout=-1,
    eventConsumed=0x0, ipcAwaitiox=0x0) at ../common/IpcGuardian.cpp:3164
#18 0x00007f8bffe723d2 in GuaConnectionToClient::wait (this=0xf2c940, timeout=-1,
    eventConsumed=0x0, ipcAwaitiox=0x0) at ../common/IpcGuardian.cpp:2136
#19 0x00007f8bffe5d35d in IpcSetOfConnections::waitOnSet (this=0x7fff19f02d60, timeout=-1,
    calledByESP=0, timedout=0x0) at ../common/Ipc.cpp:1709
#20 0x00007f8bffe605eb in IpcMessageStream::waitOnMsgStream (this=0x7fff19f02bc0, timeout=-1)
    at ../common/Ipc.cpp:3272
#21 0x00007f8bffe60559 in IpcMessageStream::receive (this=0x7fff19f02bc0, waited=1)
    at ../common/Ipc.cpp:3254
#22 0x0000000000407b8c in main (argc=2, argv=0x7fff19f03358) at ../bin/arkcmp.cpp:304

[Pavani Puppala (pavani-puppala)]

This bug is mostly the same problem as
https://bugs.launchpad.net/trafodion/+bug/1328271 --- got mxosrvr core while allowing constraint as a reference object to be dropped

This bug is not reproducible easily but bug 1328271 is reproducible easily, so it is better to debug the other one to find the root cause.

[Pavani Puppala (pavani-puppala)]

After discussing with Julie lowered the priority to HIGH as the problem does not happen always.

[Roberta Marton (roberta-marton)]

Encountered a similar problem while testing grant and revoke with referential integrity constraints.
Here is some background on the problem and what was observed:

An RI constraint is a relationship between a set of columns on one table with a set of columns on another table. Each set of columns must be defined as an unique constraint (which include primary key constraints).
Relationships are stored through the constraints not their underlying tables.

 for example:

      create table dept (dept_no int not null primary key, ... );
      create table empl (empl_no int not null primary key, dept_no int, ... );
      alter table empl add constraint empl_dept foreign key references dept;

 empl_dept is the name of the RI constraint
 during the alter table empl request, a unique constraint is silently added

Now:

empl - has three constraints
  primary key constraint
  unique constraint on dept_no
  referential constraint called empl_dept

dept - has one constraint
   primary key constraint on dept_no

What is observed is that the system defined unique constraint automatically defined on the referencing table (empl) is missing.
That is, the REF_CONSTRAINTS and UNIQUE_REF_CONSTR_USAGE tables contain the references between empl & dept but the corresponding constraint identified by the UNIQUE_CONTRAINT_UID is not longer defined in the metadata (TABLE_CONSTRAINTS and OBJECTS).

The method CmpSeabaseDDL::getSeabaseSequenceDesc reads the metadata to get relationship information:

select trim(O.catalog_name || '.' || '\"' ||
            O.schema_name || '\"' || '.' || '\"' ||
            O.object_name || '\"' ) constr_name,
       trim(O2.catalog_name || '.' || '\"' ||
            O2.schema_name || '\"' || '.' || '\"' ||
            O2.object_name || '\"' ) table_name
  from TRAFODION."_MD_".REF_CONSTRAINTS R,
       TRAFODION."_MD_".OBJECTS O,
       TRAFODION."_MD_".OBJECTS O2,
       TRAFODION."_MD_".TABLE_CONSTRAINTS T
  where O.object_uid = R.unique_constraint_uid and
         O2.object_uid = T.table_uid and
         T.constraint_uid = R.unique_constraint_uid and
         R.ref_constraint_uid = <uid of ref_constraint, e.g. empl_dept>

This query returns the table name (dept) and constraint name (dept's primary key) that the empl table references.
But since the metadata is missing from TABLE_CONSTRAINTS for the empl's unique constraint, this query returns no rows. Therefore data is not set up in the descriptor structure (it is NULL). Later when code references this data, a core file is generated - accessing a NULL pointer.

The code in CmpSeabaseDDL::getSeabaseSequenceDesc should check to see if any rows are returned. If not, an internal error should be generated. It would be good to support a cleanup option for drop table to handle this issue and similar issues going forward.

Will continue to see how the metadata got in this state in the first place ...

[Mike Hanlon (mike-hanlon)]

I cannot reproduce this in a workspace with the new QI feature. But I can reproduce it regularly in a (recent) workspace without the QI feature. Since the QI feature was designed to prevent this type of anomaly, I think we ought to close this bug and consider it fixed.