linux-traipu team mailing list archive

[Vijay Samuel <787793@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

SELECT * FROM DATA_DICTIONARY.INNODB_SYS_TABLES;
TABLE_ID	NAME	FLAG	N_COLS	SPACE
11	SYS_FOREIGN	0	7	0
12	SYS_FOREIGN_COLS	0	7	0
13	SYS_REPLICATION_LOG	0	9	0
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_INDEXES;
INDEX_ID	NAME	TABLE_ID	TYPE	N_FIELDS	PAGE_NO	SPACE
11	ID_IND	11	3	1	302	0
12	FOR_IND	11	0	1	303	0
13	REF_IND	11	0	1	304	0
14	ID_IND	12	3	2	305	0
15	PRIMARY	13	3	2	307	0
16	COMMIT_IDX	13	0	2	308	0
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_COLUMNS;
TABLE_ID	NAME	POS	MTYPE	PRTYPE	LEN
11	ID	0	1	2949124	0
11	FOR_NAME	1	1	2949124	0
11	REF_NAME	2	1	2949124	0
11	N_COLS	3	6	0	4
12	ID	0	1	2949124	0
12	POS	1	6	0	4
12	FOR_COL_NAME	2	1	2949124	0
12	REF_COL_NAME	3	1	2949124	0
13	ID	0	6	0	8
13	SEGID	1	6	0	4
13	COMMIT_ID	2	6	0	8
13	END_TIMESTAMP	3	6	0	8
13	MESSAGE_LEN	4	6	0	4
13	MESSAGE	5	5	4129792	0
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FIELDS;
INDEX_ID	NAME	POS
11	ID	0
12	FOR_NAME	0
13	REF_NAME	0
14	ID	0
14	POS	1
15	ID	0
15	SEGID	1
16	COMMIT_ID	0
16	ID	1
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_TABLESTATS;
TABLE_ID	NAME	STATS_INITIALIZED	NUM_ROWS	CLUST_INDEX_SIZE	OTHER_INDEX_SIZE	MODIFIED_COUNTER	AUTOINC	HANDLES_OPENED
11	SYS_FOREIGN	Uninitialized	0	0	0	0	0	0
12	SYS_FOREIGN_COLS	Uninitialized	0	0	0	0	0	0
13	SYS_REPLICATION_LOG	Uninitialized	0	0	0	0	0	0
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
CONSTRAINT constraint_test
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE) ENGINE=INNODB;
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/constraint_test	test/child	test/parent	1	1
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/constraint_test	parent_id	id	0
INSERT INTO parent VALUES(1);
SELECT name, num_rows, handles_opened
FROM DATA_DICTIONARY.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
name	num_rows	handles_opened
test/parent	1	1
SELECT NAME, FLAG, N_COLS, SPACE FROM DATA_DICTIONARY.INNODB_SYS_TABLES;
NAME	FLAG	N_COLS	SPACE
SYS_FOREIGN	0	7	0
SYS_FOREIGN_COLS	0	7	0
SYS_REPLICATION_LOG	0	9	0
test/child	1	5	0
test/parent	1	4	0
SELECT name, n_fields
from DATA_DICTIONARY.INNODB_SYS_INDEXES
WHERE table_id In (SELECT table_id from
DATA_DICTIONARY.INNODB_SYS_TABLES
WHERE name LIKE "%parent%");
name	n_fields
PRIMARY	1
SELECT name, n_fields
from DATA_DICTIONARY.INNODB_SYS_INDEXES
WHERE table_id In (SELECT table_id from
DATA_DICTIONARY.INNODB_SYS_TABLES
WHERE name LIKE "%child%");
name	n_fields
GEN_CLUST_INDEX	0
par_ind	1
SELECT name, pos, mtype, len
from DATA_DICTIONARY.INNODB_SYS_COLUMNS
WHERE table_id In (SELECT table_id from
DATA_DICTIONARY.INNODB_SYS_TABLES
WHERE name LIKE "%child%");
name	pos	mtype	len
id	0	6	4
parent_id	1	6	4
DROP TABLE child;
DROP TABLE parent;
CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL,
PRIMARY KEY (id, newid)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
CONSTRAINT constraint_test
FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid)
ON DELETE CASCADE) ENGINE=INNODB;
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/constraint_test	test/child	test/parent	2	1
SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/constraint_test	id	id	0
test/constraint_test	parent_id	newid	1
INSERT INTO parent VALUES(1, 9);
SELECT * FROM parent WHERE id IN (SELECT id FROM parent);
id	newid
1	9
SELECT name, num_rows, handles_opened
FROM DATA_DICTIONARY.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
name	num_rows	handles_opened
test/parent	1	2
DROP TABLE child;
DROP TABLE parent;

** Affects: drizzle
     Importance: Medium
         Status: Confirmed

-- 
You received this bug notification because you are a member of UBUNTU -
BR - LINUX TRAIPU, which is subscribed to Drizzle.
https://bugs.launchpad.net/bugs/787793

Title:
  innobase.system-table-view failing

Status in A Lightweight SQL Database for Cloud Infrastructure and Web Applications:
  Confirmed

Bug description:
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_TABLES;
  TABLE_ID	NAME	FLAG	N_COLS	SPACE
  11	SYS_FOREIGN	0	7	0
  12	SYS_FOREIGN_COLS	0	7	0
  13	SYS_REPLICATION_LOG	0	9	0
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_INDEXES;
  INDEX_ID	NAME	TABLE_ID	TYPE	N_FIELDS	PAGE_NO	SPACE
  11	ID_IND	11	3	1	302	0
  12	FOR_IND	11	0	1	303	0
  13	REF_IND	11	0	1	304	0
  14	ID_IND	12	3	2	305	0
  15	PRIMARY	13	3	2	307	0
  16	COMMIT_IDX	13	0	2	308	0
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_COLUMNS;
  TABLE_ID	NAME	POS	MTYPE	PRTYPE	LEN
  11	ID	0	1	2949124	0
  11	FOR_NAME	1	1	2949124	0
  11	REF_NAME	2	1	2949124	0
  11	N_COLS	3	6	0	4
  12	ID	0	1	2949124	0
  12	POS	1	6	0	4
  12	FOR_COL_NAME	2	1	2949124	0
  12	REF_COL_NAME	3	1	2949124	0
  13	ID	0	6	0	8
  13	SEGID	1	6	0	4
  13	COMMIT_ID	2	6	0	8
  13	END_TIMESTAMP	3	6	0	8
  13	MESSAGE_LEN	4	6	0	4
  13	MESSAGE	5	5	4129792	0
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FIELDS;
  INDEX_ID	NAME	POS
  11	ID	0
  12	FOR_NAME	0
  13	REF_NAME	0
  14	ID	0
  14	POS	1
  15	ID	0
  15	SEGID	1
  16	COMMIT_ID	0
  16	ID	1
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN;
  ID	FOR_NAME	REF_NAME	N_COLS	TYPE
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN_COLS;
  ID	FOR_COL_NAME	REF_COL_NAME	POS
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_TABLESTATS;
  TABLE_ID	NAME	STATS_INITIALIZED	NUM_ROWS	CLUST_INDEX_SIZE	OTHER_INDEX_SIZE	MODIFIED_COUNTER	AUTOINC	HANDLES_OPENED
  11	SYS_FOREIGN	Uninitialized	0	0	0	0	0	0
  12	SYS_FOREIGN_COLS	Uninitialized	0	0	0	0	0	0
  13	SYS_REPLICATION_LOG	Uninitialized	0	0	0	0	0	0
  CREATE TABLE parent (id INT NOT NULL,
  PRIMARY KEY (id)) ENGINE=INNODB;
  CREATE TABLE child (id INT, parent_id INT,
  INDEX par_ind (parent_id),
  CONSTRAINT constraint_test
  FOREIGN KEY (parent_id) REFERENCES parent(id)
  ON DELETE CASCADE) ENGINE=INNODB;
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN;
  ID	FOR_NAME	REF_NAME	N_COLS	TYPE
  test/constraint_test	test/child	test/parent	1	1
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN_COLS;
  ID	FOR_COL_NAME	REF_COL_NAME	POS
  test/constraint_test	parent_id	id	0
  INSERT INTO parent VALUES(1);
  SELECT name, num_rows, handles_opened
  FROM DATA_DICTIONARY.INNODB_SYS_TABLESTATS
  WHERE name LIKE "%parent";
  name	num_rows	handles_opened
  test/parent	1	1
  SELECT NAME, FLAG, N_COLS, SPACE FROM DATA_DICTIONARY.INNODB_SYS_TABLES;
  NAME	FLAG	N_COLS	SPACE
  SYS_FOREIGN	0	7	0
  SYS_FOREIGN_COLS	0	7	0
  SYS_REPLICATION_LOG	0	9	0
  test/child	1	5	0
  test/parent	1	4	0
  SELECT name, n_fields
  from DATA_DICTIONARY.INNODB_SYS_INDEXES
  WHERE table_id In (SELECT table_id from
  DATA_DICTIONARY.INNODB_SYS_TABLES
  WHERE name LIKE "%parent%");
  name	n_fields
  PRIMARY	1
  SELECT name, n_fields
  from DATA_DICTIONARY.INNODB_SYS_INDEXES
  WHERE table_id In (SELECT table_id from
  DATA_DICTIONARY.INNODB_SYS_TABLES
  WHERE name LIKE "%child%");
  name	n_fields
  GEN_CLUST_INDEX	0
  par_ind	1
  SELECT name, pos, mtype, len
  from DATA_DICTIONARY.INNODB_SYS_COLUMNS
  WHERE table_id In (SELECT table_id from
  DATA_DICTIONARY.INNODB_SYS_TABLES
  WHERE name LIKE "%child%");
  name	pos	mtype	len
  id	0	6	4
  parent_id	1	6	4
  DROP TABLE child;
  DROP TABLE parent;
  CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL,
  PRIMARY KEY (id, newid)) ENGINE=INNODB;
  CREATE TABLE child (id INT, parent_id INT,
  INDEX par_ind (parent_id),
  CONSTRAINT constraint_test
  FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid)
  ON DELETE CASCADE) ENGINE=INNODB;
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN;
  ID	FOR_NAME	REF_NAME	N_COLS	TYPE
  test/constraint_test	test/child	test/parent	2	1
  SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FOREIGN_COLS;
  ID	FOR_COL_NAME	REF_COL_NAME	POS
  test/constraint_test	id	id	0
  test/constraint_test	parent_id	newid	1
  INSERT INTO parent VALUES(1, 9);
  SELECT * FROM parent WHERE id IN (SELECT id FROM parent);
  id	newid
  1	9
  SELECT name, num_rows, handles_opened
  FROM DATA_DICTIONARY.INNODB_SYS_TABLESTATS
  WHERE name LIKE "%parent";
  name	num_rows	handles_opened
  test/parent	1	2
  DROP TABLE child;
  DROP TABLE parent;