• From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Topic: DB Schema anomalies

The PRIMARY KEY of the 0_bom table in both FA 2.3 and FA 2.4 is part of the list below:

  PRIMARY KEY  (`parent`,`component`,`workcentre_added`,`loc_code`),
  KEY `component` (`component`),
  KEY `id` (`id`),
  KEY `loc_code` (`loc_code`),
  KEY `parent` (`parent`,`loc_code`),
  KEY `workcentre_added` (`workcentre_added`)

It can be re-arranged like this to save an index that would then have become redundant:

  PRIMARY KEY  (`parent`,`loc_code`,`component`,`workcentre_added`),
  KEY `component` (`component`),
  KEY `id` (`id`),
  KEY `loc_code` (`loc_code`),
  KEY `workcentre_added` (`workcentre_added`)

Also why have the erstwhile VARCHAR fields like loc_code and some other fields now become CHAR in FA 2.4?

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: DB Schema anomalies

After cleaning up the entire standard Chart of Accounts, we are left with the following CHAR fields (whilst all else have been mostly changed to VARCHAR):

0_bank_accounts:  `bank_curr_code` char(3) NOT NULL DEFAULT '',
0_crm_persons:  `lang` char(5) DEFAULT NULL,
0_currencies:  `curr_abrev` char(3) NOT NULL DEFAULT '',
0_debtors_master:  `curr_code` char(3) NOT NULL DEFAULT '',
0_exchange_rates:  `curr_code` char(3) NOT NULL DEFAULT '',
0_journal:  `currency` char(3) NOT NULL DEFAULT '',
0_prices:  `curr_abrev` char(3) NOT NULL DEFAULT '',
0_stock_category:  `dflt_mb_flag` char(1) NOT NULL DEFAULT 'B',
0_stock_master:  `mb_flag` char(1) NOT NULL DEFAULT 'B',
0_stock_master:  `depreciation_method` char(1) NOT NULL DEFAULT 'S',
0_suppliers:  `curr_code` char(3) DEFAULT NULL,

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: DB Schema anomalies

In MySQL 5.0 and later, TEXT/BLOB fields cannot have default values.

The only case in FA where it occurs is in the sys_prefs table and that too only in FA 2.4.x:

`value` TEXT NOT NULL DEFAULT '',

It should be:

`value` TEXT NOT NULL,

In FA 2.3.x it was:

`value` tinytext,

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: DB Schema anomalies

Schema update / synch scripts added to my repo - FA23, FA24.

FA 2.4 Schema optimisations consolidated

- @joe/@itronics can vet it for inclusion in the core.

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: DB Schema anomalies

Why was the index:

UNIQUE KEY `name` (`name`,`rate`)

removed from the 0_tax_types table in FA 2.4 while it was there in FA 2.3?

Do we allow the same named tax with the same rate to appear more than once?

If so, how would we distinguish them in the select drop-down boxes?

@joe: what say you?

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: DB Schema anomalies

MySQL Bugs #13794 TEXT cannot use empty string as default value

(It is) not a bug.
It is caused by the configuration file automatically generated by the configuration tool under Windows.
In the my.ini, sql-mode is set to STRICT_TRANS_TABLES by default, which causes this error.

apmuthu's Website