Problem with Foreign Keys

[Daniel Sage]

Hi,

I've just recently had an issue with my three node Galera cluster, where some queries have caused various nodes to crash, with the error: "[ERROR] WSREP: FK key set failed: 11" or something similar. These queries normally suceed, but today there's been no end to the issues. The current setup is that all queries are sent to haproxy, which then balances the queries between Nodes 2 & 3, with node 1 acting has a hot spare.

I've included the logs for all three nodes, which show the problem:

Node 1: http://pastebin.com/zNNAdFes

Node 2: http://pastebin.com/D72U33mU

Node 3: http://pastebin.com/rnPcfbsi

The output of SHOW STATUS LIKE 'wsrep%'; on one of the nodes is:

+----------------------------+--------------------------------------+

| Variable_name              | Value                                |

+----------------------------+--------------------------------------+

| wsrep_local_state_uuid     | 3a6d219b-7712-11e1-0800-fc184468fe93 |

| wsrep_protocol_version     | 3                                    |

| wsrep_last_committed       | 334                                  |

| wsrep_replicated           | 0                                    |

| wsrep_replicated_bytes     | 0                                    |

| wsrep_received             | 2                                    |

| wsrep_received_bytes       | 138                                  |

| wsrep_local_commits        | 0                                    |

| wsrep_local_cert_failures  | 0                                    |

| wsrep_local_bf_aborts      | 0                                    |

| wsrep_local_replays        | 0                                    |

| wsrep_local_send_queue     | 0                                    |

| wsrep_local_send_queue_avg | 0.000000                             |

| wsrep_local_recv_queue     | 0                                    |

| wsrep_local_recv_queue_avg | 0.000000                             |

| wsrep_flow_control_paused  | 0.000000                             |

| wsrep_flow_control_sent    | 0                                    |

| wsrep_flow_control_recv    | 0                                    |

| wsrep_cert_deps_distance   | 0.000000                             |

| wsrep_apply_oooe           | 0.000000                             |

| wsrep_apply_oool           | 0.000000                             |

| wsrep_apply_window         | 0.000000                             |

| wsrep_commit_oooe          | 0.000000                             |

| wsrep_commit_oool          | 0.000000                             |

| wsrep_commit_window        | 0.000000                             |

| wsrep_local_state          | 4                                    |

| wsrep_local_state_comment  | Synced (6)                           |

| wsrep_cert_index_size      | 0                                    |

| wsrep_cluster_conf_id      | 1                                    |

| wsrep_cluster_size         | 1                                    |

| wsrep_cluster_state_uuid   | 3a6d219b-7712-11e1-0800-fc184468fe93 |

| wsrep_cluster_status       | Primary                              |

| wsrep_connected            | ON                                   |

| wsrep_local_index          | 0                                    |

| wsrep_provider_name        | Galera                               |

| wsrep_provider_vendor      | Codership Oy <in...@codership.com>    |

| wsrep_provider_version     | 23.2.0(r120)                         |

| wsrep_ready                | ON                                   |

+----------------------------+--------------------------------------+

And mysqld -V shows:

mysqld Ver 5.5.20 for Linux on x86_64 (Source distribution, wsrep_23.4.r3713)

If there is any other info you need, let me know.

Regards,

Daniel Sage

[seppo....@codership.com]

Quoting Daniel Sage <thehe...@gmail.com>:

Hi Daniel,

These are very strange error messages, the message:

"InnoDB: Error: record has 140671975026592 fields"

suggests that table definition is corrupted.

Can you send the table defitions for analysis, at least for
sales_flat_order_item and the referenced parent table ? Use:

mysql> SHOW CREATE TABLE sales_flat_order_item;

Try also:

mysql> CHECK TABLE sales_flat_order_item;

What kind of SQL statements is used for these tables? Is is regular
INSERT/UPDATE/DELETE load or do you use e.g. ALTER TABLE on regular
basis?

This is probably confidential information, you can send directly to
mail email address (seppo....@codership.com)

-seppo

[Teemu Ollakka]

Hi Daniel,

You may have hit into bug https://bugs.launchpad.net/codership-mysql/+bug/967134. I was able to reproduce case like yours by creating FK reference to table having nearly 100 columns. 

Could you check if your table sales_flat_order_address refers to table having large number of columns or possibly to several tables?

- Teemu

[Daniel Sage]

Hey,

Just to update the list, the problem is related to the bug that Teemu mentioned in his reply, and now I'm just waiting on the next version.

Regards,

Sage