date:20170316

RE: issue wtih foreign keys

2017-03-16 Thread Sandhya Sundaresan

Hi,
FYI - I will be staging a 2.1 version of Trafodion in a couple of days to the 
dev staging site. Not quite ready for today - most likely Monday. 2.1 version 
has 300+ fixes.
Sandhya

From: Qifan Chen [mailto:qifan.c...@esgyn.com]
Sent: Thursday, March 16, 2017 9:50 AM
To: user@trafodion.incubator.apache.org
Subject: Re: issue wtih foreign keys

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 >
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
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
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
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
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
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

RE: issue wtih foreign keys

2017-03-16 Thread Dave Birdsall

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
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
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
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
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
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

RE: issue wtih foreign keys

2017-03-16 Thread Eric Owhadi

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
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
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
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
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
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

RE: issue wtih foreign keys

2017-03-16 Thread mathieu ferlay

Hi Dave,

Im using the version of the docker image trafodion\incubator and I believe
its the 2.0.0 .

 

Regards,

Mathieu

 

De : Dave Birdsall [mailto:dave.birds...@esgyn.com] 
Envoyé : jeudi 16 mars 2017 16:26
À : user@trafodion.incubator.apache.org
Objet : RE: issue wtih foreign keys

 

Hi Mathieu,

 

Error 8838 usually indicates a core in the tdm_arkcmp process. So youre
likely running into a bug. What version of Trafodion are you using? (What
banner comes up when you enter, sqlci?)

 

I tried your CREATE statements in the latest version of Trafodion and they
worked for me.

 

Dave

 

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

 

Hi again, 

I dont 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 thats 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
 
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. Its look like an infinite loop inside the execute
statement.

I dont 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
 
Objet : issue wtih foreign keys

 

Hi, ill 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

RE: issue wtih foreign keys

2017-03-16 Thread mathieu ferlay

Hi Eric,

 

I have put this command SET TRANSACTION AUTOCOMMIT ON; at the entry of my
script but Im not fully sure that it have no commit in the java part. Im
currently checking on that.

 

With sqlci, I have not already did it yet, but I have no issue by passing
each request one by one.

 

Regards,

Mathieu

 

De : Eric Owhadi [mailto:eric.owh...@esgyn.com] 
Envoyé : jeudi 16 mars 2017 16:25
À : user@trafodion.incubator.apache.org
Objet : 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
 
Subject: RE: issue wtih foreign keys

 

Hi again, 

I dont 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 thats 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
 
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. Its look like an infinite loop inside the execute
statement.

I dont 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
 
Objet : issue wtih foreign keys

 

Hi, ill 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

RE: issue wtih foreign keys

2017-03-16 Thread Dave Birdsall

Hi Mathieu,

Error 8838 usually indicates a core in the tdm_arkcmp process. So you're likely 
running into a bug. What version of Trafodion are you using? (What banner comes 
up when you enter, sqlci?)

I tried your CREATE statements in the latest version of Trafodion and they 
worked for me.

Dave

From: mathieu ferlay [mailto:mfer...@gnubila.fr]
Sent: Thursday, March 16, 2017 7:46 AM
To: 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
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
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

RE: issue wtih foreign keys

2017-03-16 Thread Eric Owhadi

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
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
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
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

RE: issue with a request

2017-03-16 Thread mathieu ferlay

Thanks Eric,

For the moment i’ll just try to migrate our script from sql to trafodion but I 
notice it for our second phase corresponding to the optimization.

Regards,

Mathieu

 

De : Eric Owhadi [mailto:eric.owh...@esgyn.com] 
Envoyé : jeudi 16 mars 2017 14:36
À : user@trafodion.incubator.apache.org
Objet : RE: issue with a request

 

Hi Mathieu,

Just a small comment: you are using UTF8 for storing UUID, on a VARCHAR.

If this is just for testing functionality that’s OK.

But for real production and high volume, if you want to optimize, please 
consider:

CHAR(36) CHARACTER SET ISO88591

Trafodion would reserve 4 times more space for the key in UTF8 compared to 
ISO88591 plus 2 bytes for variable length of varchar that is not needed since 
UUID are always 36 char long. 

Hope this helps,
Eric

 

From: mathieu ferlay [mailto:mfer...@gnubila.fr] 
Sent: Thursday, March 16, 2017 2:46 AM
To: user@trafodion.incubator.apache.org 
 
Subject: RE: issue with a request

 

Thanks Benny.

 

Best regard

 

FERLAY Mathieu

 

 

De : Wang, Ai-Min (Benny) [mailto:aimin.w...@esgyn.cn] 
Envoyé : jeudi 16 mars 2017 08:42
À : user@trafodion.incubator.apache.org 
 
Objet : RE: issue with a request

 

Hi:

 

Pleaes use following sql， UUID  is reserved word and please use “” to enclose 
it：

 

CREATE TABLE IF NOT EXISTS Token (

  "UUID" VARCHAR(36) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,

  Expiration DATE NOT NULL,

  SOPInstanceUUID VARCHAR(64) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,

  PRIMARY KEY ("UUID")

);

 

 

Best Regrard！

 

Benny/Wang Aimin

MP：13501196050

Mail：aimin.w...@esgyn.cn  

 

From: mathieu ferlay [mailto:mfer...@gnubila.fr] 
Sent: Thursday, March 16, 2017 3:39 PM
To: user@trafodion.incubator.apache.org 
 
Subject: issue with a request

 

Hi everybody, i try to load these following lines:

 

CREATE TABLE IF NOT EXISTS Token (

  UUID VARCHAR(36) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,

  Expiration DATE NOT NULL,

  SOPInstanceUUID VARCHAR(64) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,

  PRIMARY KEY (UUID)

);

 

But I obtain systematically a mistake on UUID. Do you know how to success it 
with this column’s name?

 

Thanks,

Regards

 

FERLAY Mathieu

RE: issue with a request

2017-03-16 Thread Eric Owhadi

Hi Mathieu,
Just a small comment: you are using UTF8 for storing UUID, on a VARCHAR.
If this is just for testing functionality that’s OK.
But for real production and high volume, if you want to optimize, please 
consider:
CHAR(36) CHARACTER SET ISO88591
Trafodion would reserve 4 times more space for the key in UTF8 compared to 
ISO88591 plus 2 bytes for variable length of varchar that is not needed since 
UUID are always 36 char long.
Hope this helps,
Eric

From: mathieu ferlay [mailto:mfer...@gnubila.fr]
Sent: Thursday, March 16, 2017 2:46 AM
To: user@trafodion.incubator.apache.org
Subject: RE: issue with a request

Thanks Benny.

Best regard

FERLAY Mathieu


De : Wang, Ai-Min (Benny) [mailto:aimin.w...@esgyn.cn]
Envoyé : jeudi 16 mars 2017 08:42
À : 
user@trafodion.incubator.apache.org
Objet : RE: issue with a request

Hi:

Pleaes use following sql， UUID  is reserved word and please use “” to enclose 
it：

CREATE TABLE IF NOT EXISTS Token (
  "UUID" VARCHAR(36) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  Expiration DATE NOT NULL,
  SOPInstanceUUID VARCHAR(64) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  PRIMARY KEY ("UUID")
);


Best Regrard！

Benny/Wang Aimin
MP：13501196050
Mail：aimin.w...@esgyn.cn

From: mathieu ferlay [mailto:mfer...@gnubila.fr]
Sent: Thursday, March 16, 2017 3:39 PM
To: 
user@trafodion.incubator.apache.org
Subject: issue with a request

Hi everybody, i try to load these following lines:

CREATE TABLE IF NOT EXISTS Token (
  UUID VARCHAR(36) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  Expiration DATE NOT NULL,
  SOPInstanceUUID VARCHAR(64) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  PRIMARY KEY (UUID)
);

But I obtain systematically a mistake on UUID. Do you know how to success it 
with this column’s name?

Thanks,
Regards

FERLAY Mathieu

RE: issue wtih foreign keys

2017-03-16 Thread mathieu ferlay

Hi, 

I have change my request by using alter table do it but I still have some
lost of process. Its look like an infinite loop inside the execute
statement.

I dont 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
Objet : issue wtih foreign keys

 

Hi, ill 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

issue wtih foreign keys

2017-03-16 Thread mathieu ferlay

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

RE: issue with a request

2017-03-16 Thread Wang, Ai-Min (Benny)

Hi:

Pleaes use following sql， UUID  is reserved word and please use “” to enclose 
it：

CREATE TABLE IF NOT EXISTS Token (
  "UUID" VARCHAR(36) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  Expiration DATE NOT NULL,
  SOPInstanceUUID VARCHAR(64) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  PRIMARY KEY ("UUID")
);


Best Regrard！

Benny/Wang Aimin
MP：13501196050
Mail：aimin.w...@esgyn.cn

From: mathieu ferlay [mailto:mfer...@gnubila.fr]
Sent: Thursday, March 16, 2017 3:39 PM
To: user@trafodion.incubator.apache.org
Subject: issue with a request

Hi everybody, i try to load these following lines:

CREATE TABLE IF NOT EXISTS Token (
  UUID VARCHAR(36) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  Expiration DATE NOT NULL,
  SOPInstanceUUID VARCHAR(64) CHARACTER SET utf8 COLLATE DEFAULT NOT NULL,
  PRIMARY KEY (UUID)
);

But I obtain systematically a mistake on UUID. Do you know how to success it 
with this column’s name?

Thanks,
Regards

FERLAY Mathieu

RE: issue wtih foreign keys

RE: issue wtih foreign keys

RE: issue wtih foreign keys

RE: issue wtih foreign keys

RE: issue wtih foreign keys

RE: issue wtih foreign keys

RE: issue wtih foreign keys

RE: issue with a request

RE: issue with a request

RE: issue wtih foreign keys

issue wtih foreign keys

RE: issue with a request

12 matches

Site Navigation

Mail list logo

Footer information