Find MySQL row identified by number in a warning message

Question

The MySQL "show warnings" output identifies problematic rows by number. What's the best way to quickly see all the data for such a row?

For example, after running an update statement the result indicates "1 warning" and running show warnings gives a message like this: "Data truncated for column 'person' at row 65278". How can I select exactly that row?

Here is a concrete example exploring the limit solution:

create table test1 (
  id     mediumint,
  value  varchar(2)
);
insert into test1 (id, value) values
  (11, "a"),
  (12, "b"),
  (13, "c"),
  (14, "d"),
  (15, "ee"),
  (16, "ff");
update test1 set value = concat(value, "X") where id % 2 = 1;
show warnings;

That results in this warning output:

+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'value' at row 5 | 
+---------+------+--------------------------------------------+

To get just that row 5 I can do this:

select * from test1 limit 4,1;

resulting in this:

+------+-------+
| id   | value |
+------+-------+
|   15 | ee    | 
+------+-------+

So it seems that the limit offset (4) must be one less than the row number, and the row number given in the warning is for the source table of the update without regard to the where clause.

Answer 1

As far as I'm aware, the only way to select those rows is to just SELECT them using the criteria from your original UPDATE query:

mysql> UPDATE foo SET bar = "bar" WHERE baz = "baz";
mysql> SHOW WARNINGS;
...
Message: Data truncated for column 'X' at row 420
...
mysql> SELECT * FROM foo WHERE baz = "baz" LIMIT 420,1;

Obviously, this doesn't work if you've modified one or more of the columns that were part of your original query.

Answer 2

LIMIT x,y returns y number of rows after row x, based on the order of the resultset from your select query. However, if you look closely at what I just said, you'll notice that without an ORDER BY clause, you've got no way to guarantee the position of the row(s) you're trying to get.

You might want to add an autoincrement field to your insert or perhaps a trigger that fires before each insert, then use that index to ensure the order of the results to limit by.