2.7.8 Upgrade

BG

Hello,
It seems the mango update this morning is not working. It has been stuck at 40% for a while. The last line on the web page says that it was trying to write to an upgrade log.

Here is a copy of the last part of the

INFO 2016-03-16 12:58:46,176 (com.infiniteautomation.mangoApi.MangoApiReloadingProperties.checkForReload:146) - (mangoApiHeaders) Found updated file at /opt/mango/web/modules/mangoApi/classes/mangoApiHeaders.properties. Reloading properties
INFO 2016-03-16 12:58:46,366 (com.serotonin.m2m2.Lifecycle.loadLic:464) - Checking license...
INFO 2016-03-16 12:58:47,187 (com.serotonin.m2m2.db.BasePooledProxy.initializeImpl:36) - Initializing pooled connection manager
WARN 2016-03-16 12:58:48,748 (com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade:96) - Upgrading 'core' from 11 to 12
INFO 2016-03-16 12:58:48,763 (com.serotonin.m2m2.db.upgrade.DBUpgrade.createUpdateLogOutputStream:152) - Writing upgrade log to /opt/mango/logs/com.serotonin.m2m2.db.upgrade.Upgrade11.log
ERROR 2016-03-16 12:58:49,644 (com.serotonin.m2m2.Main.main:120) - Error during initialization
com.serotonin.ShouldNeverHappenException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [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 clob, primary key (id))engine=InnoDB; ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'clob, primary key (id))engine=InnoDB' at line 1
at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:102)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:34)
at com.serotonin.m2m2.db.DatabaseProxy.initialize(DatabaseProxy.java:180)
at com.serotonin.m2m2.Lifecycle.databaseInitialize(Lifecycle.java:602)
at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:280)
at com.serotonin.m2m2.Main.main(Main.java:116)
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [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 clob, primary key (id))engine=InnoDB; ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'clob, primary key (id))engine=InnoDB' at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:439)
at com.serotonin.m2m2.db.BasePooledProxy.runScript(BasePooledProxy.java:73)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.runScript(DBUpgrade.java:129)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.runScript(DBUpgrade.java:143)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.runScript(DBUpgrade.java:134)
at com.serotonin.m2m2.db.upgrade.Upgrade11.upgrade(Upgrade11.java:27)
at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:98)
... 5 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'clob, primary key (id))engine=InnoDB' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2761)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:431)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:404)
... 12 more
INFO 2016-03-16 12:58:49,649 (com.serotonin.m2m2.Lifecycle.terminate:344) - Mango Lifecycle terminating...
INFO 2016-03-16 12:58:49,673 (com.serotonin.m2m2.db.BasePooledProxy.terminateImpl:110) - Stopping database

I will attemp to reinstall version 2.7.6 but putting the zip in the home folder.

Brian

JoelHaggar

Looks like an issue specifically to upgrading on MySQL and something we should be able to fix first thing this morning. I'm not sure putting the 2.7.6 zip files back in you might need to restore a backup of your MySQL database.

When using MySQL as your database the best practice to follow is to first dump your database and then make a zip file of your Mango install folder this way you can always do a full roll back.

BG

Hello Joel,
I am not using the Mysql as the main database but I know that Mango uses some of the tables for things like events and users.

I have a recent Mango back up to restore. We do an automatic back up each morning approx 5 am local time here.

I am halfway through uploading the 2.7.6 core files so will continue with that.

Thank you for your reply.

Cheers

Brian

JoelHaggar

Hey Brian,

I think you should receck your settings, this error seems to be clearly a MySQL error and we only saw this error happen if using a MySQL database. You can look at your env.properties file and also go to the system settings page in Mango and see what it says as the Database type at the top.

ERROR 2016-03-16 12:58:49,644 (com.serotonin.m2m2.Main.main:120) - Error during initialization
com.serotonin.ShouldNeverHappenException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [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 clob, primary key (id))engine=InnoDB; ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

BG

Hello Joel,
I have expanded the 2.7.6 files over the existing files on the linux server. And restarted the service. I have also put the 5 module zip files for ver 2.7.6 into the home folder.

After restarting the Mango service, it tries to start 2.7.8:

[root@M-2-M ~]# ma-start: MA started with Process ID: 3547
INFO 2016-03-16 16:04:11,233 (com.serotonin.m2m2.Main.main:82) -** Starting Mango 2.7.8** with Schema Version: 12

And I can see the updated modules getting loaded as well.

After the modules get loaded it looks like the same lines get put in.

I take you point that it looks like a Mysql error but I did not specifically set up any MySQL tables. These must tables that Mango use natively and used without error in 2.7.6 or are new tables in ver 2.7.8

bad SQL grammar [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 clob, primary key (id))engine=InnoDB; ]

I don't know what table is 'jsonData'
It also looks like it is terminating something called Mango Lifecycle:

INFO 2016-03-16 16:04:14,263 (com.serotonin.m2m2.Lifecycle.terminate:344) - Mango Lifecycle terminating...
INFO 2016-03-16 16:04:14,279 (com.serotonin.m2m2.db.BasePooledProxy.terminateImpl:110) - Stopping database

Now I cannot get back into my mango to check any settings in any event.

Any advice would be greatly appreciated.

Brian

phildunlap

Hi Brian,

I put a new Core version, 2.7.10, out to fix this issue.

BG

So do I now need to do a full re-install?

I can't get back to our mango site to do an upgrade the normal way.

Brian

phildunlap

Brian,

Please check your env.properties for your database setting. It would appear to me Mango is trying to create a new database on MySQL.

Yeah, you will need to drop the m2m2-core-2.7.10.zip into your Mango/ directory, then relaunch.

phildunlap

Should that not be sufficient to resolve it, you can manually set the database back to the 2.7.6 schema and let .10 do the upgrade again, properly.

Step 1) Gain contact with the database...
H2:

Navigate to Mango/bin/h2-web-console.sh
Edit the script and add -webAllowOthers if you are not browsing from the same machine Mango runs on, and remember the port at the top (8081 by default).
Launch the script. Navigate to http://[ip]:h2-port/ in your browser
Fill in the credentials, and /path/to/Mango/databases/mah2 then connect

MySQL:

command line, mysql --user=[env's db.user] --password=[env's db.password] [databaseName]

Run the commands...

select * from systemSettings where settingname='databaseSchemaVersion'; is 11, is 12?
if 11,
Please post error from trying to start 2.7.10
if 12,
update systemSettings set settingvalue='11' where settingname='databaseSchemaVersion'; drop table jsonData;

Disconnect from the database, and finally, start up with 2.7.10 in your Mango/ directory.

BG

Hello Phil,
My env.properties in in the overrides folder and i have the following lines for the MySQL:

#MySQL database settings. Your MySQL instance must already be running and configured before this can be used.
db.type=mysql
db.url=jdbc:mysql://localhost/Mango_M2M
db.username=[valid username]
db.password=[valid password]

username and password removed but the ones listed are valid.

Again though the MySQL tables that are in that database are the ones created and used by Mango.

dataPointHierarchy, dataPoints, dataSources, eventHandlers, events, excelReportTemplates, excelReports, globalScripts, graphicalViews, mailingListInactive, mailingListMembers, mailingLists, maintenanceEvents, pointEventDetectors, pointLinks, pointValueAnnotations, pointValues, publishers, reportInstanceData, reportInstanceDataAnnotations, reportInstanceEvents, reportInstancePoints, reportInstanceUserComments, reportInstances, reports, scheduledEvents, selectedWatchList, systemSettings, templates, userComments, userEvents, users, watchListPoints, watchLists.

All of the data points are being kept in the NoSQL database.

#Database settings for conversion. If the db.* settings point to a new database instance, and the convert type setting
#is set, Mango Automation will attempt to convert from the convert.db.* settings to the db.* settings
#Note that database conversions should not be performed in the same step as an upgrade. First upgrade, then convert.
convert.db.type=
convert.db.url=${convert.db.url}
convert.db.username=${convert.db.username}
convert.db.password=${convert.db.password}

#Set the base path for where the NoSQL data will be stored
db.nosql.location=${ma.home}/databases/
#Set the folder name of the point value store
db.nosql.pointValueStoreName=mangoTSDB

These are the same settings I have been using for a while now with no problem.

I will try to force my service on to 2.7.10 as you suggest

Brian

phildunlap

Ah. I am sure you have it configured rightly. We would phrase that situation as using MySQL and the NoSQL module, since your system's configuration and even events will be in the MySQL database. Simply a misunderstanding.

BG

Hello Phil,
Due to our excrutiatingly slow broadband at times... I finally have 2.7.10 put into the mango home folder.

I have started your steps to correct this:

Step 1) Gain contact with the database...
H2:

Navigate to Mango/bin/h2-web-console.sh

DONE
Edit the script and add -webAllowOthers if you are not browsing from the same machine Mango runs on, and remember the port at the top (8081 by default).
DONE
Launch the script. Navigate to http://[ip]:h2-port/ in your browser
DONE
Fill in the credentials, and /path/to/Mango/databases/mah2 then connect
?? the path says it should be "mango/databases/mah2 but there is no mah2 folder or file in my databases folder

What do you want me to do from here?

Thanks

Brian

phildunlap

Use the MySQL instructions. You are on MySQL

BG

oops, yeah sorry, its been a long day.

BG

How much longer will you be available today?

phildunlap

I will be available for many more hours, and I am happy to work directly with you to fix this. I will send you a message with some contact options.

BG

Hopefully we will have this finished soon. Thanks for your help.

phildunlap

I have sent you an email, let's conduct the rest there and I'll post anything insightful at the end if there is something to share.