* 질문 등록 시 다음의 내용을 꼭 기입하여 주세요.
|
|
CentOS 64bit |
|
|
CUBRID 10.2 (10.2.7.8896-cebe4e9) (64bit release build for Linux) (Jan 26 2022 11:35:52) |
|
|
SQLGate 9.9.8.0 |
|
|
JAVA |
* CUBRID 응용 오류, SQL 오류 또는 SQL 튜닝 관련된 문의는 반드시 다음의 내용을 추가해 주세요. 비밀글이나 비밀 댓글도 가능합니다.
* 저희가 상황을 이해하고, 재현이 가능해야 알 수 있는 문제들이 많습니다. 가능한 정보/정황들을 부탁합니다.
| 에러 내용 및 재현 방법 | 재현 가능한 Source와 SQL |
| 관련 테이블(인덱스, 키정보 포함) 정보 | CUBRID 홈 디렉토리 아래 log 디렉토리 압축 |
-------------- 아래에 질문 사항을 기입해 주세요. ------------------------------------------------------------------------
OS: CentOS
[cubuser@localhost ~]$ cubrid_rel
CUBRID 10.2 (10.2.7.8896-cebe4e9) (64bit release build for Linux) (Jan 26 2022 11:35:52)
응용환경: JAVA
JDBC-10.2-latest-cubrid
JDBC PreparedStatement를 사용하여 특정 SQL 문을 실행시키면 다음 오류가 발생합니다.
cubrid.jdbc.driver.CUBRIDException: Cannot communicate with the broker[CAS INFO-192.168.0.203:30000,2,16669],[SESSION-4],[URL-jdbc:cubrid:192.168.0.203:30000:free:dba:********:?charset=utf-8].
at cubrid.jdbc.jci.UTimedDataInputStream.read(UTimedDataInputStream.java:154)
at cubrid.jdbc.jci.UInputBuffer.<init>(UInputBuffer.java:131)
at cubrid.jdbc.jci.UConnection.send_recv_msg(UConnection.java:1859)
at cubrid.jdbc.jci.UConnection.send_recv_msg(UConnection.java:1871)
at cubrid.jdbc.jci.UStatement.executeInternal(UStatement.java:825)
at cubrid.jdbc.jci.UStatement.execute(UStatement.java:894)
at cubrid.jdbc.driver.CUBRIDStatement.executeCoreInternal(CUBRIDStatement.java:906)
at cubrid.jdbc.driver.CUBRIDStatement.executeCore(CUBRIDStatement.java:871)
at cubrid.jdbc.driver.CUBRIDPreparedStatement.execute(CUBRIDPreparedStatement.java:497)
at itc.dbtx.DBTXManager.execStatement(DBTXManager.java:906)
at itc.dbtx.DBTXManager.doUpdate(DBTXManager.java:745)
at peoplecar.bsonex.S3Export.loadCharge(S3Export.java:527)
at peoplecar.bsonex.S3Export.upload(S3Export.java:127)
at peoplecar.bsonex.S3Export.main(S3Export.java:646)
SQL 문:
update reserve_trans r
inner join
(
select c.rsv_id, sum(if(c.paid_status = 1, c.paid_amt, 0)) as pay_charge
, max(c.charged) as charged, max(ifnull(p.charged, to_date('1970-01-01'))) as charged_unpaid
from charge_trans c
left outer join
charge_trans p
on p.job_id = ?
and c.rsv_id = p.rsv_id
and c.charged = p.charged
and p.charge_type = 1
and p.paid_status != 1
where c.job_id = ?
group by c.rsv_id
) c
on r.job_id = ? and c.rsv_id = r.id
set r.pay_charge = c.pay_charge
, r.pay_yn = if(c.charged = c.charged_unpaid, 'N', 'Y')
Table Script:
CREATE TABLE [DBA].[reserve_trans] (
[job_id] INTEGER NOT NULL,
[id] CHARACTER VARYING(32) NOT NULL,
[product_name] CHARACTER VARYING(64) NOT NULL,
[reserver_id] CHARACTER VARYING(40) NOT NULL,
[reserver_name] CHARACTER VARYING(64) NOT NULL,
[age] INTEGER NOT NULL,
[sex] INTEGER NOT NULL,
[status] CHARACTER VARYING(10) NOT NULL,
[reserve_type] CHARACTER VARYING(10) NOT NULL,
[trip_type] CHARACTER VARYING(20) NOT NULL,
[car_number] CHARACTER VARYING(20) NOT NULL,
[model] CHARACTER VARYING(40) NOT NULL,
[owner] CHARACTER VARYING(40) NOT NULL,
[origin_area] INTEGER NOT NULL,
[origin_spot] CHARACTER VARYING(50) NOT NULL,
[dest_area] INTEGER NOT NULL,
[dest_spot] CHARACTER VARYING(50) NOT NULL,
[reserve_start] DATETIME NOT NULL,
[reserve_end] DATETIME NOT NULL,
[use_start] DATETIME,
[use_end] DATETIME,
[engine_off] DATETIME,
[run_dist] INTEGER NOT NULL,
[deposit] BIGINT NOT NULL,
[exclude] BIGINT NOT NULL,
[deposit_yn] CHARACTER(1) NOT NULL,
[pickup_charge] BIGINT NOT NULL,
[pickup_charge_yn] CHARACTER(1) NOT NULL,
[pickup_excharge] BIGINT NOT NULL,
[pickup_excharge_yn] CHARACTER(1) NOT NULL,
[ride_charge] BIGINT NOT NULL,
[ride_charge_yn] CHARACTER(1) NOT NULL,
[etc_charge] BIGINT NOT NULL,
[etc_charge_yn] CHARACTER(1) NOT NULL,
[use_charge] BIGINT NOT NULL,
[use_charge_yn] CHARACTER(1) NOT NULL,
[use_point] BIGINT NOT NULL,
[use_coupon] CHARACTER VARYING(64) NOT NULL,
[coupon_discount] BIGINT NOT NULL,
[pay_charge] BIGINT NOT NULL,
[pay_yn] CHARACTER(1) NOT NULL,
CONSTRAINT [pk_reserve_trans_job_id_id]
PRIMARY KEY ([job_id], [id]),
INDEX [ref109] ([job_id]),
INDEX [ix_reserve_trans1] ([job_id], [reserver_id]),
CONSTRAINT [fk_reserve_trans_job_id] FOREIGN KEY ([job_id])
REFERENCES [job] ([id]) ON DELETE CASCADE ON UPDATE RESTRICT
)
COLLATE utf8_bin
CREATE TABLE [DBA].[charge_trans] (
[job_id] INTEGER NOT NULL,
[id] CHARACTER VARYING(32) NOT NULL,
[bill_id] CHARACTER VARYING(32) NOT NULL,
[rsv_id] CHARACTER VARYING(32) NOT NULL,
[product_id] CHARACTER VARYING(16) NOT NULL,
[product_name] CHARACTER VARYING(64) NOT NULL,
[paid_amt_reward] BIGINT NOT NULL,
[paid_vat_reward] BIGINT NOT NULL,
[discount_amt] BIGINT NOT NULL,
[discount_vat] BIGINT NOT NULL,
[amt] BIGINT NOT NULL,
[vat] BIGINT NOT NULL,
[paid_amt] BIGINT NOT NULL,
[paid_vat] BIGINT NOT NULL,
[cancel_amt] BIGINT NOT NULL,
[cancel_vat] BIGINT NOT NULL,
[use_coupon] CHARACTER VARYING(64) NOT NULL,
[charge_type] INTEGER NOT NULL,
[status] INTEGER NOT NULL,
[paid_status] INTEGER NOT NULL,
[charged] DATETIME NOT NULL,
[paid] DATETIME,
CONSTRAINT [pk_charge_trans_job_id_id]
PRIMARY KEY ([job_id], [id]),
INDEX [ix_charge_trans1] ([job_id], [bill_id]),
INDEX [ix_charge_trans2] ([job_id], [rsv_id]),
CONSTRAINT [fk_charge_trans_job_id] FOREIGN KEY ([job_id])
REFERENCES [job] ([id]) ON DELETE CASCADE ON UPDATE RESTRICT
)
COLLATE utf8_bin
charge_transe의 데이타 건수: 137769
Exception 이 발생한 JAVA source:
boolean hasResult = stmt.execute();
