Jump to content

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


bernhard
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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 by kongondo
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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).

  • Thanks 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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 ? 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

  • Like 2
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...