SQLyog Ultimate has a DB Synch Wizard that compares 2 databases and spits out the one way sync SQL statements needed. Default core data can also be compared using the Data Synch Wizard and a similar SQL delete / update / insert set needed would be output.

In short, it is an unsupported case. The actual lessons from the initial failure were not captured by popsicles12 from his Mysql error logs and hence cannot enlighten us on the exact cause of failure.

Without clear answer to this question I don't know whether was this upgrade procedure failure or just unsupported case.

Janusz

Appended (no allowed attachments here) below is the contents of the upgrade sql file that popsicles12 used:

==============

ALTER TABLE `4_audit_trail`
    DROP KEY `fiscal_year`,
    ADD KEY `Seq`(`fiscal_year`,`gl_date`,`gl_seq`),
    ADD KEY `Type_and_Number`(`type`,`trans_no`);

ALTER TABLE `4_bank_accounts`
    CHANGE `account_code` `account_code` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' first,
    ADD KEY `account_code`(`account_code`);

ALTER TABLE `4_bank_trans`
    CHANGE `bank_act` `bank_act` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `trans_no`,
    ADD KEY `bank_act_2`(`bank_act`,`reconciled`),
    ADD KEY `bank_act_3`(`bank_act`,`trans_date`);

ALTER TABLE `4_bom`
    DROP KEY `Parent_2`;

ALTER TABLE `4_budget_trans`
    CHANGE `account` `account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `tran_date`,
    ADD KEY `Account`(`account`,`tran_date`,`dimension_id`,`dimension2_id`);

ALTER TABLE `4_chart_class`
    CHANGE `cid` `cid` varchar(3)  COLLATE latin1_swedish_ci NOT NULL first;

ALTER TABLE `4_chart_master`
    CHANGE `account_code` `account_code` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' first,
    CHANGE `account_code2` `account_code2` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `account_code`,
    CHANGE `account_type` `account_type` varchar(10)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '0' after `account_name`,
    DROP KEY `account_code`,
    ADD KEY `accounts_by_type`(`account_type`,`account_code`);

ALTER TABLE `4_chart_types`
    CHANGE `id` `id` varchar(10)  COLLATE latin1_swedish_ci NOT NULL first,
    CHANGE `class_id` `class_id` varchar(3)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `name`,
    CHANGE `parent` `parent` varchar(10)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '-1' after `class_id`,
    ADD KEY `class_id`(`class_id`);

ALTER TABLE `4_comments`
    ADD KEY `type_and_id`(`type`,`id`);

CREATE TABLE `4_crm_categories`(
    `id` int(11) NOT NULL  auto_increment COMMENT 'pure technical key' ,
    `type` varchar(20) COLLATE latin1_swedish_ci NOT NULL  COMMENT 'contact type e.g. customer' ,
    `action` varchar(20) COLLATE latin1_swedish_ci NOT NULL  COMMENT 'detailed usage e.g. department' ,
    `name` varchar(30) COLLATE latin1_swedish_ci NOT NULL  COMMENT 'for category selector' ,
    `description` tinytext COLLATE latin1_swedish_ci NOT NULL  COMMENT 'usage description' ,
    `system` tinyint(1) NOT NULL  DEFAULT 0 COMMENT 'nonzero for core system usage' ,
    `inactive` tinyint(1) NOT NULL  DEFAULT 0 ,
    PRIMARY KEY (`id`) ,
    UNIQUE KEY `type`(`type`,`action`) ,
    UNIQUE KEY `type_2`(`type`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET='latin1';

CREATE TABLE `4_crm_contacts`(
    `id` int(11) NOT NULL  auto_increment ,
    `person_id` int(11) NOT NULL  DEFAULT 0 COMMENT 'foreign key to crm_contacts' ,
    `type` varchar(20) COLLATE latin1_swedish_ci NOT NULL  COMMENT 'foreign key to crm_categories' ,
    `action` varchar(20) COLLATE latin1_swedish_ci NOT NULL  COMMENT 'foreign key to crm_categories' ,
    `entity_id` varchar(11) COLLATE latin1_swedish_ci NULL  COMMENT 'entity id in related class table' ,
    PRIMARY KEY (`id`) ,
    KEY `type`(`type`,`action`)
) ENGINE=InnoDB DEFAULT CHARSET='latin1';

CREATE TABLE `4_crm_persons`(
    `id` int(11) NOT NULL  auto_increment ,
    `ref` varchar(30) COLLATE latin1_swedish_ci NOT NULL  ,
    `name` varchar(60) COLLATE latin1_swedish_ci NOT NULL  ,
    `name2` varchar(60) COLLATE latin1_swedish_ci NULL  ,
    `address` tinytext COLLATE latin1_swedish_ci NULL  ,
    `phone` varchar(30) COLLATE latin1_swedish_ci NULL  ,
    `phone2` varchar(30) COLLATE latin1_swedish_ci NULL  ,
    `fax` varchar(30) COLLATE latin1_swedish_ci NULL  ,
    `email` varchar(100) COLLATE latin1_swedish_ci NULL  ,
    `lang` char(5) COLLATE latin1_swedish_ci NULL  ,
    `notes` tinytext COLLATE latin1_swedish_ci NOT NULL  ,
    `inactive` tinyint(1) NOT NULL  DEFAULT 0 ,
    PRIMARY KEY (`id`) ,
    KEY `ref`(`ref`)
) ENGINE=InnoDB DEFAULT CHARSET='latin1';

ALTER TABLE `4_cust_allocations`
    ADD KEY `From`(`trans_type_from`,`trans_no_from`),
    ADD KEY `To`(`trans_type_to`,`trans_no_to`);

ALTER TABLE `4_cust_branch`
    CHANGE `contact_name` `contact_name` varchar(60)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `salesman`,
    CHANGE `default_location` `default_location` varchar(5)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `contact_name`,
    CHANGE `tax_group_id` `tax_group_id` int(11)   NULL after `default_location`,
    CHANGE `sales_account` `sales_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `tax_group_id`,
    CHANGE `sales_discount_account` `sales_discount_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `sales_account`,
    CHANGE `receivables_account` `receivables_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `sales_discount_account`,
    CHANGE `payment_discount_account` `payment_discount_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `receivables_account`,
    CHANGE `default_ship_via` `default_ship_via` int(11)   NOT NULL DEFAULT 1 after `payment_discount_account`,
    CHANGE `disable_trans` `disable_trans` tinyint(4)   NOT NULL DEFAULT 0 after `default_ship_via`,
    CHANGE `br_post_address` `br_post_address` tinytext  COLLATE latin1_swedish_ci NOT NULL after `disable_trans`,
    CHANGE `group_no` `group_no` int(11)   NOT NULL DEFAULT 0 after `br_post_address`,
    CHANGE `notes` `notes` tinytext  COLLATE latin1_swedish_ci NOT NULL after `group_no`,
    CHANGE `inactive` `inactive` tinyint(1)   NOT NULL DEFAULT 0 after `notes`,
    DROP COLUMN `phone`,
    DROP COLUMN `phone2`,
    DROP COLUMN `fax`,
    DROP COLUMN `email`,
    DROP KEY `br_name`,
    ADD KEY `branch_ref`(`branch_ref`),
    ADD KEY `group_no`(`group_no`);

ALTER TABLE `4_debtor_trans`
    CHANGE `dimension_id` `dimension_id` int(11)   NOT NULL DEFAULT 0 after `ship_via`,
    CHANGE `dimension2_id` `dimension2_id` int(11)   NOT NULL DEFAULT 0 after `dimension_id`,
    ADD COLUMN `payment_terms` int(11)   NULL after `dimension2_id`,
    DROP COLUMN `trans_link`,
    DROP KEY `PRIMARY`, add PRIMARY KEY(`type`,`trans_no`),
    ADD KEY `tran_date`(`tran_date`);

ALTER TABLE `4_debtor_trans_details`
    ADD COLUMN `src_id` int(11)   NOT NULL after `qty_done`,
    ADD KEY `src_id`(`src_id`),
    ADD KEY `Transaction`(`debtor_trans_type`,`debtor_trans_no`);

ALTER TABLE `4_debtors_master`
    CHANGE `tax_id` `tax_id` varchar(55)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `address`,
    CHANGE `curr_code` `curr_code` char(3)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `tax_id`,
    CHANGE `sales_type` `sales_type` int(11)   NOT NULL DEFAULT 1 after `curr_code`,
    CHANGE `dimension_id` `dimension_id` int(11)   NOT NULL DEFAULT 0 after `sales_type`,
    CHANGE `dimension2_id` `dimension2_id` int(11)   NOT NULL DEFAULT 0 after `dimension_id`,
    CHANGE `credit_status` `credit_status` int(11)   NOT NULL DEFAULT 0 after `dimension2_id`,
    CHANGE `payment_terms` `payment_terms` int(11)   NULL after `credit_status`,
    CHANGE `discount` `discount` double   NOT NULL DEFAULT 0 after `payment_terms`,
    CHANGE `pymt_discount` `pymt_discount` double   NOT NULL DEFAULT 0 after `discount`,
    CHANGE `credit_limit` `credit_limit` float   NOT NULL DEFAULT 1000 after `pymt_discount`,
    CHANGE `notes` `notes` tinytext  COLLATE latin1_swedish_ci NOT NULL after `credit_limit`,
    CHANGE `inactive` `inactive` tinyint(1)   NOT NULL DEFAULT 0 after `notes`,
    DROP COLUMN `email`,
    ADD UNIQUE KEY `debtor_ref`(`debtor_ref`),
    DROP KEY `name`, add KEY `name`(`name`);

ALTER TABLE `4_dimensions`
    ADD KEY `date_`(`date_`),
    ADD KEY `due_date`(`due_date`),
    ADD KEY `type_`(`type_`);

ALTER TABLE `4_fiscal_year`
    ADD UNIQUE KEY `begin`(`begin`),
    ADD UNIQUE KEY `end`(`end`);

ALTER TABLE `4_gl_trans`
    CHANGE `account` `account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `tran_date`,
    ADD KEY `account_and_tran_date`(`account`,`tran_date`),
    ADD KEY `dimension2_id`(`dimension2_id`),
    ADD KEY `dimension_id`(`dimension_id`),
    ADD KEY `tran_date`(`tran_date`);

ALTER TABLE `4_grn_batch`
    ADD KEY `delivery_date`(`delivery_date`),
    ADD KEY `purch_order_no`(`purch_order_no`);

ALTER TABLE `4_grn_items`
    ADD KEY `grn_batch_id`(`grn_batch_id`);

ALTER TABLE `4_item_codes`
    ADD KEY `item_code`(`item_code`);

ALTER TABLE `4_purch_order_details`
    ADD KEY `order`(`order_no`,`po_detail_item`);

ALTER TABLE `4_purch_orders`
    ADD COLUMN `total` double   NOT NULL DEFAULT 0 after `delivery_address`,
    ADD COLUMN `tax_included` tinyint(1)   NOT NULL DEFAULT 0 after `total`,
    ADD KEY `ord_date`(`ord_date`);

ALTER TABLE `4_quick_entries`
    ADD COLUMN `bal_type` tinyint(1)   NOT NULL DEFAULT 0 after `base_desc`;

ALTER TABLE `4_quick_entry_lines`
    CHANGE `dest_id` `dest_id` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `action`;

ALTER TABLE `4_refs`
    ADD KEY `Type_and_Reference`(`type`,`reference`);

ALTER TABLE `4_sales_order_details`
    ADD KEY `sorder`(`trans_type`,`order_no`);

ALTER TABLE `4_sales_orders`
    ADD COLUMN `payment_terms` int(11)   NULL after `delivery_date`,
    ADD COLUMN `total` double   NOT NULL DEFAULT 0 after `payment_terms`;

ALTER TABLE `4_stock_category`
    CHANGE `dflt_sales_act` `dflt_sales_act` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `dflt_mb_flag`,
    CHANGE `dflt_cogs_act` `dflt_cogs_act` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `dflt_sales_act`,
    CHANGE `dflt_inventory_act` `dflt_inventory_act` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `dflt_cogs_act`,
    CHANGE `dflt_adjustment_act` `dflt_adjustment_act` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `dflt_inventory_act`,
    CHANGE `dflt_assembly_act` `dflt_assembly_act` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `dflt_adjustment_act`;

ALTER TABLE `4_stock_master`
    CHANGE `sales_account` `sales_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `mb_flag`,
    CHANGE `cogs_account` `cogs_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `sales_account`,
    CHANGE `inventory_account` `inventory_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `cogs_account`,
    CHANGE `adjustment_account` `adjustment_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `inventory_account`,
    CHANGE `assembly_account` `assembly_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `adjustment_account`,
    ADD COLUMN `editable` tinyint(1)   NOT NULL DEFAULT 0 after `no_sale`;

ALTER TABLE `4_stock_moves`
    ADD KEY `Move`(`stock_id`,`loc_code`,`tran_date`);

ALTER TABLE `4_supp_allocations`
    ADD KEY `From`(`trans_type_from`,`trans_no_from`),
    ADD KEY `To`(`trans_type_to`,`trans_no_to`);

ALTER TABLE `4_supp_invoice_items`
    CHANGE `gl_code` `gl_code` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `supp_trans_type`,
    ADD KEY `Transaction`(`supp_trans_type`,`supp_trans_no`,`stock_id`);

ALTER TABLE `4_supp_trans`
    ADD COLUMN `tax_included` tinyint(1)   NOT NULL DEFAULT 0 after `alloc`,
    DROP KEY `PRIMARY`, add PRIMARY KEY(`type`,`trans_no`),
    ADD KEY `tran_date`(`tran_date`);

ALTER TABLE `4_suppliers`
    CHANGE `gst_no` `gst_no` varchar(25)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `supp_address`,
    CHANGE `contact` `contact` varchar(60)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `gst_no`,
    CHANGE `supp_account_no` `supp_account_no` varchar(40)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `contact`,
    CHANGE `website` `website` varchar(100)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `supp_account_no`,
    CHANGE `bank_account` `bank_account` varchar(60)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `website`,
    CHANGE `curr_code` `curr_code` char(3)  COLLATE latin1_swedish_ci NULL after `bank_account`,
    CHANGE `payment_terms` `payment_terms` int(11)   NULL after `curr_code`,
    ADD COLUMN `tax_included` tinyint(1)   NOT NULL DEFAULT 0 after `payment_terms`,
    CHANGE `dimension_id` `dimension_id` int(11)   NULL DEFAULT 0 after `tax_included`,
    CHANGE `dimension2_id` `dimension2_id` int(11)   NULL DEFAULT 0 after `dimension_id`,
    CHANGE `tax_group_id` `tax_group_id` int(11)   NULL after `dimension2_id`,
    CHANGE `credit_limit` `credit_limit` double   NOT NULL DEFAULT 0 after `tax_group_id`,
    CHANGE `purchase_account` `purchase_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `credit_limit`,
    CHANGE `payable_account` `payable_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `purchase_account`,
    CHANGE `payment_discount_account` `payment_discount_account` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `payable_account`,
    CHANGE `notes` `notes` tinytext  COLLATE latin1_swedish_ci NOT NULL after `payment_discount_account`,
    CHANGE `inactive` `inactive` tinyint(1)   NOT NULL DEFAULT 0 after `notes`,
    DROP COLUMN `phone`,
    DROP COLUMN `phone2`,
    DROP COLUMN `fax`,
    DROP COLUMN `email`,
    ADD KEY `supp_ref`(`supp_ref`);

CREATE TABLE `4_sys_prefs`(
    `name` varchar(35) COLLATE latin1_swedish_ci NOT NULL  DEFAULT '' ,
    `category` varchar(30) COLLATE latin1_swedish_ci NULL  ,
    `type` varchar(20) COLLATE latin1_swedish_ci NOT NULL  DEFAULT '' ,
    `length` smallint(6) NULL  ,
    `value` tinytext COLLATE latin1_swedish_ci NULL  ,
    PRIMARY KEY (`name`) ,
    KEY `category`(`category`)
) ENGINE=MyISAM DEFAULT CHARSET='latin1';

ALTER TABLE `4_tag_associations`
    CHANGE `record_id` `record_id` varchar(15)  COLLATE latin1_swedish_ci NOT NULL first;

ALTER TABLE `4_tax_types`
    CHANGE `sales_gl_code` `sales_gl_code` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `rate`,
    CHANGE `purchasing_gl_code` `purchasing_gl_code` varchar(15)  COLLATE latin1_swedish_ci NOT NULL DEFAULT '' after `sales_gl_code`;

ALTER TABLE `4_trans_tax_details`
    ADD KEY `tran_date`(`tran_date`),
    ADD KEY `Type_and_Number`(`trans_type`,`trans_no`);

ALTER TABLE `4_useronline`
    ADD KEY `ip`(`ip`);

ALTER TABLE `4_wo_issues`
    ADD KEY `workorder_id`(`workorder_id`);

ALTER TABLE `4_wo_manufacture`
    ADD KEY `workorder_id`(`workorder_id`);

ALTER TABLE `4_wo_requirements`
    ADD KEY `workorder_id`(`workorder_id`);

=============

Free Advice and Free Diff File Sent:

popsicles12 then did so appropriately and had this to say:

I do hope this will serve to assist others in a similar throe. popsicles12 bravado and persistence saw him thru.

What if I create one of the companies with the 2.3 database schema and dump the database, will it upgrade? please help out.

thanks

Popsicles12.

the schema diff between the default company and the other companies are as follows:

the default company has "0_crm_category, 0_crm_contacts, 0_crm_persons and 0_sys_prefs but the other companies does not have them.

What do I do next?

thanks.

Popsicles12

Thanks for your response.

I followed the procedure you gave me by going to the *_sys_pref table to manually change the Version number to 2.3rc. On changing it, the default company worked but the other companies are still showing the error "Access to application has been blocked until database upgrade is completed by system administrator."
How do I make the other companies work since it is a multi-company setup.

Thanks so very much

Popsicles12

In general, when any change is done to the database - schema or standard data - it should be faithfully propagated to the necessary alter*.sql file and the demo sql file as well. It is possible that some changes may solely reside in the demo sql file alone!