could not start gridsql

After install the gridsql 2.0 and config it, then start the gs-server.sh , then start gs-dbstart.sh -d test.

always create an error 'can not bring db test online'

I had two nodes. (172.25.165.179 and 172.25.165.175)
1. on 172.25.165.179 I had install the gridsql and config it , follow is the gridsql.config file
xdb.port=6453
xdb.maxconnections=10

###
### Node & JDBC Pool configuration
###

### Set defaults for all nodes and MetaData database.
### These can be overriden.

xdb.default.dbusername=gridsql
xdb.default.dbpassword=p123ost!

xdb.default.dbport=5432

### Connection thread defaults for each node
### Note that these are pooled, so the number of clients connected
### to GridSQL can be greater than pool size.

xdb.default.threads.pool.initsize=5
xdb.default.threads.pool.maxsize=10

### Connectivity for MetaData database

xdb.metadata.database=XDBSYS
xdb.metadata.dbhost=172.25.165.179

### The number of nodes in cluster

xdb.nodecount=2

### The hosts of the underlying databases

xdb.node.1.dbhost=172.25.165.179
xdb.node.2.dbhost=172.25.165.175
#xdb.node.3.dbhost=127.0.0.1
#xdb.node.4.dbhost=127.0.0.1

### Designate coordinator node number
### In practice, the coordinator node should be the node where
### GridSQL is running.

xdb.coordinator.node=1

###
### The next few sections are required when wanting to run agents on the nodes.
### Uncomment them and modify to communicate with agents
###

### Only for agent version
### Port for node
xdb.node.2.port=6455
xdb.node.2.host=172.25.165.175
#xdb.node.2.port=6455
#xdb.node.2.host=192.168.123.101
#xdb.node.3.port=6455
#xdb.node.3.host=192.168.123.102
#xdb.node.4.port=6455
#xdb.node.4.host=192.168.123.103

### Designate coordinator node
### In practice, the coordinator node should be the node where
### GridSQL is running.

xdb.coordinator.host=172.25.165.179
xdb.coordinator.port=6454

# Specify protocol types.
# Can use local connection between coordinator and node 1,
# since they are the same system

xdb.connector.0.1=0
xdb.connector.1.0=0

Hi,

Have you installed gridsql agent on 172.25.165.175?
If not, please remove or comment out lines

xdb.node.2.port=6455
xdb.node.2.host=172.25.165.175

from the config file

Andrei

yes, I had installed. follows if the file gridsql_agent.config
xdb.coordinator.host=172.25.165.179
xdb.coordinator.port=6454

###
### Logging settings
###

log4j.rootLogger=WARN, console

log4j.logger.Server=ALL, console

# A1 is set to be a ConsoleAppender.
log4j.appender.console=org.apache.log4j.RollingFileAppender

# A1 uses PatternLayout.
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%r [%t] %-5p %c %x - %m%n
log4j.appender.console.File=/usr/local/gridsql-agent-2.0/log/agent.log

----------bye the way, I found may be set somthing with the log4j, I found from the server.log error information: log4j: WARN No appenders could be found for logger .
log4j: WARN Please initialize the log4j system properly.

on the coordinator run the command under enterprisedb

I had create metadata db successfully with cmd gs-createdb.sh -d test -u admin -p secret -n 1,2

then I run : gs-server.sh -u admin -p secret -d test

it error:
Starting....
Error starting XDBServer
server.log output:
Error: Unknown argument: -u
Parameters: [-d database_list] [-x] [-m]
where <database_list> is a space-separated list of Nodes to launch
-x Start node databases -m Start Metadata database

Ok, so please add to the main config file:

xdb.node.1.port=6455
xdb.node.1.host=172.25.165.179

to the gridsql_agent.config:

xdb.node.2.port=6455

Then perform following steps (you may have done some already)

On coordinator (172.25.165.179):

1. Run gs-createmddb.sh -u admin -p secret
2. Ensure XDBSYS postgres database exists
3. Run gs-server.sh

On node (172.25.165.175):

4. Run gs-agent.sh -n 2

On any client machine:

5. Run gs-createdb.sh -h 172.25.165.179 -d test -u admin -p secret -n 1,2
6. Check if you can connect to the database: gs-cmdline.sh -h 172.25.165.179 -d test -u admin -p secret
7. Shutdown cluster: gs-shutdown.sh -h 172.25.165.179 -u admin -p secret -f

Check all is OK:
On (172.25.165.179): gs-server.sh
On (172.25.165.179): gs-agent.sh -n 2
On any: gs-dbstart.sh -h 172.25.165.179 -d test -u admin -p secret

Or shorter:
On (172.25.165.179): gs-server.sh -d test
On (172.25.165.179): gs-agent.sh -n 2

Andrei

Thanks, that is ok now.

a new question: I want to restore one of our test db schema, and I don't know how to do it , use the psql , it not work, may be need manual to edit the create table script ? or else.

CREATE TABLE test (accessory_id integer NOT NULL default 0,
accessory_serial_number character varying(30) NOT NULL DEFAULT ''::character varying,
apk_id integer NOT NULL DEFAULT nextval(('"pk_accessory_id"'::text))::regclass),
CONSTRAINT pk_accessory PRIMARY KEY (apk_id));

I run this always error.

CREATE TABLE part ( p_partkey INTEGER NOT NULL,p_name VARCHAR(55) NOT NULL) PARTITIONING KEY p_partkey ON ALL;

when I run this , error happen, ' SQLException: ERROR: Server has aborted execution, cause is: java.nio.channels.ClosedChannelException : null'

then I see the log of gridsql with the console.log;

ERROR Catching throwable:
java.nio.channels.ClosedChannelException
at sun.nio.ch.SocketChannelImpl.finishConnect(SocketChannelImpl.java:555)
at com.edb.gridsql.communication.SocketConnector$SendingThread.send(Unknown Source)
at com.edb.gridsql.communication.AbstractConnector$AbstractSendingThread.sendFailed(Unknown Source)
at com.edb.gridsql.communication.SocketConnector$SendingThread.sendFailed(Unknown Source)
at com.edb.gridsql.communication.AbstractConnector$AbstractSendingThread.run(Unknown Source)
at java.lang.Thread.run(Thread.java:619)
2010-06-14 12:11:53,227 - WARN Message was not consumed: NodeMessage[Type:MSG_PING, From:1, To: [0], Session: 29, Request: 0]
2010-06-14 12:11:53,227 - WARN Message was not consumed: NodeMessage[Type:MSG_PING, From:1, To: [0], Session: 29, Request: 0]
2010-06-14 12:11:53,227 - WARN Message was not consumed: NodeMessage[Type:MSG_PING, From:1, To: [0], Session: 28, Request: 0]
2010-06-14 12:11:53,227 - WARN Message was not consumed: NodeMessage[Type:MSG_PING, From:1, To: [0], Session: 28, Request: 0]
2010-06-14 12:11:53,227 - WARN Message was not consumed: NodeMessage[Type:MSG_PING, From:1, To: [0], Session: 31, Request: 0]
2010-06-14 12:11:53,227 - WARN Message was not consumed: NodeMessage[Type:MSG_PING, From:1, To: [0], Session: 31, Request: 0]
2010-06-14 12:11:53,227 - WARN Message was not consumed: NodeMessage[Type:MSG_PING, From:1, To: [0], Session: 30, Request: 0]
2010-06-14 12:11:53,227 - WARN Message was not consumed: NodeMessage[Type:MSG_PING, From:1, To: [0], Session: 30, Request: 0]
2010-06-14 12:11:53,227 - ERROR Catching throwable:
java.nio.channels.NotYetConnectedException
at sun.nio.ch.SocketChannelImpl.ensureReadOpen(SocketChannelImpl.java:115)
at sun.nio.ch.SocketChannelImpl.read(SocketChannelImpl.java:156)
at com.edb.gridsql.communication.SocketConnector$ReceivingThread.read(Unknown Source)
at com.edb.gridsql.communication.SocketConnector$ReceivingThread.receive(Unknown Source)
at com.edb.gridsql.communication.AbstractConnector$AbstractReceivingThread.run(Unknown Source)
at java.lang.Thread.run(Thread.java:619)
2010-06-14 12:11:53,229 - ERROR Catching throwable:
java.nio.channels.ClosedChannelException
at sun.nio.ch.SocketChannelImpl.finishConnect(SocketChannelImpl.java:555)
at com.edb.gridsql.communication.SocketConnector$SendingThread.send(Unknown Source)
at com.edb.gridsql.communication.AbstractConnector$AbstractSendingThread.sendFailed(Unknown Source)
at com.edb.gridsql.communication.SocketConnector$SendingThread.sendFailed(Unknown Source)
at com.edb.gridsql.communication.AbstractConnector$AbstractSendingThread.run(Unknown Source)
at java.lang.Thread.run(Thread.java:619)
2010-06-14 12:11:53,229 - ERROR Can not deliver message, sending MSG_ABORT back to Agent
2010-06-14 12:11:53,229 - ERROR Can not deliver message, sending MSG_ABORT back to Agent
2010-06-14 12:11:53,231 - ERROR Throwing throwable:
com.edb.gridsql.exception.XDBMessageMonitorException: Server has aborted execution, cause is: java.nio.channels.ClosedChannelException : null

Please check the config files, if all hosts and ports are correct, check if settings in agent config files match to main config file, check if agents on remote nodes are up and running

hi, a new question.

Dose gridsql support sequence like that in postgresql db.

CREATE SEQUENCE seq_accessory_id
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1
CYCLE;

I test it not work.

GridSQL does not support sequences

hi, Andrei

Is there a better method to dump the postgresql db schema and data, then import the gridsql db.
or I must copy out the table create script and edit it , such as

CREATE TABLE aml
(
aml_id integer NOT NULL DEFAULT 0,
pn_id integer NOT NULL DEFAULT 0,
manufacturer_id integer NOT NULL DEFAULT 0,
manufacturer_pn character varying(254) NOT NULL DEFAULT ''::character varying,
customer_pn character varying(254) NOT NULL DEFAULT ''::character varying,
created_time integer NOT NULL DEFAULT 0,
status_id integer NOT NULL DEFAULT 0,
last_update integer NOT NULL DEFAULT 0,
update_status integer NOT NULL DEFAULT 0,
created_date timestamp without time zone NOT NULL DEFAULT now(),
created_user integer NOT NULL DEFAULT 0,
updated_date timestamp without time zone NOT NULL DEFAULT now(),
updated_user integer NOT NULL DEFAULT 0,
aplant character(5) NOT NULL DEFAULT ''::character varying,
afile_id integer NOT NULL DEFAULT 0,
apk_id integer NOT NULL DEFAULT nextval(('pk_aml_id'::text)::regclass),
aactive integer NOT NULL DEFAULT 0,
aatime timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT pk_aml PRIMARY KEY (apk_id)
)
WITH (OIDS=FALSE);

I must change this line apk_id integer NOT NULL DEFAULT nextval(('pk_aml_id'::text)::regclass), because it not support sequence.
Is that mean I must edit the table script one bye , then import to the gridsql?

2. I dump the table data with the pg_dump , such as table aml data, pg_dump -at aml -f aml.sql -d test.

then I use the gs-loader.sh to import the data to the gridsql db table aml: gs-loader.sh -t aml -d ptsiii -u admin -p secret -i aml.sql

but error: SQLException: ERROR: java.lang.ArrayIndexOutOfBoundsException: 14

Or a better method for the data import . thanks

You will have to edit the script generated by pg_dump.
In general it is a good idea to edit the script anyway to optimize your database for GridSQL, at least add partitioning clauses.
Also you should take care about syntax differences, other limitations.

You can run your updated scrtipt against GridSQL using psql.

Andrei

hi, thanks for your help

you said gridsql not support sequence, why not say it support serial with the same function .

when I use the command in gridsql ,: copy aml from '/home/enterprisedb/aml.sql';

error happen: SQLException: ERROR: java.lang.ArrayIndexOutOfBoundsException: 14

hi
1.when I create a table with replicated, then import about 630,000 rows
then type select count(*) from table , it will run ok,

2.but when I create a table with partition on column , it will show error:

SQLException: ERROR: Failed To Get Results For ( SQL , NodeURL) : ( SELECT count(*) AS "X COL1" FROM "aml_partition_aplant" ) eQS Node 2 has aborted execution, cause is: com.edb.gri dsql.exception.XDBServerException : Can not send data to Nodes

3.when I create a table with partition , and with about 30 rows data insert ,
then type select count(*) from table , it will run ok,

very surprise
?

GridSQL does support serial data type.
You should change
apk_id integer NOT NULL DEFAULT nextval(('pk_aml_id'::text)::regclass),
to
apk_id serial NOT NULL,

Regarding the error, please see the log for the root cause, why node 2 can not send over intermediate results.
Something is wrong with GridSQL or Postgres configuration.
If count() runs against replicated table it is executed on one node, performing load balancing.
Count() against partitioned table runs in 2 steps, first - count() on each node, second - sum() of results of first step.
There is a problem between step 1 and 2, but I am not sure about the cause.

Andrei

when run the select count(*) from the partition table with rows 680,000,
error

Caused by: com.edb.gridsql.exception.XDBMessageMonitorException: Node 2 has aborted execution, cause is: com.edb.gridsql.exception.XDBServerException : Can not send data to Nodes
at com.edb.gridsql.engine.MessageMonitor.checkMessages(Unknown Source)
at com.edb.gridsql.engine.MultinodeExecutor.executeStep(Unknown Source)
... 13 more

but run the select count(*) from the partition table with rows 31

It will not create any error,

hi, Andrei

The problem had been found , I change the gridsql.config file .
my gridsql structure is , two nodes: 172.25.165.179---- node 1 and also the coordinator ; 172.25.165.175 ----node 2

xdb.metadata.database=XDBSYS
xdb.metadata.dbhost=172.25.165.179 --------------- original is 127.0.0.1

xdb.node.1.dbhost=172.25.165.179 ------------------127.0.0.1

xdb.coordinator.host=172.25.165.179 ---------------127.0.0.1
xdb.coordinator.port=6454

all change the 127.0.0.1 to 172.25.165.179 , it is ok now.

hi,

I also want to ask a new question , how did the gridsql work, when copy data to a table that is replicate table stucture, how did it work.

when delete the data from the replicate table , how it work?
ths

hi, Andrei

I want to create a procedure (function ) in the gridsql. it seems not support , or there is a better method for this?

When you inserted the 30 rows, did you use COPY or insert?

Are you using loader defaults in gridsql.config, or did you override any settings?

For (2), was there any other additional information in the log file? Was the problematic node on its own server, or shared with others? Is its profile any different from the other nodes?

Thanks,

Mason