[Archivesspace_Users_Group] [EXTERNAL] timezone issue

Fri Mar 13 11:08:23 EDT 2020

I just threw together a few Gists to help with this. The problem seems to be there's a bad date in a table. Seems like it's not always in the same table. The date probably looks something like '2020-03-08 02:00%' this year.

ALWAYS BACKUP YOUR DATABASE BEFORE YOU CHANGE ANYTHING

https://gist.github.com/Blake-

There's 2 Gists to search every ArchivesSpace table for bad dates. One for user_mtimes and one for system_mtimes. I guess we can't be sure which table and mtime is bad, so you can copy/paste those and use them to go hunting. Once you find a bad date you should be able to change it to a real date and everything will come back online.

Another Gist that'll update the system_mtimes on EVERY table to be the current date/time. It would be a bad idea to update ALL your tables at the same time. Don't just do that, but you might be able to copy and paste some useful lines there.

________________________________
From: archivesspace_users_group-bounces at lyralists.lyrasis.org <archivesspace_users_group-bounces at lyralists.lyrasis.org> on behalf of Huebschen, Alan M <ahueb2 at uis.edu>
Sent: Friday, March 13, 2020 10:05 AM
To: Archivesspace Users Group <archivesspace_users_group at lyralists.lyrasis.org>
Subject: Re: [Archivesspace_Users_Group] [EXTERNAL] timezone issue

I had a similar issue when I first launched ASpace, I did attempt using CDT but it didn't like that.

Appending '&serverTimezone=America/Chicago' to the db_url made it happy. This may somewhat depend on what your MySQL server is using, but I'm not 100% sure on that.

In our db_url I also included '&useLegacyDatetimeCode=false' just before the serverTimezone, but I don't know how much it matters.

We are currently using ASpace v2.5.0

-Alan Huebschen

University of Illinois at Springfield

Brookens Library Information Systems

________________________________
From: archivesspace_users_group-bounces at lyralists.lyrasis.org <archivesspace_users_group-bounces at lyralists.lyrasis.org> on behalf of Daniel Sprouse <dsprouse at tsl.texas.gov>
Sent: Thursday, March 12, 2020 4:09 PM
To: Archivesspace Users Group
Subject: [EXTERNAL] [Archivesspace_Users_Group] timezone issue

having same timezone issue on three servers, two of which are new instancess of archivesspace. Server one has had archivesspace running for multiple years, is at v2.7.0 and has been for months.

I tried removing the time zone string from the connect string in config.rb...
AppConfig[:db_url] = "jdbc:mysql://[REDACTED]:3306/archivesspace?user=[REDACTED]&password=[REDACTED]&useUnicode=true&characterEncoding=UTF-8"
and it just tryied picking up the system time, didn't like it.
I tried CDT and CT as serverTimezone values and neither worked.
- CST and UTC worked, but would not allow us to login, giving a 'sesison timed out' error
- MST worked and allowed us to login.

So I guess my question is what serverTimezone string is going to give us the same value as CDT?

We had an issue two weeks ago where an item was lost and we did end up restarted apsce at that time, no issue. Today we had an issue that was similar, where a user was unable to locate a container that was linked to the record. This time we were unable to restart archivesspace on server one, our production instance.

# active lines in config.rb...
grep -v -e '#' -e '^$' config/config.rb
AppConfig[:db_url] = "jdbc:mysql://[REDACTED]:3306/archivesspace?user=[REDACTED]&password=[REDACTED]&useUnicode=true&characterEncoding=UTF-8"
AppConfig[:public_cookie_secret] = "[REDACTED]"
AppConfig[:backend_url] = "http://[REDACTED]:8089"
AppConfig[:public_url] = "http://[REDACTED]:8081"
AppConfig[:solr_backup_schedule] = "0 * * * *"
AppConfig[:solr_backup_number_to_keep] = 12
AppConfig[:plugins] = ['local', 'next_accession', 'lcnaf',  'aspace-import-excel']
AppConfig[:mysql_binlog] = true
AppConfig[:frontend_proxy_url] = "https://[REDACTED]"
AppConfig[:container_management_extent_calculator] = {
       :report_volume => true,
       :unit => :feet,
       :decimal_places => 3
}
AppConfig[:pui_branding_img] = '/assets/images/logo-tslac.png'

java -version
java version "1.8.0_111"
Java(TM) SE Runtime Environment (build 1.8.0_111-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.111-b14, mixed mode)
[root at tslac4avned archivesspace]#
[root at tslac4avned lib]# ls | grep mysql
mysql-connector-java-8.0.17.jar
[root at tslac4avned lib]#

# this is the aspace_diagnostic file every time we start archivesspace, and it's the same on all three servers
# the last part shows a database connection error, and I can connect to the db fine using the connect string info in the config.rb db_url.
[root at tslac4avned archivesspace]# cat /var/www/aris/html/archivesspace/data/tmp/aspace_diagnostic_1584039423.txt
{
  "version": "v2.7.0",
  "appconfig": {
    "db_url": "jdbc:mysql://tslac4avned.tsl.state.tx.us:3306/archivesspace?user=[REDACTED]&password=[REDACTED]&useUnicode=true&characterEncoding=UTF-8",
    "db_max_connections": 28,
    "backend_url": "http://[REDACTED]:8089",
    "frontend_url": "http://localhost:8080",
    "public_url": "http://[REDACTED]:8081",
    "oai_url": "http://localhost:8082",
    "solr_url": "http://localhost:8090",
    "indexer_url": "http://localhost:8091",
    "docs_url": "http://localhost:8888",
    "frontend_log": "default",
    "frontend_log_level": "debug",
    "backend_log": "default",
    "backend_log_level": "debug",
    "pui_log": "default",
    "pui_log_level": "debug",
    "indexer_log": "default",
    "indexer_log_level": "debug",
    "db_debug_log": false,
    "mysql_binlog": true,
    "solr_backup_schedule": "0 * * * *",
    "solr_backup_number_to_keep": 12,
    "solr_backup_directory": "/var/www/aris/html/archivesspace/data/solr_backups",
    "solr_params": {
      "q.op": "AND"
    },
    "locale": "en",
    "plugins": [
      "local",
      "next_accession",
      "lcnaf",
      "aspace-import-excel"
~~TRUNCATED~~
  "exception": {
    "msg": "Database connection failed",
    "backtrace": [
      "/var/www/aris/html/archivesspace/data/tmp/jetty-0.0.0.0-8089-backend.war-_-any-/webapp/WEB-INF/app/main.rb:102:in `block in ArchivesSpaceService'",
      "/var/www/aris/html/archivesspace/gems/gems/sinatra-1.4.7/lib/sinatra/base.rb:1411:in `configure'",
      "/var/www/aris/html/archivesspace/data/tmp/jetty-0.0.0.0-8089-backend.war-_-any-/webapp/WEB-INF/app/main.rb:79:in `<class:ArchivesSpaceService>'",
      "/var/www/aris/html/archivesspace/data/tmp/jetty-0.0.0.0-8089-backend.war-_-any-/webapp/WEB-INF/app/main.rb:39:in `<main>'",
      "org/jruby/RubyKernel.java:956:in `require'",
      "uri:classloader:/META-INF/jruby.home/lib/ruby/stdlib/rubygems/core_ext/kernel_require.rb:55:in `require'",
      "/var/www/aris/html/archivesspace/data/tmp/jetty-0.0.0.0-8089-backend.war-_-any-/webapp/WEB-INF/config.ru:1:in `block in (root)'",
      "org/jruby/RubyBasicObject.java:1691:in `instance_eval'",
      "/var/www/aris/html/archivesspace/data/tmp/jetty-0.0.0.0-8089-backend.war-_-any-/webapp/WEB-INF/config.ru:4:in `(root)'",
      "uri:classloader:/vendor/rack-1.6.8/rack/builder.rb:55:in `<main>'",
      "launcher/launcher.rb:92:in `start_server'",
      "launcher/launcher.rb:157:in `main'",
      "launcher/launcher.rb:261:in `<main>'"
    ]

# and this is the archivesspace.out output when attempting to start aspace:

ArchivesSpace base directory: /var/www/aris/html/archivesspace
ArchivesSpace started!  See logs/archivesspace.out for details.
~~TRUNCATED~~
ArchivesSpaceThreadDump: Touch the file '/var/www/aris/html/archivesspace/thread_dump_backend.txt' to trigger a thread dump
I, [2020-03-12T14:09:06.219773 #10697]  INFO -- : Thread-2000: Connecting to database: jdbc:mysql://tslac4avned.tsl.state.tx.us:3306/archivesspace?user=[REDACTED]&password=[REDACTED]&useUnicode=true&characterEncoding=UTF-8. Max connections: 28
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
E, [2020-03-12T14:09:06.737499 #10697] ERROR -- : Thread-2000: DB connection failed: Java::JavaSql::SQLException: The server time zone value 'CDT' 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 specifc time zone value if you want to utilize time zone support.
E, [2020-03-12T14:09:07.175799 #10697] ERROR -- : Thread-2000: ***** DATABASE CONNECTION FAILED *****

Thanks for any guidance you can offer.

[cid:82518750-5aa3-4908-bafb-78722b96d3ac]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20200313/26c2a70b/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Outlook-2uct2zew.jpg
Type: image/jpeg
Size: 30834 bytes
Desc: Outlook-2uct2zew.jpg
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20200313/26c2a70b/attachment.jpg>