0

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.

4
  • 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
    Commented Apr 15, 2020 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
    Commented Apr 16, 2020 at 15:26
  • @Akina, in this case, the query that generated the warning was an alter table statement...
    – enharmonic
    Commented Apr 16, 2020 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
    Commented Apr 16, 2020 at 18:22

1 Answer 1

1

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.

1
  • 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
    Commented Apr 20, 2020 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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