MySQL Bugs: #47775: MEM upgrader cannot handle separate MySQL from installation directory

Bug #47775	MEM upgrader cannot handle separate MySQL from installation directory
Submitted:	2 Oct 2009 1:40	Modified:	29 Jun 2010 10:58
Reporter:	Jonathon Coombes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Installing	Severity:	S2 (Serious)
Version:	1.x,2.x	OS:	Any
Assigned to:	BitRock Merlin	CPU Architecture:	Any
Tags:	mem, Merlin, upgrade

Description:
The upgrade binaries will do a backup of the existing MEM installation before proceeding with the install. This is mandatory and is not available as an option, unlike the data backup option. If the mysql directory does not exist, or any directories expected below it, then an error is returned saying that MEM cannot move the offending directory/file.

A similar error occurs if you have a symlink for the mysql directory as it cannot be moved across partitions when the upgrade binary tries the backup.

This is of vital importance for any customer running a large number of MySQL services which are being monitored. The recommendation is to separate the database from the monitoring server, but this bug makes it difficult to upgrade.

How to repeat:
A couple of scenarios that produced the same error:

1. Remove the mysql directory and create a symlink to a different partition for the MySQL binaries and data storage.

2. Remove the mysql directory and simply create a new one to try and trick it, and you will have to create every file and directory underneath as well.

Another scenario yet untested is if the Tomcat configuration is changed so that there is no mysql directory, but Tomcat points to a different server by IP address.

Suggested fix:
The upgrade software needs to allow for separation of MySQL from the Tomcat server. The main problem is with the backup feature, so look at having two separate options:

--createDataBackup <createDataBackup>

--createProgramBackup <createProgramBackup>

Workaround would seem to be to use the installer program instead of upgrade binary and then remove mysql directory and point to the old database install. Alternatively, change the xml configuration to point to the data elsewhere.

Gary Whizin writes: 
* Use cases:
** add full installer support for using existing mysql instance to manage repo
*** if mysql instance does not meet minimum mysql requirement, installer aborts...
** add update installer "   "   "   "
*** if mysql instance does not meet minimum mysql requirement, installer aborts...
*** before preceding with upgrade, we will warn user to backup/upgrade remote mysql instance...

Oops.  Forgot step #5 in each case.

Full Installer
--------------
1) Asks if you want to install a MySQL server or use an existing one.
2) If customer says "install one for me", behave as we do now.
3) Otherwise ask for hostname, port, username, and password.
4) Check the version of MySQL on the other box:
   mysql -uxxx -pxxx -hxxx -Pxxx -e "SELECT version()"
   - If you can't login, show error message and allow retry,
     possibly with new parameters (e.g. they entered the wrong port)
   - If you can, check version: if it's not >= a min version we
     specify, show error message and quit
5) If all is well, install everything as usual except for the local
   MySQL server

Upgrade Installer
-----------------
1) Use data in config.properties to determine if customer is using a
   remote MySQL server.
2) If not, behave as we do now.
3) If so, check the version on the other box:
   mysql -uxxx -pxxx -hxxx -Pxxx -e "SELECT version()"
   - If you can't login, show error message and allow retry
   - If you can, check version: if it's not >= a min version we
     specify, show error message and quit (i.e. they must upgrade the
     remote MySQL server themselves first)
4) If all is well, show a message reminding them to backup their data
   before proceeding with the rest of the upgrade.
5) Upgrade as you do now, but don't fail if you can't backup the
   existing MySQL server on that box (because it might now be there)

Hi Andy,

We are working on this new feature and we would like to get your feedback about some questions.

- The installer will ask to the user if he would like to use an existing database. If not, the installation will continue as usual.

- If the user select to use an existing database, the installer will check that it exists a MySQL client in the machine. If the MySQL client is not in the PATH, the installer will request the location of the MySQL client. Should the installer check the MySQL client version? Another option is to install only the MySQL client files that the installer will use to check the connection.

- The next page will request the IP/Hostname, Port, user and password of the MySQL database. Is it also necessary to ask to the user the socket location to support existing databases in the same machine for Unix platforms?

- The installer will check the connection and the MySQL database version.

- If the default "mem" database exists, the installer will ask to the user a different name for the database and it will check if this database does not exist.

- The installer will create the database and will populate it.

Please let us know if you have any question or suggestion about this approach.

BitRock,

Here are my responses, point by point.

- Sounds good.

- I think it's better to just go ahead and install the MySQL client files.  That way you know it's there, you know where it is, and you know it's a fairly recent version.

- It is not necessary to ask for the socket location.  The whole point of doing this (allowing them to use their own MySQL server rather than installing one as part of Merlin) is so they can put it on another machine, for performance reasons.

- Good.

- There is no need to check if the default "mem" database exists.  In our documentation for this scenario we will tell them to either create an empty "mem" DB or to be sure the login account has CREATE DATABASE privileges.

- How can the installer create the database and populate it?  It has no knowledge (AFAIK) of our database structure and initial data to populate it.  That's the job of the service manager.

I would just like to emphasis that the new features for allowing entry of the separate MySQL server details is great, but want to make sure the other side is addressed. The other half of the problem is when the upgrade software tries to copy the mysql directory from under the install directory during the upgrade. 

Please make sure that there is at least an option to avoid this action as part of the upgrade process. If you have large amounts of historical data, then even if you choose not to backup the data, it will still try and move the directory to a backup location as part of the upgrade.

Patch sent to Keith.

Keith Russell writes: 
Patch installed in versions => 2.2.0.1516.

Diego Medina writes: 
Ah, for the docs.

What I end up doing to install a remote mysqld is to use the same installer, and just rm -Rf the tomcat folder on the mysqld server 
(and edit the etc/defaults/mysqlmonitorctl.conf  file to have:

START_MYSQL=yes
START_TOMCAT=no)

This will allow me to run the upgrade installer on the mysqld host (and save me a few minutes running a manual upgrade)

Setting to documenting so that it is tracked.

A note has been added to the 2.2.0 changelog: 

        When upgrading an installation of &merlin_server; where the                                                                                        
        MySQL being used is not the bundled MySQL server, the upgrade                                                                                      
        would fail while creating a backup of the MySQL installation.

Martin MC Brown writes: 
Re-closing