Could not create connection to database server [MariaDB]

Castillo5 years ago

Hi everyone!

Since I installed Traccar 4.2 in my server it was work fine, but 3 days ago something occurred and now Traccar can not start.
I did not change nothing, used MariaDB, that it is accessible through 3306 port.

This is my traccar config: traccar.xml

<entry key='database.driver'>com.mysql.jdbc.Driver</entry> 
<entry key='database.url'>jdbc:mysql://localhost:3306/traccarDB?useSSL=false&amp;allowMultiQueries=true&amp;autoReconnect=true&amp;useUnicode=yes&amp;characterEncoding=UTF-8&amp;sessionVariables=sql_mode=''</entry>
<entry key='database.user'>traccar2</entry> 
<entry key='database.password'><masked></entry>

Using systemctl to restart service:

alt text

That is my log: /opt/traccar/logs/tracker-server.log

2018-12-17 10:36:40 DEBUG: Driver class com.mysql.jdbc.Driver found in Thread context class loader jdk.internal.loader.ClassLoaders$AppClassLoader@16e3f4e
2018-12-17 10:36:40 DEBUG: HikariPool-1 - configuration:
2018-12-17 10:36:40 DEBUG: allowPoolSuspension.............false
2018-12-17 10:36:40 DEBUG: autoCommit......................true
2018-12-17 10:36:40 DEBUG: catalog.........................none
2018-12-17 10:36:40 DEBUG: connectionInitSql..............."SELECT 1"
2018-12-17 10:36:40 DEBUG: connectionTestQuery.............none
2018-12-17 10:36:40 DEBUG: connectionTimeout...............30000
2018-12-17 10:36:40 DEBUG: dataSource......................none
2018-12-17 10:36:40 DEBUG: dataSourceClassName.............none
2018-12-17 10:36:40 DEBUG: dataSourceJNDI..................none
2018-12-17 10:36:40 DEBUG: dataSourceProperties............{password=<masked>}
2018-12-17 10:36:40 DEBUG: driverClassName................."com.mysql.jdbc.Driver"
2018-12-17 10:36:40 DEBUG: healthCheckProperties...........{}
2018-12-17 10:36:40 DEBUG: healthCheckRegistry.............none
2018-12-17 10:36:40 DEBUG: idleTimeout.....................600000
2018-12-17 10:36:40 DEBUG: initializationFailTimeout.......1
2018-12-17 10:36:40 DEBUG: isolateInternalQueries..........false
2018-12-17 10:36:40 DEBUG: jdbcUrl.........................jdbc:mysql://localhost:3306/traccarDB?useSSL=false&allowMultiQueries=true&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8&sessionVariables=sql_mode=''
2018-12-17 10:36:40 DEBUG: leakDetectionThreshold..........0
2018-12-17 10:36:40 DEBUG: maxLifetime.....................1800000
2018-12-17 10:36:40 DEBUG: maximumPoolSize.................10
2018-12-17 10:36:40 DEBUG: metricRegistry..................none
2018-12-17 10:36:40 DEBUG: metricsTrackerFactory...........none
2018-12-17 10:36:40 DEBUG: minimumIdle.....................10
2018-12-17 10:36:40 DEBUG: password........................<masked>
2018-12-17 10:36:40 DEBUG: poolName........................"HikariPool-1"
2018-12-17 10:36:40 DEBUG: readOnly........................false
2018-12-17 10:36:40 DEBUG: registerMbeans..................false
2018-12-17 10:36:40 DEBUG: scheduledExecutor...............none
2018-12-17 10:36:40 DEBUG: schema..........................none
2018-12-17 10:36:40 DEBUG: threadFactory...................internal
2018-12-17 10:36:40 DEBUG: transactionIsolation............default
2018-12-17 10:36:40 DEBUG: username........................"traccar2"
2018-12-17 10:36:40 DEBUG: validationTimeout...............5000
2018-12-17 10:36:40  INFO: HikariPool-1 - Starting...
2018-12-17 10:36:48 DEBUG: HikariPool-1 - Failed to create/setup connection: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
2018-12-17 10:36:48 DEBUG: HikariPool-1 - Cannot acquire connection from data source - Could not create connection to database server. Attempted reconnect 3 times. Giving up. - MySQLNonTransientConnectionException (... < DataManager:139 < *:89 < Context:382 < Main:110 < ...)
2018-12-17 10:36:49 ERROR: HikariPool-1 - Exception during pool initialization. - Could not create connection to database server. Attempted reconnect 3 times. Giving up. - MySQLNonTransientConnectionException (... < DataManager:139 < *:89 < Context:382 < Main:110 < ...)
2018-12-17 10:36:49 ERROR: Main method error - Failed to initialize pool: Could not create connection to database server. Attempted reconnect 3 times. Giving up. - PoolInitializationException (... < DataManager:139 < *:89 < Context:382 < Main:110 < ...)
Castillo5 years ago

EDIT #1

I have tried changing database connector, but it still does not working
https://www.traccar.org/forums/topic/howto-mariadb-configuration-in-traccar-xml/

Anton Tananaev5 years ago

You need to find out why database is not accessible.

Castillo5 years ago

I do not know Anton, can you tell me what I have to search, please?

Castillo5 years ago

The database is accesible:

mysql -utraccar1 -pcontrasena traccarDB

alt text

Anton Tananaev5 years ago

Try TCP connection, not unix socket.

Castillo5 years ago

Thanks Anton! I solved the problem. I have Pi-Hole in my server to block ads in my LAN and it affects 'localhost' operations

When I created the database on MySQL i did:

CREATE DATABASE traccarDB;
CREATE USER 'traccar1'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON traccarDB.* TO 'traccar1'@'localhost';
FLUSH PRIVILEGES;
exit;

The misteke is that Traccar can not connect like traccar1@localhost, it have to be traccar1@127.0.0.1

That's how it worked for me:

CREATE DATABASE traccarDB;
CREATE USER 'traccar1'@'127.0.0.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON traccarDB.* TO 'traccar1'@'127.0.0.1';
FLUSH PRIVILEGES;
exit;
Castillo5 years ago

Could you add "How to create database" to you Documentation page? I think it could help users like me.
https://www.traccar.org/mysql/

How to create a database to Traccar:

CREATE DATABASE traccarDB;
CREATE USER 'traccar1'@'127.0.0.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON traccarDB.* TO 'traccar1'@'127.0.0.1';
FLUSH PRIVILEGES;
exit;
Anton Tananaev5 years ago

I think it's too case specific. Some user would want to have remote database, or use existing data, or use different user/database names.

Castillo5 years ago

Yes, of course! =)

People who wants a remote database it is for something special, and I'm sure of they know how to do it, but normal people like me could need a bit of help.

Something like this:

How to create a database to Traccar:

CREATE DATABASE [Database Name];
CREATE USER '[User Name]'@'127.0.0.1' IDENTIFIED BY '[Password]';
GRANT ALL PRIVILEGES ON traccarDB.* TO '[User Name]'@'127.0.0.1';
FLUSH PRIVILEGES;
exit;