Mysql – Select data at row number in warning

MySQLmysql-5.6warning

When running show warnings in mysql, sometimes the results give a row number, such as "Warning | 1265 | Data truncated for column 'identifier' at row 12343".

I'm interested in doing something like:

select identifier from tablename where rownum = 12343

but of course this fails because there is no column named rownum. The table does not have an auto_increment column. I tried:

alter table tablename add column rownum int auto_increment unique first

but when I ran the select query again, the result did not seem to match the warning (it was a 9-character value in a varchar(12) column).

What's the best way to see the data referenced by mysql warnings? In this question I'm not so much asking about why the error is occurring, but am asking about how to view the data that is triggering the warning.

Best Answer

LIMIT 12342,3 -- to get the row and the adjacent rows. No need to worry about the PRIMARY KEY, etc. the query will probably be in the right order.

Related Solutions

Mysql – Data Truncated for Column

What is the the value of float_one at the time of conversion ???

Note this example from MySQL 5.5.12 in Windows

mysql> select convert(20000,decimal(7,2));
+-----------------------------+
| convert(20000,decimal(7,2)) |
+-----------------------------+
|                    20000.00 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select convert(200000,decimal(7,2));
+------------------------------+
| convert(200000,decimal(7,2)) |
+------------------------------+
|                     99999.99 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'convert(200000,decimal(7,2))' at row 1 |
+---------+------+-----------------------------------------------------------------------+

1 row in set (0.00 sec)

It may be possible that data was truncated if a number bigger than 99999.99 was in float_one. Perhaps, mysql was converting float_one and float_two to DECIMAL(7,2) individually before performing division. Try using DECIMAL(10,2) or greater to accommodate large values.

UPDATE 2011-07-25 15:05 EDT

There are definite truncation problem going on here

According to MySQL Reference Manual Page 442 Paragraphs 2,3

DECIMAL and NUMERIC values are stored as strings, rather than as binary floating point numbers, in order to preserve the decimal precision of those numbers. One character is used for each digit of the value, the deciaml point (if scale > 0) and the - sign (for negative numbers). If the scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.

The maximum range of DECIMAL and NUMERIC is the same as for DOUBLE, but the actual range for the given DECIMAL or NUMERIC column can be constrainted by the precision and scale for a give column, When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is rounded to that scale. When a DECIMAL or NUMERIC column is assigned a value whose magnitude exceeds the range implied by the specified (or defaulted) precision and scale, MySQL stores the value representing the corresponding endpoint of that range.

You need to accommodate a larger precision and/or scale.

Here is an example as to why

I wrote this stored procedure using your specifications for DECMIAL and NUMERIC.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`NumTest` $$
CREATE PROCEDURE `test`.`NumTest` (num1 NUMERIC(7,2), num2 NUMERIC(7,2))
BEGIN

  DECLARE float_one,float_two,my_result NUMERIC(7,2);
  DECLARE f1,f2 DOUBLE(7,2);

  SET f1 = num1;
  SET f2 = num2;

  SET float_one = num1;
  SET float_two = num2;

  SELECT f1 / f2;
  SELECT float_one / float_two;
  SELECT CONVERT(float_one / float_two,DECIMAL(7,2));
  SET my_result = CONVERT(float_one / float_two,DECIMAL(7,2));
  SELECT my_result;

END $$

DELIMITER ;

I used two values: 290.0 and 14.5 for this test.

Before calling the NumTest stored procedure, I manually calculated 290.0 / 14.5

mysql> select 290.0 / 14.5;
+--------------+
| 290.0 / 14.5 |
+--------------+
|     20.00000 |
+--------------+
1 row in set (0.00 sec)

I divided each number by 100, 10000, and 1000000 and tried again

mysql> select 2.9 / .145;
+------------+
| 2.9 / .145 |
+------------+
|   20.00000 |
+------------+
1 row in set (0.00 sec)

mysql> select .029 / .00145;
+---------------+
| .029 / .00145 |
+---------------+
|    20.0000000 |
+---------------+
1 row in set (0.00 sec)

mysql> select .00029 / .0000145;
+-------------------+
| .00029 / .0000145 |
+-------------------+
|      20.000000000 |
+-------------------+
1 row in set (0.00 sec)

So far, so good !!! Now for the stored procedure.

mysql> call numtest(290.0,14.5);
+-----------+
| f1 / f2   |
+-----------+
| 20.000000 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             20.000000 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       20.00 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     20.00 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

So my orignal two numbers work fine. Let's divide them by 100 and try again.

mysql> call numtest(2.9,0.145);
+-----------+
| f1 / f2   |
+-----------+
| 19.333333 |
+-----------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|             19.333333 |
+-----------------------+
1 row in set (0.00 sec)

+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
|                                       19.33 |
+---------------------------------------------+
1 row in set (0.00 sec)

+-----------+
| my_result |
+-----------+
|     19.33 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'num2' at row 2 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

WAIT, we lost some precision. What happened ??? How did this happen ??? You need to accommodate more decmial digits (> 2)

UPDATE 2011-07-25 17:37 EDT

I substituted (7,2) with (10,7) in the stored procedure and got back the proper precision

mysql> call numtest(2.9,0.145);
+----------------+
| f1 / f2        |
+----------------+
| 20.00000000000 |
+----------------+
1 row in set (0.00 sec)

+-----------------------+
| float_one / float_two |
+-----------------------+
|        20.00000000000 |
+-----------------------+
1 row in set (0.01 sec)

+----------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(10,7)) |
+----------------------------------------------+
|                                   20.0000000 |
+----------------------------------------------+
1 row in set (0.03 sec)

+------------+
| my_result  |
+------------+
| 20.0000000 |
+------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.06 sec)

mysql>

Mysql – Why cp1256 collation is not 1 byte in MySQL

That's a limit of the InnoDB engine. It cannot index more than 767 bytes (for a single column)

See the manual:

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

By default, an index key for a single-column index can be up to 767 bytes
...
When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length

Best Answer

Related Solutions

Mysql – Data Truncated for Column

Mysql – Why cp1256 collation is not 1 byte in MySQL

Related Question