#763 (ok 4.5) Warning before silent data conversion/truncation

[lakshmiprasanna]

1.In phpMyAdmin open the database
2.Try to insert a character set for integer type of values
for a primary key having auto increment property
3.See that it is accepting it
4.Try to retreive the record set
5.See that in place of characters it has just
autoincremented the values

[Marc Delisle]

Logged In: YES
user_id=210714

MySQL does silent type conversion. Read the manual.

[lakshmiprasanna]

Logged In: YES
user_id=1326931

These kind of silent data truncations, silent data type
conversions dont make sense for any end user especially if
he is using a graphical tool like phpMyAdmin. Any graphical
tool should make sure that the user understands what is
happening in the background silently.

I see phpMyAdmin as a great graphical tool which only
allows the user to administer the MySQL database, but also
should be helpful to the user to avoid any wrong doings or
anything that could corrupt or truncate the Data by giving
informative messages from time to time.

In this case, why it allowed inserting a character data into an
integer primary key which should be auto incremented?
It allowed entering wrong data into the field and later it is
correcting it by itself. Logically it doesnt make sense and
creates confusion for the user.

It should give the user an informative message while inserting
the character data into an integer filed like Do you want to
continue with the insertion of data? If you do so, your data
will be lost as MySQL does a silent conversion of data
types. This informative message is very important for any
user who is trying to use this one.

I think any tool like phpMyAdmin should target all ranges &
levels of users, not only expert users who know MySQL.
Novice users always appreciate some informative messages
to know what is happening behind the scene and correct their
way of working with MySQL database using this tool.

Hope you got my point.

[Marc Delisle]

Logged In: YES
user_id=210714

IMO this is not a bug but a feature request.

[pratik]

Seems like error is shown now, rather than truncating.

[Marc Delisle]

Your screenshot shows another kind of error.

[pratik]

Sorry,forgot to mention that the screenshot was actually for lakshmiprasanna's statement -"it allowed inserting a character data into an integer primary key".

[Marc Delisle]

The error shown in the screenshot is not a warning for a silent truncation.

[pratik]

I meant that it's not about silent truncation, rather its generated when we enter a character into primary field of type int.

[Deven Bansod]

Hi,

Currently we show the possibility of error due to exceeding the char limit or typing a different datatype instead of expected datatype by making the text box red.

Also, we show the MySQL warning "Warning: #1366 Incorrect integer value: 'asd' for column 'asd' at row 1" after the insertion of characters into AutoIncrement integer and the auto incremented value gets inserted and "Warning: #1265 Data truncated for column '123' at row 1" after insertion.

Does this request is about showing a possibility of such warnings beforehand so that the user may make necessary changes or is it something more ?

[Isaac Bennetch]

To me it appears as if this ticket should be closed. I'm on mobile so checking the commit history and fixing the ideas list aren't easy at the moment, but I'll try to later today. Until then, I'm not sure what went wrong but I'm pretty sure this is resolved.

[pratik]

The user wants that warning should be shown before the truncation which is still not implemented.

[Marc Delisle]

I am testing with MySQL 5.7.6 with a column CHAR(3). When trying to insert "abcd" I get an error "#1406 Data too long" and I stay on the insert panel. However with MySQL 5.5.40 I don't get a warning before the truncation, which is asked in this RFE.

[Aayush]

MySQL has fixed this issue in its latest version. Also in latest versions of PMA, input fields turn red on invalid/long input. So, is this feature still required for users having older version of MySQL?

[Marc Delisle]

In Firefox 36, for a CHAR(3) the input field does not turn red when entering "abcd" but does when entering "abcde", so something is not right here.

[Aayush]

I feel this is some other kind of bug. Let me reproduce it..

[Aayush]

In firebox 28 also, I can reproduce it..
I tried to alert maxlen variable in tbl_change.js. Surprisingly it is throwing value 4 instead of 3. Chrome is throwing 3.
I tested again with char (20). For this, it is throwing 20 only..
strange..

[Aayush]

oh sorry.. same thing is happening in chrome also. please see. max-length value is 4 in place of 3. I am looking for what is wrong happening.

[Aayush]

Hi marc, I think I found something. See insert_edit.lib.php line 1064. It has a comment saying "field size should be at least 4". So I don't feel this behavior is out of some accident. But it is intended, in the sense that min value of size is 4.
So, if u type just "a" or "ab" or "abcd", input field will not turn red at char(3).
Now what do you suggest, should I dig more to find why it is done so and change it to 1 (if possible). Your suggestions?

[Marc Delisle]

Hi,
I believe that a proper solution to this would be to display a clearer warning; might be based on something like jQuery validate.