Data truncated for column 'base_scrap_material_cost' error on 12.9.3 update

[akrause]

I receive the error

pymysql.err.DataError: (1265, “Data truncated for column ‘base_scrap_material_cost’ at row 1”)

when I bench update from 12.8.0 to 12.9.3. Before the update, the field ‘base_scrap_material_cost’ in table ‘tabBOM’ is datatype VARCHAR. Shouldn’t this be DECIMAL? If I change it to DECIMAL(18,6) and disallow and remove nulls the update works.

[Manan_Shah]

Try this Data truncated for column - #2 by surajshetty

field probably has some NULL values which is causing the problem, try setting it as 0 from DB (bench mariadb), it might solve the problem

[pagliaso]

Hi, same use over here, when trying to update just now.

Thanks and kind regards from Spain,

Alb

[pagliaso]

Hello,

I am trying to do what you suggest with tabBOM, but I get an error message telling that the table does not exist, even though I see it in there?!

Any advice?

Many thanks and kind regards,

Alb

@akrause

when I bench update from 12.8.0 to 12.9.3. Before the update, the field ‘base_scrap_material_cost’ in table ‘tabBOM’ is datatype VARCHAR. Shouldn’t this be DECIMAL? If I change it to DECIMAL(18,6) and disallow and remove nulls the update works.

[akrause]

I used HeidiSQL to change the nulls to zero. You could also use the mysql command line client to run a sql statement.

[Manan_Shah]

Make sure you are querying the right database, check the DB name in site config file.

Try
SHOW TABLES; and see if the table exists.
tabBOM should exist, please double check the command as well.

[pagliaso]

Hello,

Many thanks for your replies.

I think I was using a wrong ’

I am writing this now:

update `tabBOM Type` set base_scrap_material_cost = '0' where trim(coalesce(base_scrap_material_cost, ''))= '';

I don’t get any error, but I don’t get any result either!?

Thanks for your suggestion to use Heidi @akrause. I tried, but my Mariadb server only accepts local connections, and I tried to do a ssh tunnel with no luck.

I don’t know if it is the heat we are having, but I am feeling I am all dumbs with this…

Thanks and kind regards from Spain,

Alb

[akrause]

I use Putty to create a tunnel to the server. This screenshot shows my setup.

I hope someone with ERPNext expertise can tell me if I am correct assuming the ‘base_scrap_material_cost’ should be datatype DECIMAL.

[pagliaso]

Hi @akrause,

Many thanks for your message. That was very kind.

With your screenshot I was able to connect Heidi to the database solve the issue with the nulls, and unlock my update that was stuck.

I am very sorry that I don’t know what type ‘base_scrap_material_cost’ should be.

Many thanks and kind regards from Spain,

Alb