[bug] [issue-reported] SQLSTATE[01000]: Warning: 1265 Data truncated for column 'ratio' at row 1

[bernhard]

I get this error with the new Image field when trying to upload an image as non-superuser on LINUX + MariaDB 10.2.31 (local dev setup on win + mysql works).

Has anybody experienced something similar or can help me fix this issue? I narrowed it down to line 141 of FieldtypeImage: https://github.com/processwire/processwire/blob/dde4c92b784b3d89ef36fa3a731081b10e50422d/wire/modules/Fieldtype/FieldtypeImage.module#L141

When I hardcode the ratio to 1 everything works. When I dump the value via Tracy it says a ratio of 1.33; Hardcoding 1.33 does show the same error.

Any ideas?

[kongondo]

Not a solution, but just some thoughts (which you are probably aware of...)

That error can occur if either the data you are trying to insert is too big for the column or due to data mismatch. I doubt whether the former is the issue here.

The column ratio is of type Decimal (4,2). The fact that this works on your local setup but not on your remote site does not tell us much because the are dissimilar (i.e. MySQL versus maria DB). Have you checked your remote site to make sure the Image field DB schema updated correctly? E.g., is the ratio field of type Decimal?

What's your MySQL version? I seem to recall a change in decimal types between some MySQL versions. Could also be an issue with strict mode.

In addition, it is not clear whether you are stating that the problem only occurs for non-superusers on your remote site. Is this the case? Does it work for superusers?

Edit: see strict mode edit above

Edited April 20, 2020 by kongondo

[bernhard]

Hi @kongondo thx for jumping in ? 

20 minutes ago, kongondo said:

Have you checked your remote site to make sure the Image field DB schema updated correctly? E.g., is the ratio field of type Decimal?

Yes, I checked that.

21 minutes ago, kongondo said:

In addition, it is not clear whether you are stating that the problem only occurs for non-superusers on your remote site. Is this the case? Does it work for superusers?

Sorry if I was unclear. That's exactly the case. It works for superusers but it does not work for regular site editors. First I thought this was related to my repeater-setup. But I checked with a single image field on the home template and got the same result.

I have no idea how that can happen and why the value of ratio would be related to the users role. Different behaviour across different db systems is reasonable for me, but not across different pw roles ? 

Also, I don't understand why it does not accept a hardcoded value of 1.33 as decimal(4,2) for ratio?

[kongondo]

3 hours ago, bernhard said:

I have no idea how that can happen and why the value of ratio would be related to the users role. Different behaviour across different db systems is reasonable for me, but not across different pw roles ? 

Strange indeed. I'll try and replicate. I have Maria DB 10.4.10 in local setup (windows).

[adrian]

@bernhard - isn't this related to: 

 

[bernhard]

Thx @adrian this is indeed related. Totally forgot about that. But still, it does neither solve the problem (because saving the ratio as INT is at the moment not possible) nor does it explain anything (for me).

I thought that maybe saving the ratio as INT (1.33 = 133) might be better, but I guess that would also just be a hack, because saving 1.33 into a database cell should really not be a problem. I'm not that deep into databases... maybe @LostKobrakai @Robin S or @BitPoet have an idea?

[adrian]

5 hours ago, bernhard said:

When I dump the value via Tracy it says a ratio of 1.33

Is it 1.33 whether you're superuser or another role?

[adrian]

Try changing the DECIMAL ration field from 4,2 to 12,6 or something equally ridiculous to see what it's actually trying to save to that field.

[adrian]

Or perhaps a varchar just to make sure it's not trying to save something that isn't supported by the DECIMAL type at all.

[bernhard]

VARCHAR was a good idea adrian, thx! It tries to save 1,33 and not 1.33

So it seems to be related to locale settings. That explains why my superuser works (EN) while the other user does not (DE). Any ideas how that could be fixed and where (is this related to fieldtype code or is it a DB setting)? I guess it is related to PW because the user settings should not have any effect on the db?

[kongondo]

3 minutes ago, bernhard said:

So it seems to be related to locale settings. That explains why my superuser works (EN) while the other user does not (DE)

My guess. English decimal versus German comma ?

Meaning, it is what I mentioned earlier. A data mismatch...

[bernhard]

Just now, kongondo said:

My guess. English decimal versus German comma ?

Yes, obviously - but how to make sure that this is not an issue? ? I guess other users will run into the same problem if we don't take care of it ? 

[adrian]

3 minutes ago, bernhard said:

So it seems to be related to locale settings. That explains why my superuser works (EN) while the other user does not (DE). Any ideas how that could be fixed and where (is this related to fieldtype code or is it a DB setting)? I guess it is related to PW because the user settings should not have any effect on the db?

Yeah, I don' think MySQL's DECIMAL supports using a comma as the decimal separator. I believe PW needs to handle conversion from comma to period and back again on wakeup if the locale settings require it.

[kongondo]

1 minute ago, bernhard said:

Yes, obviously - but how to make sure that this is not an issue? ? I guess other users will run into the same problem if we don't take care of it ? 

True. It should be the work of the Fieldtype to make sure it is saving correctly formatted values to the DB. So, I would file a bug report

[bernhard]

$item['ratio'] = number_format($pagefile->ratio, 2, '.', '');

This works! ?

I guess waking up from the DB should not be an issue because it will always get a decimal dot from the DB...

I'll file an issue on github, thx for your help @adrian and @kongondo, VARCHAR was the key ? 

[kongondo]

Just FYI,

PHP has a NumberFormatter class.

https://www.php.net/manual/en/class.numberformatter.php

[bernhard]

https://github.com/processwire/processwire-issues/issues/1155