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

[Rouslan Placella]

On 01/09/2013 12:27 PM, Rouslan Placella wrote:
> On 01/09/2013 05:27 AM, Chanaka Dharmarathna wrote:
>> Hi,
>>
>>       > > 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:
>
> 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)
>
> mysql> insert into actor (actor_id, first_name, last_name)
> values (99999999,"bar","baz");
> Query OK, 1 row affected, 1 warning (0.00 sec)

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)

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 ?
>>
>> Regards !
>> --
>> Chanaka Dharmarathna