How can I see if MySQL truncated a field during an Update using Perl DBI

Question

When I run this Update SQL in DBVis, I get an error (Data truncation: Data too long for column 'Description' at row 1).

However, in Perl (DBI), this is not giving me an error I am guessing because the record actually was updated with the truncated value.

Can anyone tell me how to show this error?

score 7 · Accepted Answer · answered Dec 13 '10 at 20:25

7

As data truncations produce warnings you can use SHOW WARNINGS to see them. And you can promote warnings to fatal errors by setting server's SQL Mode to one that forbids truncations (e.g. TRADITIONAL).

answered Dec 13 '10 at 20:25

Eugene Yarmash

142,882
41
325
378

This works, but means I have to do a seperate sql execute. I was hoping I could get it somehow in the original one. – Jeremey Dec 13 '10 at 21:03
2

This is indeed the correct answer. Before you perform any queries (right after connecting would make sense), add $dbh->do("set sql_mode=traditional"); - if the data doesn't fit, you'll get an error, rather than MySQL silently munging it to fit. – David Precious Dec 14 '10 at 13:56
2

@David: you can also set sql_mode in `connect()`. See http://search.cpan.org/perldoc?DBI for an example – Eugene Yarmash Dec 14 '10 at 15:13

score 2 · Answer 2 · answered Dec 13 '10 at 20:25

2

When you say "this is not giving me an error", how are you checking for it? Usually, you can check the status of a DBI operation by checking the value of $DBI::err or $dbh->errstr. Also, you can force errors to be visible as exceptions if you set up your connection with the option RaiseError => 1.

See the documentation and search for "err" for detailed discussion of all the options available to you.

answered Dec 13 '10 at 20:25

Ether

53,118
13
86
159

I have tried setting RaiseError and nothing prints in $DBI:err or $dbh->errstr. – Jeremey Dec 13 '10 at 20:51
1

RaiseError will only make actual errors returned by MySQL become exceptions. By default, if MySQL is given data that doesn't fit the table schema, it munges it to fit (which is a horrendous idea, whoever thought that was sensible should be shot), and raises a warning rather than an error. The accepted answer above of using sql_mode will make it behave in a more sane fashion. (For instance, $dbh->do('set sql_mode=traditional') before you do any queries) – David Precious Dec 14 '10 at 13:59
@David: right, so errors become exceptions, but warnings don't become errors; you need "show warnings" in eugene's answer for that. – Ether Dec 14 '10 at 17:41

score 1 · Answer 3 · answered Dec 13 '10 at 20:24

1

Is interpreted as warnings instead of errors,

So,

show warnings;

details: http://dev.mysql.com/doc/refman/5.0/en/faqs-cjk.html#qandaitem-B-11-1-9

answered Dec 13 '10 at 20:24

ajreal

46,720
11
89
119

How can I see if MySQL truncated a field during an Update using Perl DBI

3 Answers3