[7.11.20]error inserting into table when a comma is in the "price" field (french version)

SuiteCRM Forum - English Language Installation & Upgrade Help
1

Hello,
Since update to the 7.11.20, i have an mysql error, when i add or modify a record with comma in the price field (invoice, quote, product module)
for exemple in my logs

[FATAL] Error inserting into table: aos_products: Query Failed: INSERT INTO aos_products (id,name,date_entered,date_modified,modified_user_id,created_by,description,deleted,maincode,part_number,type,cost,cost_usdollar,currency_id,price,price_usdollar,url,contact_id,aos_product_category_id)
					VALUES ('2f210a2a-9011-76ec-ced7-611129e53656','Test product','2021-08-09 13:12:28','2021-08-09 13:12:28','1','1','',0,'XXXX','','Good',0,0,'-99',0,6,0,6,'','',''): MySQL error 1136: Column count doesn't match value count at row 1

My currency is Euro, and my decimal symbol is “,”

Any idea ?
thanks

2

if you change decimal symbol to " . ", the products are added fine then?
in Edit Quote view, while adding line items, do you see any errors in console ?

3

Thanks for your help.

  1. No I have the same error

    SET name=‘Frais de déplacements (km)’,date_modified=‘2021-08-10 11:05:56’,modified_user_id=‘1’,created_by=‘a5bf91e5-7fb3-f020-6190-4ecbb59bd88c’,description=NULL,assigned_user_id=’’,maincode=‘XXXX’,part_number=‘frais km’,category=NULL,type=‘Service’,cost=0,6,cost_usdollar=0,6,currency_id=’’,price=0,price_usdollar=0,url=NULL,contact_id=’’,product_image=NULL,aos_product_category_id=’’
    WHERE aos_products.id = ‘4339359a-f14c-6489-9046-5d1afddc540e’ AND deleted=0: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘6,cost_usdollar=0,6,currency_id=’’,price=0,price_usdollar=0,url=NUL…’ at line 2

  2. Yes in french " Défaillance de la base de données. Veuillez consulter suitecrm.log pour plus de détails."
    And in the llogs

    Query Failed: SELECT COUNT(id) FROM ( SELECT aos_quotes.*, accounts.id AS account_id, accounts.name AS billing_account, opportunity_id AS opportunity, billing_contact_id AS billing_contact, ‘’ AS created_by_name, ‘’ AS modified_by_name, ‘’ AS assigned_user_name FROM aos_products JOIN aos_products_quotes ON aos_products_quotes.product_id = aos_products.id AND aos_products.id = ‘4339359a-f14c-6489-9046-5d1afddc540e’ AND aos_products_quotes.deleted = 0 AND aos_products.deleted = 0 JOIN aos_quotes ON aos_quotes.id = aos_products_quotes.parent_id AND aos_quotes.stage = ‘Closed Accepted’ AND aos_quotes.deleted = 0 JOIN accounts ON accounts.id = aos_quotes.billing_account_id – AND accounts.deleted = 0 GROUP BY aos_quotes.id ) AS aos_quotes LIMIT 1: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘’ at line 1

4

cost =0,6
this is obviously error part which means the price when is being saved should have been Quoted.

5

Thanks you, but why ? :slight_smile:

6

Its definitely coming from line_items.php
can you check following fix

7

Can you also share screenshot of your locale screen.

8

I’have found. It’s the PHP version. I change the PHP version from 7.3 to 7.1 and it’s working now.

Thanks you.

9

That doesn’t make much sense to me, since PHP 7.3 is supported, and many people use it without problems.

Changing PHP version also changes the php.ini being used, and there are several locale settings there. So I guess that’s probably what makes the difference… it would be worth investigating.

10

I 've checked php.ini there are very similar. I will t ry on a fresh install.

11

After a fresh install

with php 7.3
[FATAL] Error inserting into table: aos_products: Query Failed: INSERT INTO aos_products (id,name,date_entered,date_modified,modified_user_id,created_by,description,deleted,maincode,part_number,type,cost,cost_usdollar,currency_id,price,price_usdollar,url,contact_id,aos_product_category_id)
VALUES (‘ebde6d33-c621-962c-e3af-61162df107b2’,‘test’,‘2021-08-13 08:30:44’,‘2021-08-13 08:30:44’,‘1’,‘1’,’’,0,‘XXXX’,’’,‘Good’,0,6,0,6,’-99’,0,6,0,6,’’,’’,’’): MySQL error 1136: Column count doesn’t match value count at row 1

with php 7.1
INSERT INTO aos_products (id,name,date_entered,date_modified,modified_user_id,created_by,description,deleted,maincode,part_number,type,cost,cost_usdollar,currency_id,price,price_usdollar,url,contact_id,aos_product_category_id)
VALUES (‘1475f35e-e958-66d2-f486-61162eb85fbd’,‘test2’,‘2021-08-13 08:34:30’,‘2021-08-13 08:34:30’,‘1’,‘1’,’’,0,‘XXXX’,’’,‘Good’,0.6,0.6,’-99’,0.6,0.6,’’,’’,’’)

12

is Php 7.1 skipping the last column mismatch ?

13

I’am not sure to understand this question.

in php 7.1 the price field “0.6” is record as “0.6” => no mismatch
but with my php 7.3 version ther price field “0.6” become “0,6” => mismatch