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.

  • When server tells at row 12343 it means "in query's recordset". If you want to understand what record causes the problem you must use ORDER BY clause in your query, which' expression is unique in this recordset - this allows you to execute according SELECT with the same ordering and limitation, and obtain the problematic record. If the query type does not allow sorting you must transform it into the form which allows (for example, convert multi-table query to single-table with correlated subquery).
    – Akina
    Apr 15 '20 at 22:06
  • If you don't have a primary key, you can add a auto_increment and find the number. Else you can create a new table withou primary key and copy all data and add then the auto_increment. It is nit the easiest approach, if there are more thatn one row, you can find quick the correct row
    – nbk
    Apr 16 '20 at 15:26
  • @Akina, in this case, the query that generated the warning was an alter table statement...
    – enharmonic
    Apr 16 '20 at 15:55
  • the query that generated the warning was an alter table statement.. In this case the ordering matches to the clustered index expression.
    – Akina
    Apr 16 '20 at 18:22

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.

  • Thanks for the hint about using limit as an offset! From the docs: "The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants...With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)".
    – enharmonic
    Apr 20 '20 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.