Transaction log does not appear to be capturing FOREIGN KEY information

[Patrick Crews]

The transaction log does not appear to be capturing FOREIGN KEY information, at least as reported by the UDF - print_transaction_message:

This test is in the linked branch:
test-run --suite=transaction_log transaction_log_create
It will not fail, but the .result file / output will allow you to see this.

Test case:
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));

CREATE TABLE t2(a INT NOT NULL, b INT , PRIMARY KEY(a), KEY b_key (b),
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE SET NULL ON UPDATE CASCADE);

SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));

DROP TABLE t1, t2;

The output for the CREATE TABLE t2 statement from print_transaction_message:
+PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
+transaction_context {
+ server_id: 1
+ transaction_id: 4
+ START_TIMESTAMP
+ END_TIMESTAMP
+}
+statement {
+ type: CREATE_TABLE
+ START_TIMESTAMP
+ END_TIMESTAMP
+ create_table_statement {
+ table {
+ name: "t2"
+ engine {
+ name: "InnoDB"
+ }
+ field {
+ name: "a"
+ type: INTEGER
+ constraints {
+ is_nullable: false
+ }
+ }
+ field {
+ name: "b"
+ type: INTEGER
+ options {
+ default_null: true
+ }
+ }
+ indexes {
+ name: "PRIMARY"
+ is_primary: true
+ is_unique: true
+ type: UNKNOWN_INDEX
+ key_length: 4
+ index_part {
+ fieldnr: 0
+ compare_length: 4
+ }
+ options {
+ }
+ }
+ indexes {
+ name: "b_key"
+ is_primary: false
+ is_unique: false
+ type: UNKNOWN_INDEX
+ key_length: 4
+ index_part {
+ fieldnr: 1
+ compare_length: 4
+ }
+ options {
+ null_part_key: true
+ }
+ }
+ type: STANDARD
+ schema: "test"
+ options {
+ collation: "utf8_general_ci"
+ collation_id: 45
+ }
+ CREATE_TIMESTAMP
+ UPDATE_TIMESTAMP
+ }
+ }
+}

[Brian Aker (brianaker)]

The parser not collecting this is work that Stewart needs to complete.

The create table code path used in replication, is not the same that is used in SHOW CREATE TABLE.

On Jun 23, 2010, at 4:44 PM, Patrick Crews wrote:

> Public bug reported:
>
> The transaction log does not appear to be capturing FOREIGN KEY
> information, at least as reported by the UDF -
> print_transaction_message:
>
> This test is in the linked branch:
> test-run --suite=transaction_log transaction_log_create
> It will not fail, but the .result file / output will allow you to see this.
>
> Test case:
> DROP TABLE IF EXISTS t1, t2;
> CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
>
> CREATE TABLE t2(a INT NOT NULL, b INT , PRIMARY KEY(a), KEY b_key (b),
> CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE SET NULL ON UPDATE CASCADE);
>
> SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select
> max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
>
> DROP TABLE t1, t2;
>
> The output for the CREATE TABLE t2 statement from print_transaction_message:
> +PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
> +transaction_context {
> + server_id: 1
> + transaction_id: 4
> + START_TIMESTAMP
> + END_TIMESTAMP
> +}
> +statement {
> + type: CREATE_TABLE
> + START_TIMESTAMP
> + END_TIMESTAMP
> + create_table_statement {
> + table {
> + name: "t2"
> + engine {
> + name: "InnoDB"
> + }
> + field {
> + name: "a"
> + type: INTEGER
> + constraints {
> + is_nullable: false
> + }
> + }
> + field {
> + name: "b"
> + type: INTEGER
> + options {
> + default_null: true
> + }
> + }
> + indexes {
> + name: "PRIMARY"
> + is_primary: true
> + is_unique: true
> + type: UNKNOWN_INDEX
> + key_length: 4
> + index_part {
> + fieldnr: 0
> + compare_length: 4
> + }
> + options {
> + }
> + }
> + indexes {
> + name: "b_key"
> + is_primary: false
> + is_unique: false
> + type: UNKNOWN_INDEX
> + key_length: 4
> + index_part {
> + fieldnr: 1
> + compare_length: 4
> + }
> + options {
> + null_part_key: true
> + }
> + }
> + type: STANDARD
> + schema: "test"
> + options {
> + collation: "utf8_general_ci"
> + collation_id: 45
> + }
> + CREATE_TIMESTAMP
> + UPDATE_TIMESTAMP
> + }
> + }
> +}
>
> ** Affects: drizzle
> Importance: Medium
> Status: Confirmed
>
> ** Branch linked: lp:~patrick-crews/drizzle/transaction_log_tests
>
> ** Changed in: drizzle
> Importance: Undecided => Medium
>
> ** Changed in: drizzle
> Status: New => Confirmed
>
> --
> Transaction log does not appear to be capturing FOREIGN KEY information
> https://bugs.launchpad.net/bugs/597910
> You received this bug notification because you are a member of Drizzle-
> developers, which is subscribed to Drizzle.
> ...

[Stewart Smith (stewart)]

https://blueprints.launchpad.net/drizzle/+spec/foreign-keys-in-table-proto
and
https://blueprints.launchpad.net/drizzle/+spec/show-create-table-uses-table-proto

will fix this