Bug #26761 adminstrator user resources, negative values
Submitted: 1 Mar 2007 18:41 Modified: 15 Jun 2007 10:09
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Administrator Severity:S3 (Non-critical)
Version:1.2.10 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any
Tags: qc

[1 Mar 2007 18:41] Martin Friebe
Description:
In Mysql Administrator go to the user page, select an existing user, and go to the resource tab.

set max connection, max questions or max updates to a negative value.

Apply changes. No error will be shown, the server will store 0 in the permission cache. but the Administrator, will still show the negative value

How to repeat:
see above

Suggested fix:
a warning or error should be reported
[1 Mar 2007 21:21] Valeriy Kravchuk
Thank you for a problem report. Can you repeat this for any user? I tried with root@localhost and newly created user1, but I get error message:

"Error while storing the user information. The user might have been deleted. Please refresh the user list"

when I try to apply changes.
[1 Mar 2007 21:34] Martin Friebe
I have tried with a variety of users (as target), including root@localhost and test@%

I have tried against a 4.1 and a 5.0.38-debug server and it always happens (both over TCP)

I have just checked the debug logs, the Update sent contains the negative values, so it all depends on the server response.

And this means, it is probably the SQL_MODE. my server is accepting the invalid values, and will cut them off (with a warning)

If your server generates an error on the incorrect values, then that explains the difference.

Also I find your error msg notable: "the user may have been deleted", technical correct, but it has not been deleted. so maybe this error msg should be considered a 2nd bug.
[1 Mar 2007 21:42] Martin Friebe
If the above turns out correct, I would suggest:
- the input validation in the administrator can be improved, or left as it is.
- better interpretation of errors returned (differentiate from "user may be deleted")
- MUST check for warnings
- if warnings have been caused by the query, reload the data from the DB, and show the data as it has been saved (possible warn (with a list of fields) and highlight affected fields)
- be aware, that the warning can have been caused by username truncation => handle this with a special warning.
[2 Mar 2007 18:29] Valeriy Kravchuk
Please, try to repeat with a newer version, 1.2.10 (from GUI Tools 5.0-r10), and inform about the results. In case of the same problem, please, send the results of:

SHOW VARIABLES LIKE 'sql%';
[2 Mar 2007 18:32] Valeriy Kravchuk
Please, try to repeat with a newer version, 1.2.10 (from GUI Tools 5.0-r10), and inform about the results. In case of the same problem, please, send the results of:

SHOW VARIABLES LIKE 'sql%';
[13 Mar 2007 14:12] Martin Friebe
Yes, still reproducible with 1.2.10.

No error, no warning. The invalid values are cached, and will be displayed, until MysqlAdmin is restarted. Then 0 will be shown.

In the DB the values will be zero all time.

It is also noticeable, that non integer values (such as "11x" or "abc" are silently replaced by 0 => a warning would be better (better than silent replace))

The server connection is via TCP/IP, the server runs on a separate box (but that may or may not be relevant)

+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.0.33-log |
+---------------+------------+
+-----------------+-------------------------+
| Variable_name   | Value                   |
+-----------------+-------------------------+
| sql_big_selects | ON                      |
| sql_mode        | NO_UNSIGNED_SUBTRACTION |
| sql_notes       | ON                      |
| sql_warnings    | OFF                     |
+-----------------+-------------------------+
[10 Apr 2007 13:44] Valeriy Kravchuk
Please, try to repeat with a newer version, 1.2.11, and inform about the results.
[10 Apr 2007 14:50] Martin Friebe
Still present in 1.2.11. Tested versus mysql 5.0.40, sql modes as in previous post.

I found the following SQL in the servers debug output:
(left out the bit in the middle)

UPDATE mysql.user SET 
Select_priv='N', Insert_priv='N', Update_priv='N', Delete_priv='N', 
.........., Alter_routine_priv='N', max_questions='-9', max_updates='-9',
max_connections='-9', max_user_connections='-9' WHERE User='rr' AND Host='%'

If I execute it by hand, the following result is returned:
 Query OK, 0 rows affected, 4 warnings (0.54 sec)
 Rows matched: 1  Changed: 0  Warnings: 4
 Warning (Code 1264): Out of range value adjusted for column 'max_questions' at row 1
 Warning (Code 1264): Out of range value adjusted for column 'max_updates' at row 1
 Warning (Code 1264): Out of range value adjusted for column 'max_connections' at row 1
 Warning (Code 1264): Out of range value adjusted for column 'max_user_connections' at row 1

Apparently MAdmin does only check for errors, but not warnings? Since the userdat also appears to be cached, the values will be displayed, even if you view another user, and then go back.

My suggestions:
a) check for warnings (should be done on any SQL). And inform the end user about them
b) after updating any tables (or other info on the server). Re-read the information. Do this at least, if there had been errors or warnings
[15 May 2007 10:09] Sveta Smirnova
Thank you for the report.

I can see negative value after successfull update too, but this value changes to correct (0) after refreshing of user list. Do you experience same behaviour?
[15 Jun 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".