| Bug #11006 | Upgrade damages user permission tables | ||
|---|---|---|---|
| Submitted: | 1 Jun 2005 3:14 | Modified: | 4 Dec 2007 21:14 |
| Reporter: | Peter Zaitsev (Basic Quality Contributor) | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S2 (Serious) |
| Version: | 4.1.12 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[26 Jun 2005 4:36]
Aleksey Kishkin
tested on suse 9.3 with mysqls: 4.0.24 , 4.1.9 and 4.1.12-standard-sles
[26 Aug 2005 19:44]
Jim Winstead
i don't understand the problem here. the messages in the error log are just warnings, and there's no explanation of how the user permission tables are damaged.
[26 Aug 2005 20:36]
Peter Zaitsev
Jim, Please take a look at SHOW CREATE TABLE which I pasted for table after upgrade. `User` char(5) character set utf8 collate utf8_bin NOT NULL default '', `Password` char(5) character set utf8 collate utf8_bin NOT NULL Do you think 5 chars is enough to hold user name and password ? In 4.0 Password is 16 chars=16 bytes. As you set utf8 in my.cnf, MySQL 4.0 tables are instantly treated as utf8 and 16 bytes become just 5 chars. I have not tested it but I guess CHECK TABLE should complain as well.
[19 Oct 2005 0:08]
Jim Winstead
this appears to happen because the server RPM does not run mysql_fix_privilege_tables.
[9 Jun 2006 9:23]
Magnus Blåudd
The rpm installer stops the mysqld and then performs the install of system tables, setting up file permissions and ownership etc. The creation of system tables are piped from the mysql_create_system_tables script to "mysqld --bootstrap", we should make sure the mysql_fix_privilieges_table.sql also is piped to "mysqld --bootstrap" as this will upgrade any system tables that need upgrading.
[9 Jun 2006 17:51]
Magnus Blåudd
Looked a little at this and think we should probably run the mysql_fix_privileges_table.sql as part of the mysql_install_db.sh script and pipe it to the "mysqld --bootstrap", as it shouldn't "hurt" is we run it if the privilieges tables are already fixed. This is for 4.1 In 5.0, the mysql_upgrade _program_ will take care of running the required scripts.
[4 Dec 2007 21:14]
Konstantin Osipov
Only S1 bugs are fixed in 4.1, solved in 5.0/5.1
Description: The following scenario leads to broken permission tables: 1) Install OS which comes with pre 4.1 MySQL server, ie FC2 2) Upgrade to new MySQL server, ie 4.1.11 using RPM 3) set default character set to utf8 in my.cnf 4) Do second upgrade ie to MySQL 4.1.12, this will break grant ables: I guess you can skip #2 and just set charset to utf8 before upgrading to 4.1 but it is unusual use case. From error logs: Version: '4.1.12-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) 050531 20:03:18 [Warning] './mysql/user' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed 050531 20:03:34 [Warning] './mysql/columns_priv' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed 050531 20:03:34 [Warning] './mysql/db' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed 050531 20:03:34 [Warning] './mysql/func' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed 050531 20:03:34 [Warning] './mysql/host' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed 050531 20:03:34 [Warning] './mysql/tables_priv' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed Here is like privilege tables look: 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' ... The data in user table will be appropriately connected, meaning user names and passwords are lost. How to repeat: See above Suggested fix: I guess we need more gentle handling of permission tables. I guess we need to fix character set information in permission tables once upgrade is done so such problem does not happen.