database.positionsHistoryDays issues

Hello,

I set the parameter : database.positionsHistoryDays at 70 days in config file but i still have records before 70 days in positions table.

I have restarted the traccar service.

See below my taccar.xml config file

Can you tell me if it is correct ?

Can you tell what i need to check ?

I use 3.11 version of traccar and i have more than 3 millions records for 250 device.

For your information i see "WARN: Lock wait timeout exceeded" in traccar-server.log when i restarted the service.

Thanks

    <entry key='database.driver'>com.mysql.jdbc.Driver</entry> 
    <entry key='database.url'>jdbc:mysql://localhost:3306/traccar?allowMultiQueries=true&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8&sessionVariables=sql_mode=''</entry>
    <entry key='database.user'>traccar</entry> 
    <entry key='database.password'>traccar</entry>
    
    <entry key='database.positionsHistoryDays'>70</entry>

Note that cleanup job is executed once in 24 hours, so you might need to wait. Also, you need to check for any errors. If your database is slow, job might just timeout.

Thanks for your reply

How can i force execution of this job ? By restarting the service ?

I checked the code and it looks like restart should trigger it.

Hello

I think also because each time i restart the service i have period without log and then i have a WARN with Mysql error : lock wait time excedeed.

It seems to be the period during the cleanup job was executed.

I increase mysql server variable : innodb_lock_wait_timeout to 3600 and innodb_buffer_pool_size to 512M

And after i don't have any warning.

But i still have the same number of records in table positions and i have position before the number of days in positionsHistoryDays.

What i need to check ?

Thanks

2017-08-22 09:15:54 DEBUG: [02...] HEX: 
2017-08-22 09:15:54 DEBUG: [02...] HEX: 01
2017-08-22 09:50:39  INFO: [AB...] connected
2017-08-22 09:50:39  INFO: [A9...] connected

If you have a lot of records, I would recommend to do a manual cleanup before you attempt automated one.

Do you have following in the log file:

Clearing history earlier than ...

I don't have following in the log file

Clearing history earlier than

How can i do manual clean up ?

If you don't have it, then there is something wrong with your config.

You need to know SQL to clean the database manually.

I check the source code of cleanup job in DataManager.java and i show that parameter is named database.historyDays
But the documentation say to use database.positionsHistoryDays it is normal ?

public void clearHistory() throws SQLException {
        long historyDays = config.getInteger("database.historyDays");
        if (historyDays != 0) {
            Date timeLimit = new Date(System.currentTimeMillis() - historyDays * 24 * 3600 * 1000);
            Log.debug("Clearing history earlier than " + new SimpleDateFormat(Log.DATE_FORMAT).format(timeLimit));
            QueryBuilder.create(dataSource, getQuery("database.deletePositions"))
                    .setDate("serverTime", timeLimit)
                    .executeUpdate();
            QueryBuilder.create(dataSource, getQuery("database.deleteEvents"))
                    .setDate("serverTime", timeLimit)
                    .executeUpdate();
        }
    }

Looks like documentation was slightly out of day. Fixed it now.

but in version 3.11 the parameter is good and they don't have any log

but i still have all positions before the number that i set in the parameter

    public void clearPositionsHistory() throws SQLException {
        long historyDays = config.getInteger("database.positionsHistoryDays");
        if (historyDays != 0) {
            QueryBuilder.create(dataSource, getQuery("database.deletePositions"))
                    .setDate("serverTime", new Date(System.currentTimeMillis() - historyDays * 24 * 3600 * 1000))
                    .executeUpdate();
        }
    }

I would recommend to upgrade and see if you get log about clearing history.

I checked your SQL code for database.deletePositions

You use

DELETE FROM positions WHERE serverTime < :serverTime AND id NOT IN (SELECT positionId FROM devices)

But with mysql (5.7) if they positionId set to NULL in devices table the query don't deleted any records due to the NULL value

you rewrite the query like this (i test with NOT EXISTS and it's slower)

DELETE FROM positions WHERE serverTime < :serverTime AND id NOT IN (SELECT positionId FROM devices WHERE positionId IS NOT NULL)

what do you think about ?

Good idea. I have updated the config.