Bug #18573 ndb nodes don't connected to started sql serve
Submitted: 28 Mar 2006 11:42 Modified: 26 Aug 2006 5:36
Reporter: Ilya Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:Ver 5.0.19 OS:Solaris (Solaris)
Assigned to: Assigned Account CPU Architecture:Any

[28 Mar 2006 11:42] Ilya 
Description:

I installed and configured MySQL Cluster for 3 hosts:
host2 and host3 as ndbd nodes and
host1 as mgm and sql nodes.

OS config:
host1: SunOS 5.8 Generic_117000-01 sun4u sparc SUNW,Sun-Fire-880;
host2, host3: SunOS 5.10 Generic_118822-27 sun4u sparc SUNW,Sun-Fire-V440

MySQL Cluster is up successfully. It was be possible to see by mgm client
"ndb_mgm" and command "show". All 4 nodes (2 ndbd, 1 mgm and sql) were
connected.

MySQL Cluster was being shutdown due to MySQL Documentation: on server host1 (by root) stop management node and sql server
i. ndb_mgm -e shutdown
ii. mysqladmin shutdown

After that I tried to restart MySQL Cluster folling next steps:
I. On server host1 (by root) start management node:
ndb_mgmd -f /var/lib/mysql-cluster/config.ini
II. On server host2, host3 (by root) start ndbd node:
ndbd
III. On server host1 (by root) start mysql server :
mysqld --user=mysql &

And I got trouble with starting sql:
060324 17:26:26 InnoDB: Started; log sequence number 0 0
060324 17:26:26 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

I use "/etc/init.d/mysql start" and sql server starts, but when I verify by management client 3 nodes (mgm and 2 ndbd) are connected
successfully but sql node can't work properly: ndb_mgm shows that sql node
is not connected.
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @10.50.3.18 (Version: 5.0.19, Nodegroup: 0, Master)
id=3 @10.50.3.36 (Version: 5.0.19, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.50.3.2 (Version: 5.0.19)

[mysqld(API)] 1 node(s)
id=4 (not connected, accepting connect from 10.50.3.2)

I tried to revive MySQL Cluster but mysql server doesn't work. These attempts included: changing configurations, clearing data source, reinstalling mysql package on each server, removing mysql package from servers at all and install it again and
shutdown servers with restarting.

Currently we have working mysql server but ndb_mgm shows that mysql node is not connected to cluster.

I tried to change data (create tables and insert data) by mysql client but
directories with data changed on server with mysql only. 

How to repeat:
I.
Create tables:
CREATE TABLE ACCOUNT (
ACCOUNT_ID           NUMERIC(18,0)                  NOT NULL,
BALANCE              NUMERIC(14,6)                  DEFAULT 0 NOT NULL,
IS_UNLIMITED         NUMERIC(1)                     DEFAULT 0 NOT NULL,
IS_LOCKED            NUMERIC(1)                     DEFAULT 0 NOT NULL,
BEGIN_DATE           TIMESTAMP                      DEFAULT NOW() NOT NULL,
CONSTRAINT PK_ACCOUNT_ID PRIMARY KEY (ACCOUNT_ID)
)ENGINE=NDBCLUSTER;

CREATE TABLE ACC_USER (
ACC_USER_ID          NUMERIC(18,0)                  NOT NULL,
ACCOUNT_ID           NUMERIC(18,0),
IS_LOCKED            NUMERIC(1)                     DEFAULT 0 NOT NULL,
BEGIN_DATE           TIMESTAMP                      DEFAULT NOW() NOT NULL,
END_DATE             TIMESTAMP                      NOT NULL,
M_VPN_EXT            VARCHAR(64),
IS_RING_TIME_INCLUDED NUMERIC(1),
CONSTRAINT PK_ACC_USER PRIMARY KEY (ACC_USER_ID)
)ENGINE=NDBCLUSTER;

CREATE TABLE T_COUNTER (
T_COUNTER_ID         NUMERIC(18,0)                  NOT NULL,
ACC_USER_ID          NUMERIC(18,0)                  NOT NULL,
M_KEY                VARCHAR(32)                    NOT NULL,
M_AMOUNT             NUMERIC(18,0),
M_USAGE              NUMERIC(18,0),
M_LAST_RESET_DATE    TIMESTAMP                      NOT NULL,
M_DESC               VARCHAR(64),
M_PERIOD             NUMERIC(14)                    NOT NULL,
M_PERIOD_UNIT        NUMERIC(3)                     NOT NULL,
M_STEP_COUNT         NUMERIC(3),
M_ALL_AMOUNT         NUMERIC(14),
M_ALL_USAGE          NUMERIC(14),
CONSTRAINT PK_T_COUNTER PRIMARY KEY (T_COUNTER_ID)
)ENGINE=NDBCLUSTER;

CREATE TABLE T_IDENT (
T_IDENT_ID           NUMERIC(18,0)                  NOT NULL,
ACC_USER_ID          NUMERIC(18,0)                  NOT NULL,
M_KEY                NUMERIC(2,0)                   NOT NULL,
M_VALUE              VARCHAR(32)                    NOT NULL,
CONSTRAINT PK_IDENT PRIMARY KEY (T_IDENT_ID),
CONSTRAINT UK_IDENT_KV UNIQUE (M_KEY, M_VALUE)
)ENGINE=NDBCLUSTER;

ALTER TABLE ACC_USER
   ADD CONSTRAINT FK_AU_ACC FOREIGN KEY (ACCOUNT_ID)
      REFERENCES ACCOUNT (ACCOUNT_ID);

ALTER TABLE T_COUNTER
   ADD CONSTRAINT FK_COUNTER_AU FOREIGN KEY (ACC_USER_ID)
      REFERENCES ACC_USER (ACC_USER_ID);

ALTER TABLE T_IDENT
   ADD CONSTRAINT FK_IDENT_AU FOREIGN KEY (ACC_USER_ID)
      REFERENCES ACC_USER (ACC_USER_ID);

alter table T_IDENT
  add constraint UK_IDENT_KAU unique (ACC_USER_ID,M_KEY);

alter table T_IDENT
  add constraint UK_IDENT_KV unique (M_KEY,M_VALUE);

create index IDX_ACC_USER_ACCOUNT on ACC_USER (ACCOUNT_ID);

create index IDX_T_COUNTER_ACC_USER on T_COUNTER (ACC_USER_ID);

create index IDX_T_IDENT_ACC_USER on T_IDENT (ACC_USER_ID);

create index IDX_T_IDENT_KEY on T_IDENT (M_KEY,M_VALUE);

II.
Insert data by means of JDBC.

III.
restart mysql cluster:
on server host1 (by root) stop management node and sql server
1.
a) ndb_mgm -e shutdown
b) mysqladmin shutdown
2.
a) On server host1 (by root) start management node:
ndb_mgmd -f /var/lib/mysql-cluster/config.ini
b) On server  host2, host3 (by root) start ndbd node:
ndbd
c) On server host1 (by root) start mysql server :
mysqld --user=mysql &
[26 Jul 2006 5:36] Hartmut Holzgraefe 
> III. On server host1 (by root) start mysql server :
> mysqld --user=mysql &

> And I got trouble with starting sql:
> 060324 17:26:26 InnoDB: Started; log sequence number 0 0
> 060324 17:26:26 [ERROR] Fatal error: Can't open and lock privilege tables: 
> Table 'mysql.host' doesn't exist

no system tables found in $datadir

> I use "/etc/init.d/mysql start" and sql server starts, 

probably explicit --datadir or --defaults-file settings in that script

> but when I verify by management client 3 nodes (mgm and 2 ndbd) 
> are connected successfully but sql node can't work properly: 
> ndb_mgm shows that sql node is not connected.

Anything about that node in the cluster log?
Or any messages in the mysqld error log?
SHOW VARIABLES and SHOW STATUS output?
[26 Aug 2006 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".