Frontaccounting Upgrade Error! (Page 1 of 2)

Hello Joe,

Thanks for your reply. I have done forced upgrade severally and it is still showing thesame errors.

Kindly, help me out.

Thanks.

Popsicles12.

First, seems you have partially upgraded database, and this can block 2.2 to 2.3 upgrade process. Before the update is preformed following database structure elements are checked:
. type field is included in 0_comments table index;
. 0_sys_prefs table exists;
. 0_sales_orders.payment_terms field exists;
. 0_purch_orders.tax_included field exists;

If any of those preconditions are met, the database is considered as partially upgraded. You should fix the database structure manually (e.g. via phpmyadmin) before upgrade.

But those are not the only changes which could rise errors during upgrade: any database structure change done on elements involved in FA upgrade procedure  can rise errors and block the upgrade. To avoid any errors, generally your database should have structure exactly as it is defined in en_US-*.sql in 2.2 distribution tarball.

Janusz

The whole upgrade process is too complex to do it manually. You should just find the exact problem which block the upgrade, and fix it before you continue.
You should check the list above and fix the database scheme respectively. When it is done right, you should see upgrade option for version 2.3 in place of 'partially Installed (1/4)' info.

Application upgrade procedures for switching to version 2.3 are coded in file alter2.3.php and two sql files: alter2.3sql and alter2.3rc.sql. You will find them all in sql folder. As you can see all the procedure is rather complex, so you should rather:

0. run FA backup, install new sources in separate folder and restore backup file in the new database;
1. run upgrade procedure available under Software Upgrade menu option;
2. note any errors displayed during upgrade;
3. fix the errors in your database manually using phpmyadmin and retry again from p.1, until no errors appears.

Upgrade procedure tries to restore database in case of failure, but this not always is possible (depends on errors encountered), thus the best method is starting starting from database restore from scratch using backup file.
Janusz

I'm not sure why it has happened, but seems the databse scheme version has not been updated during upgrade procedure. This should never happen if the upgradewas successfull. Anyway you can try to fix database scheme version tag manually. Open phpmyadmin on your database, find record in *_sys_prefs table with name=='version_id', and set value field to '2.3rc'. If all previous upgrade steps where performed sucessfully, you should be able to login and work on just upgraded application.

Janusz

The sql files in 2.3.11 are corect. This stable branch .
Janusz

Do a db schema diff check between the default company and the other companies.... or change the field value to be 2.3rc in each of the companies databases / tables

Seems your case is more complex than expected. Seems the original proces failed on first company, but the others could be consistent. You can try following last resort procedure:
. comment out the first company in config_db.php temporarily;
. retry upgrade after login to admin account on the first (not commented out) company;
. if all will go right just uncommnet first company and you are done.
Before starting above procedure I strongly recommend detailed inspection of all companies database schemes - all should be identical to sheme used in version  2.2 (providing you have started upgrade from 2.2).
Janusz

Hello Janusz,
I commented out the default company but the login page will not display on the browser. it was showing error of not connecting to the default database.
If I may ask, since there is a difference on the database schema of the default company and the other companies as the default company has 79 tables while the other companies has 75 tables, how do I make the other companies upgrade to 79 tables. The missing tables as as follows; 0_crm_category, 0_crm_contacts, 0_crm_persons and 0_sys_prefs.

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.

Epilogue: popsicles12 contacted me offiline sending me the sql dump on one database and I found that many tables had to be altered for field types / sizes.

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.

Actually, what happens is when even one SQL statement of the alter*.sql files fail or are executed external to the attendant alter*.php files, the rest of the SQL statements are not executed and the successive dependant statements in later files too would fail.

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`);

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