error migrating data to NoSQL: Errcode: 28 "No space left on device"

[AldoRamos]

Still having an odd problem migrating to NoSQL. I get an error:

java.sql.SQLException: Error writing file '/tmp/MYiO9VmW' (Errcode: 28 "No space left on device")

But /tmp is not full:

$ df -h /tmp; df -i /tmp
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           503M   36K  503M   1% /tmp
Filesystem     Inodes IUsed  IFree IUse% Mounted on
tmpfs          128614    25 128589    1% /tmp

The long contains this information:

ERROR 2017-12-05T20:48:31,566 (com.infiniteautomation.nosql.maint.GenericSqlPointValueDao.query:365) - Error writing file '/tmp/MYiO9VmW' (Errcode: 28 "No space left on device")
java.sql.SQLException: Error writing file '/tmp/MYiO9VmW' (Errcode: 28 "No space left on device")
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) ~[mysql-connector-java-5.1.26.jar:?]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190) ~[mysql-connector-java-5.1.26.jar:?]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122) ~[mysql-connector-java-5.1.26.jar:?]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) ~[mysql-connector-java-5.1.26.jar:?]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.26.jar:?]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812) ~[mysql-connector-java-5.1.26.jar:?]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2761) ~[mysql-connector-java-5.1.26.jar:?]
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612) ~[mysql-connector-java-5.1.26.jar:?]
        at com.infiniteautomation.nosql.maint.GenericSqlPointValueDao.query(GenericSqlPointValueDao.java:357) [mangoNoSqlDatabase-3.2.3.jar:?]
        at com.infiniteautomation.nosql.maint.GenericSqlPointValueDao.getPointValuesBetween(GenericSqlPointValueDao.java:230) [mangoNoSqlDatabase-3.2.3.jar:?]
        at com.infiniteautomation.nosql.maint.MangoNoSqlMigrationWorkItem.execute(MangoNoSqlMigrationWorkItem.java:150) [mangoNoSqlDatabase-3.2.3.jar:?]
        at com.serotonin.m2m2.rt.maint.BackgroundProcessing$RejectableWorkItemRunnable.run(BackgroundProcessing.java:556) [mango-3.2.2.jar:?]
        at com.serotonin.timer.Task.runTask(Task.java:179) [mango-3.2.2.jar:?]
        at com.serotonin.timer.TaskWrapper.run(TaskWrapper.java:23) [mango-3.2.2.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_131]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_131]
        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_131]

That suggests a Mariadb/MySQL issue, but it's on a different volume, with plenty of space as well.

I've tried running with "Maximum point value queue size" as low as 1000, but it still fails on this "No space left on device" error, which halts the migration process.

Out of ideas to try.

Aldo

[AldoRamos]

I discovered the following line in my log:

Dec 05 19:26:18 EdgeServer129.ia3.io ma.sh[3885]: Warning: mysqldump: ignoring option '--databases' due to invalid value 'mango'

which made me realize you were using mysqldump for backup, and I started to wonder if perhaps you were using it for dumping in the migration process, also. Probably not, but apparently it is buffering in those /tmp/MYxxxx files, which is where the problem arose.

MySQL configuration options to force using a tmpdir on a larger volume were unsuccessful, so I had to unmount /tmp and link it to the desired volume. Much easier said than done, trust me.

But it's hard to argue with success:
Run Time: 288 seconds
Total Processed 14116734 of 14116734

Significantly faster and, more importantly, successful!

The question then becomes, is it possible to configure Mango to use an alternative tmpdir so this can be accomplished more easily?

[phildunlap]

Yes it is possible to set the temporary directory for Java. It's a command line argument, so you can use an ext-enabled script like...

#!/bin/bash
case "$1" in
    init)
        # Startup with tmp directory /temporary
        JAVAOPTS="$JAVAOPTS -Djava.io.tmpdir=/temporary "
        ;;
esac

[AldoRamos]

Excellent! That will improve upgrades/migrations on other instances.

Quick question: after executing the migration, it's safe to purge/drop the pointValues table, correct? What about other time-series tables, such as events and userEvents? Or are those still in MySQL? Is pointValues the only table in NoSQL? Apologies if this is addressed elsewhere (feel free to just give me the links) but I honestly tried to search first. ;-)

[phildunlap]

You are correct that you can drop pointValues if you like. Events is still in the SQL database. The only other thing that could be stored in either and would prefer the NoSQL if it is installed is point values for the Reports module (the HTML reports). That's the "reportInstanceData" table.

[AldoRamos]

How do I specify which db to use for reportInstanceData? Just its absence in the MySQL db?

[phildunlap]

I meant to say it will use the NoSQL if installed. That's what I meant by prefer. I don't believe we created any tools to covert existing reportInstanceData tables to the NoSQL data store. So, I would expect any old report instances you have don't have their data available.