Problem updating windows mango server

hornetgr

Hi Phil thank you for your quick response.

Our DB is MySQL
Contents of the particular log is null.
Upgrade is done from server screen and we see the command prompt but nothing different than the attached log

Best Regards

phildunlap

My mistake! I did not notice the log file, only the image. I am looking now...

phildunlap

Well, I can't say i know how it got in this state (I'll run some tests on that when I have a minute to sanity check) but this should be a path to resolution:

Open a MySQL terminal into your Mango's database, run the following commands. Don't worry if they give errors about already existing!

CREATE TABLE templates ( id int NOT NULL auto_increment, xid varchar(50) NOT NULL, name VARCHAR(255), templateType VARCHAR(50), readPermission VARCHAR(255), setPermission VARCHAR(255), data longblob NOT NULL, PRIMARY KEY (id) ) engine=InnoDB;
ALTER TABLE templates ADD CONSTRAINT templatesUn1 UNIQUE (xid);
ALTER TABLE dataPoints ADD COLUMN templateId INT;
ALTER TABLE dataPoints ADD CONSTRAINT dataPointsFk2 FOREIGN KEY (templateId) REFERENCES templates(id);

This is what Upgrade10 was trying to do for the most part. Then run

UPDATE systemSettings SET settingvalue='11' WHERE settingname='databaseSchemaVersion';

And it should all work!

hornetgr

Thx Phil, we will try again tomorrow (it is late night here in Greece now) and we will keep you posted.

hornetgr

Goodmorning Phil,

Well the upgrade is not going well either.

We did all the things that you send us and we had no luck at all i would say.
We attached a newer log to see.

We would like here to review the upgrade procedure that we did.

After shutdown mango and backup etc etc we did the following:

-delete lib directory
-verify that env properties file is at MA_HOME/overrides/properties directory
-We input all the commands you send us to the MySQL command line prompt (response is attached)
-unzip and copy all files of core 2.7.12 to MA_HOME and also place zip modules at web/modules directory
-replace the ma_start.bat and copy upgrade.bat withh the newer vesrion from your site to MA_HOME/bin

fire ma_start.bat

The rest is at the logs..
0_1479381266006_ma.log

0_1479381279814_mango sql log.txt

Best Regards
Stathis

phildunlap

How bizarre. I don't see the errors from the first post in the log you just attached. Did you restore a backup and then run the commands I included? If so, I suspect you skipped Upgrade 9 to 10 (not in your log) and only did the upgrade for 10 to 11, then tried 11 to 12. If I am mistaken, please share your Upgrade9.log file.

Your procedure looks fine, but you can't skip Upgrade 9. It would be considerably more painful to try to perform Upgrade 9 from the command line (unless your existing user permission settings are not very important to you, then you can run the commands in the mysql portion of https://github.com/infiniteautomation/ma-core-public/blob/2.7.x/Core/src/com/serotonin/m2m2/db/upgrade/Upgrade9.java

But I would encourage you to,

restore from a backup,
place Core zip in Mango/ directory
place module zips in Mango/web/modules
start Mango with ma-start.bat
IFF Mango fails in upgrade 10 with the 'templates table exists' SQL error, run the commands I gave you

hornetgr

Hi again,

The new script is not running at all!

we used the old script (ma-start) and it stopped at the upgrade 10 we did the commands and then again stops with an error.

the logs attached

0_1479399223522_ma (1).log

hornetgr

we forgot to mention that all the upgrade logs are null

hornetgr

And also we have another question.

Why we need the new scripts, its seems that the old ma-start is doing the same thing.
In all the other cases we used the new scripts and the did the same thing.

Sorry for the multi threads but i cannot find the edit button

phildunlap

Edit is in the gear icon in the bottom left of your post.

The scripts in the Mango/bin directory may have been updated. You can compare versions of the script on our Github, if you like: https://github.com/infiniteautomation/ma-core-public/blob/2.7.x/Core/scripts/ma-start.bat . I believe at some point there was an issue with how the update was being performed on Windows.

I suspect a lot of the SQL problems we're having are a result of doing the upgrades so many times. And, you're restoring from a MySQL dump, yes? Those typically begin with a bunch of DROP TABLE IF EXISTS `tableName`; statements, so it's not dropping the tables made by previous attempts at running because they're unknown at the time of the dump! I'll raise the idea of including the DROP TABLE IF EXISTS in future versions, but it does seem like something else would have to have gone wrong and we shouldn't presume the table ought be purged. So, in addition to potentially circumventing upgrade 10 if it fails from a clean restore, you may need to

DROP TABLE templates;
DROP TABLE jsonData;

before running the upgrades. Consider also renaming the files in your Mango/classes/ directory, log4j.xml --> normal-log4j.xml, debug-log4j.xml --> log4j.xml to enable debug logging. If you are letting the script unzip the upgrade (if it isn't just unzipped by now), though, you may wish to place it in your Mango/overrides/properties directory as log4j.xml, and skip renaming it.

phildunlap

You could also try just setting your database schema version to 12, since it seems like everything has already seemed run to some extent. Upgrade 11 is only two statements:

CREATE TABLE jsonData (id int not null auto_increment,xid varchar(50) not null, name varchar(255) not null, readPermission varchar(255), editPermission varchar(255), data longtext, primary key (id))engine=InnoDB;
ALTER TABLE jsonData ADD CONSTRAINT jsonDataUn1 UNIQUE (xid);

so consider

UPDATE systemSettings SET settingvalue='12' WHERE settingname='databaseSchemaVersion';

if

SHOW CREATE TABLE jsonData;

looks alright.

hornetgr

About the edit button , so silly of me , thanks.

Well after each unsuccessful upgrade attempt we always perform a clean install of mango, (we delete everything and copy from another location). We also restore from a database SQL dump that is exported before doing anything.

You send us much information and work for tomorrow. We will keep you informed.

hornetgr

Hi Phil,
well we settle for now at 2.6.0 version ( well its a start!) and we are going to try to upgrade to 2.7.12 next week..

No luck from 2.5.2 to 2.7.12 it is impossible with our setup. Numerous errors in MySQL as you predicted.
We also had many errors in 2.6.0 from a free bacnet datasource, that we deleted and after that 2.6 is working.

I am send you the ma log from early morning trials.

0_1479488668091_ma (2).log

hornetgr

Hi Phil

As we are clearly unsuccesful to upgrade to 2,7 versions ,we are at the proccess to upgrade to the latest 2.8.4. (i remind you that we are at 2.6 version)

Is this possible ?

Also do we have to delete tables from our MySQL schema or you have implemented to the latest version the solution to the problems that we encounter. You have advise us to drop some tables in the prvious atempts.

Thx
.

phildunlap

The database would undergo the same updates it would if it were upgrading to 2.7.

No the upgrades have not been updated to drop the tables, it slipped my mind to get "DROP IF EXISTS" into the mysql upgrade scripts, so you may have to drop the tables the upgrades create, or restore your MySQL dump into a new MySQL database.