<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
ALWAYS BACKUP YOUR DATABASE BEFORE YOU CHANGE ANYTHING </div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<a href="https://gist.github.com/Blake-">https://gist.github.com/Blake-</a><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div>
<div id="Signature">
<div id="divtagdefaultwrapper" dir="ltr" style="color:rgb(0,0,0); background-color:rgb(255,255,255)">
</div>
</div>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> archivesspace_users_group-bounces@lyralists.lyrasis.org <archivesspace_users_group-bounces@lyralists.lyrasis.org> on behalf of Huebschen,
Alan M <ahueb2@uis.edu><br>
<b>Sent:</b> Friday, March 13, 2020 10:05 AM<br>
<b>To:</b> Archivesspace Users Group <archivesspace_users_group@lyralists.lyrasis.org><br>
<b>Subject:</b> Re: [Archivesspace_Users_Group] [EXTERNAL] timezone issue</font>
<div> </div>
</div>
<style type="text/css" style="display:none">
<!--
input, textarea, select, button
{background-color:#181a1b}
html, body, input, textarea, select, button
{border-color:#575757;
color:#e8e6e3}
a
{color:#3391ff}
table
{border-color:#4c4c4c}
input:-webkit-autofill, textarea:-webkit-autofill, select:-webkit-autofill
{background-color:#545b00!important;
color:#e8e6e3!important}
*
{}
p
{margin-top:0;
margin-bottom:0}
.x_vimvixen-hint
{background-color:#7b5300!important;
border-color:#d8b013!important;
color:#f3e8c8!important}
input, textarea, select, button
{background-color:#181a1b}
html, body, input, textarea, select, button
{border-color:#575757;
color:#e8e6e3}
a
{color:#3391ff}
table
{border-color:#4c4c4c}
input:-webkit-autofill, textarea:-webkit-autofill, select:-webkit-autofill
{background-color:#545b00!important;
color:#e8e6e3!important}
.x_vimvixen-hint
{background-color:#7b5300!important;
border-color:#d8b013!important;
color:#f3e8c8!important}
-->
</style>
<div dir="ltr" style="font-size:12pt; color:#000000; background-color:#FFFFFF; font-family:Calibri,Arial,Helvetica,sans-serif">
<div dir="ltr" style="font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255); font-family:Calibri,Arial,Helvetica,sans-serif">
<p>I had a similar issue when I first launched ASpace, I did attempt using CDT but it didn't like that.</p>
<p><br>
</p>
<p>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.<br>
</p>
<p><br>
</p>
<p>In our db_url I also included '&useLegacyDatetimeCode=false' just before the serverTimezone, but I don't know how much it matters.<br>
</p>
<p><br>
</p>
<p>We are currently using ASpace v2.5.0<br>
</p>
<p><br>
</p>
<span dir="ltr"><font size="3" face="Calibri,Arial,Helvetica,sans-serif" color="black" style=""><span style="font-size:12pt">
<div style="background-color:white; margin:0px 0px 16px">
<p>-Alan Huebschen</p>
<p>University of Illinois at Springfield<br>
</p>
<p>Brookens Library Information Systems</p>
</div>
</span></font></span>
<p><br>
</p>
<div dir="ltr" style="color:rgb(220,218,212)">
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="x_divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> archivesspace_users_group-bounces@lyralists.lyrasis.org <archivesspace_users_group-bounces@lyralists.lyrasis.org> on behalf of Daniel Sprouse
<dsprouse@tsl.texas.gov><br>
<b>Sent:</b> Thursday, March 12, 2020 4:09 PM<br>
<b>To:</b> Archivesspace Users Group<br>
<b>Subject:</b> [EXTERNAL] [Archivesspace_Users_Group] timezone issue</font>
<div> </div>
</div>
<div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)">
<span><br>
</span>
<div><br>
</div>
<div>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.<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>I tried removing the time zone string from the connect string in config.rb...<br>
</div>
<div>AppConfig[:db_url] = "jdbc:mysql://[REDACTED]:3306/archivesspace?user=[REDACTED]&password=[REDACTED]&useUnicode=true&characterEncoding=UTF-8"<br>
</div>
<div>and it just tryied picking up the system time, didn't like it.<br>
</div>
<div>I tried CDT and CT as serverTimezone values and neither worked.<br>
</div>
<div>- CST and UTC worked, but would not allow us to login, giving a 'sesison timed out' error<br>
</div>
<div>- MST worked and allowed us to login.<br>
</div>
<div><br>
</div>
<div>So I guess my question is what serverTimezone string is going to give us the same value as CDT?<br>
</div>
<div><br>
</div>
<div>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.<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div># active lines in config.rb...<br>
</div>
<div>grep -v -e '#' -e '^$' config/config.rb<br>
</div>
<div>AppConfig[:db_url] = "jdbc:mysql://[REDACTED]:3306/archivesspace?user=[REDACTED]&password=[REDACTED]&useUnicode=true&characterEncoding=UTF-8"<br>
</div>
<div>AppConfig[:public_cookie_secret] = "[REDACTED]"<br>
</div>
<div>AppConfig[:backend_url] = "http://[REDACTED]:8089"<br>
</div>
<div>AppConfig[:public_url] = "http://[REDACTED]:8081"<br>
</div>
<div>AppConfig[:solr_backup_schedule] = "0 * * * *"<br>
</div>
<div>AppConfig[:solr_backup_number_to_keep] = 12<br>
</div>
<div>AppConfig[:plugins] = ['local', 'next_accession', 'lcnaf', 'aspace-import-excel']<br>
</div>
<div>AppConfig[:mysql_binlog] = true<br>
</div>
<div>AppConfig[:frontend_proxy_url] = "https://[REDACTED]"<br>
</div>
<div>AppConfig[:container_management_extent_calculator] = {<br>
</div>
<div> :report_volume => true,<br>
</div>
<div> :unit => :feet,<br>
</div>
<div> :decimal_places => 3<br>
</div>
<div>}<br>
</div>
<div>AppConfig[:pui_branding_img] = '/assets/images/logo-tslac.png'<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>java -version<br>
</div>
<div>java version "1.8.0_111"<br>
</div>
<div>Java(TM) SE Runtime Environment (build 1.8.0_111-b14)<br>
</div>
<div>Java HotSpot(TM) 64-Bit Server VM (build 25.111-b14, mixed mode)<br>
</div>
<div>[root@tslac4avned archivesspace]#<br>
</div>
<div>[root@tslac4avned lib]# ls | grep mysql<br>
</div>
<div>mysql-connector-java-8.0.17.jar<br>
</div>
<div>[root@tslac4avned lib]# <br>
</div>
<div><br>
</div>
<div># this is the aspace_diagnostic file every time we start archivesspace, and it's the same on all three servers<br>
</div>
<div># 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.
<br>
</div>
<div>[root@tslac4avned archivesspace]# cat /var/www/aris/html/archivesspace/data/tmp/aspace_diagnostic_1584039423.txt<br>
</div>
<div>{<br>
</div>
<div> "version": "v2.7.0",<br>
</div>
<div> "appconfig": {<br>
</div>
<div> "db_url": "jdbc:mysql://tslac4avned.tsl.state.tx.us:3306/archivesspace?user=[REDACTED]&password=[REDACTED]&useUnicode=true&characterEncoding=UTF-8",<br>
</div>
<div> "db_max_connections": 28,<br>
</div>
<div> "backend_url": "http://[REDACTED]:8089",<br>
</div>
<div> "frontend_url": "http://localhost:8080",<br>
</div>
<div> "public_url": "http://[REDACTED]:8081",<br>
</div>
<div> "oai_url": "http://localhost:8082",<br>
</div>
<div> "solr_url": "http://localhost:8090",<br>
</div>
<div> "indexer_url": "http://localhost:8091",<br>
</div>
<div> "docs_url": "http://localhost:8888",<br>
</div>
<div> "frontend_log": "default",<br>
</div>
<div> "frontend_log_level": "debug",<br>
</div>
<div> "backend_log": "default",<br>
</div>
<div> "backend_log_level": "debug",<br>
</div>
<div> "pui_log": "default",<br>
</div>
<div> "pui_log_level": "debug",<br>
</div>
<div> "indexer_log": "default",<br>
</div>
<div> "indexer_log_level": "debug",<br>
</div>
<div> "db_debug_log": false,<br>
</div>
<div> "mysql_binlog": true,<br>
</div>
<div> "solr_backup_schedule": "0 * * * *",<br>
</div>
<div> "solr_backup_number_to_keep": 12,<br>
</div>
<div> "solr_backup_directory": "/var/www/aris/html/archivesspace/data/solr_backups",<br>
</div>
<div> "solr_params": {<br>
</div>
<div> "q.op": "AND"<br>
</div>
<div> },<br>
</div>
<div> "locale": "en",<br>
</div>
<div> "plugins": [<br>
</div>
<div> "local",<br>
</div>
<div> "next_accession",<br>
</div>
<div> "lcnaf",<br>
</div>
<div> "aspace-import-excel"<br>
</div>
<div>~~TRUNCATED~~<br>
</div>
<div> "exception": {<br>
</div>
<div> "msg": "Database connection failed",<br>
</div>
<div> "backtrace": [<br>
</div>
<div> "/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'",<br>
</div>
<div> "/var/www/aris/html/archivesspace/gems/gems/sinatra-1.4.7/lib/sinatra/base.rb:1411:in `configure'",<br>
</div>
<div> "/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>'",<br>
</div>
<div> "/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>'",<br>
</div>
<div> "org/jruby/RubyKernel.java:956:in `require'",<br>
</div>
<div> "uri:classloader:/META-INF/jruby.home/lib/ruby/stdlib/rubygems/core_ext/kernel_require.rb:55:in `require'",<br>
</div>
<div> "/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)'",<br>
</div>
<div> "org/jruby/RubyBasicObject.java:1691:in `instance_eval'",<br>
</div>
<div> "/var/www/aris/html/archivesspace/data/tmp/jetty-0.0.0.0-8089-backend.war-_-any-/webapp/WEB-INF/config.ru:4:in `(root)'",<br>
</div>
<div> "uri:classloader:/vendor/rack-1.6.8/rack/builder.rb:55:in `<main>'",<br>
</div>
<div> "launcher/launcher.rb:92:in `start_server'",<br>
</div>
<div> "launcher/launcher.rb:157:in `main'",<br>
</div>
<div> "launcher/launcher.rb:261:in `<main>'"<br>
</div>
<div> ]<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div># and this is the archivesspace.out output when attempting to start aspace:<br>
</div>
<div><br>
</div>
<div>ArchivesSpace base directory: /var/www/aris/html/archivesspace<br>
</div>
<div>ArchivesSpace started! See logs/archivesspace.out for details.<br>
</div>
<div>~~TRUNCATED~~<br>
</div>
<div>ArchivesSpaceThreadDump: Touch the file '/var/www/aris/html/archivesspace/thread_dump_backend.txt' to trigger a thread dump<br>
</div>
<div>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<br>
</div>
<div>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.<br>
</div>
<div>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.<br>
</div>
<div>E, [2020-03-12T14:09:07.175799 #10697] ERROR -- : Thread-2000: ***** DATABASE CONNECTION FAILED *****<br>
</div>
<div><br>
</div>
<div><br>
</div>
<span>Thanks for any guidance you can offer.</span><br>
</div>
<div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div id="x_Signature">
<div id="x_divtagdefaultwrapper" dir="ltr" style="font-size:12pt; font-family:Calibri,Helvetica,sans-serif; color:rgb(0,0,0)">
<p style="margin-top:0px; margin-bottom:0px; margin-top:0; margin-bottom:0"></p>
<div><img class="x_EmojiInsert" data-outlook-trace="F:1|T:1" src="cid:82518750-5aa3-4908-bafb-78722b96d3ac"><br>
</div>
<br>
<p style="margin-top:0px; margin-bottom:0px"></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>