Description:
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 application 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)
The table can be created using the statement below but if any of the commented fields are uncommented, we get the errors.
Finally, we have confirmed using ndb_size.pl that the settings that we are using should support this CREATE statement.
Thanks
Brett
How to repeat:
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;
Description: 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 application 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) The table can be created using the statement below but if any of the commented fields are uncommented, we get the errors. Finally, we have confirmed using ndb_size.pl that the settings that we are using should support this CREATE statement. Thanks Brett How to repeat: 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;