Bug #7539 Upgrade from 3.23.x to 4.1.x results in broken user tables.

• Submitted: 27 Dec 2004 8:05
• Modified: 28 Jan 2005 0:09
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server: Installing
• Severity: S3 (Non-critical)
• Version: 4.1.8
• OS: Linux (Linux)
• Assigned to: Jim Winstead
• CPU Architecture: Any

[27 Dec 2004 8:05] Peter Zaitsev

Description:
I upgraded  MySQL 3.23.58 shipped with Fedora Core 2  to MySQL 4.1.8 using RPMs, following by 
setting  default charset to utf8;

This resulted in very tricky problem: My "user" table was converted wrong way resulting in:

user  |CREATE TABLE `user` (
  `Host` char(20) character set utf8 collate utf8_bin NOT NULL default '',
  `User` char(5) character set utf8 collate utf8_bin NOT NULL default '',
  `Password` char(5) character set utf8 collate utf8_bin NOT NULL default '',
  `Select_priv` enum('N','Y') NOT NULL default 'N',
  `Insert_priv` enum('N','Y') NOT NULL default 'N',
  `Update_priv` enum('N','Y') NOT NULL default 'N',
  `Delete_priv` enum('N','Y') NOT NULL default 'N',
  `Create_priv` enum('N','Y') NOT NULL default 'N',
  `Drop_priv` enum('N','Y') NOT NULL default 'N',
  `Reload_priv` enum('N','Y') NOT NULL default 'N',
  `Shutdown_priv` enum('N','Y') NOT NULL default 'N',
  `Process_priv` enum('N','Y') NOT NULL default 'N',
  `File_priv` enum('N','Y') NOT NULL default 'N',
  `Grant_priv` enum('N','Y') NOT NULL default 'N',
  `References_priv` enum('N','Y') NOT NULL default 'N',
  `Index_priv` enum('N','Y') NOT NULL default 'N',
  `Alter_priv` enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Users and global privileges'

As you see "user" and "password" fields are way too short,  3 times shorted which means size 
of columns in bytes was used to compute utf8 length. 

Now we have series of the problems 

1) It should not happen at all perhaps.
2) After it is happens Server is still starts normally without any warnings printed into log file.
3) Adding new users works... until you restart MySQL server or flush grants.  Last one happens because GRANT modifies internal cache as well as grant tables and if modification returns warnings it does not care.    Also old users (added before upgrade continue to work). 

This is how passwords look:

mysql> select password from user;
+------------------+
| password         |
+------------------+
| 2994e33f4c94cd74 |
| 2994e33f4c94cd74 |
|                  |
|                  |
| 5edbb            |
+------------------+

As you can see old password are not "cut" while new ones are cut on insert. 

How to repeat:
See above

[5 Jan 2005 2:00] Jim Winstead

This happened because the default server character set was changed to utf8 in my.cnf before 
mysql_fix_privilege_tables was run.

To protect against this, the patch adds ALTER TABLE <table> MODIFY <column> char(<val>) ... 
commands for all of the char columns in the privilege tables, assuring they end up with the 
proper length.

(This will also make it obvious where to change those lengths if the size of those columns is 
changed in a future release.)

[28 Jan 2005 0:09] Paul DuBois

Mentioned in 4.1.9 change notes.