#911 ServerDB::deleteServer crashes master

1.2.3
closed
Stefan H.
Murmur (142)
5
2014-01-18
2012-09-10
Steven Hartland
No

When a virtual server with channels deeper than 15 levels is deleted from master server running mysql the master will crash due to SQL error.

This is caused by innodb's max self referential limit being 15, above this the on delete cascade fails e.g.
<F>2012-09-01 00:02:18.694 SQL Error [DELETE FROM 29441_servers WHERE server_id = ?]: Cannot delete or update a parent row: a foreign key constraint fails (mumble.29441_channels, CONSTRAINT 29441_channels_parent_del FOREIGN KEY (server_id, parent_id) REFERENCES 29441_channels (server_id, channel_id) ON DELETE CASCADE) QMYSQL3: Unable to execute statement.

This limitation is documented here:-
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
Specifically:-
"Deviation from SQL standards: Cascading operations may not be nested more than 15 levels deep."

I see two possible fixes:-
1. Removing the constraint and processing channel delete's manually
2. Limit the depth of channels that can be created to match the underlying storage e.g. on mysql max 15.

2 seems reasonable but also wont prevent existing servers that have this from crashing so maybe #1 is the better overall solution.

Discussion

  • Stefan H.
    Stefan H.
    2012-09-10

    We will introduce a configurable channel nesting limit with a reasonable default to solve this issue. Thanks for reporting.

     
  • Thanks for the speedy reply,

    Could I also suggest that all SQL failures are made none fatal, as in a hosting environment you never want a single SQL failure to take down the master server as this could effect hundreds of servers.

    There's also some disparity between error handling in prepare from exec, I've attached a suggested patch which looks to improve this

     
  • Updated patch improving reconnect logic

     
    Attachments
  • Stefan H.
    Stefan H.
    2012-09-18

    Fixed by introduction of channel nesting limit:
    145d9f9 Introduce channel nesting limit (default=10).

    Thanks for reporting.

    Would be great if you could create a pull request on github (alternatively create a patch request here on sf but reviewing on github is just so much easier) for that patch. Otherwise it might get lost as I close this issue.