Error creating server with SDK

[levine (Barry Levine)]

Hi, I"m getting issues in setting up a server with SDK. I’m using sdk on an iMac. Here is the link to view the interaction in pastebin: sdk issue - Pastebin.com My production server is OpenMRS Version 2.3.0 Build b3ade0 I’m a bit flexible in being able to use most any 2.x Thanks much in advance. Barry

[kitaroghope (Rogers Kitamirike)]

Hello, sorry for exeperiencing this error, from your logs, the You must configure either the server or JDBC driver (via the ‘serverTimezone’ configuration property) to use a more specific time zone value if you want to utilize time zone support.

Open the database configuration file in your project (usually in resources folder) and change the serverTimezone to a specific time zone like GMT or your local time zone.

Hope this brightens your day.

[kitaroghope (Rogers Kitamirike)]

You can also choose to omit the usage of time zone

[josephbate (joseph ssegujja)]

hello @levine i think you should try to leave the db username and password as default

1. Please specify database password (-DdbPassword): ***

try again and this time leave the password as default in otherwords leave it blank see if you get the same error @levine

[levine (Barry Levine)]

Thanks for your suggestions. I run sdk with “jdbc:mysql://localhost:3306/rmp” and still get the same error. I provided a db password. Note, I’ve used sdk successfully in the past with the same sdk parameters without issues.

[wikumc (Wikum Weerakutti)]

Look for a file named my.cnf or my.ini, depending on your operating system and MySQL version. This file typically resides in the MySQL installation directory. Add or modify the default-time-zone setting: If the default-time-zone option is already present, make sure it is set to a valid time zone value. For example, you can set it to UTC by adding or modifying the following line:

default-time-zone = UTC

Save the file and restart the MySQL server.

[ibacher (Ian Bacher)]

@levine

Note, I’ve used sdk successfully in the past with the same sdk parameters without issues.

I would’ve thought your connection string is working. Between 2.3.0 and the current version, we updated the JDBC driver to use MySQL Connector/J 8 (though this should be compatible with recentish versions of MySQL 5.7 and with some tweaks, older versions of 5.7 and 5.6).

Re: your specific error, there’s a bug filed against the MySQL Connector/J 5.1 that has some relatively recent discussion related to Connector/J 8. That said, serverTimezone is the property that you should be able to use to override that and the fact that in your log, it’s complaining about PDT seems to mean that its not understanding the UTC value in the parameter.

The immediate thing I’d try is to set serverTimezone to Etc/UTC on the assumption that maybe the MySQL Connector is a little strict about IANA timezones (UTC is an IANA timezone, but Etc/UTC is its canonical name).

[levine (Barry Levine)]

@ibacher

Etc/UTC

I set it per your suggestion: “jdbc:mysql://localhost:3306/rmpdb?serverTimezone=Etc/UTC” but I still get the same issue

[ibacher (Ian Bacher)]

Could you try adding &forceConnectionTimeZoneToSession=true to your connection string?

[levine (Barry Levine)]

@ibacher

&forceConnectionTimeZoneToSession=true

This time I used: "jdbc:mysql://localhost:3306/rmpdb?serverTimezone=Etc/UTC&forceConnectionTimeZoneToSession=true

I still get the same issue. Would it help if I turn off time zone support? If so, how would I do that??

[ibacher (Ian Bacher)]

@levine

Would it help if I turn off time zone support?

I think at this point, the only real option is to modify the default-time-zone value @wikumc mentioned. AFAICT, there’s no way to completely disable timezone processing.

[levine (Barry Levine)]

@wikumc

default-time-zone = UTC

I appreciate your help but I created the my.cnf file in the appropriate directory and it’s still not working.

[levine (Barry Levine)]

I just reverted to an older version of sdk and it works “mvn org.openmrs.maven.plugins:openmrs-sdk-maven-plugin:4.1.1:setup”

[jwnasambu (Juliet Wamalwa)]

Kindly just incase you still want to explore, You can modify the JDBC URL: Add the serverTimezone parameter to the JDBC URL with a valid time zone value. In this case, you can replace 'PDT' with a more specific time zone such eg America/Los_Angeles . The modified JDBC URL should look like this:

jdbc:mysql://localhost:3306/rmp?serverTimezone=America/Los_Angeles&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&sessionVariables=default_storage_engine%3DInnoDB

N/B If you are using a specific JDBC driver, you might need to configure the time zone explicitly. Locate the configuration property for the JDBC driver’s time zone and set it to the same value you specified in the JDBC URL. The exact configuration process may vary depending on the driver you’re using.

In conclusion, specifying a more specific time zone value, such as 'America/Los_Angeles' , you ensure that the server recognises and correctly handles the time zone information.

[levine (Barry Levine)]

Ach, the error persists:

The distribution requires a MySQL database. Please specify database uri (-DdbUri) (default: ‘jdbc:mysql://localhost:3306/@DBNAME@’): jdbc:mysql://localhost:3306/rmp?serverTimezone=America/Los_Angeles&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&sessionVariables=default_storage_engine%3DInnoDB

Please specify database username (-DdbUser) (default: ‘root’):

Please specify database password (-DdbPassword): ***

[INFO] ------------------------------------------------------------------------

[INFO] BUILD FAILURE

[INFO] ------------------------------------------------------------------------

[INFO] Total time: 01:22 min

[INFO] Finished at: 2023-05-24T16:11:30-07:00

[INFO] ------------------------------------------------------------------------

[ERROR] Failed to execute goal org.openmrs.maven.plugins:openmrs-sdk-maven-plugin:4.5.0:setup (default-cli) on project standalone-pom: Failed to setup server: Failed to connect to the specified database jdbc:mysql://localhost:3306/rmp?serverTimezone=America%2FLos_Angeles&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&sessionVariables=default_storage_engine%3DInnoDB: The server time zone value ‘PDT’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the ‘serverTimezone’ configuration property) to use a more specific time zone value if you want to utilize time zone support. → [Help 1]

[ERROR]

[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.

[ERROR] Re-run Maven using the -X switch to enable full debug logging.

[ERROR]

[ERROR] For more information about the errors and possible solutions, please read the following articles:

[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

[jwnasambu (Juliet Wamalwa)]

The error indicates that the server time zone value ‘PDT’ (Pacific Daylight Time) is unrecognised or ambiguous.To resolve this issue, you need to configure either the server or the JDBC driver to use a more specific time zone value. Here are a few steps you can try:

1. Update the server time zone: You can configure the MySQL server to use a specific time zone. Connect to your MySQL server and execute the following SQL command:
   SET GLOBAL time_zone = 'America/Los_Angeles';

2. Configure the JDBC driver: If updating the server time zone doesn’t resolve the issue, you can specify the time zone in the JDBC URL or through the JDBC driver configuration property. Modify your JDBC URL by adding serverTimezone=America/Los_Angeles to the connection string. Here’s an example:jdbc:mysql://localhost:3306/rmp?serverTimezone=America/Los_Angeles&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&sessionVariables=default_storage_engine%3DInnoDBAdjust the time zone value in the URL to match your desired time zone.

3. Verify the time zone mappings: If the above steps don’t work, ensure that the time zone mappings are properly configured in the MySQL server. You can check the time zone tables in the MySQL database to verify the mappings.

       SELECT * FROM mysql.time_zone;  
       SELECT * FROM mysql.time_zone_name;

If the tables are empty or the mappings are incorrect, you may need to update or reload the time zone information using the mysql_tzinfo_to_sql utility or consult the MySQL documentation for further instructions.

[jwnasambu (Juliet Wamalwa)]

@levine

Ach, the error persists:

The distribution requires a MySQL database. Please specify database uri (-DdbUri) (default: ‘jdbc:mysql://localhost:3306/@DBNAME@’): jdbc:mysql://localhost:3306/rmp?serverTimezone=America/Los_Angeles&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&sessionVariables=default_storage_engine%3DInnoDB

After updating the JDBC driver configuration did you confirm the server’s time zone is correctly configured? Look for the JDBC connection URL in your OpenMRS configuration file (e.g., runtimeproperties, openmrs-runtime.properties, ). It should contain a parameter serverTimezone followed by the time zone value. The output should be something like this:

jdbc:mysql://localhost:3306/rmp?serverTimezone=America/Los_Angeles&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&sessionVariables=default_storage_engine=InnoDB