Cluster Create Table Limitation?
Firstly, we're using version 5.1.9-beta.
We're in the process of upgrading our server to a cluster environment. We've done a mysqldump of our old database structure and are trying to import it into the cluster.
Most of the database is imported with the exception of one table which refuses to be stuffed, rammed, pushed or otherwise into the cluster in its current form. We've reduced the query to below 4096 characters (including spaces) but no difference. We can split the create statement into two tables and they go in without any problems but our code doesn't support this.
I'm confident that the create statement is OK as we can load it as InnoDB but as soon as we try to convert it to NDB or use ENGINE = ndbcluster we get errors - specifically:
Error Code : 1005
Can't create table 'v2test.tbl_property_details' (errno: 140)
My question relates to any limitations of the NDB cluster create table settings. Is there a maximum number of fields? Is there are maximum number of field types?
Can anyone spot the problem with the create statement below? It goes in in this form but if any of the commented fields are uncommented, we get errors.
Thanks
Brett
CREATE TABLE `tbl_property_details` (
`propdet_id` int(11) unsigned NOT NULL auto_increment,
`ppt_style_attached` text,
`condition_external` varchar(255),
`condition_internal` varchar(255),
`currentuse` varchar(255),
`floor` varchar(255),
`interest` varchar(255),
`intval` varchar(255),
`valcomp` varchar(255),
`layout` varchar(255),
`ppt_style_levels` varchar(255),
`lining` varchar(255),
`ppt_id` int(11) unsigned NOT NULL default '0',
`roof` varchar(255),
`ppt_street_appeal` text,
`unit_id` int(11) unsigned default '0',
`walls` varchar(255),
`winframes` varchar(255),
`propdet_num_brms` tinyint(4),
`propdet_num_bathrooms` tinyint(4),
`propdet_year_built` varchar(10),
`propdet_year_built_additions` varchar(10),
`propdet_rent_until` varchar(15),
`propdet_car_accomodation` text,
`propdet_features` text,
`propdet_car_area` tinyint(4),
`propdet_living_area` smallint(6),
`propdet_outdoor_area` smallint(6),
`propdet_other_area` smallint(6),
`propdet_heritage_issues` enum('y','n') NOT NULL default 'n',
`propdet_environ_issues` enum('y','n') NOT NULL default 'n',
`propdet_ess_repairs` enum('y','n') NOT NULL default 'n',
`propdet_risk_location` tinyint(4),
`propdet_risk_land` tinyint(4),
`propdet_risk_environ` tinyint(4),
`propdet_risk_improve` tinyint(4),
`propdet_mkt_risk_value` tinyint(4),
`propdet_mkt_risk_volatility` tinyint(4),
`propdet_mkt_risk_economy` tinyint(4),
`propdet_mkt_risk_segment` tinyint(4),
`propdet_value_land` int(10),
`propdet_value_improvements` int(10),
`propdet_value_rent_actual` int(10),
`propdet_value_rent_market_top` int(10),
`propdet_value_rent_market` int(10),
`propdet_value_replacement` int(10),
`propdet_value_replacement_text` varchar(255),
`propdet_value_market_now` int(10),
`propdet_value_market_comp` int(10),
`propdet_value_market_comp_to` int(10),
`propdet_ubd_ref` varchar(10),
`propdet_builder` varchar(50),
`propdet_improvements_ancilliary` text,
`propdet_improvements_other` text,
`propdet_accomodation` text,
`propdet_pc_items` text,
`propdet_fixtures` text,
`propdet_comments_additional` text,
`propdet_car_spaces` smallint(6) unsigned,
`propdet_year_built_circa` smallint(6),
`propdet_market_recent_sale_date` bigint(20),
`propdet_market_recent_sale_is_contract` enum('y','n'),
`propdet_market_recent_sale_price` int(11),
`propdet_market_recent_sale` text,
`propdet_in_salesdb` enum('y','n'),
`propdet_market_earlier_sale` text,
`propdet_market_activity` text,
`propdet_market_direction` text,
`propdet_tier_market` text,
# `building_type` text,
# `additions` text,
# `cl_detail` text,
# `propdet_rail_issues` enum('y','n') default 'n',
# `propdet_land_issues` enum('y','n') default 'n',
# `propdet_electrical_issues` enum('y','n') default 'n',
# `propdet_road_issues` enum('y','n') default 'n',
# `propdet_pest_issues` enum('y','n') default 'n',
# `propdet_encroachment_issues` enum('y','n') default 'n',
# `comments_market` text,
# `comments_risk` text,
# `comments_issues` text,
# `comments` text,
# `propdet_units_in_development` int(10),
# `propdet_unit_entitlement` int(10),
# `propdet_unit_entitlement_out_of` int(10),
# `comments_heritage` text,
# `comments_repairs` text,
# `comments_environment` text,
# `propdet_rec_docs_to_sight` text,
# `comments_tier_market` text,
# `completed` int(3),
# `value` decimal(10,2),
# `cost` decimal(10,2),
# `previously` decimal(10,2),
# `payment_type` int(11),
# `due` decimal(10,2),
# `propdet_external_reason` text,
# `propdet_indicative_market_from` text,
# `propdet_indicative_market_to` text,
# `propdet_external_or_indicative` enum('ext','ind'),
# `propdet_max_lvr` tinyint(3),
# `ppt_style_type` varchar(255),
PRIMARY KEY (`propdet_id`),
KEY `fk_ppt_id` (`ppt_id`),
KEY `fk_unit_id` (`unit_id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;