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

[Chanaka Dharmarathna]

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 ?

Regards !
-- 
Chanaka Dharmarathna
*Virtusa (Pvt) Ltd. | **Sri Lanka*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20130110/2ec8d1b2/attachment.html>