Traccar stopped saving positions to EventData
I just upgraded to 4.2 and see this error "No value specified for parameter ..... ". Here is a snippet of it.
2018-12-20 19:48:08 INFO: [6a786376] connected
2018-12-20 19:48:08 INFO: [70aa8484: 5023 < 106.200.147.194] HEX: 78780a1346060400020003a97d0d0a
2018-12-20 19:48:08 INFO: [70aa8484: 5023 > 106.200.147.194] HEX: 787805130003cae30d0a
2018-12-20 19:48:08 INFO: Query not provided: database.updateDeviceExtended
2018-12-20 19:48:08 WARN: Failed to store position - No value specified for parameter 1 - SQLException (... < QueryBuilder:480 < DataManager:458 < DefaultDataHandler:30 < ...)
2018-12-20 19:48:08 WARN: [70aa8484] error - NullPointerException
2018-12-20 19:48:08 INFO: [70aa8484] disconnected
2018-12-20 19:48:08 INFO: Query not provided: database.updateDeviceExtended
2018-12-20 19:48:08 INFO: [2c2522c3] connected
2018-12-20 19:48:08 INFO: [7abc618a: 5023 < 106.76.217.164] HEX: 78780d010861442040002323004a33ec0d0a
2018-12-20 19:48:08 INFO: [7abc618a: 5023 > 106.76.217.164] HEX: 78780501004a250b0d0a
2018-12-20 19:48:08 INFO: Query not provided: database.updateDeviceExtended
2018-12-20 19:48:08 INFO: [ad5f48a9] connected
2018-12-20 19:48:08 INFO: [cef71178: 5023 < 223.238.95.165] HEX: 78780d010358511021895028000688880d0a
2018-12-20 19:48:08 INFO: [cef71178: 5023 > 223.238.95.165] HEX: 787805010006ad630d0a
2018-12-20 19:48:08 INFO: Query not provided: database.updateDeviceExtendedLooks like the configuration that I'm using is not supported in 4.2. Can somebody point me to the right configuration to calculate the distance from the last position, update the device table and insert in to EventData table? Appreciate the help.
You need to fix variables that you use in your SQL query to match Position model.
Hi Anton,
The Position(We call it EventData) model doesn't have accountId, our application has dependency on these. I'm still puzzled why it stopped working with traccar version 3.2?
We need to update the device with the latest coordiantes and calculate distance, which I think is multiple SQL queries. Does 4.2 support such behavior? If it does please provide me pointers to documentation. Thank you.
Yes, it does. Your problem, as I already said, is using wrong parameter names.
This is the position schema I have in the database.
*mysql> desc positions;
+------------+---------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| protocol | varchar(128) | YES | | NULL | |
| deviceid | int(11) | NO | MUL | NULL | |
| servertime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| devicetime | timestamp | NO | | 0000-00-00 00:00:00 | |
| fixtime | timestamp | NO | | 0000-00-00 00:00:00 | |
| valid | bit(1) | NO | | NULL | |
| latitude | double | NO | | NULL | |
| longitude | double | NO | | NULL | |
| altitude | float | NO | | NULL | |
| speed | float | NO | | NULL | |
| course | float | NO | | NULL | |
| address | varchar(512) | YES | | NULL | |
| attributes | varchar(4000) | YES | | NULL | |
| accuracy | double | NO | | 0 | |
| network | varchar(4000) | YES | | NULL | |
+------------+---------------+------+-----+---------------------+-----------------------------+I do not see accountId!! Also I'm looking in to the code. DataManager.addObject(BaseModel entity) is executing executeUpdate() once. Does this mean that the config 'database.insertPosition' should just have one insert query? Before I had almost 3 queries find device, update device and insert in to EventData.
Traccar never had an account id.
Hi Anton,
I understand Traccar doesn't have accountId but we added to our schema. The config which worked in version 3.2 is not working with version 4.2
START TRANSACTION;
SELECT @lastLatitude := lastValidLatitude, @lastLongitude := lastValidLongitude, @odometer := lastOdometerKM, @lastSpeedKPH := lastValidSpeedKPH FROM Device WHERE imeiNumber = :device_id;
SELECT @distance := 1.609344 * 3956 * 2 * ASIN(SQRT( POWER(SIN((@lastLatitude - :latitude) * pi()/180 / 2), 2) +COS(@lastLatitude * pi()/180) * COS(:latitude * pi()/180) * POWER(SIN((@lastLongitude - :longitude) * pi()/180 / 2), 2) ));
SET @distance = IF( (@distance > 500), 0, @distance); <!-- need to do more exeprimentation to see if this required or not-->
UPDATE Device SET lastValidHeading = :course, lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastOdometerKM = @odometer + @distance, lastGPSTimestamp = UNIX_TIMESTAMP(:time), lastValidSpeedKPH = :speed, lastUpdateTime = UNIX_TIMESTAMP(NOW()) WHERE imeiNumber = :device_id;
SELECT @accountID := accountID, @deviceID := deviceID FROM Device WHERE imeiNumber = :device_id;
INSERT INTO EventData (accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading, altitude, rawData, distanceKM, odometerKM, creationTime)
VALUES (@accountID, @deviceID, UNIX_TIMESTAMP(:time), 0, :latitude, :longitude, :speed * 1.852, :course, :altitude, '', @distance, @odometer + @distance, UNIX_TIMESTAMP(NOW()));
COMMIT;What changes do I have to make to the configuration to make it work?
Hi there,
I was using traccar 3.2. We have the below configuration in conf/traccar.xml
database.insertPosition(I removed the entry tag)
START TRANSACTION; SELECT @lastLatitude := lastValidLatitude, @lastLongitude := lastValidLongitude, @odometer := lastOdometerKM, @lastSpeedKPH := lastValidSpeedKPH FROM Device WHERE imeiNumber = :device_id; SELECT @distance := 1.609344 * 3956 * 2 * ASIN(SQRT( POWER(SIN((@lastLatitude - :latitude) * pi()/180 / 2), 2) +COS(@lastLatitude * pi()/180) * COS(:latitude * pi()/180) * POWER(SIN((@lastLongitude - :longitude) * pi()/180 / 2), 2) )); SET @distance = IF( (@distance > 500), 0, @distance); <!-- need to do more exeprimentation to see if this required or not--> UPDATE Device SET lastValidHeading = :course, lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastOdometerKM = @odometer + @distance, lastGPSTimestamp = UNIX_TIMESTAMP(:time), lastValidSpeedKPH = :speed, lastUpdateTime = UNIX_TIMESTAMP(NOW()) WHERE imeiNumber = :device_id; SELECT @accountID := accountID, @deviceID := deviceID FROM Device WHERE imeiNumber = :device_id; INSERT INTO EventData (accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading, altitude, rawData, distanceKM, odometerKM, creationTime) VALUES (@accountID, @deviceID, UNIX_TIMESTAMP(:time), 0, :latitude, :longitude, :speed * 1.852, :course, :altitude, '', @distance, @odometer + @distance, UNIX_TIMESTAMP(NOW())); COMMIT;Since last few days it stopped populating table 'EventData'. I can't understand what went wrong. Any pointers please?