SQLServerException: Incorrect syntax near 'LIMIT' with traccar 5.4

[dleidert]

Describe the bug

We use traccar in the dockerized version with a MS SQL server. We recently switched from version 5.2 to 5.4. After that switch, the Android traccar client reports a sending failure. We set the logging facility to all and the logs show an SQL syntax error (see below for an excerpt). We can verify that this issue has been introduced with version 5.4. Earlier versions work fine.

To Reproduce
Steps to reproduce the behavior:
The issue is reproducible for us. However, I have no idea how to create a test case so you can reproduce it as well. But we are willing to provide any extra information you require to debug the issue and resolve it.

Expected behavior
Of course, there shouldn't be any SQL syntax error and the positional update should work.

Screenshots
I cannot provide a screenshot, but this is the log excerpt:

2022-10-07 16:08:00 DEBUG: SQLServerPreparedStatement:618: calling sp_executesql: SQL:SELECT * FROM tc_commands_queue WHERE deviceId = @P0 ORDER BY id LIMIT 1
2022-10-07 16:08:00 TRACE: TDSWriter@5a62b2a4 (ConnectionID:1) Writing 144 bytes
2022-10-07 16:08:00 TRACE: TDSWriter@5a62b2a4 (ConnectionID:1) Writing 20 bytes
2022-10-07 16:08:00 TRACE: TDSCommand@672dee5d (SQLServerPreparedStatement:618 executeXXX): finishing request
2022-10-07 16:08:00 TRACE: TDSWriter@5a62b2a4 (ConnectionID:1) Finishing TDS message
2022-10-07 16:08:00 TRACE: /172.17.0.2:36942 SPID:63 TDSWriter@5a62b2a4 (ConnectionID:1) sending packet (237 bytes)
03 01 00 ED 00 3F 01 00 16 00 00 00 12 00 00 00   .....?..........
02 00 00 00 00 00 00 00 00 00 01 00 00 00 FF FF   ................
0A 00 00 00 00 00 E7 40 1F 09 04 D0 00 34 90 00   .......@.....4..
53 00 45 00 4C 00 45 00 43 00 54 00 20 00 2A 00   S.E.L.E.C.T. .*.
20 00 46 00 52 00 4F 00 4D 00 20 00 74 00 63 00    .F.R.O.M. .t.c.
5F 00 63 00 6F 00 6D 00 6D 00 61 00 6E 00 64 00   _.c.o.m.m.a.n.d.
73 00 5F 00 71 00 75 00 65 00 75 00 65 00 20 00   s._.q.u.e.u.e. .
57 00 48 00 45 00 52 00 45 00 20 00 64 00 65 00   W.H.E.R.E. .d.e.
76 00 69 00 63 00 65 00 49 00 64 00 20 00 3D 00   v.i.c.e.I.d. .=.
20 00 40 00 50 00 30 00 20 00 4F 00 52 00 44 00    .@.P.0. .O.R.D.
45 00 52 00 20 00 42 00 59 00 20 00 69 00 64 00   E.R. .B.Y. .i.d.
20 00 4C 00 49 00 4D 00 49 00 54 00 20 00 31 00    .L.I.M.I.T. .1.
00 00 E7 40 1F 09 04 D0 00 34 14 00 40 00 50 00   ...@.....4..@.P.
30 00 20 00 62 00 69 00 67 00 69 00 6E 00 74 00   0. .b.i.g.i.n.t.
00 00 26 08 08 15 00 00 00 00 00 00 00            ..&..........
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream@19f9d595 Writing 266 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream@19f9d595 Flushing
2022-10-07 16:08:00 TRACE: TDSCommand@672dee5d (SQLServerPreparedStatement:618 executeXXX): request complete
2022-10-07 16:08:00 TRACE: TDSCommand@672dee5d (SQLServerPreparedStatement:618 executeXXX): Reading response...
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@40620d8e Reading 8 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@7de4a01f Reading 5 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@2ef8a8c3 Reading 5 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@2ef8a8c3 Read 5 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@7de4a01f Read 5 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@7de4a01f Reading 137 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@2ef8a8c3 Reading 137 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@2ef8a8c3 Read 137 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@7de4a01f Read 137 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@40620d8e Read 8 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@40620d8e Reading 105 bytes
2022-10-07 16:08:00 TRACE: com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream@40620d8e Read 105 bytes
2022-10-07 16:08:00 TRACE: /172.17.0.2:36942 SPID:63 TDSReader@696 (ConnectionID:1 ClientConnectionId: 601f991d-09d6-43b5-a7ef-c5011d5d2a5c) received Packet:1 (105 bytes)
04 01 00 71 00 3F 01 00 AA 54 00 66 00 00 00 01   ...q.?...T.f....
0F 1E 00 49 00 6E 00 63 00 6F 00 72 00 72 00 65   ...I.n.c.o.r.r.e
00 63 00 74 00 20 00 73 00 79 00 6E 00 74 00 61   .c.t. .s.y.n.t.a
00 78 00 20 00 6E 00 65 00 61 00 72 00 20 00 27   .x. .n.e.a.r. .'
00 4C 00 49 00 4D 00 49 00 54 00 27 00 2E 00 05   .L.I.M.I.T.'....
68 00 65 00 6E 00 72 00 79 00 00 01 00 00 00 79   h.e.n.r.y......y
66 00 00 00 FE 02 00 E0 00 00 00 00 00 00 00 00   f...............
00                                                .
2022-10-07 16:08:00 TRACE: TDSCommand@672dee5d (SQLServerPreparedStatement:618 executeXXX): disabling interrupts
2022-10-07 16:08:00 DEBUG: unprocessedResponseCount < 0 on increment. Disabling connection resiliency.
2022-10-07 16:08:00 TRACE: TDSReader@696 (ConnectionID:1 ClientConnectionId: 601f991d-09d6-43b5-a7ef-c5011d5d2a5c) Moving to next packet -- unlinking consumed packet
2022-10-07 16:08:00 TRACE: TDSReader@696 (ConnectionID:1 ClientConnectionId: 601f991d-09d6-43b5-a7ef-c5011d5d2a5c): getNextResult: Processing TDS_ERR (0xAA)
2022-10-07 16:08:00 TRACE: TDSReader@696 (ConnectionID:1 ClientConnectionId: 601f991d-09d6-43b5-a7ef-c5011d5d2a5c) Reading 60 bytes from offset 11
2022-10-07 16:08:00 TRACE: TDSReader@696 (ConnectionID:1 ClientConnectionId: 601f991d-09d6-43b5-a7ef-c5011d5d2a5c) Reading 10 bytes from offset 72
2022-10-07 16:08:00 TRACE: TDSReader@696 (ConnectionID:1 ClientConnectionId: 601f991d-09d6-43b5-a7ef-c5011d5d2a5c): getNextResult: Processing TDS_RET_STAT (0x79)
2022-10-07 16:08:00 TRACE: TDSReader@696 (ConnectionID:1 ClientConnectionId: 601f991d-09d6-43b5-a7ef-c5011d5d2a5c): getNextResult: Processing TDS_DONEPROC (0xFE)
2022-10-07 16:08:00 DEBUG: unprocessedResponseCount < 0 on decrement. Disabling connection resiliency.
2022-10-07 16:08:00 DEBUG: *** SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'LIMIT'. Msg 102, Level 15, State 1, Incorrect syntax near 'LIMIT'.
2022-10-07 16:08:00 DEBUG: ENTRY
2022-10-07 16:08:00 TRACE: TDSCommand@672dee5d (SQLServerPreparedStatement:618 executeXXX): closing...
2022-10-07 16:08:00 TRACE: TDSCommand@672dee5d (SQLServerPreparedStatement:618 executeXXX): processing response...
2022-10-07 16:08:00 TRACE: TDSReader@696 (ConnectionID:1 ClientConnectionId: 601f991d-09d6-43b5-a7ef-c5011d5d2a5c): batch completion: Processing EOF
2022-10-07 16:08:00 DEBUG: RETURN
2022-10-07 16:08:00 DEBUG: ENTRY
2022-10-07 16:08:00 DEBUG: RETURN
2022-10-07 16:08:00  INFO: [T5f07e70b] error - Incorrect syntax near 'LIMIT'. - SQLServerException (... < QueryBuilder:417 < DatabaseStorage:71 < CommandsManager:102 < ...)
2022-10-07 16:08:00  INFO: [T5f07e70b] disconnected

Smartphone (please complete the following information):

• Device: any (tested with three different devices)

[tananaev]

When you provide logs, please use the standard default logging level.

[dleidert]

@tananaev So you want me to provide the logs again, but in default logging level?

[tananaev]

Yes.

[tananaev]

This should fix the issue. Let me know if it works.

[dleidert]

Thanks! I'll test it and send you feedback.

Edit: We'll test next Friday. Just FYI.