EveOnline - upgrading LMeve

As promised a few months ago, here's a guide on how to upgrade/update your LMeve installation to the lastest release. As usual, you will find the list of required below, and where to download them. Always use the lastest available. Those listed here were the ones available on the 8th of july.
This guide is by no way as complete as I would like, since I don't use a lots of functionality in LMeve, I may miss some points.
This is especially true for the LMeve database. I can't stress you enough, if your LMeve instance is used by multiple users and/or corporations, to backup your DB and/or snapshot your system prior any modifications.
You will find at the end of the article a way to re-inject your settings in the new LMeve DB.
If you are familiar with phpmyadm, most of the mysql stuff during this upgrade should be doable from that interface. I don't use it because I'm not familiar enough with mysql already, I don't want to add an other layer of fog in front of it :-)

If you have questions about LMeve per se, you should go to Lukas' dev site ((https://github.com/roxlukas/lmeve)
I'm just a lambda user of this tool, who struggled to install it the first time. These guides are there to help you out, but I'm definitively not an expert with LMeve.

Feel free to comment if you find these articles useful.



Enough talk, let's do it.

** files to download **
lmeve-0.1.57-beta.tar.gz    (https://github.com/roxlukas/lmeve/releases)   
YC-118-6_1.1_Icons.zip        (https://developers.eveonline.com/resource/resources)
YC-118-6_1.1_Renders.zip
YC-118-6_1.1_Types.zip
mysql-latest.tar.bz2        (https://www.fuzzwork.co.uk/dump/)



First, let's stop LMeve polling.
# crontab -l | sed 's/*/#*/' > /tmp/cron_temp; crontab /tmp/cron_temp; rm /tmp/cron_temp

***************************
*** Very important step ***
***************************
Backup LMeve BD. This is very important if you have an existing user database, tasks, messages that you want to keep in the new version.

# mysqldump -u root -p  --all-databases > /tmp/mysql_backup_20160708.sql
# gzip -9 /tmp/mysql_backup_20160708.sql

You may want to take a backup/snapshot of your VM now, before doing anything else.
# shutdown -h now
Take a snapshot/clone of your VM, and restart it.
In case something goes wrong for you, you will go back to that state afterwards.
(Taking a snapshot or cloning the VM is beyond the scope of this article)

Let's do the upgrade now.
As you will see, it will be way faster than a fresh install.

**************************************************************
* Naming convention for the rest of this article.
* My EVE DB is name "eve_aeg100_dbo", not the best name, but it was its name at the Aegis release.
* The LMeve specific DB is name, well, "lmeve" ;-)
**************************************************************


If you have followed my previous guide, your /opt/lmeve is a symlink to your current release of LMeve, thus, you could keep your current LMeve install in a separate directory while preparing the new one.

drwxrwxr-x  11 root root  1024 May 10 13:32 lmeve -> /opt/lmeve-0.1.54-beta
drwxrwxr-x  10 root root  4096 Jul 24  2015 lmeve-0.1.53-beta
drwxrwxr-x  10 root root  4096 Aug 24  2015 lmeve-0.1.54-beta

Stop mysqld and apache.    Mysqld isn't mandatory if you have only LMeve running on your VM. As I have multiple tools connecting to those DBs, I'd rather have them fail than fetching boggus data.
# systemctl stop mysqld
# systemctl stop httpd

# cd /opt
# cp /opt/lmeve/config/config.php /tmp/config.php
# rm lmeve
# tar xzvf ~user/lmeve-0.1.57-beta.tar.gz
# ln -s /opt/lmeve-0.1.57-beta  /opt/lmeve
# cp /tmp/config.php /opt/lmeve/config/config.php

To avoid too much garbage in the poller, edit your config.php to add the following lines:
$API_BASEURL="https://api.eveonline.com";
$CREST_BASEURL="https://crest-tq.eveonline.com";

(for example, at the end of the config file, just before the line "?>")


I do the following commands because my VM is running on my laptop which didn't have a full time Internet access, thus, I have a lot of garbage/errors which get logged for nothing, and which fill up my FS.
# cd /opt/lmeve/var
# rm -f http_errors.txt; ln -s /dev/null http_errors.txt
# rm -f poller.txt; ln -s /dev/null poller.txt

Extract images from CCP.
# cd /opt/lmeve/wwwroot/ccp_icons/
# unzip ~user/lmeve_upgrade/YC-118-6_1.1_Icons.zip
# mv Icons/* .
# ln -s items/* .
# rmdir Icons
# cd /opt/lmeve/wwwroot/ccp_img
# unzip ~user/lmeve_upgrade/YC-118-6_1.1_Types.zip
# mv Types/* .
# rmdir Types
# cd /opt/lmeve/wwwroot
# unzip ~user/lmeve_upgrade/YC-118-6_1.1_Renders.zip

LMeve application has been updated, now, let's take care of its DB, then, EveOnline DB.
*****************************************************************************
* WARNING ** WARNING ** WARNING ** WARNING ** WARNING ** WARNING ** WARNING *
*****************************************************************************
** As I was writing these lines, I realised that it is not the optimum way to do it.
** In my procedure, I drop the whole LMeve DB, which mean, destroying all users, roles, messages, tasks, etc.
** Since I don't use them, I have never pay attention to that issue.
** At the end of this guide, you will find a way to re-import the content these tables from your backup into your new LMeve DB.
*****************************************************************************

# systemctl start mysqld
# cd /opt/lmeve/data
# mysql -u root -p
mysql> drop database lmeve;
Query OK, 58 rows affected (0.35 sec)

mysql> create database lmeve;
Query OK, 1 row affected (0.00 sec)

mysql> quit
Bye
# mysql -u root -p lmeve < schema.sql
Enter password:
#

There's three way to import your corporate key into LMeve:
_ the GUI way, this function was added in LMeve-0.1.55 : See http://pozniak.pl/wp/?p=9577 for a picture (at the end of his article)
_ via phpmyadmin (http://your.lmeve.ip/phpmyadmin. Sorry, I never used this tool, I can't help you with that. But I've been told that it's quite intuitive.
_ via the command line, which is, of course, my prefered way :-)
# mysql -u root -p lmeve
mysql> insert into cfgapikeys values (1,'1234567','api-key-here');
Query OK, 1 row affected (0.01 sec)



# cd ~user/lmeve_upgrade
# bunzip2 -c mysql-latest.tar.bz2 | tar xf -
# cd sde-20160704-TRANQUILITY/
# mysql -u root -p eve_aeg100_dbo < sde-20160704-TRANQUILITY.sql

If you don't need to re-import your previous user DB, you could still re-create the admin/admin login/passwd for LMeve:
# php /opt/lmeve/bin/passwd.php

The last steps, is to remove the INSTALL file in /opt/lmeve/ and restore the crontab.
# rm /opt/lmeve/INSTALL
# crontab -e
 remove the # in front of the poller, save and exit.


That's it, we just have to restart the httpd, LMeve has been upgraded to 0.1.57-beta.



*************** IMPORTING PREVIOUS LMeve data (users, roles, etc) *****************************

At writing this upgrade guide, I noticed that it couldn't be complete if I don't deal with the existing informations such as users, roles, tasks and so on. Since I don't use messages nor tasks, I will do only the user/passwd part. For messages and  tasks, it will be same procedure, but with differents tables.

To restore our user-base and roles, we will recover our DB dump we did at the begining of this procedure, and uncompress it.
# ls -la /tmp/mysql_backup_20160708.sql
-rw-r--r-- 1 root root 325553334 Jul  8 09:56 /tmp/mysql_backup_20160708.sql

First thing first, make a copy :-) better worry than sorry.
Then edit your copy, and search for "LOCK TABLES `lmuserroles`", copy paste the lines between "LOCK TABLES" and "UNLOCK TABLES" (included) and add them in a file (inject.sql) Do the same research for lmusers, lmchars, lmconfig and for every other tables you want to restore (messages, lmtasks)


You will have to change the lmuserroles entry from:
    INSERT INTO
to
    INSERT IGNORE INTO
Because at lmuserroles creation (via schema.sql), an initial entry is created. It may be the case for some other tables. You will have to check in the /opt/lmeve/data/schema.sql.



It will look something like than in the end:

# cat inject.sql
LOCK TABLES `lmuserroles` WRITE;
/*!40000 ALTER TABLE `lmuserroles` DISABLE KEYS */;
INSERT IGNORE INTO `lmuserroles` VALUES (1,1),(2,1);
/*!40000 ALTER TABLE `lmuserroles` ENABLE KEYS */;
UNLOCK TABLES;

LOCK TABLES `lmusers` WRITE;
/*!40000 ALTER TABLE `lmusers` DISABLE KEYS */;
INSERT INTO `lmusers` VALUES (1,'admin','9fdd8383c10e946350e81c9202f08eba','192.168.51.1','30.07.2015 19:27',0,'css/rixxjavix.css',1),(2,'user','ce1ad3325b7ac3dbc0f230fcf876feee','192.168.51.1','08.07.2016 11:35',6,'css/crius.css',1);
/*!40000 ALTER TABLE `lmusers` ENABLE KEYS */;
UNLOCK TABLES;

LOCK TABLES `lmchars` WRITE;
/*!40000 ALTER TABLE `lmchars` DISABLE KEYS */;
INSERT INTO `lmchars` VALUES (XXX,2),(YYY,2),(ZZZZ,2);
/*!40000 ALTER TABLE `lmchars` ENABLE KEYS */;
UNLOCK TABLES;

... etc etc


Inject these in your mysql DB:
# mysql -u root -p lmeve < inject.sql
# mysql -u root -p lmeve

mysql> select * from lmuserroles;
+--------+--------+
| userID | roleID |
+--------+--------+
|      1 |      1 |
|      2 |      1 |
+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from lmusers;
+--------+-------+----------------------------------+--------------+------------------+-------------+-------------------+------+
| userID | login | pass                             | lastip       | last             | defaultPage | css               | act  |
+--------+-------+----------------------------------+--------------+------------------+-------------+-------------------+------+
|      1 | admin | 9fdd8383c10e946350e81c9202f08eba | 192.168.51.1 | 30.07.2015 19:27 |           0 | css/rixxjavix.css |    1 |
|      2 | user  | ce1ad3325b7ac3dbc0f230fcf876feee | 192.168.51.1 | 08.07.2016 11:35 |           6 | css/crius.css     |    1 |
+--------+-------+----------------------------------+--------------+------------------+-------------+-------------------+------+
2 rows in set (0.00 sec)

Once re-injected, your users should be able to connect as usual (I tried with my own user, it worked fine)