Hi Rouslan,<br><br><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="HOEnZb"><div>>> > > I recently involved in fixing bug [0]. While fixing it, I was bit<br>
>> > confused that, PMA renders user input as it is while inline editing,<br>
>> > without getting real value saved in database. Yes, PMA do this after<br>
>> > checking the result of query execution function. But though result is<br>
>> > not an error, it doesn't mean that data is saved as it was.<br>
>> Mainly this<br>
>> > happens with numeric fields. Just type decimal value for column with<br>
>> > integer type and see what happens after refresh. As mentioned bug<br>
>> > description, MySQL truncate numeric data (not sure about other data<br>
>> > types) without noticing.<br>
>> ><br>
>> > Of course checking again the saved value is cost. But it's really<br>
>> odd to<br>
>> > see a less precise data, represent the real data. I think it's<br>
>> better to<br>
>> > clearly identify the data types, with or without having this<br>
>> behaviour.<br>
>> > So that, we can only recheck needed columns.<br>
>> ><br>
>> > When introducing inline edit functionality, this may have been<br>
>> > discussed. But I think better to discuss again. What do you think ?<br>
>><br>
>><br>
>> It sounds quite simple to me, so not sure if I'm missing something here.<br>
>><br>
>> Anyway, we could just check if any warnings were generated by the query<br>
>> (mysql_warning_count or similar function). If yes, then select the value<br>
>> and send it to the client along with the response. Right?<br>
>><br>
>><br>
>> Rouslan,<br>
>> Yeah, there are functions to detect warnings. But unfortunately this<br>
>> kind of queries doesn't throw warnings. As I mentioned MySQL doesn't<br>
>> notice about the data truncation.<br>
><br>
> You absolutely sure? Because to me it looks like MySQL *is* throwing<br>
> warnings. See this:</div></div></blockquote><div> </div><div>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.<br>
<br>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.<br> </div><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div class="HOEnZb"><div class="h5">
> mysql> use sakila;<br>
> Database changed<br>
><br>
> mysql> insert into actor (actor_id, first_name, last_name)<br>
> values ("foo","bar","baz");<br>
> Query OK, 1 row affected, 1 warning (0.00 sec)<br></div></div></blockquote><div><br>This success due to field is <b>auto increment</b>. Anyway you can't do this with inline edit.<br> </div><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div class="HOEnZb"><div class="h5">
> mysql> insert into actor (actor_id, first_name, last_name)<br>
> values (99999999,"bar","baz");<br>
> Query OK, 1 row affected, 1 warning (0.00 sec)<br></div></div></blockquote><div><br>Yeah, this happens regardless of auto increment or not. Can do with inline edit.<br>(I don't know why I couldn't execute this with my local db, may be due to version :( )<br>
</div><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div class="HOEnZb"><div class="h5">
</div></div>And how about another example with non-numerics:<br>
<div class="im"><br>
mysql> insert into actor (actor_id, first_name, last_name) values<br>
</div>(999,"baraaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa","baz");<br>
Query OK, 1 row affected, 1 warning (0.01 sec)<br></blockquote><div><br>Query is okay. This can't do with inline edit.<br> </div><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
mysql> show warnings;<br>
+---------+------+-------------------------------------------------+<br>
| Level | Code | Message |<br>
+---------+------+-------------------------------------------------+<br>
| Warning | 1265 | Data truncated for column 'first_name' at row 1 |<br>
+---------+------+-------------------------------------------------+<br>
1 row in set (0.00 sec)<br>
<div class="HOEnZb"><div class="h5"><br>
> Bye,<br>
> Rouslan<br>
><br>
>> I'll do this (re checking and return saved value) only for numeric since<br>
>> identified only those.<br>
>> Any objections ?<br clear="all"></div></div></blockquote></div><br>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.<br>
1. So I think it's better to recheck numeric fields as I already suggested.<br><br>2. Best solution is to recheck any column which can inline edit.<br><br>Any ideas on using 1,2 or any other criteria ?<br><br>Regards !<br>
-- <br><font size="4">Chanaka Dharmarathna<br></font><div><font color="#999999"><b>Virtusa (Pvt) Ltd. | </b></font><b style="color:rgb(153,153,153)">Sri Lanka</b></div>