search

vitalidze / traccar-web

Traccar Web UI mod
http://traccar.litvak.su/
152 stars 145 forks source link

LIST ABOVE 1000 devices not loading #1169

Open gpproton opened 6 years ago

gpproton commented 6 years ago

Good day, Litvak

I got this error today that the maximum allowed devices on the list can only be 1000, i'm however trying to see if a work around on database.selectDevicesAll will fix this any help will be appreciated.

INFO|3932/0|Service traccar|17-09-07 12:32:22|Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 INFO|3932/0|Service traccar|17-09-07 12:32:22| FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82) FINEST|3932/0|Service traccar|17-09-07 12:32:23| ... 67 more

akmal1999 commented 6 years ago

Google, allows 1000 devices after which you will require license for google map to handle more.

thanks.

On Thu, Sep 7, 2017 at 7:46 AM, Godwin peter .O notifications@github.com wrote:

INFO|3932/0|Service traccar|17-09-07 12:32:22|Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 INFO|3932/0|Service traccar|17-09-07 12:32:22| FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver. T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver. T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver. T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver. OracleStatement.executeMaybeDescribe(OracleStatement.java:897) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver. OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver. OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver. OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at oracle.jdbc.driver. OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper .java:1502) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at com.zaxxer.hikari.pool. ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at com.zaxxer.hikari.pool. HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement. java) FINEST|3932/0|Service traccar|17-09-07 12:32:23| at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl. extract(ResultSetReturnImpl.java:82) FINEST|3932/0|Service traccar|17-09-07 12:32:23| ... 67 more

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/vitalidze/traccar-web/issues/1169, or mute the thread https://github.com/notifications/unsubscribe-auth/AVCsv_9FUiNl6PZdYuZ2n8jZlA5zvD3yks5sf9eggaJpZM4PPseR .

gpproton commented 6 years ago

I feel this is the entry responsible

SELECT u.id AS userId, d.id AS deviceId FROM users AS u, devices AS d WHERE u.admin = 1 UNION SELECT ud.users_id AS userId, ud.devices_id AS deviceId FROM users_devices AS ud INNER JOIN users AS u ON ud.users_id=u.id WHERE u.admin=0 AND u.readOnly=0
vitalidze commented 6 years ago

This error comes from a query execution by hibernate. The database.selectDevicesAll has nothing to do with this issue. You need to see a bigger stack trace, which should be showing the actual method throwing error. My guess is that it should be a query with "IN" clause having all devices identifiers passed in that clause parameters.

This can be a query, which loads maintenances:

SELECT m FROM Maintenance m WHERE m.device IN :devices ORDER BY m.indexNo ASC

Or query, which loads sensors

SELECT s FROM Sensor s WHERE s.device IN :devices ORDER BY s.id ASC

I hate Oracle DBMS for these limitations coming from 90s (including limitation for the name length of any object like table, sequence, etc.)

Anyway, you will need to modify this query somehow, for example by splitting devices into subsets with 1000s (or 999) of devices in each.