Re: issue wtih foreign keys

[Qifan Chen]

Hi Dave and Eric,


I wonder if providing Mathieu with the latest Trafodion is an option.


I did not recall similar bugs in this area.


Thanks --Qifan

________________________________
From: Dave Birdsall <dave.birds...@esgyn.com>
Sent: Thursday, March 16, 2017 11:33:21 AM
To: user@trafodion.incubator.apache.org
Subject: RE: issue wtih foreign keys

So, evidently this is a bug that has been fixed?

I wonder if we can find a JIRA reference.

From: Eric Owhadi [mailto:eric.owh...@esgyn.com]
Sent: Thursday, March 16, 2017 8:55 AM
To: user@trafodion.incubator.apache.org
Subject: RE: issue wtih foreign keys

With trafci on latest of esgynDB (derivative of trafodion), both with 
autocommit on or off, this set of create table work fine.
Eric

From: Dave Birdsall [mailto:dave.birds...@esgyn.com]
Sent: Thursday, March 16, 2017 10:33 AM
To: 
user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org>
Subject: RE: issue wtih foreign keys

Hi Eric & Mathieu,

I tried Mathieu’s three CREATE statements in sqlci twice, once with autocommit 
on and (after dropping the tables) once with autocommit off, and it worked both 
times for me. But that’s on the latest Trafodion (master branch).

I haven’t tried it in trafci… maybe the behavior is different?

Dave

From: Eric Owhadi [mailto:eric.owh...@esgyn.com]
Sent: Thursday, March 16, 2017 8:25 AM
To: 
user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org>
Subject: RE: issue wtih foreign keys

Are you saying that autocommit is off, and you are executing the 3 ddl 
statement then manual commit? Then you see  the issue?
Or autocommit is on, and you see the issue?
When you perform the same ddl operation using command line with sqlci, you see 
no problem right?
Eric

From: mathieu ferlay [mailto:mfer...@gnubila.fr]
Sent: Thursday, March 16, 2017 9:46 AM
To: 
user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org>
Subject: RE: issue wtih foreign keys

Hi again,
I don’t know if explain my process will help but maybe:
    try (Connection connection = DriverManager.getConnection(dbUrl, dbUser, 
dbPwd);
        BufferedReader reader = new BufferedReader(
            new FileReader("create-tables.sql"));) {
     ScriptRunner runner = new ScriptRunner(connection, false, true);
      runner.runScript(reader);
      connection.close();
    } catch (Exception e) {
      e.printStackTrace();
      throw e;
    }

In the sql file, I have all my requests to create all my tables and the script 
runner execute one statement by request.
I  start to think that’s due to the autocommit and so by consequence the 
“database” access.

Regards,
Mathieu

De : mathieu ferlay [mailto:mfer...@gnubila.fr]
Envoyé : jeudi 16 mars 2017 15:02
À : 
user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org>
Objet : RE: issue wtih foreign keys

Hi,
I have change my request by using alter table do it but I still have some 
“lost” of process. It’s look like an infinite loop inside the execute statement.
I don’t know what it could causes that.

Regards,
Mathieu

De : mathieu ferlay [mailto:mfer...@gnubila.fr]
Envoyé : jeudi 16 mars 2017 10:05
À : 
user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org>
Objet : issue wtih foreign keys

Hi, i’ll try to create the following table C. I assume that tables A & B are 
executed before C.  But when I try to execute the statement for the table C, 
the process nerver back from the statement.execute(request). Furthermore, I 
obtain also the following trace when I force the commit with 
connection.commit();
' (line 120): *** 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. [2017-03-16 09:02:56]
Could you tell me which kind of reasons could provoked it.



CREATE TABLE IF NOT EXISTS A (
  AID VARCHAR(255) CHARACTER SET utf8 COLLATE DEFAULT  NOT NULL,
  Description  VARCHAR(255) CHARACTER SET utf8 COLLATE DEFAULT  NULL,
  PRIMARY KEY(AID)
)

CREATE TABLE IF NOT EXISTS B (
  Name VARCHAR(255) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  FirstName VARCHAR(255) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  BID VARCHAR(255) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  PRIMARY KEY(BID)
)



CREATE TABLE IF NOT EXISTS C (
  BID VARCHAR(255) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  AID VARCHAR(255) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  PRIMARY KEY(AID,BID),
  FOREIGN KEY(AID)
    REFERENCES A(AID),
  FOREIGN KEY(BID)
    REFERENCES B(BID)
)


Regards
Mathieu FERLAY