Hi Rouslan,
> > I recently involved in fixing bug [0]. While fixing it, I wasbit
> confused that, PMA renders user input as it is while inlineediting,
> without getting real value saved in database. Yes, PMA do thisafter
> checking the result of query execution function. But thoughresult 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 columnwith
> integer type and see what happens after refresh. As mentionedbug
> description, MySQL truncate numeric data (not sure about otherdata
> types) without noticing. > > Of course checking again the saved value is cost. But it'sreally
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 youthink ?
It sounds quite simple to me, so not sure if I'm missing somethinghere.
Anyway, we could just check if any warnings were generated by thequery
(mysql_warning_count or similar function). If yes, then select thevalue
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 !