1

Highest value in column price is 165990.00 and lowest NULL (column has nullable options on) so i am in range.

I want change datetype (decimal 15,2 to 6,2) in my mysql table but i get this error:

1264 - Out of range value for column 'price' at row 534

How i can know which row it is? I if look to row with id 534 it looks ok (price = 2000.00)

EDIT:

Ok I can order tablo by price and keep looking but what means row 534? How i can find exactly that row?

Query:

ALTER TABLE `tablo` CHANGE `price` `price` DECIMAL( 6, 2 ) NULL DEFAULT NULL
Community
  • 1
  • 1
Lajdák Marek
  • 2,969
  • 8
  • 29
  • 58
  • Possible duplicate of [MySQL Error 1264: out of range value for column](http://stackoverflow.com/questions/14284494/mysql-error-1264-out-of-range-value-for-column) – jophab Jan 20 '17 at 13:51
  • Show your query. Because maybe the "out of range" don't came from the field itself but from a calculation you are doing, using the field. – Peter Jan 20 '17 at 14:08
  • @jophab his question is not how to handle an out of range column, but how to interpret 'row 534' therefore it's not a duplicate. – David V. Jun 30 '17 at 09:20

4 Answers4

2

OK, it's old but let's answer it.
It is the 534th row of your query to the database starting from 1 (i.e.: first row has index 1, not in the PHP or whatever file). Somewhere in your query you had a problem and it is trying to tell you where.

I truly hope my answer was helpful.

N. Boumakis
  • 83
  • 9
1

Did you check row number 534 instead of ID. A simple way is to order by descending of that column and you will get to know which all are exceeding the limit.

you can also try order by length(column) descending just to make sure its not doing an alphabetical sort or have -ve numbers.

Srihari Karanth
  • 2,067
  • 2
  • 24
  • 34
1

165990.00 needs DECIMAL(8,2) because there are a total of 8 digits.

What is the min non-NULL value? It might be -1234567.89, which would need (9,2).

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Yep :) my bad... Byt still my question is how i can in future get problematic row? Is there any way? – Lajdák Marek Jan 23 '17 at 08:58
  • The column declaration must be big enough to handle any future values. Or, you need to ALTER again to make it bigger. For `DECIMAL`, you have a tradeoff between taking too much space versus risking "out of range". – Rick James Jan 23 '17 at 17:36
0

I know this is an old question, but I had the same problem today, so I thought I'd share what I found.

You have to use (and increment) a counter in the query. Then you simply look for the result where the counter matches the row number you need. In my case, I needed to find row number 104,808. So I used:

SET @_counter = 0;
SELECT @_counter := @_counter+1 AS my_counter, T.*
FROM my_table_name T
HAVING my_counter = 104808;
AdamM
  • 116
  • 4