8 Jan
2013
8 Jan
'13
3:05 p.m.
Hi All,
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 ?
[0] :
http://sourceforge.net/tracker/?func=detail&aid=3576788&group_id=23…
Regards !
--
Chanaka Dharmarathna
*Virtusa (Pvt) Ltd. | **Sri Lanka*
8 Jan
8 Jan
3:23 p.m.
New subject: [Phpmyadmin-devel] MySQL data truncation without warning effect to inline edit
On 01/08/2013 05:05 PM, Chanaka Dharmarathna wrote:
Hi All,
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?
Bye,
Rouslan
9 Jan
9 Jan
3:27 a.m.
New subject: [Phpmyadmin-devel] MySQL data truncation without warning effect to inline edit
Hi,
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.
I'll do this (re checking and return saved value) only for numeric since
identified only those.
Any objections ?
Regards !
--
Chanaka Dharmarathna
*Virtusa (Pvt) Ltd. | **Sri Lanka*
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?
10:27 a.m.
New subject: [Phpmyadmin-devel] MySQL data truncation without warning effect to inline edit
On 01/09/2013 05:27 AM, Chanaka Dharmarathna wrote:
Hi,
Mainly this
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)
Bye,
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. 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. I'll do this (re checking and return saved value)
only for numeric since
identified only those.
Any objections ?
Regards !
--
Chanaka Dharmarathna
10:49 a.m.
New subject: [Phpmyadmin-devel] MySQL data truncation without warning effect to inline edit
On 01/09/2013 12:27 PM, Rouslan Placella wrote:
On 01/09/2013 05:27 AM, Chanaka Dharmarathna wrote:
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)
Hi,
Mainly this
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) 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. 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. Bye,
Rouslan
> I'll do this (re checking and return saved value) only for numeric since
> identified only those.
> Any objections ?
>
> Regards !
> --
> Chanaka Dharmarathna
10 Jan
10 Jan
9:47 a.m.
New subject: [Phpmyadmin-devel] MySQL data truncation without warning effect to inline edit
Hi Rouslan,
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.
This success due to field is *auto increment*. Anyway you can't do this
with inline edit.
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 :( )
>
> 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)
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)
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*
Bye,
Rouslan
> I'll do this (re checking and return saved value) only for numeric since
> identified only those.
> Any objections ?
9:56 a.m.
New subject: [Phpmyadmin-devel] MySQL data truncation without warning effect to inline edit
On 01/10/2013 11:47 AM, Chanaka Dharmarathna wrote:
Hi Rouslan,
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.
If there is no significant performance problem, I would go with #2, just
to be on the safe side.
Bye,
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: 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 ? 
8 Jan
8 Jan
3:30 p.m.
Chanaka Dharmarathna a écrit :
Hi All,
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 ?
Chanaka,
Indeed, the fix should be extended for other cases where truncation or
type conversion occurs; for example when you grid edit A inside an
integer, the value stored is 0 but the displayed value is A until you
refresh the page.
--
Marc Delisle
http://infomarc.info
4258
days inactive
4260
days old
7 comments
3 participants
participants (3)
-
Chanaka Dharmarathna -
Marc Delisle -
Rouslan Placella