My database is full

Kamil6 years ago

My database doesn't clear old records.

config:

<properties>

    <entry key="config.default">./conf/default.xml</entry>
    <entry key='web.port'>80</entry>
    <entry key='geocoder.enable'>false</entry>
    <entry key='database.driver'>com.mysql.jdbc.Driver</entry>
    <entry key='database.url'>jdbc:mysql://localhost/traccar?allowMultiQueries=true&amp;autoReconnect=true&amp;useUnicode=yes&amp;characterEncoding=UTF-8&amp;sessionVariables=sql_mode=''</entry>
    <entry key='database.user'>root</entry>
    <entry key='database.password'>xxxxxxxxxx</entry>
    <entry key='server.timeout'>120</entry>
	<entry key='database.positionsHistoryDays'>100</entry>
</properties>

wrapper log:

INFO|1500/0|Service traccar|19-05-15 21:23:56|[qtp1274225913-33] WARN org.eclipse.jetty.http.HttpParser - badMessage: 400 No Host for HttpChannelOverHttp@20c39d2f{r=0,c=false,a=IDLE,uri=/}
INFO|1500/0|Service traccar|19-05-15 21:23:57|[qtp1274225913-45] WARN org.eclipse.jetty.http.HttpParser - badMessage: 400 No Host for HttpChannelOverHttp@3c792ee2{r=0,c=false,a=IDLE,uri=/}
INFO|1500/0|Service traccar|19-05-15 21:23:58|[qtp1274225913-34] WARN org.eclipse.jetty.http.HttpParser - badMessage: 400 No Host for HttpChannelOverHttp@12622833{r=0,c=false,a=IDLE,uri=/}
INFO|1500/0|Service traccar|19-05-15 21:24:20|[qtp1274225913-36] WARN org.eclipse.jetty.http.HttpParser - badMessage: 400 No Host for HttpChannelOverHttp@53dc4c18{r=0,c=false,a=IDLE,uri=/}
INFO|wrapper|Service traccar|19-05-15 21:26:15|stopping process with pid/timeout 1500 45000
INFO|wrapper|Service traccar|19-05-15 21:26:15|Shutting down Wrapper
INFO|1500/0|Service traccar|19-05-15 21:26:16|wrapper manager received stop command
INFO|1500/0|Service traccar|19-05-15 21:26:16|[Thread-12] INFO org.eclipse.jetty.server.ServerConnector - Stopped ServerConnector@2909b845{HTTP/1.1}{0.0.0.0:80}
INFO|1500/0|Service traccar|19-05-15 21:26:16|[Thread-12] INFO org.eclipse.jetty.server.handler.ContextHandler - Stopped o.t.w.@ca8ef3a{/,null,UNAVAILABLE}
INFO|1500/0|Service traccar|19-05-15 21:26:16|[Thread-12] INFO org.eclipse.jetty.server.handler.ContextHandler - Stopped o.e.j.s.ServletContextHandler@6cbe68e9{/api,null,UNAVAILABLE}
WARNING|wrapper|Service traccar|19-05-15 21:27:07|YAJSW: yajsw-stable-12.12
WARNING|wrapper|Service traccar|19-05-15 21:27:07|OS   : Linux/4.4.0-145-generic/amd64
WARNING|wrapper|Service traccar|19-05-15 21:27:07|JVM  : Oracle Corporation/1.8.0_212//usr/lib/jvm/java-8-openjdk-amd64/jre/64
INFO|wrapper|Service traccar|19-05-15 21:27:07|exec:java -classpath /opt/traccar/wrapperApp.jar:/opt/traccar/tracker-server.jar:/opt/traccar/lib/log4j-1.2.17.jar:/opt/traccar/lib/joda-time-2.9.9.jar:/opt/traccar/lib/commons-codec-1.11.jar:/opt/traccar/lib/h2-1.4.196.jar:/opt/traccar/lib/mysql-connector-java-5.1.45.jar:/opt/traccar/lib/postgresql-42.2.1.jre7.jar:/opt/traccar/lib/mssql-jdbc-6.4.0.jre7.jar:/opt/traccar/lib/HikariCP-java7-2.4.13.jar:/opt/traccar/lib/slf4j-api-1.7.21.jar:/opt/traccar/lib/netty-3.10.6.Final.jar:/opt/traccar/lib/async-http-client-1.9.40.jar:/opt/traccar/lib/slf4j-simple-1.7.25.jar:/opt/traccar/lib/javax.json-1.0.4.jar:/opt/traccar/lib/jetty-server-9.2.24.v20180105.jar:/opt/traccar/lib/javax.servlet-api-3.1.0.jar:/opt/traccar/lib/jetty-http-9.2.24.v20180105.jar:/opt/traccar/lib/jetty-io-9.2.24.v20180105.jar:/opt/traccar/lib/jetty-servlet-9.2.24.v20180105.jar:/opt/traccar/lib/jetty-security-9.2.24.v20180105.jar:/opt/traccar/lib/jetty-webapp-9.2.24.v20180105.jar:/opt/traccar/lib/jetty-xml-9.2.24.v20180105.jar:/opt/traccar/lib/jetty-jndi-9.2.24.v20180105.jar:/opt/traccar/lib/jetty-util-9.2.24.v20180105.jar:/opt/traccar/lib/jetty-proxy-9.2.24.v20180105.jar:/opt/traccar/lib/jetty-client-9.2.24.v20180105.jar:/opt/traccar/lib/websocket-server-9.2.24.v20180105.jar:/opt/traccar/lib/websocket-common-9.2.24.v20180105.jar:/opt/traccar/lib/websocket-api-9.2.24.v20180105.jar:/opt/traccar/lib/websocket-client-9.2.24.v20180105.jar:/opt/traccar/lib/websocket-servlet-9.2.24.v20180105.jar:/opt/traccar/lib/jersey-container-servlet-2.25.1.jar:/opt/traccar/lib/jersey-container-servlet-core-2.25.1.jar:/opt/traccar/lib/javax.inject-2.5.0-b32.jar:/opt/traccar/lib/jersey-common-2.25.1.jar:/opt/traccar/lib/javax.annotation-api-1.2.jar:/opt/traccar/lib/jersey-guava-2.25.1.jar:/opt/traccar/lib/hk2-api-2.5.0-b32.jar:/opt/traccar/lib/hk2-utils-2.5.0-b32.jar:/opt/traccar/lib/aopalliance-repackaged-2.5.0-b32.jar:/opt/traccar/lib/hk2-locator-2.5.0-b32.jar:/opt/traccar/lib/javassist-3.20.0-GA.jar:/opt/traccar/lib/osgi-resource-locator-1.0.1.jar:/opt/traccar/lib/jersey-server-2.25.1.jar:/opt/traccar/lib/jersey-client-2.25.1.jar:/opt/traccar/lib/jersey-media-jaxb-2.25.1.jar:/opt/traccar/lib/validation-api-1.1.0.Final.jar:/opt/traccar/lib/javax.ws.rs-api-2.0.1.jar:/opt/traccar/lib/jersey-media-json-jackson-2.25.1.jar:/opt/traccar/lib/jersey-entity-filtering-2.25.1.jar:/opt/traccar/lib/jackson-jaxrs-base-2.8.4.jar:/opt/traccar/lib/jackson-core-2.8.4.jar:/opt/traccar/lib/jackson-databind-2.8.4.jar:/opt/traccar/lib/jackson-jaxrs-json-provider-2.8.4.jar:/opt/traccar/lib/jackson-module-jaxb-annotations-2.8.4.jar:/opt/traccar/lib/jackson-annotations-2.8.4.jar:/opt/traccar/lib/liquibase-core-3.5.5.jar:/opt/traccar/lib/snakeyaml-1.17.jar:/opt/traccar/lib/mail-1.4.7.jar:/opt/traccar/lib/activation-1.1.jar:/opt/traccar/lib/jxls-2.4.4.jar:/opt/traccar/lib/commons-jexl-2.1.1.jar:/opt/traccar/lib/commons-beanutils-1.9.2.jar:/opt/traccar/lib/jcl-over-slf4j-1.7.12.jar:/opt/traccar/lib/logback-core-1.1.3.jar:/opt/traccar/lib/jxls-poi-1.0.14.jar:/opt/traccar/lib/poi-3.17.jar:/opt/traccar/lib/poi-ooxml-3.17.jar:/opt/traccar/lib/poi-ooxml-schemas-3.17.jar:/opt/traccar/lib/xmlbeans-2.6.0.jar:/opt/traccar/lib/stax-api-1.0.1.jar:/opt/traccar/lib/curvesapi-1.04.jar:/opt/traccar/lib/velocity-1.7.jar:/opt/traccar/lib/commons-collections-3.2.1.jar:/opt/traccar/lib/commons-lang-2.4.jar:/opt/traccar/lib/velocity-tools-2.0.jar:/opt/traccar/lib/commons-digester-1.8.jar:/opt/traccar/lib/commons-chain-1.1.jar:/opt/traccar/lib/commons-logging-1.1.jar:/opt/traccar/lib/servlet-api-2.3.jar:/opt/traccar/lib/commons-validator-1.3.1.jar:/opt/traccar/lib/dom4j-1.1.jar:/opt/traccar/lib/oro-2.0.8.jar:/opt/traccar/lib/sslext-1.2-0.jar:/opt/traccar/lib/struts-core-1.3.8.jar:/opt/traccar/lib/antlr-2.7.2.jar:/opt/traccar/lib/struts-taglib-1.3.8.jar:/opt/traccar/lib/struts-tiles-1.3.8.jar:/opt/traccar/lib/commons-collections4-4.1.jar:/opt/traccar/lib/ical4j-2.0.5.jar:/opt/traccar/lib/commons-lang3-3.6.jar:/opt/traccar/lib/threetenbp-1.3.6.jar:/opt/traccar/lib/ch-smpp-5.0.9.jar:/opt/traccar/lib/ch-commons-util-6.0.2.jar:/opt/traccar/lib/ch-commons-charset-3.0.2.jar -Dfile.encoding=UTF-8 -Xrs -Dwrapper.service=true -Dwrapper.console.visible=false -Dwrapper.port=15003 -Dwrapper.key=8614135815122815728 -Dwrapper.teeName=8614135815122815728$1557948427398 -Dwrapper.tmp.path=/opt/traccar/tmp -Djna_tmpdir=/opt/traccar/tmp -Dwrapper.config=/opt/traccar/conf/wrapper.conf -Dwrapper.visible=false -Dwrapper.pidfile=/run/wrapper.traccar.pid org.rzo.yajsw.app.WrapperJVMMain 
INFO|wrapper|Service traccar|19-05-15 21:27:07|posix_spawn pid 1568
INFO|wrapper|Service traccar|19-05-15 21:27:07|started process 1568
INFO|wrapper|Service traccar|19-05-15 21:27:07|started process with pid 1568
INFO|1568/0|Service traccar|19-05-15 21:27:10|[INFO] StandardFileSystemManager - Using "/tmp/vfs_cache" as temporary files store.
INFO|1568/0|Service traccar|19-05-15 21:27:11|[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:12|[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:12|Wed May 15 21:27:12 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:13|Wed May 15 21:27:13 CEST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO|1568/0|Service traccar|19-05-15 21:27:13|INFO 5/15/19 9:27 PM: liquibase: Clearing database change log checksums
INFO|1568/0|Service traccar|19-05-15 21:27:13|INFO 5/15/19 9:27 PM: liquibase: Successfully acquired change log lock
INFO|1568/0|Service traccar|19-05-15 21:27:13|INFO 5/15/19 9:27 PM: liquibase: Successfully released change log lock
INFO|1568/0|Service traccar|19-05-15 21:27:13|INFO 5/15/19 9:27 PM: liquibase: Successfully acquired change log lock
INFO|1568/0|Service traccar|19-05-15 21:27:16|INFO 5/15/19 9:27 PM: liquibase: Reading from traccar.DATABASECHANGELOG
INFO|1568/0|Service traccar|19-05-15 21:27:16|INFO 5/15/19 9:27 PM: liquibase: Reading from traccar.DATABASECHANGELOG
INFO|1568/0|Service traccar|19-05-15 21:27:16|INFO 5/15/19 9:27 PM: liquibase: ./schema/changelog-master.xml: changelog-3.8::changelog-3.8-dropuniquetoken::author: Change set changelog-3.8::changelog-3.8-dropuniquetoken::author failed, but failOnError was false.  Error: Can't DROP 'uk_user_token'; check that column/key exists [Failed SQL: ALTER TABLE traccar.users DROP KEY uk_user_token]
INFO|1568/0|Service traccar|19-05-15 21:27:16|INFO 5/15/19 9:27 PM: liquibase: Successfully released change log lock
INFO|1568/0|Service traccar|19-05-15 21:27:17|[main] INFO org.eclipse.jetty.util.log - Logging initialized @9030ms
INFO|1568/0|Service traccar|19-05-15 21:27:19|[main] INFO org.eclipse.jetty.server.Server - jetty-9.2.24.v20180105
INFO|1568/0|Service traccar|19-05-15 21:27:21|[main] INFO org.eclipse.jetty.server.handler.ContextHandler - Started o.e.j.s.ServletContextHandler@6f5e16cf{/api,null,AVAILABLE}
INFO|1568/0|Service traccar|19-05-15 21:27:21|[main] INFO org.eclipse.jetty.server.handler.ContextHandler - Started o.t.w.@17043b04{/,null,AVAILABLE}
INFO|1568/0|Service traccar|19-05-15 21:27:21|[main] INFO org.eclipse.jetty.server.ServerConnector - Started ServerConnector@1ba467c2{HTTP/1.1}{0.0.0.0:80}
INFO|1568/0|Service traccar|19-05-15 21:27:21|[main] INFO org.eclipse.jetty.server.Server - Started @13508ms

Anton Tananaev6 years ago

I would recommend using external script because if your database is too big, the request can easily timeout.

Kamil6 years ago

Or do you know the command by putty to delete old records? I don't have phpmyadmin

Kamil6 years ago

Can you configure me to delete data via cron? I am able to provide you with a server

Anton Tananaev6 years ago

Send an email to support if you need assistance with configuring your server.

Slawek6 years ago

mysql -u root -p

after login run command:

use [tabelaname];

use traccar;

then run commands:

delete from tc_positions where servertime < DATE(NOW() - INTERVAL 100 day);
delete from tc_statistics where capturetime < DATE(NOW() - INTERVAL 100 day);
delete from tc_events where servertime < DATE(NOW() - INTERVAL 100 day);

logout:

exit

Try to find some database manager then all is more easy.

Michał Piasecki6 years ago

You have this line in your configuration:
<entry key='database.positionsHistoryDays'>100</entry>
Try changing it to
<entry key='database.HistoryDays'>100</entry>

If documentation is correct, this should work

Slawek6 years ago

my config :

<entry key='database.historyDays'>550</entry>

Kamil6 years ago

mysql -u root -p

after login run command:

use traccar;

then run commands:

delete from positions where servertime < DATE(NOW() - INTERVAL 100 day);
delete from statistics where capturetime < DATE(NOW() - INTERVAL 100 day);
delete from events where servertime < DATE(NOW() - INTERVAL 100 day);

logout:

This solution works!
I don't have a tc prefix in my database.

I changed the value to:

<entry key='database.HistoryDays'>100</entry>

Unfortunately, it didn't work because I have too much database.

Slawek6 years ago

make cronjob

Kamil6 years ago

Cleaning the database does not reduce its space on the server. The backup is reduced.

Slawek6 years ago

run:

it show you database size

SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

how big is your backup file ? is compressed ?