FieldtypeDecimal: "Warning: 1265 Data truncated for column 'data' at row 1"

[hgassen]

Setup:

• ProcessWire latest
• PHP 7.4 (!)
• Using locale where decimal separator is a comma (not a dot), e.g. de_DE.utf8
• Field with FieldtypeDecimal (core), configured with 2 decimals

When trying to save a page with the (changed) decimal field in admin, I get the error

Warning: 1265 Data truncated for column 'data' at row 1

The new value is not saved. The error does not occur on PHP >= 8.0

The problem seems to be here:

https://github.com/processwire/processwire/blob/6783c4824b8a3b0afec18e2922c6bd8eec23aa94/wire/core/Sanitizer.php#L4545

After this line, the string $value has a decimal comma (instead of a dot).

For my use case, I could fix it by replacing this line with

$value = stripos("$value", 'E') ? rtrim(sprintf("%.15$f", (float) $value), '0') : number_format($value, 2, '.', '');

But there might be <> 2 decimals...

[ryancramerdesign]

@hgassen I'm not sure I fully understand the issue yet, or how to reproduce it. Does PHP give you a backtrace? That might be helpful to look at, so that we can see the path it took to get to the float sanitizer. For instance, maybe the FieldtypeDecimal needs to call the float sanitizer with a precision argument, or maybe it needs to do some preparation on the returned value before sending to MySQL. But I'm not yet clear if the issue is with the float sanitizer or FieldtypeDecimal. If you think it's with the float sanitizer, it would be worth trying to reproduce by calling $sanitizer->float(...) with an argument that will trip it up, and that should give us a clearer picture on how to fix it.

[hgassen]

As far as I understand, it is an issue with sanitizer when using a locale where decimal separator is a comma (not a dot), e.g. de_DE.utf8.

PHP 7.4:

$sanitizer->float(1.5, array( 'precision' => null, 'blankValue' => '', 'getString' => true, ));

Returns: 1,5 (comma)

PHP 8.x:

$sanitizer->float(1.5, array( 'precision' => null, 'blankValue' => '', 'getString' => true, ));

Returns: 1.5 (dot)

sanitizer->float() is called from FieldtypeDecimal (here) with the same arguments as above -- that is how I discovered the issue.

[knoedelsalat]

Came across this today when installing a Duplicator copy of a live site in a local DDEV environment.

A whole lot of data got truncated from the DB dump. Workaround was to manually export the DB via Adminer, then use

ddev import-db --file=my-dump-file.sql.zip

on the local machine. Everything working as expected.