search

phpmyadmin / phpmyadmin

A web interface for MySQL and MariaDB
https://www.phpmyadmin.net/
GNU General Public License v2.0
7.06k stars 3.36k forks source link

Warning before silent data conversion/truncation #5462

Closed pma-import closed 8 years ago

pma-import commented 18 years ago

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

pma-import commented 18 years ago

Logged In: YES user_id=210714

MySQL does silent type conversion. Read the manual.

pma-import commented 18 years ago
pma-import commented 18 years ago

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.

pma-import commented 18 years ago
pma-import commented 18 years ago
pma-import commented 18 years ago

Logged In: YES user_id=210714

IMO this is not a bug but a feature request.

pma-import commented 18 years ago
pma-import commented 13 years ago
pma-import commented 9 years ago
pma-import commented 9 years ago

Seems like error is shown now, rather than truncating.

pma-import commented 9 years ago

Your screenshot shows another kind of error.

pma-import commented 9 years ago

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

pma-import commented 9 years ago

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

pma-import commented 9 years ago

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

pma-import commented 9 years ago

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 ?

pma-import commented 9 years ago

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.

pma-import commented 9 years ago

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

pma-import commented 9 years ago

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.

pma-import commented 9 years ago

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?

pma-import commented 9 years ago

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.

pma-import commented 9 years ago

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

pma-import commented 9 years ago

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..

pma-import commented 9 years ago

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.

pma-import commented 9 years ago

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?

pma-import commented 9 years ago

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

pma-import commented 9 years ago

(y) I liked the idea of jQuery validation.. and I would like to implement that.. But even to implement that, it is important to to pass the value of "size" and "data max length" correctly even if it is char(1). your views..?

pma-import commented 9 years ago

Indeed, a CHAR(1) receiving a value too large must generate a pre-warning.

pma-import commented 9 years ago

So, I take it a yes from you to my last qstn, " if I should dig more to find why it is done so and change the possible minimum value of "size" and "data max length" to 1 (if possible)". Then I will start working on the jQuery validation. Thank you..

pma-import commented 9 years ago

Well, I don't remember why we put a minimum length of 4 there, so I'm not sure about your last question.

pma-import commented 9 years ago

ok.. If I find something. I will report to you. :)

pma-import commented 9 years ago

hi Marc. Today I made this request https://github.com/phpmyadmin/phpmyadmin/pull/1622 Please review this. I played around javascript and used jQuery form validation api. Since, I was not able to find the reason of min length 4 in char/varchar, so I didn't touched the php file, rather used some innovative way to fix this exception. I wanted to ask you one thing. When I was commiting my changes, I got this warning "LF replaced by CRLF" for the jQuery files "jquery.validate.js" and "additional-method.js" which I added in jquery folder. I searched on Google and used "git config --global core.autocrlf false" to remove warning. Please can you enlighten me on this. Thank you

pma-import commented 9 years ago

Hi Aayush, just to confirm, you are probably working on a Windows environment, right?

pma-import commented 9 years ago

Yes Marc.. I was working on windows.

pma-import commented 9 years ago

I am not familiar with working on git under Windows, please ask on the phpmyadmin-devel list. Meanwhile, your commit contains tab characters, please fix this.

pma-import commented 9 years ago

Ok.. I believe, I have already fixed the tab character issue with my 2nd commit. phpmyadmin-bot didn't report any such warning on my 2nd issue. Please see.. Basically that tab issue was in the jquery files I added. I forgot to check them for tab and added directly, which I corrected later.

pma-import commented 9 years ago

I confirm that the tab character was fixed; I was reading the early bot report.

pma-import commented 9 years ago