Data persistence with MySQL doesn't quite work

[arthmoeros]

Hello,

We are trying to store data on a Azure MySQL database, but we have run in some problems.

First, on table creation, the database engine throws the following error:

Failed to create a new table for appid 'xxx' in the SQL database [Specified key was too long; max key length is 3072 bytes (Error Code: 1071, SQLState: 42000)]

I realized it was because the table creation script specifies a primary key varchar(2048) utf8mb4, when the max allowed is varchar(768) utf8mb4.

Even after fixing that, I got another issue:

Although when creating objects they are stored in the MySQL database, when the Para instance is rebooted it doesn't seem to read them back, so I end up with a "empty" Para instance again (but initialized anyway, odd).

I tried to get to the bottom of this, but I took to much time and gave up, I didn't quite know if the problem is in the core or on the sql plugin.

This is my config (obscured sensitive info):

# the name of the root app
para.app_name = "xxx"
# or set it to 'production'
para.env = "production"
# if true, users can be created without verifying their emails
para.security.allow_unverified_emails = false
# if hosting multiple apps on Para, set this to false
para.clients_can_access_root_app = true
# if false caching is disabled
para.cache_enabled = true
# enable API request signature verification
para.security.api_security = true
# the node number from 1 to 1024, used for distributed ID generation
para.worker_id = 1

para.cors_enabled = true

para.security.ldap.server_url = "ldap://xxx/"
para.security.ldap.base_dn = "ou=xxx,o=xxx,c=xxx"
para.security.ldap.bind_dn = ""
para.security.ldap.bind_pass = ""
para.security.ldap.user_search_base = "ou=xxx"
para.security.ldap.user_search_filter = "(uid={0})"
para.security.ldap.user_dn_pattern = "uid={0},ou=xxx"
para.security.ldap.password_attribute = "xxx"


para.dao = "SqlDAO"

para.sql.ssl_enabled = true
# The fully-qualified class name for your SQL driver. Defaults to null.
para.sql.driver = "com.mysql.cj.jdbc.Driver"
# The server URL to connect to, *without the jdbc: prefix. Defaults to null.
para.sql.url = "mysql://xxx.mysql.database.azure.com:xxx/xxx?verifyServerCertificate=false&useSSL=true&requireSSL=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC"
# The username with access to the database. Defaults to user.
para.sql.user = "xxx"
# The DB password. Defaults to secret.
para.sql.password = "xxx"

This is my dockerfile:

FROM xxxxx (private java 8 base image)
USER root
RUN apk add --no-cache --update curl && rm -rf /var/cache/apk/*
USER java

WORKDIR /usr/src
COPY --chown=java:java . para

WORKDIR /usr/src/para

EXPOSE 8080
CMD ["java", "-classpath", "./lib/mysql-connector-java-8.0.11.jar:./para-war-1.29.2.war", "-Dconfig.file=./application.conf", "org.springframework.boot.loader.WarLauncher"]

(had to run with java classpath because Para wasn't reading the driver from lib)

[albogdano]

As discussed in #44 the SqlDAO is not quite ready for production. The plugin is new and needs some more work and testing. The main issue here is that the main table for the root app is not created because the schema is incorrect (bug).

[albogdano]

This should be fixed in 1.30.0 https://github.com/Erudika/para-dao-sql/releases/tag/1.30.0

[albogdano]

The classpath issue is now fixed in 1.30.0 with the introduction of a new JAR package which uses the ZIP layout (see Spring Boot docs). To load the SQL plugin properly, start Para with

java -jar para.jar -Dloader.path=./lib

which will add the lib folder to the classpath and, in turn, allow the plugin to load the SQL drivers from that folder.