Postgresql

伺服器的 DateStyle 參數已更改為 ISO。JDBC 驅動程序要求 DateStyle 以 ISO 開頭才能正確操作

  • January 21, 2019

我在 CentOS Linux 7.4 上使用 PostgreSQL 9.6.6 並在升級 postgresql-jdbc 軟體包後 -

# sudo yum update
Failed to set locale, defaulting to C
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.fra10.de.leaseweb.net
* epel: mirror.wiuwiu.de
* extras: centosmirror.netcup.net
* updates: mirror.wiuwiu.de
Resolving Dependencies
--> Running transaction check
---> Package java-1.8.0-openjdk.x86_64 1:1.8.0.151-5.b12.el7_4 will be updated
---> Package java-1.8.0-openjdk.x86_64 1:1.8.0.161-0.b14.el7_4 will be an update
---> Package java-1.8.0-openjdk-headless.x86_64 1:1.8.0.151-5.b12.el7_4 will be updated
---> Package java-1.8.0-openjdk-headless.x86_64 1:1.8.0.161-0.b14.el7_4 will be an update
---> Package postgresql-jdbc.noarch 0:42.1.4-1.rhel7 will be updated
---> Package postgresql-jdbc.noarch 0:42.2.0-1.rhel7 will be an update
---> Package postgresql-jdbc-javadoc.noarch 0:42.1.4-1.rhel7 will be updated
---> Package postgresql-jdbc-javadoc.noarch 0:42.2.0-1.rhel7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================
Package                                          Arch                        Version                                      Repository                    Size
==============================================================================================================================================================
Updating:
java-1.8.0-openjdk                               x86_64                      1:1.8.0.161-0.b14.el7_4                      updates                      243 k
java-1.8.0-openjdk-headless                      x86_64                      1:1.8.0.161-0.b14.el7_4                      updates                       32 M
postgresql-jdbc                                  noarch                      42.2.0-1.rhel7                               pgdg96                       566 k
postgresql-jdbc-javadoc                          noarch                      42.2.0-1.rhel7                               pgdg96                       456 k

Transaction Summary
==============================================================================================================================================================
Upgrade  4 Packages

Total size: 33 M
Total download size: 32 M
Is this ok [y/d/N]: y
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
(1/2): java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64.rpm                                                                             | 243 kB  00:00:00     
(2/2): java-1.8.0-openjdk-headless-1.8.0.161-0.b14.el7_4.x86_64.rpm                                                                    |  32 MB  00:00:00     
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                          45 MB/s |  32 MB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Updating   : 1:java-1.8.0-openjdk-headless-1.8.0.161-0.b14.el7_4.x86_64                                                                                 1/8 
warning: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64/jre/lib/security/java.security created as /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64/jre/lib/security/java.security.rpmnew
restored /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64/jre/lib/security/java.security.rpmnew to /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64/jre/lib/security/java.security
 Updating   : 1:java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64                                                                                          2/8 
 Updating   : postgresql-jdbc-42.2.0-1.rhel7.noarch                                                                                                      3/8 
 Updating   : postgresql-jdbc-javadoc-42.2.0-1.rhel7.noarch                                                                                              4/8 
 Cleanup    : postgresql-jdbc-42.1.4-1.rhel7.noarch                                                                                                      5/8 
 Cleanup    : 1:java-1.8.0-openjdk-1.8.0.151-5.b12.el7_4.x86_64                                                                                          6/8 
 Cleanup    : postgresql-jdbc-javadoc-42.1.4-1.rhel7.noarch                                                                                              7/8 
 Cleanup    : 1:java-1.8.0-openjdk-headless-1.8.0.151-5.b12.el7_4.x86_64                                                                                 8/8 
 Verifying  : 1:java-1.8.0-openjdk-1.8.0.161-0.b14.el7_4.x86_64                                                                                          1/8 
 Verifying  : postgresql-jdbc-42.2.0-1.rhel7.noarch                                                                                                      2/8 
 Verifying  : 1:java-1.8.0-openjdk-headless-1.8.0.161-0.b14.el7_4.x86_64                                                                                 3/8 
 Verifying  : postgresql-jdbc-javadoc-42.2.0-1.rhel7.noarch                                                                                              4/8 
 Verifying  : 1:java-1.8.0-openjdk-1.8.0.151-5.b12.el7_4.x86_64                                                                                          5/8 
 Verifying  : postgresql-jdbc-javadoc-42.1.4-1.rhel7.noarch                                                                                              6/8 
 Verifying  : postgresql-jdbc-42.1.4-1.rhel7.noarch                                                                                                      7/8 
 Verifying  : 1:java-1.8.0-openjdk-headless-1.8.0.151-5.b12.el7_4.x86_64                                                                                 8/8 

Updated:
 java-1.8.0-openjdk.x86_64 1:1.8.0.161-0.b14.el7_4   java-1.8.0-openjdk-headless.x86_64 1:1.8.0.161-0.b14.el7_4   postgresql-jdbc.noarch 0:42.2.0-1.rhel7  
 postgresql-jdbc-javadoc.noarch 0:42.2.0-1.rhel7    

Complete!

在連接時,我以前工作的 Java 程式碼(在 Jetty 9.4.7 中作為 WAR 執行)突然出現異常 -

String SQL_MERGE_USERS = "SELECT words_merge_users(?::jsonb, ?::inet, ?::text, ?::text, ?::text)";

// the next line in MyListener.java throws the exception

try (Connection db = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PASS);
       PreparedStatement st = db.prepareStatement(SQL_MERGE_USERS)) {
   st.setString(1, users);
   st.setString(2, ip);
   st.setString(3, fcm);
   st.setString(4, apns);
   st.setString(5, adm);
   ResultSet rs = st.executeQuery();

這裡是從 Jetty 日誌中複製的異常(我在埠 6432 使用 pgbouncer)-

WARNING: SQLException occurred while connecting to 127.0.0.1:6432
org.postgresql.util.PSQLException: The server's DateStyle parameter was changed to ISO. The JDBC driver requires DateStyle to begin with ISO for correct operation.
at org.postgresql.core.v3.QueryExecutorImpl.receiveParameterStatus(QueryExecutorImpl.java:2603)
at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2569)
at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:131)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:212)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:452)
at org.postgresql.Driver.connect(Driver.java:254)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at de.slova.websockets.MyListener.mergeUsers(MyListener.java:152)
at de.slova.websockets.MyListener.handleLogin(MyListener.java:208)
at de.slova.websockets.MyListener.onWebSocketText(MyListener.java:83)
at org.eclipse.jetty.websocket.common.events.JettyListenerEventDriver.onTextMessage(JettyListenerEventDriver.java:189)
at org.eclipse.jetty.websocket.common.message.SimpleTextMessage.messageComplete(SimpleTextMessage.java:69)
at org.eclipse.jetty.websocket.common.events.AbstractEventDriver.appendMessage(AbstractEventDriver.java:66)
at org.eclipse.jetty.websocket.common.events.JettyListenerEventDriver.onTextFrame(JettyListenerEventDriver.java:158)
at org.eclipse.jetty.websocket.common.events.AbstractEventDriver.incomingFrame(AbstractEventDriver.java:162)
at org.eclipse.jetty.websocket.common.WebSocketSession.incomingFrame(WebSocketSession.java:375)
at org.eclipse.jetty.websocket.common.extensions.AbstractExtension.nextIncomingFrame(AbstractExtension.java:182)
at org.eclipse.jetty.websocket.common.extensions.compress.PerMessageDeflateExtension.nextIncomingFrame(PerMessageDeflateExtension.java:105)
at org.eclipse.jetty.websocket.common.extensions.compress.CompressExtension.forwardIncoming(CompressExtension.java:142)
at org.eclipse.jetty.websocket.common.extensions.compress.PerMessageDeflateExtension.incomingFrame(PerMessageDeflateExtension.java:85)
at org.eclipse.jetty.websocket.common.extensions.ExtensionStack.incomingFrame(ExtensionStack.java:220)
at org.eclipse.jetty.websocket.common.Parser.notifyFrame(Parser.java:220)
at org.eclipse.jetty.websocket.common.Parser.parse(Parser.java:256)
at org.eclipse.jetty.websocket.common.io.AbstractWebSocketConnection.readParse(AbstractWebSocketConnection.java:679)
at org.eclipse.jetty.websocket.common.io.AbstractWebSocketConnection.onFillable(AbstractWebSocketConnection.java:511)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:110)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)
at org.eclipse.jetty.util.thread.Invocable.invokePreferred(Invocable.java:128)
at org.eclipse.jetty.util.thread.Invocable$InvocableExecutor.invoke(Invocable.java:222)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:294)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:199)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:673)
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:591)
at java.lang.Thread.run(Thread.java:745)

我已經搜尋了DateStyle並且沒有太多關於這個錯誤的資訊。

從錯誤文本中我不確定要更改什麼 - 我應該修改我的 Java 程序還是應該更改我的 Linux 伺服器上的某些設置?

UDPATE:

我已經送出了一個錯誤問題 #1080

以防其他人遇到此問題,它已在 pgjdbc 42.2.1 中修復。只需升級您的版本,它應該可以工作。

引用自:https://dba.stackexchange.com/questions/195824