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