Configure Database for Mideye: MySQL, MariaDB, MSSQL

Mideye Server stores all data in a single relational database. Choose one of the supported engines below and configure the application-prod.yml file.

| Engine | JDBC driver shipped | Default port | Typical OS | |---|---|---|---| | MariaDB 10.5 + | mariadb-java-client | 3306 | Linux | | MySQL 8.0 + | mariadb-java-client (compatible) | 3306 | Linux | | Microsoft SQL Server 2016 – 2025 | mssql-jdbc | 1433 | Windows |

Which database should I choose?

• Linux installations → MariaDB or MySQL (default in the Linux/Debian packages).
• Windows installations → Microsoft SQL Server (configured by the MSI installer).
• MariaDB and MySQL use the same JDBC driver and the same configuration structure.

For supported database versions, see the Preinstall checklist.

---

Configuration file location

| OS | Path | |---|---| | Linux | /opt/mideyeserver6/config/application-prod.yml | | Windows | C:\Program Files (x86)\Mideye Server 6\config\application-prod.yml |

YAML syntax matters

• Use 4-space indentation consistently (the default in shipped config files).
• Enclose database passwords in single quotes ('). If the password itself contains a single quote, double it: 'Pass''word123'
• A single indentation error will prevent Mideye Server from starting.

---

Choose your database

MariaDB / MySQL

1. Create the database

MariaDB

CREATE DATABASE mideyeserver
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'mideye'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON mideyeserver.* TO 'mideye'@'localhost';
FLUSH PRIVILEGES;

MySQL 8

CREATE DATABASE mideyeserver
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

CREATE USER 'mideye'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT ALL PRIVILEGES ON mideyeserver.* TO 'mideye'@'localhost';
FLUSH PRIVILEGES;

Collation matters, MariaDB and MySQL 8 use different defaults

• MySQL 8 → utf8mb4_0900_ai_ci (MySQL 8 default, best performance)
• MariaDB → utf8mb4_unicode_ci (MariaDB does not support 0900 collations)

Do not mix them. Liquibase migrations do not specify character set per table, so every table inherits the database default. If the database was created with latin1 or a mismatched collation, you will get encoding errors when storing non-ASCII data (e.g. usernames from RADIUS).

If you see errors like Incorrect string value or Cannot convert from utf8mb4 to latin1, see the collation mismatch troubleshooting below.

If the database is on a different host, replace 'mideye'@'localhost' with 'mideye'@'%' or 'mideye'@'10.0.0.0/255.255.255.0' to restrict access to a specific subnet.

2. Configure application-prod.yml

spring:
    datasource:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mariadb://localhost:3306/mideyeserver
        username: mideye
        password: 'your_strong_password'
        hikari:
            initializationFailTimeout: 3600000
            data-source-properties:
                cachePrepStmts: true
                prepStmtCacheSize: 250
                prepStmtCacheSqlLimit: 2048
                useServerPrepStmts: true
                max-lifetime: 600000
    jpa:
    liquibase:
        contexts: prod

No database-platform needed

Hibernate auto-detects the correct dialect for MariaDB and MySQL. Do not add a database-platform property, legacy values like MySQL5InnoDBDialect are removed in current versions.

Remote database or MySQL 8 SSL

MySQL 8+ enforces SSL by default. If the database server uses a self-signed certificate (common in development), add ?sslMode=trust to the URL:

url: jdbc:mariadb://db.example.com:3306/mideyeserver?sslMode=trust

For production, prefer sslMode=verify-full with a properly signed certificate.

SQL Server (SQL auth)

1. Create the database

CREATE DATABASE mideyeserver;
GO

-- Create a login and map it to the database
CREATE LOGIN mideye WITH PASSWORD = 'your_strong_password';
GO
USE mideyeserver;
CREATE USER mideye FOR LOGIN mideye;
ALTER ROLE db_owner ADD MEMBER mideye;
GO

2. Configure application-prod.yml

spring:
    datasource:
        type: com.zaxxer.hikari.HikariDataSource
        driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
        url: jdbc:sqlserver://localhost:1433;databaseName=mideyeserver;trustServerCertificate=true
        username: mideye
        password: 'your_strong_password'
        hikari:
            connection-test-query: SELECT 1
            initializationFailTimeout: 1000
            data-source-properties:
                cachePrepStmts: true
                prepStmtCacheSize: 250
                prepStmtCacheSqlLimit: 2048
                useServerPrepStmts: true
    jpa:
    liquibase:
        contexts: prod

trustServerCertificate

SQL Server 2022+ enforces encrypted connections by default. Set trustServerCertificate=true to accept the server's self-signed certificate. For production, install a proper TLS certificate and remove this flag.

SQL Server (Windows auth / NTLM)

How Windows authentication works

On Windows, the MSI installer generates a JDBC URL that includes NTLM parameters. The URL always contains user=PLACEHOLDER_DO_NOT_CHANGE_OR_REMOVE;password=PLACEHOLDER_DO_NOT_CHANGE_OR_REMOVE: these are required placeholders, not real credentials. The MSSQL JDBC driver ignores them when integratedSecurity=true is set and uses the Windows service account instead.

1. Create the database

Open SQL Server Management Studio as an administrator:

CREATE DATABASE mideyeserver;
GO

-- Grant the service account access
USE mideyeserver;
CREATE USER [DOMAIN\ServiceAccount] FOR LOGIN [DOMAIN\ServiceAccount];
ALTER ROLE db_owner ADD MEMBER [DOMAIN\ServiceAccount];
GO

Replace DOMAIN\ServiceAccount with the Windows account running the Mideye Server service.

2. Configure application-prod.yml

spring:
    datasource:
        type: com.zaxxer.hikari.HikariDataSource
        driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
        url: jdbc:sqlserver://localhost:1433;databaseName=mideyeserver;integratedSecurity=true;authenticationScheme=NTLM;useNTLMv2=true;user=PLACEHOLDER_DO_NOT_CHANGE_OR_REMOVE;password=PLACEHOLDER_DO_NOT_CHANGE_OR_REMOVE;trustServerCertificate=true
        username: ''
        password: ''
        hikari:
            connection-test-query: SELECT 1
            initializationFailTimeout: 1000
            data-source-properties:
                cachePrepStmts: true
                prepStmtCacheSize: 250
                prepStmtCacheSqlLimit: 2048
                useServerPrepStmts: true
    jpa:
    liquibase:
        contexts: prod

Do not remove the PLACEHOLDER values

The user=PLACEHOLDER_DO_NOT_CHANGE_OR_REMOVE and password=PLACEHOLDER_DO_NOT_CHANGE_OR_REMOVE tokens in the URL must stay. They are parsed by the JDBC driver even in integrated-security mode. The actual Windows credentials come from the service account, not from these fields.

Optional: Domain parameter

If the SQL Server is in a different domain, add the domain FQDN:

;domain=corp.example.com

SQL Server (AD domain login)

How Active Directory domain login works

When SQL Server is joined to an Active Directory domain, you can authenticate using a domain user account instead of a SQL Server login or the local Windows service account. This uses NTLM authentication with an explicit domain, username, and password, the JDBC driver sends these credentials directly to SQL Server.

1. Grant the domain account access

Open SQL Server Management Studio as a domain administrator:

-- Create a login for the AD domain account
CREATE LOGIN [CORP\MideyeService] FROM WINDOWS;
GO

USE mideyeserver;
CREATE USER [CORP\MideyeService] FOR LOGIN [CORP\MideyeService];
ALTER ROLE db_owner ADD MEMBER [CORP\MideyeService];
GO

Replace CORP\MideyeService with your actual domain and account name.

2. Configure application-prod.yml

spring:
    datasource:
        type: com.zaxxer.hikari.HikariDataSource
        driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
        url: jdbc:sqlserver://sqlserver.corp.example.com:1433;databaseName=mideyeserver;encrypt=true;trustServerCertificate=true;authenticationScheme=NTLM;domain=corp.example.com
        username: MideyeService
        password: 'domain_account_password'
        hikari:
            connection-test-query: SELECT 1
            initializationFailTimeout: 1000
            data-source-properties:
                cachePrepStmts: true
                prepStmtCacheSize: 250
                prepStmtCacheSqlLimit: 2048
                useServerPrepStmts: true
    jpa:
    liquibase:
        contexts: prod

Key differences from Windows auth / NTLM

• domain=corp.example.com: the FQDN of the Active Directory domain.
• username and password: the domain user's credentials (not placeholders).
• No integratedSecurity=true: credentials are provided explicitly, not inherited from the service account.
• Works on both Linux and Windows hosts, as long as the domain is reachable.

SQL Server 2022 + / 2025: encrypt=true

Starting with SQL Server 2022 and the latest JDBC drivers, encrypt=true is the default. You no longer need to add it explicitly, but including it makes the intent clear. Use trustServerCertificate=true only for self-signed certificates, in production, install a proper TLS certificate and set trustServerCertificate=false.

---

Step-by-step: Change database configuration

1. Stop Mideye Server

   Windows

   Stop-Service "Mideye Server 6"

   Linux

   sudo systemctl stop mideyeserver6

2. Back up the current configuration

   Windows

   Copy-Item "C:\Program Files (x86)\Mideye Server 6\config\application-prod.yml" `
             "C:\Program Files (x86)\Mideye Server 6\config\application-prod.yml.bak"

   Linux

   sudo cp /opt/mideyeserver6/config/application-prod.yml \
            /opt/mideyeserver6/config/application-prod.yml.bak

3. Edit application-prod.yml

   Update the spring.datasource section using the examples above.

   Windows

   Open as Administrator:

   C:\Program Files (x86)\Mideye Server 6\config\application-prod.yml

   Linux

   sudo nano /opt/mideyeserver6/config/application-prod.yml

4. Start and validate

   Start the service and watch the log to catch errors immediately.

   Windows

   PowerShell's Get-Content -Wait works like Linux tail -f: it streams new lines as they are written:

   Start-Service "Mideye Server 6"
   Get-Content "C:\Program Files (x86)\Mideye Server 6\log\mideyeserver.log" -Wait -Tail 50

   Press Ctrl+C to stop following the log.

   Linux

   sudo systemctl start mideyeserver6
   sudo journalctl -u mideyeserver6 -f --no-pager

   Or tail the application log directly:

   tail -f /opt/mideyeserver6/log/mideyeserver.log

   Look for Started MideyeServerApp in the log. If the database connection fails, the error appears within the first few seconds.

---

Connection string reference

MariaDB / MySQL

| Scenario | URL | |---|---| | Local, default port | jdbc:mariadb://localhost:3306/mideyeserver | | Remote host | jdbc:mariadb://db.example.com:3306/mideyeserver | | Self-signed SSL | jdbc:mariadb://db.example.com:3306/mideyeserver?sslMode=trust | | Verified SSL | jdbc:mariadb://db.example.com:3306/mideyeserver?sslMode=verify-full |

SQL Server

| Scenario | URL | |---|---| | SQL auth, local | jdbc:sqlserver://localhost:1433;databaseName=mideyeserver;trustServerCertificate=true | | SQL auth, remote | jdbc:sqlserver://db.example.com:1433;databaseName=mideyeserver;trustServerCertificate=true | | Named instance | jdbc:sqlserver://localhost\MSSQLSERVER;databaseName=mideyeserver;trustServerCertificate=true | | Windows auth (NTLM) | jdbc:sqlserver://localhost:1433;databaseName=mideyeserver;integratedSecurity=true;authenticationScheme=NTLM;useNTLMv2=true;user=PLACEHOLDER_DO_NOT_CHANGE_OR_REMOVE;password=PLACEHOLDER_DO_NOT_CHANGE_OR_REMOVE;trustServerCertificate=true | | AD domain login | jdbc:sqlserver://sqlserver.corp.example.com:1433;databaseName=mideyeserver;encrypt=true;trustServerCertificate=true;authenticationScheme=NTLM;domain=corp.example.com | | Encrypted connection | jdbc:sqlserver://db.example.com:1433;databaseName=mideyeserver;encrypt=true;trustServerCertificate=false |

---

Troubleshooting

Where to find logs

| OS | Log directory | |---|---| | Linux | /opt/mideyeserver6/log/ | | Windows | C:\Program Files (x86)\Mideye Server 6\log\ |

Tail the log while starting the service to see database errors in real time:

# Windows (PowerShell): equivalent of Linux "tail -f"
Get-Content "C:\Program Files (x86)\Mideye Server 6\log\mideyeserver.log" -Wait -Tail 100 | Select-String -Pattern "error|exception|datasource|hikari"

# Linux
tail -f /opt/mideyeserver6/log/mideyeserver.log | grep -i -E "error|exception|datasource|hikari"

Common errors and fixes

Communications link failure (MariaDB / MySQL)

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

Cause: The database server is unreachable, wrong host/port, firewall, or the service is not running.

Fix:

1. Verify the database is running: systemctl status mariadb or systemctl status mysql
2. Test connectivity: mysql -h localhost -u mideye -p mideyeserver
3. Check firewall rules for port 3306

---

Access denied for user (MariaDB / MySQL)

java.sql.SQLException: Access denied for user 'mideye'@'localhost' (using password: YES)

Cause: Wrong username, password, or the user does not have access to the database.

Fix:

1. Verify the password in application-prod.yml is enclosed in single quotes
2. Test manually: mysql -u mideye -p -D mideyeserver
3. Re-grant privileges if needed:

   GRANT ALL PRIVILEGES ON mideyeserver.* TO 'mideye'@'localhost';
   FLUSH PRIVILEGES;

---

Unknown database 'mideyeserver'

java.sql.SQLSyntaxErrorException: Unknown database 'mideyeserver'

Cause: The database does not exist or the name is misspelled in the URL.

Fix:

1. Verify the database exists: SHOW DATABASES;
2. Create it if missing (see the creation commands above)
3. Check for case-sensitivity issues, use the exact name from SHOW DATABASES

---

Public Key Retrieval is not allowed (MySQL 8)

java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

Cause: MySQL 8 uses caching_sha2_password by default and refuses to send the public key over an unencrypted connection.

Fix: Add ?sslMode=trust to the JDBC URL (the MariaDB JDBC driver handles this parameter):

url: jdbc:mariadb://localhost:3306/mideyeserver?sslMode=trust

---

The driver could not establish a secure connection to SQL Server (MSSQL)

com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption.

Cause: SQL Server 2022+ enforces encrypted connections but the server uses a self-signed certificate that the JDBC driver does not trust.

Fix: Add trustServerCertificate=true to the URL:

url: jdbc:sqlserver://localhost:1433;databaseName=mideyeserver;trustServerCertificate=true

---

Login failed for user (MSSQL)

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'mideye'.

Cause: Wrong credentials, the login does not exist, or the user is not mapped to the database.

Fix:

1. Verify the login exists in SQL Server: SELECT name FROM sys.server_principals WHERE name = 'mideye';
2. Verify the user is mapped: USE mideyeserver; SELECT name FROM sys.database_principals WHERE name = 'mideye';
3. Re-create if needed (see the creation commands above)

---

Collation mismatch (MariaDB / MySQL)

java.sql.SQLException: Incorrect string value: '\xC3\xA4...' for column 'username'

or conversion errors like Cannot convert from utf8mb4 to latin1.

Cause: The database or individual tables were created with a different character set (often latin1) than what the JDBC connection expects (utf8mb4). This happens when:

• The database was created without specifying CHARACTER SET utf8mb4
• An older MySQL/MariaDB installation defaulted to latin1
• Liquibase migrations create tables without explicit character set, they inherit the database default

Diagnose: Check the current character set and collation of the database and affected table:

-- Check database defaults
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'mideyeserver';

-- Check a specific table
SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mideyeserver' AND TABLE_NAME = 'blocked_attempts';

Fix: Convert the database and all affected tables to the correct character set.

For MySQL 8:

ALTER DATABASE mideyeserver
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- Convert each affected table (repeat for each table with wrong collation)
ALTER TABLE blocked_attempts
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

For MariaDB:

ALTER DATABASE mideyeserver
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

ALTER TABLE blocked_attempts
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Convert all tables at once

To find all tables with the wrong collation, run:

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mideyeserver'
  AND TABLE_COLLATION != 'utf8mb4_0900_ai_ci';

Replace utf8mb4_0900_ai_ci with utf8mb4_unicode_ci for MariaDB. This generates the ALTER TABLE statements you can copy and execute.

---

Faster debugging with initializationFailTimeout

The default initializationFailTimeout is 3600000 (60 minutes), Mideye Server will retry the connection for up to an hour before giving up. While debugging, temporarily lower this to see errors faster:

hikari:
    initializationFailTimeout: 10000

Remember to restore the original value (3600000) after the issue is resolved.

---

Changing database type

To migrate from one database engine to another:

1. Back up your current database and application-prod.yml
2. Install and configure the new database engine
3. Create the mideyeserver database on the new engine
4. Update application-prod.yml with the new connection settings
5. Restart Mideye Server and verify the log output

Caution

Changing database type does not migrate existing data. Mideye Server will create an empty schema via Liquibase on first start. For data migration instructions, see MySQL to MSSQL Migration.

---

Related resources

• Preinstall Checklist, Supported OS and database versions
• Database Overview, Architecture, encryption, and concepts
• Shared Database Configuration, Multi-server setups
• Application Configuration, Complete application-prod.yml reference
• MySQL to MSSQL Migration, Data migration guide