Comments (6)
battlecow
commented on August 11, 2024
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';
from jamfpro.
magnusviri
commented on August 11, 2024
I am using mysql_native_password. I'm actually using mariadb. Could it be that?
from jamfpro.
magnusviri
commented on August 11, 2024
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.
from jamfpro.
magnusviri
commented on August 11, 2024
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.
from jamfpro.
battlecow
commented on August 11, 2024
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.
from jamfpro.
pboushy
commented on August 11, 2024
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 ->>
from jamfpro.
Related Issues (15)
- In the README procedure, the docker pull will not succeed
- Link to Kubernetes Manifests is Dead HOT 1
- Release 0.0.14 not pushed to Docker Hub HOT 2
- arm64 version? HOT 2
- Tomcat - /usr/local/tomcat/conf/server.xml - allow server.xml somehow to be on an external volume HOT 1
- SSL cert does not persist HOT 2
- Debug logging to STDOUT HOT 2
- Please include environment variable for XFF HOT 1
- clustering.properties no longer read on each boot of Jamf Pro HOT 4
- ERROR InitializeServerThread: - Error in Setup Assistant: Unhandled Case. HOT 4
- Expose DataBase.xml jdbcParameters as environment variable HOT 1
- Add support for configure the load balancing settings HOT 1
- SSL Issues with OpenJDK HOT 6
- Tomcat Version nit Matching Latest Recommended Release? HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from jamfpro.