search

Dolibarr / dolibarr

Dolibarr ERP CRM is a modern software package to manage your company or foundation's activity (contacts, suppliers, invoices, orders, stocks, agenda, accounting, ...). it's an open source Web application (written in PHP) designed for businesses of any sizes, foundations and freelancers.
https://www.dolibarr.org
GNU General Public License v3.0
5.29k stars 2.74k forks source link

Sql errors in Accounting due to collation settings #10918

Closed jongerli closed 5 years ago

jongerli commented 5 years ago

Bug

[Sql errors in Accounting due to changed db collation settings after Dolibarr migration]

Environment

Expected and actual behavior

[When pressing the reporting lmenu-link in Accounting one expect that an overview is given but due to collation issues, an error message is presented in the UI. This happens in several accounting menu-items. The error presented: 2019-03-24 18:32:54 ERR 80.112.59.168 DoliDBMysqli::query SQL Error message: DB_ERROR_1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='. After investing the tablestructure, and correcting the tables: llx_accounting_bookkeeping & llx_product (removing the collation utf8_unicode_ci) from the tabledefinitions, The default and unchanged database collation setting is utf8_general]

Steps to reproduce the behavior

[The above error did not occur on version 8.X but after migrating to version 9.0 several of our clients got the same issues.]

TopNiz commented 5 years ago

I've came through this bug and I found it was caused, in my case, by the bad collation of field 'accountancy_code' in table 'llx_c_type_fees' Suggested remediation (it worked for me):

ALTER TABLE `llx_c_type_fees` CHANGE `accountancy_code` `accountancy_code` VARCHAR(32) 
CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
a-schild commented 5 years ago

We see the same here with 9.0.3 and MySQL

accountancy/admin/productaccount.php?mainmenu=accountancy&leftmenu=accountancy_admin

Datenbank Type Manager: mysqli

Letzter Datenbankfehler:

SELECT p.rowid, p.ref, p.label, p.description, p.tosell, p.tobuy, p.accountancy_code_sell, p.accountancy_code_buy, p.tms, p.fk_product_type as product_type, aa.rowid as aaid FROM llx_product as p LEFT JOIN llx_accounting_account as aa ON p.accountancy_code_sell = aa.account_number AND aa.fk_pcg_version = 'PCG_ZENSHIN' WHERE p.entity IN (1) AND aa.account_number IS NULL ORDER BY p.ref ASC LIMIT 51 Rückgabewert des letzten Datenbankfehlers: DB_ERROR_1267 Informationen zum letzten Datenbankfehler: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

The exported products table has this definition, which seems to have been introduced in the install/mysql/migration/6.0.0-7.0.0.sql script.

When creating a new DB with 9.0.3, then the script install/mysql/tables/llx_product.sql has no specific options/settings for the collate/charset stuff done in the migration script.

CREATE TABLE IF NOT EXISTS `llx_product` (
  `rowid` int(11) NOT NULL AUTO_INCREMENT,
  `ref` varchar(128) NOT NULL,
  `entity` int(11) NOT NULL DEFAULT '1',
  `ref_ext` varchar(128) DEFAULT NULL,
  `datec` datetime DEFAULT NULL,
  `tms` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `virtual` tinyint(4) NOT NULL DEFAULT '0',
  `fk_parent` int(11) DEFAULT '0',
  `label` varchar(255) NOT NULL,
  `description` text,
  `note` text,
  `customcode` varchar(32) DEFAULT NULL,
  `fk_country` int(11) DEFAULT NULL,
  `price` double(24,8) DEFAULT '0.00000000',
  `price_ttc` double(24,8) DEFAULT '0.00000000',
  `price_min` double(24,8) DEFAULT '0.00000000',
  `price_min_ttc` double(24,8) DEFAULT '0.00000000',
  `price_base_type` varchar(3) DEFAULT 'HT',
  `tva_tx` double(6,3) DEFAULT NULL,
  `recuperableonly` int(11) NOT NULL DEFAULT '0',
  `localtax1_tx` double(6,3) DEFAULT '0.000',
  `localtax1_type` varchar(10) NOT NULL DEFAULT '0',
  `localtax2_tx` double(6,3) DEFAULT '0.000',
  `localtax2_type` varchar(10) NOT NULL DEFAULT '0',
  `fk_user_author` int(11) DEFAULT NULL,
  `fk_user_modif` int(11) DEFAULT NULL,
  `tosell` tinyint(4) DEFAULT '1',
  `tobuy` tinyint(4) DEFAULT '1',
  `onportal` smallint(6) DEFAULT '0',
  `tobatch` tinyint(4) NOT NULL DEFAULT '0',
  `fk_product_type` int(11) DEFAULT '0',
  `duration` varchar(6) DEFAULT NULL,
  `seuil_stock_alerte` int(11) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `barcode` varchar(255) DEFAULT NULL,
  `fk_barcode_type` int(11) DEFAULT NULL,
  `accountancy_code_sell` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `accountancy_code_sell_intra` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `accountancy_code_sell_export` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `accountancy_code_buy` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `partnumber` varchar(32) DEFAULT NULL,
  `weight` float DEFAULT NULL,
  `weight_units` tinyint(4) DEFAULT NULL,
  `length` float DEFAULT NULL,
  `length_units` tinyint(4) DEFAULT NULL,
  `surface` float DEFAULT NULL,
  `surface_units` tinyint(4) DEFAULT NULL,
  `volume` float DEFAULT NULL,
  `volume_units` tinyint(4) DEFAULT NULL,
  `stock` double DEFAULT NULL,
  `pmp` double(24,8) NOT NULL DEFAULT '0.00000000',
  `fifo` double(24,8) DEFAULT NULL,
  `lifo` double(24,8) DEFAULT NULL,
  `canvas` varchar(32) DEFAULT NULL,
  `finished` tinyint(4) DEFAULT NULL,
  `hidden` tinyint(4) DEFAULT '0',
  `import_key` varchar(14) DEFAULT NULL,
  `desiredstock` int(11) DEFAULT '0',
  `fk_price_expression` int(11) DEFAULT NULL,
  `fk_unit` int(11) DEFAULT NULL,
  `cost_price` double(24,8) DEFAULT NULL,
  `default_vat_code` varchar(10) DEFAULT NULL,
  `price_autogen` smallint(6) DEFAULT '0',
  `note_public` text,
  `model_pdf` varchar(255) DEFAULT '',
  `width` float DEFAULT NULL,
  `width_units` tinyint(4) DEFAULT NULL,
  `height` float DEFAULT NULL,
  `height_units` tinyint(4) DEFAULT NULL,
  `fk_default_warehouse` int(11) DEFAULT NULL,
  PRIMARY KEY (`rowid`),
  UNIQUE KEY `uk_product_ref` (`ref`,`entity`),
  UNIQUE KEY `uk_product_barcode` (`barcode`,`fk_barcode_type`,`entity`),
  KEY `idx_product_label` (`label`),
  KEY `idx_product_barcode` (`barcode`),
  KEY `idx_product_import_key` (`import_key`),
  KEY `idx_product_seuil_stock_alerte` (`seuil_stock_alerte`),
  KEY `idx_product_fk_country` (`fk_country`),
  KEY `idx_product_fk_user_author` (`fk_user_author`),
  KEY `idx_product_fk_barcode_type` (`fk_barcode_type`),
  KEY `fk_product_fk_unit` (`fk_unit`),
  KEY `fk_product_default_warehouse` (`fk_default_warehouse`),
  CONSTRAINT `fk_product_barcode_type` FOREIGN KEY (`fk_barcode_type`) REFERENCES `llx_c_barcode_type` (`rowid`),
  CONSTRAINT `fk_product_default_warehouse` FOREIGN KEY (`fk_default_warehouse`) REFERENCES `llx_entrepot` (`rowid`),
  CONSTRAINT `fk_product_fk_country` FOREIGN KEY (`fk_country`) REFERENCES `llx_c_country` (`rowid`),
  CONSTRAINT `fk_product_fk_unit` FOREIGN KEY (`fk_unit`) REFERENCES `llx_c_units` (`rowid`)
) ENGINE=InnoDB AUTO_INCREMENT=286 DEFAULT CHARSET=utf8;

and the corresponding llx_accounting_account:

CREATE TABLE IF NOT EXISTS `llx_accounting_account` (
  `rowid` int(11) NOT NULL AUTO_INCREMENT,
  `entity` int(11) NOT NULL DEFAULT '1',
  `datec` datetime DEFAULT NULL,
  `tms` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fk_pcg_version` varchar(32) NOT NULL,
  `pcg_type` varchar(20) NOT NULL,
  `pcg_subtype` varchar(20) NOT NULL,
  `account_number` varchar(32) NOT NULL,
  `account_parent` int(11) DEFAULT '0',
  `label` varchar(255) NOT NULL,
  `fk_accounting_category` int(11) DEFAULT '0',
  `fk_user_author` int(11) DEFAULT NULL,
  `fk_user_modif` int(11) DEFAULT NULL,
  `active` tinyint(4) NOT NULL DEFAULT '1',
  `import_key` varchar(14) DEFAULT NULL,
  `extraparams` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`rowid`),
  UNIQUE KEY `uk_accounting_account` (`account_number`,`entity`,`fk_pcg_version`),
  KEY `idx_accountingaccount_fk_pcg_version` (`fk_pcg_version`),
  KEY `idx_accounting_account_account_number` (`account_number`),
  KEY `idx_accounting_account_account_parent` (`account_parent`),
  CONSTRAINT `fk_accounting_account_fk_pcg_version` FOREIGN KEY (`fk_pcg_version`) REFERENCES `llx_accounting_system` (`pcg_version`)
) ENGINE=InnoDB AUTO_INCREMENT=109083 DEFAULT CHARSET=utf8;
a-schild commented 5 years ago

This fixes the problem in the products table:

ALTER TABLE `llx_product`
    CHANGE COLUMN `accountancy_code_sell` `accountancy_code_sell` VARCHAR(32) NULL DEFAULT NULL AFTER `fk_barcode_type`,
    CHANGE COLUMN `accountancy_code_sell_intra` `accountancy_code_sell_intra` VARCHAR(32) NULL DEFAULT NULL AFTER `accountancy_code_sell`,
    CHANGE COLUMN `accountancy_code_sell_export` `accountancy_code_sell_export` VARCHAR(32) NULL DEFAULT NULL AFTER `accountancy_code_sell_intra`,
    CHANGE COLUMN `accountancy_code_buy` `accountancy_code_buy` VARCHAR(32) NULL DEFAULT NULL AFTER `accountancy_code_sell_export`;
a-schild commented 5 years ago

Same here /compta/resultat/index.php?mainmenu=accountancy&leftmenu=accountancy_report

SELECT b.doc_ref, b.numero_compte, b.subledger_account, b.subledger_label, pcg_type, date_format(b.doc_date,'%Y-%m') as dm, sum(b.debit) as debit, sum(b.credit) as credit, sum(b.montant) as amount FROM llx_accounting_bookkeeping as b, llx_accounting_account as aa WHERE b.numero_compte = aa.account_number AND b.entity = 1 AND ( (pcg_type = 'EXPENSE') OR (pcg_type = 'INCOME')) AND fk_pcg_version = 'PCG_ZENSHIN' AND b.doc_date >= '2016-01-01 00:00:00' AND b.doc_date <= '2019-12-31 23:59:59' GROUP BY b.doc_ref, b.numero_compte, b.subledger_account, b.subledger_label, pcg_type, dm

How to fix it:

ALTER TABLE `llx_accounting_bookkeeping`
    CHANGE COLUMN `numero_compte` `numero_compte` VARCHAR(20) NULL DEFAULT NULL AFTER `thirdparty_code`;

and also in llx_accounting_journal


ALTER TABLE `llx_accounting_journal`
    CHANGE COLUMN `code` `code` VARCHAR(20) NULL DEFAULT NULL AFTER `rowid`;
a-schild commented 5 years ago

Some more:

ALTER TABLE `llx_bank_account`
    CHANGE COLUMN `accountancy_journal` `accountancy_journal` VARCHAR(20) NULL DEFAULT NULL AFTER `account_number`;

ALTER TABLE `llx_c_paiement_temp`
    ALTER `code` DROP DEFAULT;
ALTER TABLE `llx_c_paiement_temp`
    CHANGE COLUMN `code` `code` VARCHAR(6) NOT NULL AFTER `entity`,
    CHANGE COLUMN `libelle` `libelle` VARCHAR(62) NULL DEFAULT NULL AFTER `code`,
    CHANGE COLUMN `accountancy_code` `accountancy_code` VARCHAR(32) NULL DEFAULT NULL AFTER `active`,
    CHANGE COLUMN `module` `module` VARCHAR(32) NULL DEFAULT NULL AFTER `accountancy_code`;

ALTER TABLE `llx_c_type_fees`
    CHANGE COLUMN `accountancy_code` `accountancy_code` VARCHAR(32) NULL DEFAULT NULL AFTER `label`;

ALTER TABLE `tmp_llx_accouting_account`
    ALTER `account_number` DROP DEFAULT,
    ALTER `fk_pcg_version` DROP DEFAULT;
ALTER TABLE `tmp_llx_accouting_account`
    CHANGE COLUMN `account_number` `account_number` VARCHAR(32) NOT NULL AFTER `MAXID`,
    CHANGE COLUMN `fk_pcg_version` `fk_pcg_version` VARCHAR(32) NOT NULL AFTER `entity`;

In other modules

ALTER TABLE `llx_expeditiondet_batch`
    CHANGE COLUMN `batch` `batch` VARCHAR(30) NULL DEFAULT NULL AFTER `sellby`;

ALTER TABLE `llx_product_batch`
    CHANGE COLUMN `batch` `batch` VARCHAR(30) NULL DEFAULT NULL AFTER `sellby`;

ALTER TABLE `llx_product_lot`
    CHANGE COLUMN `batch` `batch` VARCHAR(30) NULL DEFAULT NULL AFTER `fk_product`;

ALTER TABLE `llx_stock_mouvement`
    CHANGE COLUMN `batch` `batch` VARCHAR(30) NULL DEFAULT NULL AFTER `inventorycode`;
aspangaro commented 5 years ago

Hi @a-schild

Just run the tools include in Dolibarr : /install/repair.php?force_utf8_on_tables=confirmed

aspangaro commented 5 years ago

Please close this issue. Thanks

TopNiz commented 5 years ago

@aspangaro : where is this tool documented ? Thanks in advance. (I will close the issue after)

thom4parisot commented 5 years ago

Thanks @aspangaro, it worked for me 👍