CCTR-800 Device sending non numeric IMEI
I don't think there is an easy way to fix it. OpenGTS doesn't have numeric ids for every device, but Traccar requires it. So, in the integration config, unique ids have to be converted into numeric ids.
One way to solve this problem is to have another table with mapping from some internal numeric id to unique id of the device.
Hi Anton and many thanks for your immediate answer.
We suspected this and tried to "trick" up things by altering the two queries in traccar.xml in order to drop the first 7 leftmost characters of the device imei (as the rightmost 8 characters are only digits) and use only the zero padded 8 rightmost ones.
Unfortunately this is not enough as traccar reports the above error 2016-03-29 22:39:44 WARN: Invalid value for getLong() - 'CW9999C00035221' - SQLException (... < QueryBuilder:313 < *:409 < DataManager:231 < *:118 < *:138 < BaseProtocolDecoder:47 < ...)
May be we just did not make the right fix.
About an additional conversion table, as we are using OpenGts, we already have a table (Device) which can be used to map an internal numeric id on an external non numeric imei. In Device table imei, uniqueId, DeviceId fields exist, which I think can be used for this.
The necessary modifications can be done on config.xml only or they need to be done in tracer server code?
Can you suggest us how to fix this?
Thanks in advance,
Luca
Everything can be done in the config. You just need to alter the queries. Is deviceID numeric field?
Hi Anton,
Yes, we have no restriction on this ... we can use UniqueId or DeviceId purely numeric ... for user interface stuff we still have DeviceDescription field.
Thanks,
Luca
Try something like this:
<entry key='database.selectDevicesAll'>
SELECT deviceID AS id, imeiNumber AS uniqueId FROM Device WHERE deviceID REGEXP '^[0-9]+$';
</entry>
<entry key='database.insertPosition'>
START TRANSACTION;
UPDATE Device SET lastValidLatitude = :latitude, lastValidLongitude = :longitude, lastGPSTimestamp = UNIX_TIMESTAMP(:time), lastUpdateTime = UNIX_TIMESTAMP(NOW()) WHERE deviceID = :deviceId;
SELECT @accountID := accountID, @deviceID := deviceID FROM Device WHERE deviceID = :deviceId;
INSERT INTO EventData (accountID, deviceID, timestamp, statusCode, latitude, longitude, speedKPH, heading, altitude, rawData, creationTime, address)
VALUES (@accountID, @deviceID, UNIX_TIMESTAMP(:time), 0, :latitude, :longitude, :speed * 1.852, :course, :altitude, '', UNIX_TIMESTAMP(NOW()), :address);
COMMIT;
</entry>
Hi Anton,
I tried this mod (anyway I had to change the first query WHERE clause to imeiNumber <> ''; in order to have not numeric imeis non filtered out).
The mod makes sense to me ... the first query should get the list of all the device with a set imei field and assign OpenGTS deviceID to id (it must be pure numeric in order not to get error in getLong) and OpenGTS imeiNumber to uniqueId ... testing the query confirms this (I have only this device with imli set for now and the query correctly returns this:
id uniqueId
000000000035221 CW9999C0035221
).
The second couple of queries should look for a record in OpenGTS Device that matches the condition WHERE deviceID = :deviceId (where the second parameter should be set by the calling code to the same value returned by the previous query in the field id)
Anyway I still get the device not found error!
2016-03-31 04:23:34 INFO: Starting server... 2016-03-31 04:24:49 INFO: [33A4A65F] connected 2016-03-31 04:24:49 DEBUG: [33A4A65F: 5040 < 77.218.248.123] HEX: 2a3136303333313032323435305542303543573939393943303030333532323130323234353041343333372e323034384e30313332342e33333631453030302e303136303333313133332e333431313131313131314c3030303130385e 2016-03-31 04:24:50 WARN: Unknown device - CW9999C00035221 (77.218.248.123) 2016-03-31 04:25:00 DEBUG: [33A4A65F: 5040 < 77.218.248.123] HEX: 2a3136303333313032323435305542303048534f5e
This anyway does not seem to work yet ... it seems that there is still something hard-wired in code that still checks for imei or it may be that the passed id it is not the expected original OpenGTS deviceID.
Of course the server was restarted in order to make it read again the configuration changes.
Any idea?
Further investigation seems to show that the second group of queries is never executed:
SQL LOG:
/usr/sbin/mysqld, Version: 5.5.47-0ubuntu0.14.04.1 ((Ubuntu)). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument 160331 5:20:23 510 Quit 160331 5:21:59 381 Query SELECT deviceID AS id, imeiNumber AS uniqueId FROM Device WHERE imeiNumber <> ''
TRACCAR LOG:
2016-03-31 05:21:58 INFO: [FF8BA8B3] connected 2016-03-31 05:21:59 DEBUG: [FF8BA8B3: 5040 < 83.185.86.152] HEX: 2a3136303333313033323135395542303543573939393943303030333532323130333231353941343333372e323136364e30313332342e33363732453030302e343136303333313037322e373231313131313131314c3030303037395e 2016-03-31 05:21:59 WARN: Unknown device - CW9999C00035221 (83.185.86.152) 2016-03-31 05:22:10 DEBUG: [FF8BA8B3: 5040 < 83.185.86.152] HEX: 2a3136303333313033323135395542303048534f5e 2016-03-31 05:22:55 DEBUG: [FF8BA8B3: 5040 < 83.185.86.152] HEX: 2a3136303333313033323235375542303048534f5e 2016-03-31 05:23:40 DEBUG: [FF8BA8B3: 5040 < 83.185.86.152] HEX: 2a3136303333313033323334325542303048534f5e 2016-03-31 05:24:25 DEBUG: [FF8BA8B3: 5040 < 83.185.86.152] HEX: 2a3136303333313033323432375542303048534f5e 2016-03-31 05:25:10 DEBUG: [FF8BA8B3: 5040 < 83.185.86.152] HEX: 2a3136303333313033323531325542303048534f5e 2016-03-31 05:25:21 INFO: [FF8BA8B3] disconnected
So, it seems that the code is looking in the cached list of devices using anyway the received imei and tries to match it not with the uniqueID but with the id (which cannot match of course, as it is purely numeric)
Am I guessing wrong?
If so, it might be that the configuration file should be extended in order to being able to suggest to the code with field should be used to check it the device exists in the cached decide list.
Thanks in advance,
Luca
Why do you think it tries to match id instead of uniqueId? Is there any evidence for that statement?
There must be something wrong in your config or database.
I also don't quite understand why you changed WHERE statement in my query. It matches deviceID against regex which should be numeric. Can you please try my query without modifications.
Hi Anton,
I am sorry but I did not notice the where clause in your new query had deviceId in place of imeiNumber.
Now I copied the query as it is and something changed ...
2016-03-31 13:29:47 INFO: Starting server... 2016-03-31 13:30:47 INFO: [F030F42C] connected 2016-03-31 13:30:47 DEBUG: [F030F42C: 5040 < 77.218.251.150] HEX: 2a3136303333313131333034375542303543573939393943303030333532323131313330343741343333372e323133394e30313332342e33373331453030302e313136303333313232372e393231313131313131314c3030303031325e 2016-03-31 13:30:48 INFO: Query not provided: database.updateDeviceStatus 2016-03-31 13:30:49 WARN: Data truncation: Truncated incorrect DOUBLE value: 'test03' - MysqlDataTruncation (... < QueryBuilder:431 < DataManager:306 < DefaultDataHandler:27 < ...) 2016-03-31 13:30:49 INFO: [F030F42C] id: 35221, time: 2016-03-31 13:30:47, lat: 43.62023, lon: 13.40622, speed: 0.1, course: 227.9 2016-03-31 13:30:58 DEBUG: [F030F42C: 5040 < 77.218.251.150] HEX: 2a3136303333313131333034375542303048534f5e 2016-03-31 13:30:58 INFO: Query not provided: database.updateDeviceStatus
the points about the id:35221 device are still not appearing.
I don't understand the warning "2016-03-31 13:30:49 WARN: Data truncation: Truncated incorrect DOUBLE value: 'test03' - MysqlDataTruncation (... < QueryBuilder:431 < DataManager:306 < DefaultDataHandler:27 < ...)" ... this is another device with a non numeric deviceId (test03) that the first query correctly filters out and, moreover it is not connecting at all
As a test I deleted the device test03 from the OpenGTS db ... after that I get the same warning on another device:
2016-03-31 14:17:46 INFO: Starting server... 2016-03-31 14:18:08 INFO: [1DE58158] connected 2016-03-31 14:18:09 DEBUG: [1DE58158: 5040 < 77.218.243.225] HEX: 2a3136303333313132313735345542303543573939393943303030333532323131323137353441343333372e323131394e30313332342e33343935453030302e303136303333313331322e303231313131313131314c3036353935345e 2016-03-31 14:18:10 INFO: Query not provided: database.updateDeviceStatus 2016-03-31 14:18:11 WARN: Data truncation: Truncated incorrect DOUBLE value: 'lg_fede' - MysqlDataTruncation (... < QueryBuilder:431 < DataManager:306 < DefaultDataHandler:27 < ...)
Can this help?
Thanks again,
Luca
Update: deleting all the other devices from the db made the CCTR-800 tracker work!
Now I will try to add again the other devices, forcing a numeric vehicle ID index and see what happens
Anyway ... do you have any idea of the reason of the above warning that did not make CCTR-800 work?
Thanks again,
Luca
Update: creating again previous existing devices and forcing for them numeric-only deviceID in the Device table everything works.
This sounds strange to me as non numeric id should be allowed as the first query should filter them out.
Anyway ... for now we can live with this :-)
Thanks again for your help and please if you have any idea on how to avoid above error with non numeric deviceID in Device table please let us know.
Best regards,
Luca
It seems like the problem is somewhere in the insert or update. You can try to add deviceID REGEXP '^[0-9]+$' clause there as well.
Hi all,
we are using Traccar server together with OpenGTS, and followed all the instructions for the integration we have a CCTR-800 device that should be fully compatible with Traccar, but even if It sends data to traccar, they don't appear in OpenGTS.
Here is a part of Traccar log file:
It seems to us that the problem could be the not numeric IMEi.
Is there a any way to make It work?
We tried to modify the query inthe traccar.xml file in order to not filter not numeric IMEIs but we got the following error:
Thanks in advance
Regards