MySQL issue when disk full

Hello,
I fall into a problem with more than one Mango installations.
In particular I have developed an embedded system based on Ubuntu linux (9.04 server) on an embedded machine. I have installed OS on a CF Card, together with Tomcat, MySQL and Mango.
CF card had 3Gb of free space. After a while Tomcat logs fill all free space and both Tomcat, Mango and MySQL stopped work.
I tried then to remove manually logs files and this operation free quite all initial free space.
But... but both Tomcat, MySQL and, of course, Mango didn't start as well.

In particular MySQL return me that mango database is corrupted and cannot start.

Have you fall into this problem you too?
Have you any solution in order to restore corrupted mango database?
I would like to recover at least Mango configuration, in order to restore datalogger on another device.

Thank you for any help provided!
Best regards

Hi,

try to use the mysql admintools to recover the database...
IMHO the only thing you lost are the latest data, mysql shoud be transactional - so the last transaction is corruped...

Arne

This message was edited 1 time. Last update was at 04/07/2011 12:55:28

Dear Arne,
thank you for your answer.
I find a solution in this way:

1) I managed to restart Mysql database adding the following option to my.cnf file

2) Using mysqldump command I managed to backup database. I had to use separate commands in order to backup "configuration" tables and then datapoints one.

First command is used to save configuration and ignore datapoints table (corrupted).
The last one instead is used to save datapoints. It worked for a while and then exit with table corrupted error.
But I managed to save a lot of data.

Thank you

Hello again.

Next step after backup database is to restore it in a fresh new Mango installation.
At this point I stopped tomcat and, with a lot of patience, I used sql script obtained with mysqldump to restore database.
I have reinstalled exactly same version of Mango 1.11.2 so it should use same database structure.

However when tomcat try to start it return an exception:

4-lug-2011 10.21.04 org.apache.catalina.core.ApplicationContext log
INFO: Initializing Spring root WebApplicationContext
4-lug-2011 10.21.52 org.apache.catalina.core.StandardContext listenerStart
GRAVE: Exception sending context initialized event to listener instance of class com.serotonin.mango.MangoContextListener
com.serotonin.ShouldNeverHappenException: java.io.StreamCorruptedException: invalid stream header: C2ACC3AD
at com.serotonin.util.SerializationHelper.readObject(SerializationHelper.java:42)
at com.serotonin.mango.db.dao.ReportDao$ReportRowMapper.mapRow(ReportDao.java:86)
at com.serotonin.mango.db.dao.ReportDao$ReportRowMapper.mapRow(ReportDao.java:83)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:92)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:447)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:457)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:465)
at com.serotonin.db.spring.ExtendedJdbcTemplate.query(ExtendedJdbcTemplate.java:154)
at com.serotonin.db.DaoUtils.query(DaoUtils.java:184)
at com.serotonin.mango.db.dao.ReportDao.getReports(ReportDao.java:72)
at com.serotonin.mango.MangoContextListener.reportsInitialize(MangoContextListener.java:384)
at com.serotonin.mango.MangoContextListener.contextInitialized(MangoContextListener.java:92)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3843)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4342)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:525)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:926)
at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:889)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:492)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1149)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:719)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
at org.apache.catalina.core.StandardService.start(StandardService.java:516)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:57
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:28
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.commons.daemon.support.DaemonLoader.start(DaemonLoader.java:177)
Caused by: java.io.StreamCorruptedException: invalid stream header: C2ACC3AD
at java.io.ObjectInputStream.readStreamHeader(ObjectInputStream.java:782)
at java.io.ObjectInputStream.<init>(ObjectInputStream.java:279)
at com.serotonin.util.SerializationHelper.readObject(SerializationHelper.java:39)
... 40 more
4-lug-2011 10.21.52 org.apache.catalina.core.ApplicationContext log
INFO: Closing Spring root WebApplicationContext

What could it be? And, more important, how can I solve this?
Thank you all

Looks like the the binary data in the database are corrupted too ;-)

if its a small db you can post me the sql script I will try to figure it out.

Arne

Hi,

the mysqldump has a strange format, please try to use mysqlAdmin to backup the "original corrupted database ..."

I can restore the dump only as latin1, but it is utf8 - so the restored DB will be corrupted by the wrong charset, and there are BLOB fields.

Using "mysql -B -u root --database=mango --password=PASSWORD < Mysqldump-no_storico.sql" will produce a foreign key violation. what do you use for restoration?

If you store! blob field in mysqlQueryBrowser of dataSources, the first bytes must be 0xAC, 0xED and not 0xC2, 0xAC

Arne

Foreign key violation is due to table datapointsvalueannotations.
In mysqldump I sent you I excluded datapointsvalues table, because it weight 200MB.
Please exclude datapointsvalueannotations table and try again.

Thank you

OK,

try this.

Assuming mango is your database name on the local machine.

stop tomcat

Backup current mango - if not yet done!

Drop schema mango.

Recreate schema mango.

start tomcat - this will recreate a empty database.

stop tomcat

run this "mysql --default-character-set=utf8 -B -u USERNAME --database=mango --password=PASSWORD < DUMP_FILE.sql"

if you try to restore the file with mysqladmin/mysqlquerybrowser it will fail!

your database should now be OK

start tomcat

Arne

Dear Arne.....

YOU ARE A GENIUS!
I succeded to restore database correctly!

Thank you very much.

Given situation may be solved with the aid mysql recovery, which owns many different capabilities for settling out almost every trouble associated with mysql server files.

This message was edited 2 times. Last update was at 21/09/2011 12:43:20

Another little issue.
I have a database on MySQL with more than 7.800.000 rows on pointvalues table.
At the very start of Mango mysqld pid consume 98% of processor and I can't access web interface.
Is it possible this is due to lot of datas? I use Mango 1.12.5 and Mysql on Ubuntu server.
My machine has 512mb ram and 1ghz processor.

Thank you

---
UPDATE

I find that the problem is due to a serie of queries lanched when I start datasources.
Queries have this shape

select pv.dataType, pv.pointValue, pva.textPointValueShort, pva.textPointValueLong, pv.ts, pva.sourceType, pva.sourceId from pointValues pv left join pointValueAnnotations pva on pv.id = pva.pointValueId where pv.dataPointId=33 order by pv.ts desc

pv.dataPointId=33 is the changing part between queries.

What is this? Why this query is performed?
Is there a way to avoid it?

Thank you for all the answers.

--
UPDATE 14:40

In PointValueDao.java file there is the function to initialize very first value for variable.
Maybe this value is broken, because the limit field doesn't add any limit to the query sent to MySQL and in fact the query select ALL point values instead of latest one as expected..

private List<PointValueTime> pointValuesQuery(String sql, Object[] params, int limit) {
List<PointValueTime> result = query(sql, params, new PointValueRowMapper(), limit);
updateAnnotations(result);
return result;
}

Where is query function? Is it in Seroutils?

Thank you

@zlatan24: thank you for the advertising..
But it is not a database problem!

The class DaoUtils is in seroUtils.jar - so that is closed source ;-) maybe you can write that whole piece (com.serotonin.db.*) new and give the code to ScadaBR ...

I honesty doubt that there will be any future development of Mango (M2M2 yes, but that is closed source at all)

Arne

Excellent. Thanks for sharing!

This message was edited 1 time. Last update was at 23/09/2011 07:50:19

zumiani wrote:
UPDATE 14:40

In PointValueDao.java file there is the function to initialize very first value for variable.
Maybe this value is broken, because the limit field doesn't add any limit to the query sent to MySQL and in fact the query select ALL point values instead of latest one as expected..

private List<PointValueTime> pointValuesQuery(String sql, Object[] params, int limit) {
List<PointValueTime> result = query(sql, params, new PointValueRowMapper(), limit);
updateAnnotations(result);
return result;
}

Made a mistake.
It is not a broken function. It is developed to query all values and then take only the needed..
It could be much more faster if "limit" is inserted directly in the query passed to database.
I don't know how derby works, but MySQL has clause "LIMIT 0,x", where x is the count of elements to be returned.

Anway, if I set default cache size parameter to 1 in datapoint details, on Mango 1.12.4, it used the following function instead

public PointValueTime getLatestPointValue(int dataPointId) {
long maxTs = ejt.queryForLong("select max(ts) from pointValues where dataPointId=?",
new Object[] { dataPointId }, 0);
if (maxTs == 0)
return null;
return pointValueQuery(POINT_VALUE_SELECT + " where pv.dataPointId=? and pv.ts=?", new Object[] { dataPointId,
maxTs });
}

So, filtering with max(ts), it returned only latest value.

Thank you all