Data truncated for column 'base_scrap_material_cost' error on 12.9.3 update

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.

Try this Data truncated for column

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

1 Like

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

Thanks and kind regards from Spain,



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,


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

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

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


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,


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.

1 Like

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,


1 Like

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.