Runtime Issue
===
[TOC]
## SecServer:
##### last_d_date: 20201217
### 1. o.h.e.jdbc.spi.SqlExceptionHelper - ORA-00001: unique constraint (NEWSEC.SYS_C00145381) violated
> SEC_MSGMST
> 移除過多的secMsgmstDO.save() [CommunicationBrService.sendMessage]
### 2. NEWSEC.SYS_C00143734
> SEC_MSGBAS

### 3. org.hibernate.internal.SessionImpl - HHH000346: Error during managed flush [could not execute statement]
> 1. 移除全部flush:true

            myDO.save(flush: true) <-- 移除flush

:::info
### 4. org.hibernate.hql.internal.ast.InvalidPathException: Invalid path: 'null.replyFlag'

org.hibernate.hql.internal.ast.InvalidPathException: Invalid path: 'null.replyFlag'
      at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
      at com.hcsaastech.ehis.sec.CommunicationBrService.$tt__findSecMsgMstBySqlWhere(CommunicationBrService.groovy:667)
      at com.hcsaastech.ehis.sec.CommunicationBrService$_findSecMsgMstBySqlWhere_closure11.doCall(CommunicationBrService.groovy)

### 5. antlr.SemanticException: left-hand operand of a binary operator was null

antlr.SemanticException: left-hand operand of a binary operator was null
      at com.hcsaastech.ehis.sec.CommunicationBrService.$tt__findSecMsgMstBySqlWhere(CommunicationBrService.groovy:667)
      at com.hcsaastech.ehis.sec.CommunicationBrService$_findSecMsgMstBySqlWhere_closure11.doCall(CommunicationBrService.groovy)

### 6. antlr.NoViableAltException: unexpected end of subtree

antlr.NoViableAltException: unexpected end of subtree
      at com.hcsaastech.ehis.sec.CommunicationBrService.$tt__findSecMsgMstBySqlWhere(CommunicationBrService.groovy:667)
      at com.hcsaastech.ehis.sec.CommunicationBrService$_findSecMsgMstBySqlWhere_closure11.doCall(CommunicationBrService.groovy)

:bulb: **Hint: use alias**
> SecMsgbas.msgTo='012004' AND SecMsgbas.replyFlag='N'
> t1.msgTo='012004' AND t1.replyFlag='N'

org.springframework.orm.hibernate5.HibernateQueryException: Invalid path: 'null.msgTo' [
             	select t
               	from com.hcsaastech.ehis.sec.SecMsgmst t , com.hcsaastech.ehis.sec.SecMsgbas t1
               	where t1.secMsgmst=t.id and
           		SecMsgbas.msgTo='012004' AND SecMsgbas.replyFlag='N' ]; nested exception is org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'null.msgTo' [

:::

### 7. java.lang.Exception: java.lang.Exception: java.lang.NullPointerException: Cannot get property 'properties' on null object
>com.hcsaastech.common.utility.DTOTrans
> private static def TransObject(def fromObj, def toObj, String packagePath, boolean fromDTOFlag) throws Exception {
>> dtoObj.**properties**.keySet().each() { ...
>> doObj.**properties**.containsKey(dtoProperty)) { ...
>> 來源要做保護

### 8. Optimistic Locking
> 樂觀鎖gails預設是打開的, 透過每個domain 都以version 來control 順序性,
消極解法就是多做一次, (如果前後順序沒差的話)
這可以用橫切面來解, 但 其實可以disable 它, 可以試試看.

grails.gorm.default.mapping = {
    version false
}

http://docs.grails.org/3.1.1/ref/Database%20Mapping/version.html
https://stackoverflow.com/questions/27679114/disable-optimistic-locking-globally-in-grails/27683994

### 9. o.h.e.jdbc.spi.SqlExceptionHelper - ORA-00001: unique constraint (NEWSEC.SYS_C00145381) violated
> CommunicationBrService: ResultDTO sendMessage(SecMsgmstDTO secMsgmstDTO) {
secMsgmstDTO.id=0 <- 建議補這行

### 10. java.lang.ArrayIndexOutOfBoundsException: 1

SecSmslogBrImplService
def sendNews(SecSmslogDAO secSmslogDAO) {
...
secSmslogDAO.reportQuery.trim().split(";").each {
  replyUriBuilder.queryParam(it.split("=")[0], it.split("=")[1])
}

### 11. java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected
> 參數要保護

PortalsBrImplService
 def findAllAxis2log(QueryDTO queryDTO) throws Exception {

### １2. oracle.jdbc.OracleDatabaseException: ORA-01460: unimplemented or unreasonable conversion requested
> TO_DATE format 錯誤

CommunicationBrService
...
ResultDTO sendMessage(SecMsgmstDTO secMsgmstDTO) {
...
            def result = SecMsgmst.findAll {
                dateCreated >= startTime && dateCreated <= endTime &&
                        msgTo == secMsgmst.msgTo &&
                        msgTitle == secMsgmst.msgTitle && msgContent == secMsgmst.msgContent
            }

## CommServer
##### last_d_date: 20201217
### 1. org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not extract ResultSet; bad SQL grammar [n/a]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00936: missing expression
> emrcodedtl0_.code_desc in ()

Caused by: Error : 936, Position : 668, Sql = select emrcodedtl0_.id as id1_71_, emrcodedtl0_.version as version2_71_, emrcodedtl0_.code_desc as code_desc3_71_, emrcodedtl0_.code_no as code_no4_71_, emrcodedtl0_.code_type as code_type5_71_, emrcodedtl0_.created_by as created_by6_71_, emrcodedtl0_.date_created as date_created7_71_, emrcodedtl0_.emrcodemst_id as emrcodemst_id8_71_, emrcodedtl0_.enabled as enabled9_71_, emrcodedtl0_.eng_desc as eng_desc10_71_, emrcodedtl0_.last_updated as last_updated11_71_, emrcodedtl0_.last_updated_by as last_updated_by12_71_, emrcodedtl0_.remark_desc as remark_desc13_71_ from EMR.EMR_CODEDTL emrcodedtl0_ where emrcodedtl0_.code_type='antiBExamOrder' and (emrcodedtl0_.code_desc in ()) and emrcodedtl0_.enabled='Y', OriginalSql = select emrcodedtl0_.id as id1_71_, emrcodedtl0_.version as version2_71_, emrcodedtl0_.code_desc as code_desc3_71_, emrcodedtl0_.code_no as code_no4_71_, emrcodedtl0_.code_type as code_type5_71_, emrcodedtl0_.created_by as created_by6_71_, emrcodedtl0_.date_created as date_created7_71_, emrcodedtl0_.emrcodemst_id as emrcodemst_id8_71_, emrcodedtl0_.enabled as enabled9_71_, emrcodedtl0_.eng_desc as eng_desc10_71_, emrcodedtl0_.last_updated as last_updated11_71_, emrcodedtl0_.last_updated_by as last_updated_by12_71_, emrcodedtl0_.remark_desc as remark_desc13_71_ from EMR.EMR_CODEDTL emrcodedtl0_ where emrcodedtl0_.code_type='antiBExamOrder' and (emrcodedtl0_.code_desc in ()) and emrcodedtl0_.enabled='Y', Error Msg = ORA-00936: missing expression

### 2. java.lang.NullPointerException: Cannot set property 'displayName' on null object

### 3. CommMedReplaceService.findAllCMRDByQuery Exception: null

### 4. callWso2EsbResult throw exception

### 5. exception when export report: No input source supplied to the exporter.

### 6. exception when fill report: Error executing SQL statement for : PHM6140-1Hcaemr

### 7. exception when fill report: Error executing SQL statement for : PHM6110Hcaemr

### 8. CommMedReplaceService.getSurplusAmount Exception: Closed Connection

### 9. CommMedReplaceService.getChronicKeepQty Exception: Closed Connection

### 10. Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
> version false

## Hibernate setMaxResult()
### 從Hibernate StatementInspector 去攔截
### 1. application.grooy

sql {
    inspector{
        enable = true
        query_max_results = 1
    }
}

### 2. application.yml

hibernate:
    show_sql: true
#    use_sql_comments: true
#    format_sql: true
    session_factory:
        statement_inspector: com.hcsaastech.ehis.xxxxx.SQLInspector
    cache:
        queries: false
        use_second_level_cache: true
        use_query_cache: false
        region.factory_class: org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory

### 3. SQLInspector

import grails.util.Holders
import groovy.util.logging.Slf4j
import org.hibernate.resource.jdbc.spi.StatementInspector

@Slf4j
class SQLInspector implements StatementInspector {

    private static final String STMT_SELECT_ONLY = "(?i)(?=.*\\s*select\\s+)^(?!.*\\s+insert\\s+)^(?!.*\\s+update\\s+)^(?!.*\\s+delete\\s+).*";

    @Override
    public String inspect(String sql) {
        def enable = Holders.config.sql.inspector.enable
        def query_max_results = Holders.config.sql.inspector.query_max_results
        log.info "sql.inspector.enable: ${enable}"
        log.info "sql.inspector.enable: ${query_max_results}"
        if(enable) {
            if(sql.matches(STMT_SELECT_ONLY)) {
                StringBuffer sb = new StringBuffer();
                sb.append("SELECT * FROM ( ").append(sql).append(" ) WHERE ROWNUM <= ${query_max_results} ");
                sql = sb.toString()
            }
        }
        return sql
    }
}

## DB Lock
### find db lock by date

SELECT T.sql_id, count(1), 
(SELECT PARSING_SCHEMA_NAME FROM v$sql WHERE sql_id=T.sql_id AND rownum=1) schema_name,
(SELECT  sql_text FROM v$sql WHERE sql_id=T.sql_id AND rownum=1) sql_text
FROM (
SELECT count(1),session_id,"SESSION_SERIAL#" ,BLOCKING_SESSION,"BLOCKING_SESSION_SERIAL#" ,event,sql_id
FROM "GV$ACTIVE_SESSION_HISTORY" 
WHERE BLOCKING_SESSION IS NOT NULL
AND sample_time >sysdate -6/24
AND event LIKE '%TX%'
GROUP BY session_id,"SESSION_SERIAL#" ,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,event,sql_id
ORDER BY 7
)T GROUP BY T.sql_id
ORDER BY count(1) desc

select username,
sum(decode(substr(sql_exec_start,12,2),'00',1,0)) as "h00",
sum(decode(substr(sql_exec_start,12,2),'01',1,0)) as "h01",
sum(decode(substr(sql_exec_start,12,2),'02',1,0)) as "h02",
sum(decode(substr(sql_exec_start,12,2),'03',1,0)) as "h03",
sum(decode(substr(sql_exec_start,12,2),'04',1,0)) as "h04",
sum(decode(substr(sql_exec_start,12,2),'05',1,0)) as "h05",
sum(decode(substr(sql_exec_start,12,2),'06',1,0)) as "h06",
sum(decode(substr(sql_exec_start,12,2),'07',1,0)) as "h07",
sum(decode(substr(sql_exec_start,12,2),'08',1,0)) as "h08",
sum(decode(substr(sql_exec_start,12,2),'09',1,0)) as "h09",
sum(decode(substr(sql_exec_start,12,2),'10',1,0)) as "h10",
sum(decode(substr(sql_exec_start,12,2),'11',1,0)) as "h11",
sum(decode(substr(sql_exec_start,12,2),'12',1,0)) as "h12",
sum(decode(substr(sql_exec_start,12,2),'13',1,0)) as "h13",
sum(decode(substr(sql_exec_start,12,2),'14',1,0)) as "h14",
sum(decode(substr(sql_exec_start,12,2),'15',1,0)) as "h15",
sum(decode(substr(sql_exec_start,12,2),'16',1,0)) as "h16",
sum(decode(substr(sql_exec_start,12,2),'17',1,0)) as "h17",
sum(decode(substr(sql_exec_start,12,2),'18',1,0)) as "h18",
sum(decode(substr(sql_exec_start,12,2),'19',1,0)) as "h19",
sum(decode(substr(sql_exec_start,12,2),'20',1,0)) as "h20",
sum(decode(substr(sql_exec_start,12,2),'21',1,0)) as "h21",
sum(decode(substr(sql_exec_start,12,2),'22',1,0)) as "h22",
sum(decode(substr(sql_exec_start,12,2),'23',1,0)) as "h23"
from (
select c.username,c.sql_exec_start, max(ceil((to_date(block_end_time, 'YYYY-MM-DD HH24:MI:SS') - to_date(sql_exec_start , 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60)) block_sec from (
SELECT t.username, t.sql_id, t.session_id,t.session_serial#,t.BLOCKING_SESSION,t.BLOCKING_SESSION_SERIAL#,to_char(t.sql_exec_start,'YYYY-MM-DD HH24:MI:SS') sql_exec_start,block_end_time,
      (SELECT  sql_text FROM v$sql WHERE sql_id=T.sql_id AND rownum=1) sql_text
FROM (
         SELECT b.username,a.session_id,a.SESSION_SERIAL# ,a.BLOCKING_SESSION, a.BLOCKING_SESSION_SERIAL# ,a.event,sql_id, a.sql_exec_start,to_char(max(a.sample_time),'YYYY-MM-DD HH24:MI:SS') block_end_time
         FROM "GV$ACTIVE_SESSION_HISTORY" a, dba_users b
         WHERE a.BLOCKING_SESSION IS NOT NULL
           and b.user_id = a.user_id
           AND to_char(a.sql_exec_start,'YYYY-MM-DD') = '2021-01-18'
           AND a.event LIKE '%TX%'
         GROUP BY b.username, a.session_id, a.SESSION_SERIAL# ,a.BLOCKING_SESSION, a.BLOCKING_SESSION_SERIAL#, a.event,sql_id, a.sql_exec_start
     )t 
     GROUP BY t.username, t.sql_id, t.session_id, t.session_serial# ,t.BLOCKING_SESSION, t.BLOCKING_SESSION_SERIAL#, t.sql_exec_start, block_end_time order by sql_exec_start) c
     group by c.username, c.sql_exec_start 
     having max(ceil((to_date(block_end_time, 'YYYY-MM-DD HH24:MI:SS') - to_date(sql_exec_start , 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60)) > 1 
     and max(ceil((to_date(block_end_time, 'YYYY-MM-DD HH24:MI:SS') - to_date(sql_exec_start , 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60)) < 61) d
     group by username ;