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.
So I think it's better to recheck numeric fields as I already suggested.
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