search

jamf / jamfpro

JamfPro Docker image
https://www.jamf.com
MIT License
72 stars 15 forks source link

Not working with Jamf 10.34.0+? #18

Closed magnusviri closed 2 years ago

magnusviri commented 2 years ago

Describe the bug

When this image is used with Jamf Pro 10.34.0+ with no mysql data it consistently gets this error message when opening the webpage.

"There was an error updating the database schema. Contact JAMF Software Support."

To Reproduce

I'm using my docker-compose file. There's nothing special about my file so I can't see how it would be that. In my .env file I change JAMF_PRO_VERSION= to JAMF_PRO_VERSION=10.34.0 (or above) and then I make sure I've copied the ROOT.war into a folder named "10.34.0" located in the same dir as my docker-compose.yml file. Then I run docker-compose up -d in the dir. I wait for it to start up then I open the web browser to "http://localhost".

Expected behavior

When opening web browser it should come up to the license screen.

This docker-compose file works with Jamf Pro 10.33.0 and below.

battlecow commented 2 years ago

Check your database user to ensure it is using the mysql_native_password and not the sha2 newer variant as described: https://docs.jamf.com/technical-articles/Configuring_MySQL_8-0_for_Jamf_Pro.html I just tested with 10.35.0 and it works as expected when the native password is set. You can also manually update the user from within mysql by running: ALTER USER myuser IDENTIFIED WITH mysql_native_password BY 'mypassword';

magnusviri commented 2 years ago

I am using mysql_native_password. I'm actually using mariadb. Could it be that?

magnusviri commented 2 years ago

I still can't get this to work (Jamf Pro 10.36.0). I tried a bunch of things, including manually creating my database and users. Here's some debugging output. Maybe you can help and see if I've done anything wrong.

Running mysql in the MariaDB container:

MariaDB [mysql]> select * from user where user='jamfsoftware';
+------+--------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| Host | User         | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | Delete_history_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | is_role | default_role | max_statement_time |
+------+--------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
| %    | jamfsoftware | *secret                                   | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      | N                   |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *B8B7B8C47EF8381F4C2BBD441DAF0E52AEE035DF | N                | N       |              |           0.000000 |
+------+--------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+---------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+---------+--------------+--------------------+
1 row in set (0.007 sec)

The my.conf file in the mariadb container.

root@daefaf20216b:/# cat /etc/mysql/my.cnf
# The MariaDB configuration file
# ... >8 SNIP 8<
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
[mariadbd]
skip-host-cache
skip-name-resolve

!includedir /etc/mysql/mariadb.conf.d/
!includedir /etc/mysql/conf.d/
innodb_buffer_pool_size = 1073741824
innodb_file_per_table = ON

I also reset the jamfsoftware database.

DROP DATABASE jamfsoftware;
CREATE DATABASE jamfsoftware;
CREATE USER 'jamfsoftware'@'' IDENTIFIED BY 'jamfsw03';
GRANT ALL ON jamfsoftware.* TO 'jamfsoftware'@'';

In the tomcat container I can dump the jamfsoftware db (this is after dropping and recreating the database, so it's empty).

root@56890b940b34:/usr/local/tomcat# mysqldump jamfsoftware -h mysql -u jamfsoftware -p
Enter password:
-- MySQL dump 10.19  Distrib 10.3.31-MariaDB, for debian-linux-gnu (aarch64)
--
-- Host: mysql    Database: jamfsoftware
-- ------------------------------------------------------
-- Server version   10.6.5-MariaDB-1:10.6.5+maria~focal

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-02-17  0:08:36

Then I restart the tomcat container and the Jamf webapp creates a bunch of tables in jamfsoftware and then I get the error: "There was an error updating the database schema. Contact JAMF Software Support." Because it's created the tables I know it is connecting to the mysql container and has access to the database. But all the tables are empty. Note: I am using mariadb and not mysql because I'm on an M1 Mac and there isn't an Arm version of Mysql.

I've dumped the tables created after restarting tomcat. I could attach it but I'm not sure if you want that public. Here's the first table created (notice it's InnoDB).

-- MySQL dump 10.19  Distrib 10.3.31-MariaDB, for debian-linux-gnu (aarch64)
--
-- Host: mysql    Database: jamfsoftware
-- ------------------------------------------------------
-- Server version   10.6.5-MariaDB-1:10.6.5+maria~focal

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `account_invitations`
--

DROP TABLE IF EXISTS `account_invitations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account_invitations` (
  `account_invitation_id` int(11) NOT NULL AUTO_INCREMENT,
  `invitation` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `use_count` int(11) NOT NULL DEFAULT 0,
  `expiration_epoch` bigint(32) NOT NULL DEFAULT -1,
  PRIMARY KEY (`account_invitation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `account_invitations`
--

LOCK TABLES `account_invitations` WRITE;
/*!40000 ALTER TABLE `account_invitations` DISABLE KEYS */;
/*!40000 ALTER TABLE `account_invitations` ENABLE KEYS */;
UNLOCK TABLES;

And here's the last table created.

--
-- Table structure for table `venafi_scep_challenge_configuration`
--

DROP TABLE IF EXISTS `venafi_scep_challenge_configuration`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `venafi_scep_challenge_configuration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payload_identifier` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `configuration_profile_id` int(11) NOT NULL DEFAULT -1,
  `configuration_profile_type` int(11) NOT NULL DEFAULT -1,
  `venafi_pki_id` int(11) NOT NULL DEFAULT -1,
  `username` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `payload_identifier_unique` (`payload_identifier`),
  KEY `configuration_profile_id` (`configuration_profile_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `venafi_scep_challenge_configuration`
--

LOCK TABLES `venafi_scep_challenge_configuration` WRITE;
/*!40000 ALTER TABLE `venafi_scep_challenge_configuration` DISABLE KEYS */;
/*!40000 ALTER TABLE `venafi_scep_challenge_configuration` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-02-17  0:13:43

Thank you for looking at this.

magnusviri commented 2 years ago

I'm pretty sure the "->>" operator ("Return value from JSON column after evaluating path and unquoting the result") is what broke Jamf working with MariaDB. This is what I see when I try to upgrade my MariaDB 10.33.0 database to 10.36.0.

jamfpro-jamfpro-1  | 2022-03-10 00:30:46 ERROR VerifyDatabaseSchemaServiceImpl: - Create: java.sql.SQLSyntaxErrorException: (conn=3) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>> '$.originId') VIRTUAL,CREATE TABLE `conditional_access_devices` (
jamfpro-jamfpro-1  |   `id` int(11) NOT NULL AUTO_INCREMENT,
jamfpro-jamfpro-1  |   `device_id` int(11) NOT NULL DEFAULT -1,
jamfpro-jamfpro-1  |   `device_type` tinyint(1) NOT NULL DEFAULT -1,
jamfpro-jamfpro-1  |   `integration_type` tinyint(1) NOT NULL DEFAULT -1,
jamfpro-jamfpro-1  |   `compliance_state` tinyint(1) NOT NULL DEFAULT -1,
jamfpro-jamfpro-1  |   `compliance_changed` tinyint(1) NOT NULL DEFAULT 0,
jamfpro-jamfpro-1  |   `details` JSON NOT NULL,
jamfpro-jamfpro-1  |   `origin_id` varchar(255) GENERATED ALWAYS AS (details ->> '$.originId') VIRTUAL,
jamfpro-jamfpro-1  |   `compliance_changed_epoch` bigint(32) NOT NULL DEFAULT 0,
jamfpro-jamfpro-1  |   `removal_epoch` bigint(32) NOT NULL DEFAULT -1,
jamfpro-jamfpro-1  |   `tenant_id` varchar(255) NOT NULL DEFAULT '',
jamfpro-jamfpro-1  |  PRIMARY KEY (`id`),
jamfpro-jamfpro-1  |  KEY `device_id` (`device_id`),
jamfpro-jamfpro-1  |  KEY `origin_id` (`origin_id`)
jamfpro-jamfpro-1  | ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci
jamfpro-jamfpro-1  | java.sql.SQLSyntaxErrorException: (conn=3) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>> '$.originId') VIRTUAL,
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:242) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:171) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:248) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:230) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:157) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:192) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.4.1.jar:?]
jamfpro-jamfpro-1  |    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.1.jar:?]
jamfpro-jamfpro-1  |    at com.jamfsoftware.datasource.core.database.ThreeByteUtf8CompatiblePreparedStatement.executeUpdate(ThreeByteUtf8CompatiblePreparedStatement.java:61) ~[datasource-core-10.36.0-t1644331466.jar:?]
jamfpro-jamfpro-1  |    at com.jamfsoftware.datasource.util.database.DataSource.execute(DataSource.java:790) ~[datasource-util-10.36.0-t1644331466.jar:?]
jamfpro-jamfpro-1  |    at com.jamfsoftware.databaseschema.impl.VerifyDatabaseSchemaServiceImpl.createTable(VerifyDatabaseSchemaServiceImpl.java:1124) [database-schema-impl-10.36.0-t1644331466.jar:?]
jamfpro-jamfpro-1  |    at com.jamfsoftware.databaseschema.impl.VerifyDatabaseSchemaServiceImpl.verify(VerifyDatabaseSchemaServiceImpl.java:367) [database-schema-impl-10.36.0-t1644331466.jar:?]
jamfpro-jamfpro-1  |    at com.jamfsoftware.jss.server.initialization.InitializeServerThread.run(InitializeServerThread.java:335) [classes/:?]
jamfpro-jamfpro-1  |    at org.springframework.security.concurrent.DelegatingSecurityContextRunnable.run(DelegatingSecurityContextRunnable.java:82) [spring-security-core-5.6.0.jar:5.6.0]
jamfpro-jamfpro-1  |    at java.lang.Thread.run(Thread.java:829) [?:?]
jamfpro-jamfpro-1  | Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>> '$.originId') VIRTUAL,
jamfpro-jamfpro-1  |   `compliance_changed_epoch` bigint(32) NOT NULL DE...' at line 9
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1594) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1453) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1415) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:289) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:221) ~[mariadb-java-client-2.4.4.jar:?]
jamfpro-jamfpro-1  |    ... 11 more
jamfpro-jamfpro-1  | 2022-03-10 00:30:46 FATAL StartupStatusStoreImpl: - Fatal error logged during server initialization: There was an error updating the database schema. Contact JAMF Software Support.

I guess I can't exactly ask Jamf to support MariaDB. That's ok. I actually found an ARM version of MySQL and I've gotten my docker image to work: "arm64v8/mysql:8.0.28-oracle". I had to specify "mysql_native_password". Just for completeness this is what I am using now.

services:
  mysql:
    image: "arm64v8/mysql:8.0.28-oracle"
    command: --default-authentication-plugin=mysql_native_password

I'll close this issue now.

battlecow commented 2 years ago

Awesome, arguably most times mariadb should be a drop in but yes we do not actively test or support against it so there are instances such as this one where things are incompatible. As more macs are released no doubt there will continue to be more support for things being cross compiled with arm support.

pboushy commented 1 year ago

I'm pretty sure the "->>" operator ("Return value from JSON column after evaluating path and unquoting the result") is what broke Jamf working with MariaDB.

That's correct. For anyone investigating this later, MariaDB does not work with Jamf Pro 10.34 because Jamf started storing full JSON blobs in special fields unique to MySQL. MariaDB does not support the JSON blob type object that Jamf Pro uses. The special command that specifically fails is ->>