Split bloated menu_router tables into multiple tables so that Drupal runs on MySQL 5.x Cluster NDB engine type

Subscribe...
Hit this as well.

I got it installed by manually creating the table.

It seemed to work well... but then I hit errors like

user warning: Lock wait timeout exceeded; try restarting transaction query: menu_link_save /* root : menu_link_save */ UPDATE menu_links SET menu_name = 'admin_menu', plid = 1998, link_path = 'admin_menu/flush-cache', router_path = 'admin_menu/flush-cache', hidden = 0, external = 0, has_children = 1, expanded = 0, weight = 20, depth = 2, p1 = 1998, p2 = 2319, p3 = 0, p4 = 0, p5 = 0, p6 = 0, p7 = 0, p8 = 0, p9 = 0, module = 'admin_menu', link_title = 'Flush all caches', options = 'a:2:{s:5:\"alter\";b:1;s:5:\"query\";s:11:\"destination\";}', customized = 0 WHERE mlid = 2319 in /www_data/Aspen/www/www/research/nfb/includes/menu.inc on line 1972.
    * user warning: Lock wait timeout exceeded; try restarting transaction query: _menu_update_parental_status /* root : _menu_update_parental_status */ UPDATE menu_links SET has_children = 1 WHERE mlid = 1998 in /www_data/Aspen/www/www/research/nfb/includes/menu.inc on line 2174.

Upon trying to use the MySQL 5.0 Cluster (ndbcluster engine), it seems that tables that are both using a utf8 charset and that has >1 columns of the type text or mediumtext fails to convert. MySQL reported this as not a bug, but a limitation of the NDB Engine.

That's a pretty harsh limitation of the engine. Can you point me to where it is documented?

We could probably make this table use only one text column. Anyway, this is not a bug, but a feature request. Moving to Drupal 7.

I clarified that Drupal doesn't support NDB tables in the requirements page. [1]

[1] http://drupal.org/node/270/revisions/view/519412/577658

Could this be fixed by changing other columns to be tinytext instead of text? Since you didn't mention tinytext not sure if it is a limitation. The link to the mysql ndb limitation page isn't really clear on the limitation.

Ok so there are two ways to fix this, change schema of menu_router either by changing DEFAULT CHARSET from utf8 to latin1 or you can mess with the sizes of each data type in each column. I explain more in my blog post on setting up Drupal on ndbcluster: http://www.coderintherye.com/drupal-mysql-cluster-ndbcluster-high-availa...

I'm hesitant to provide a patch, cause I don't believe setting to latin1 is going to work as a general solution, because if user used a utf8 character in a menu name then this would probably break. The other solution is also not a general solution because if one was not careful to limit the size of the menu names/function calls/urls/etc. then they would probably overfill the limit.

menu_router is a bloated table, and in some past installs I have had mysql dumps fail to export the menu_router table or to fully import it even on regular MyISAM table.

So here is my proposed solution, let's take a logical look at menu_router and see how we can split it into multiple tables for Drupal 8. Perhaps we could take all the _callback columns and put them into a menu_callbacks table and take all the _arguments columns and put them into a menu_arguments table. Both of those tables would share the path key. This should put us under the 8kb row length limit for ndbcluster and also make menu_router more manageable size for all users. This is just a first suggestion and I hope we can consider doing this. Dries mentioned in his blog this was a possibility, but it will require a lot of work.

I'm going to create a separate request but wanted to note here for those doing ndbcluster that you will also need to remove the key of 'source' on locale_sources table if using it because you can't make a KEY of BLOB type in ndbcluster.