innobase.system-table-view failing

[Vijay Samuel]

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;

[Andrew Hutchings (linuxjedi)]

Reproduces with make test-dbqp only (running single test is fine)

Diff of output:

20110524-214111 --- /home/LinuxJedi/Programming/Bzr/drizzle/trunk/plugin/innobase/tests/r/innodb-system-table-view.result 2011-05-10 11:56:27.311748000 +0100
20110524-214111 +++ /home/LinuxJedi/Programming/Bzr/drizzle/trunk/plugin/innobase/tests/r/innodb-system-table-view.reject 2011-05-24 21:41:11.232774853 +0100
20110524-214111 @@ -2,7 +2,7 @@
20110524-214111 TABLE_ID NAME FLAG N_COLS SPACE
20110524-214111 11 SYS_FOREIGN 0 7 0
20110524-214111 12 SYS_FOREIGN_COLS 0 7 0
20110524-214111 -13 SYS_REPLICATION_LOG 0 11 0
20110524-214111 +13 SYS_REPLICATION_LOG 0 9 0
20110524-214111 SELECT * FROM DATA_DICTIONARY.INNODB_SYS_INDEXES;
20110524-214111 INDEX_ID NAME TABLE_ID TYPE N_FIELDS PAGE_NO SPACE
20110524-214111 11 ID_IND 11 3 1 302 0
20110524-214111 @@ -25,10 +25,8 @@
20110524-214111 13 SEGID 1 6 0 4
20110524-214111 13 COMMIT_ID 2 6 0 8
20110524-214111 13 END_TIMESTAMP 3 6 0 8
20110524-214111 -13 ORIGINATING_SERVER_UUID 4 5 4129792 0
20110524-214111 -13 ORIGINATING_COMMIT_ID 5 6 0 8
20110524-214111 -13 MESSAGE_LEN 6 6 0 4
20110524-214111 -13 MESSAGE 7 5 4129792 0
20110524-214111 +13 MESSAGE_LEN 4 6 0 4
20110524-214111 +13 MESSAGE 5 5 4129792 0
20110524-214111 SELECT * FROM DATA_DICTIONARY.INNODB_SYS_FIELDS;
20110524-214111 INDEX_ID NAME POS
20110524-214111 11 ID 0
20110524-214111 @@ -72,7 +70,7 @@
20110524-214111 NAME FLAG N_COLS SPACE
20110524-214111 SYS_FOREIGN 0 7 0
20110524-214111 SYS_FOREIGN_COLS 0 7 0
20110524-214111 -SYS_REPLICATION_LOG 0 11 0
20110524-214111 +SYS_REPLICATION_LOG 0 9 0
20110524-214111 test/child 1 5 0
20110524-214111 test/parent 1 4 0
20110524-214111 SELECT name, n_fields
20110524-214111
20110524-214111 drizzletest: Result content mismatch

[Patrick Crews (patrick-crews)]

Duplicate of 786568. This was caused by an issue with server management and using external datadirs. The system did not properly reset servers that had used an external data dir and that could bork subsequent tests.