| Bug #59036 | mysql.user table has NOT NULL DEFAULT NULL columns | ||
|---|---|---|---|
| Submitted: | 19 Dec 2010 4:16 | Modified: | 19 Dec 2010 14:23 |
| Reporter: | Alfredo Kojima | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S3 (Non-critical) |
| Version: | 5.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Dec 2010 8:38]
Valeriy Kravchuk
Sounds like a reasonable feature request. I doubt we can change this in 5.5.x though.
[19 Dec 2010 10:04]
Peter Laursen
SHOW CREATE TABLE returns Create Table CREATE TABLE `user` ( ... `authentication_string` text COLLATE utf8_bin NOT NULL, PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' It seems to be an issue with DESCRIBE. Actually a TEXT cannot have a default. Too fast conclusion here!!
[19 Dec 2010 10:35]
Peter Laursen
@Alfred?Valeriy .. see this (DESCRIEB USER): Field Type Null Key Default Extra ---------------------- --------------------------------- ------ ------ ------- ------ ... ssl_cipher blob NO (NULL) x509_issuer blob NO (NULL) x509_subject blob NO (NULL) ... authentication_string text NO (NULL) In the *NULL* columns it says 'NO. (NULL) in default columns means *either* 1) column has default NULL 2) the output to DESCRIBE is NULL for the default value for that column: this happens if the column has no default. To decide that column has Default NULL there are 2 requirements that output DESCRIBE must satisfy: 1) *NULL* column must read 'YES' 2) *DEFAULT* column must read '(NULL)' In summary: When DESCRIBE returns (NULL) there may be two reasons 1) It *IS NULL* in the table definition 2) DESCRIBE *does not know what it is* (what is what NULL really means!). Is this elementary mistake as regards interpretation of server output reflecting the standards of Workbench codings (I think @Alfredo is in WB team - and I guess the same mistake explains why WB user management fails with MySQL 5.5 and this is why this report was posted!)?
[19 Dec 2010 10:36]
Peter Laursen
Not a bug! (but that said: it really would be nice if BLOBs and TEXT could have a default!)
[19 Dec 2010 11:40]
Peter Laursen
Besides a NOT NULL DEFAULT NULL column cannot be created at all (also not for columns where defaults are possible): CREATE TABLE nonsense (txt VARCHAR(10) NOT NULL DEFAULT NULL); -- Error Code : 1067 -- Invalid default value for 'txt'
[19 Dec 2010 12:08]
Valeriy Kravchuk
Peter, Indeed, it would be nice to have default values for text and blob columns, at least '' as default should be supported. See bug #21532 about this. On the other side, it is not clear if text and blob are really a must as data types for these columns. I wonder if long enough varchar/varbinary may be used instead, or separate table linked by user id with this one. That's why I still think we have a valid feature request (not a bug, as it works as designed) for changes of structure of mysql.user table. This new text column in 5.5.7+ leads to problems with Workbench handling of new users. Surely these problems must be fixed in Workbench, but other data type for this column, acceptance of NULL values for it, or support for default values for text columns would help to make less changes in some kinds of software... That's why I think feature request is, essentially, valid. Others may not agree and ignore it or set to 'Not a bug', 'Won't fix', 'Duplicate', whatever...
[19 Dec 2010 13:02]
Peter Laursen
"This new text column in 5.5.7+ leads to problems with Workbench handling of new users" Then fix the bug in Workbench. Other clients (at least phpMyAdmin and SQLyog - probably each and every with the exeption of WB) have no problems with it). There is also no difference between this new column and the 3 (old) BLOB-columns in this respect. My points: 1) The whole report is based on a wrong assumption that "mysql.user table has NOT NULL DEFAULT NULL columns". It HAS NOT! For the simple reason that this is not possible at all. 2) This is not not the output from DESCRIBE 'default' column should be understood. 3) The request to allow '' (empty string) is not related to mysql.user table. It is a limitation with TEXT and BLOB in MySQL As long as this is not possible the client must (in strict mode) explicitly enter ''. 4) The request to make the columns NULLable is also not specific for the next TEXT column. Same would apply to the BLOB columns. There must be some reason why it never was possible with those columns. Please do not apply 'a dirty patch' to the server alone in order to work around a bug in a client program. This is now a 'verified feature request' for the server but should have been rejected. Appropriate bug reports with WB exist. Further is is not clear what is requested really and what was verified.
[19 Dec 2010 13:20]
Peter Laursen
Besides if GRANT/REVOKE syntax is used it is no problem (not in strict mode either), as the server will handle it. So it looks like WB executes INSERT/UPDATE statements against mysql.user and does not use GRANT/REVOKE syntax (what every client should as it will ensure that it works properly with every server version)?
[19 Dec 2010 14:23]
Valeriy Kravchuk
OK, your points are valid. Surely Workbench (as any other good client) should use GRANT/REVOKE instead of explicit inserts/updates into the mysql.user table. Let's say there is nothing new requested here at server level that should be changed.
Description: The mysql.user table has some columns defined as NOT NULL DEFAULT NULL How to repeat: mysql> describe user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | <------ | x509_issuer | blob | NO | | NULL | | <------ | x509_subject | blob | NO | | NULL | | <------ | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(60) | NO | | | | | authentication_string | text | NO | | NULL | | <----- +------------------------+-----------------------------------+------+-----+---------+-------+ Suggested fix: Either change NOT NULL DEFAULT NULL to allow NULL values or change default to ''