Bug #67959 Upgrading removes content of system tables (and folders)
Submitted: 21 Dec 2012 14:55 Modified: 15 Jan 2013 6:40
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Installing Severity:S1 (Critical)
Version:5.5.29 OS:Windows (7/64)
Assigned to: CPU Architecture:Any

[21 Dec 2012 14:55] Peter Laursen
Description:
I may have another report here about same.  But it still happens and it is a SERIOUS showstopper. But here I have a simpler testcase.

The problems is that when using the 'stand-alone' installer to upgrade *AND WHEN SKIPPING THE CONFIG WIZARD* (by unchecking 'launch config wizard now'), the 'mysql' and 'performance_schema' get emptied.

Now since there is an option to uncheck it should work!

How to repeat:
Have a previous 5.5 version installed as a service with /datadir in /program data

net stop mysql55

launch mysql-5.5.29-winx64.msi

when the installation has finished there is an OPTION to launch config wizard. UNCHECK hte option and press OK.

Now try to start service:

C:\Users\Peter>net start mysql55
Tjenesten MySQL55 starter.
Tjenesten MySQL55 kunne ikke startes.

Der opstod en systemfejl.

Systemfejlen 1067 opstod.

Processen sluttede uventet.

C:\Users\Peter>

Inspect file system:

>cd C:\ProgramData\mysql\MySQL Server 5.5\data\mysql
C:\ProgramData\mysql\MySQL Server 5.5\data\mysql>dir
Disken i drev C har ikke noget navn.
Diskens serienummer er 40AF-D553

 Indhold af C:\ProgramData\mysql\MySQL Server 5.5\data\mysql

21-12-2012  15:30    <DIR>          .
21-12-2012  15:30    <DIR>          ..
               0 fil(er)                0 byte
               2 mappe(r)  29.822.025.728 byte ledig
(so no files in the folder)

C:\ProgramData\mysql\MySQL Server 5.5\data\mysql>cd C:\Program Files\MySQL\MySQL
Server 5.5\data\mysql
C:\Program Files\MySQL\MySQL Server 5.5\data\mysql>dir
 Disken i drev C har ikke noget navn.
 Diskens serienummer er 40AF-D553

 Indhold af C:\Program Files\MySQL\MySQL Server 5.5\data\mysql

21-12-2012  15:30    <DIR>          .
21-12-2012  15:30    <DIR>          ..
10-12-2012  07:24             8.820 columns_priv.frm
10-12-2012  07:24                 0 columns_priv.MYD
10-12-2012  07:24             4.096 columns_priv.MYI
10-12-2012  07:24             9.582 db.frm
10-12-2012  07:24               880 db.MYD
10-12-2012  07:24             5.120 db.MYI
10-12-2012  07:24            10.223 event.frm
10-12-2012  07:24                 0 event.MYD
10-12-2012  07:24             2.048 event.MYI
10-12-2012  07:24             8.665 func.frm
10-12-2012  07:24                 0 func.MYD
10-12-2012  07:24             1.024 func.MYI
10-12-2012  07:24                35 general_log.CSM
10-12-2012  07:24                 0 general_log.CSV
10-12-2012  07:24             8.776 general_log.frm
10-12-2012  07:24             8.700 help_category.frm
10-12-2012  07:24            22.659 help_category.MYD
10-12-2012  07:24             3.072 help_category.MYI
10-12-2012  07:24             8.612 help_keyword.frm
10-12-2012  07:24            91.408 help_keyword.MYD
10-12-2012  07:24            16.384 help_keyword.MYI
10-12-2012  07:24             8.630 help_relation.frm
10-12-2012  07:24             9.252 help_relation.MYD
10-12-2012  07:24            19.456 help_relation.MYI
10-12-2012  07:24             8.770 help_topic.frm
10-12-2012  07:24           445.696 help_topic.MYD
10-12-2012  07:24            20.480 help_topic.MYI
10-12-2012  07:24             9.510 host.frm
10-12-2012  07:24                 0 host.MYD
10-12-2012  07:24             2.048 host.MYI
10-12-2012  07:24             8.778 ndb_binlog_index.frm
10-12-2012  07:24                 0 ndb_binlog_index.MYD
10-12-2012  07:24             1.024 ndb_binlog_index.MYI
10-12-2012  07:24             8.586 plugin.frm
10-12-2012  07:24                 0 plugin.MYD
10-12-2012  07:24             1.024 plugin.MYI
10-12-2012  07:24             9.996 proc.frm
10-12-2012  07:24                 0 proc.MYD
10-12-2012  07:24             2.048 proc.MYI
10-12-2012  07:24             8.875 procs_priv.frm
10-12-2012  07:24                 0 procs_priv.MYD
10-12-2012  07:24             4.096 procs_priv.MYI
10-12-2012  07:24             8.800 proxies_priv.frm
10-12-2012  07:24               693 proxies_priv.MYD
10-12-2012  07:24             5.120 proxies_priv.MYI
10-12-2012  07:24             8.838 servers.frm
10-12-2012  07:24                 0 servers.MYD
10-12-2012  07:24             1.024 servers.MYI
10-12-2012  07:24                35 slow_log.CSM
10-12-2012  07:24                 0 slow_log.CSV
10-12-2012  07:24             8.976 slow_log.frm
10-12-2012  07:24             8.955 tables_priv.frm
10-12-2012  07:24                 0 tables_priv.MYD
10-12-2012  07:24             4.096 tables_priv.MYI
10-12-2012  07:24             8.636 time_zone.frm
10-12-2012  07:24                 0 time_zone.MYD
10-12-2012  07:24             1.024 time_zone.MYI
10-12-2012  07:24             8.624 time_zone_leap_second.frm
10-12-2012  07:24                 0 time_zone_leap_second.MYD
10-12-2012  07:24             1.024 time_zone_leap_second.MYI
10-12-2012  07:24             8.606 time_zone_name.frm
10-12-2012  07:24                 0 time_zone_name.MYD
10-12-2012  07:24             1.024 time_zone_name.MYI
10-12-2012  07:24             8.686 time_zone_transition.frm
10-12-2012  07:24                 0 time_zone_transition.MYD
10-12-2012  07:24             1.024 time_zone_transition.MYI
10-12-2012  07:24             8.748 time_zone_transition_type.frm
10-12-2012  07:24                 0 time_zone_transition_type.MYD
10-12-2012  07:24             1.024 time_zone_transition_type.MYI
10-12-2012  07:24            10.630 user.frm
10-12-2012  07:24               212 user.MYD
10-12-2012  07:24             2.048 user.MYI
              72 fil(er)          886.220 byte
               2 mappe(r)  29.821.964.288 byte ledig

C:\Program Files\MySQL\MySQL Server 5.5\data\mysql>
(this is a `mysql` database after a fresh install

And same problem exist for the folder C:\ProgramData\mysql\MySQL Server 5.5\data\performance_schema. It is also empty

Now copy `mysql` and `performance_schema` folders from \program files to \program data and the server starts

C:\Users\Peter>net start mysql55
Tjenesten MySQL55 starter.
Tjenesten MySQL55 er startet.

... but all user privileges are gone of course.

Suggested fix:
Don't touch data!
[21 Dec 2012 15:00] Peter Laursen
The only good thing is that the isntaller creates a System Restore Point and content of the folders can be recreated from an NTFS 'shadow copy' (provided that System Restore is turned on for the partition where the /datadir resides)
[21 Dec 2012 15:07] Peter Laursen
Sanitized synopsis!
[21 Dec 2012 15:46] MySQL Verification Team
I couldn't repeat upgrading from 5.5.28 to 5.5.29 on Windows 7 Pro 64-bits:

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "%dir%";
+-----------------------------------------+---------------------------------------------------------+
| Variable_name                           | Value                                                   |
+-----------------------------------------+---------------------------------------------------------+
| basedir                                 | C:/Program Files/MySQL/MySQL Server 5.5/                |
| binlog_direct_non_transactional_updates | OFF                                                     |
| character_sets_dir                      | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
| datadir                                 | C:\ProgramData\MySQL\MySQL Server 5.5\Data\             |
| innodb_data_home_dir                    | D:\innodbfiles\                                         |
| innodb_log_group_home_dir               | .\                                                      |
| innodb_max_dirty_pages_pct              | 75                                                      |
| lc_messages_dir                         | C:\Program Files\MySQL\MySQL Server 5.5\share\          |
| plugin_dir                              | C:\Program Files\MySQL\MySQL Server 5.5\lib\plugin\     |
| slave_load_tmpdir                       | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp         |
| tmpdir                                  | C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp         |
+-----------------------------------------+---------------------------------------------------------+
11 rows in set (0.00 sec)

mysql>
[21 Dec 2012 15:51] Peter Laursen
.. so what could the culprit be and what more information could I provide?
[21 Dec 2012 16:09] MySQL Verification Team
My 5.5.28 configuration had the service started manually and the innodb_data_home_dir a custom configured. Now I will try to remove 5.2.29 and install 5.5.28 with defaults and test again.
[22 Dec 2012 13:11] MySQL Verification Team
seems to be a continuation of http://bugs.mysql.com/bug.php?id=67041 ..
[22 Dec 2012 13:15] Peter Laursen
Shane is right. The two are duplicates. My fault.

The only new thing is that the 'unified' installer is not at all invovled this time - what I suspected last time to be part of the problem.
[28 Dec 2012 8:48] MySQL Verification Team
Couldn't repeat this either.

I followed the instructions exactly, on a clean Windows 7 x64 
(never had mysql msi installed). Everything worked fine.  

o) Installed 5.5.28, checked it was running.
o) Stopped the service
o) Upgraded to 5.5.29, without running config wizard.   

Nothing went missing from 
C:\ProgramData\MySQL\MySQL Server 5.5\data  

All defaults:
mysql> show variables like '%dir%';
+-----------------------------------------+---------------------------------------------------------+
| Variable_name                           | Value                                                   |
+-----------------------------------------+---------------------------------------------------------+
| basedir                                 | C:/Program Files/MySQL/MySQL Server 5.5/                |
| binlog_direct_non_transactional_updates | OFF                                                     |
| character_sets_dir                      | C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |
| datadir                                 | C:\ProgramData\MySQL\MySQL Server 5.5\Data\             |
| innodb_data_home_dir                    |                                                         |
| innodb_log_group_home_dir               | .\                                                      |
| innodb_max_dirty_pages_pct              | 75                                                      |
| lc_messages_dir                         | C:\Program Files\MySQL\MySQL Server 5.5\share\          |
| plugin_dir                              | C:\Program Files\MySQL\MySQL Server 5.5\lib\plugin\     |
| slave_load_tmpdir                       | C:\windows\TEMP                                         |
| tmpdir                                  | C:\windows\TEMP                                         |
+-----------------------------------------+---------------------------------------------------------+
11 rows in set (0.00 sec)
[28 Dec 2012 8:49] MySQL Verification Team
error log showing no problems

Attachment: test.err (application/octet-stream, text), 3.13 KiB.