[Phpmyadmin-devel] MySQL data truncation without warning effect to inline edit

[Rouslan Placella]

On 01/10/2013 11:47 AM, Chanaka Dharmarathna wrote:
> Hi Rouslan,
>
>      >>       > > I recently involved in fixing bug [0]. While fixing
>     it, I was bit
>      >>       > confused that, PMA renders user input as it is while
>     inline editing,
>      >>       > without getting real value saved in database. Yes, PMA
>     do this after
>      >>       > checking the result of query execution function. But
>     though result is
>      >>       > not an error, it doesn't mean that data is saved as it was.
>      >>      Mainly this
>      >>       > happens with numeric fields. Just type decimal value for
>     column with
>      >>       > integer type and see what happens after refresh. As
>     mentioned bug
>      >>       > description, MySQL truncate numeric data (not sure about
>     other data
>      >>       > types) without noticing.
>      >>       >
>      >>       > Of course checking again the saved value is cost. But
>     it's really
>      >>      odd to
>      >>       > see a less precise data, represent the real data. I
>     think it's
>      >>      better to
>      >>       > clearly identify the data types, with or without having this
>      >>      behaviour.
>      >>       > So that, we can only recheck needed columns.
>      >>       >
>      >>       > When introducing inline edit functionality, this may
>     have been
>      >>       > discussed. But I think better to discuss again. What do
>     you think ?
>      >>
>      >>
>      >>      It sounds quite simple to me, so not sure if I'm missing
>     something here.
>      >>
>      >>      Anyway, we could just check if any warnings were generated
>     by the query
>      >>      (mysql_warning_count or similar function). If yes, then
>     select the value
>      >>      and send it to the client along with the response. Right?
>      >>
>      >>
>      >> Rouslan,
>      >> Yeah, there are functions to detect warnings. But unfortunately this
>      >> kind of queries doesn't throw warnings. As I mentioned MySQL doesn't
>      >> notice about the data truncation.
>      >
>      > You absolutely sure? Because to me it looks like MySQL *is* throwing
>      > warnings. See this:
>
> I'm using MySQL 5.5.15 . I tried in my local database table( with auto
> incremented id field) the mentioned queries. But I get errors. But with
> sakila db, I could run those.
>
> Anyway I'm talking about the inline edit. And there are situations that,
> you can't do update existing field with inappropriate values using PMA
> inline edit.
>
>      > mysql> use sakila;
>      > Database changed
>      >
>      > mysql> insert into actor (actor_id, first_name, last_name)
>      > values ("foo","bar","baz");
>      > Query OK, 1 row affected, 1 warning (0.00 sec)
>
>
> This success due to field is *auto increment*. Anyway you can't do this
> with inline edit.
>
>      > mysql> insert into actor (actor_id, first_name, last_name)
>      > values (99999999,"bar","baz");
>      > Query OK, 1 row affected, 1 warning (0.00 sec)
>
>
> Yeah, this happens regardless of auto increment or not. Can do with
> inline edit.
> (I don't know why I couldn't execute this with my local db, may be due
> to version :( )
>
>     And how about another example with non-numerics:
>
>     mysql> insert into actor (actor_id, first_name, last_name) values
>     (999,"baraaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa","baz");
>     Query OK, 1 row affected, 1 warning (0.01 sec)
>
>
> Query is okay. This can't do with inline edit.
>
>     mysql> show warnings;
>     +---------+------+-------------------------------------------------+
>     | Level   | Code | Message                                         |
>     +---------+------+-------------------------------------------------+
>     | Warning | 1265 | Data truncated for column 'first_name' at row 1 |
>     +---------+------+-------------------------------------------------+
>     1 row in set (0.00 sec)
>
>      > Bye,
>      > Rouslan
>      >
>      >> I'll do this (re checking and return saved value) only for
>     numeric since
>      >> identified only those.
>      >> Any objections ?
>
>
> Just try with decimal values for int or smallint columns. Those doesn't
> throw warnings. And as you mentioned sometimes it throws. But still not
> the non-numerics with inline edit.
> 1. So I think it's better to recheck numeric fields as I already suggested.
>
> 2. Best solution is to recheck any column which can inline edit.
>
> Any ideas on using 1,2 or any other criteria ?

If there is no significant performance problem, I would go with #2, just 
to be on the safe side.

Bye,
Rouslan