[MDEV-19844] (Reopen) Unexpected crash occured repeatedly Created: 2019-06-24  Updated: 2020-04-22

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Update, Optimizer, Prepared Statements
Affects Version/s: 10.2.24
Fix Version/s: None

Type: Bug Priority: Major
Reporter: SKCHOI Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux 2.6.32-696.30.1.el6.x86_64 #1 SMP Tue May 22 03:28:18 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

Issue Links:
Relates
relates to MDEV-8554 Server crashes in base_list_iterator:... Closed

 Description   

Thank you for many people's previous support for my issue.

After upgrading my DB from 10.2.8 to 10.2.24, unexpected crash has been occured again

and DB left error log below.

 
 
#######
 
190621 16:39:39 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed,
 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.2.24-MariaDB-log
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=21
 
max_threads=1002
 
thread_count=27
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 408925 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7fe5fc0009a8
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7fe808928e18 thread_stack 0x49000
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(my_print_stacktrace+0x2e)[0xdf0f9e]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(handle_fatal_signal+0x4f8)[0x7eb2c8]
 
/lib64/libpthread.so.0[0x36d580f7e0]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN13st_select_lex21save_prep_leaf_tablesEP3THD+0x72)[0x5ddf72]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN13st_select_lex21save_prep_leaf_tablesEP3THD+0xef)[0x5ddfef]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN13st_select_lex21save_prep_leaf_tablesEP3THD+0xef)[0x5ddfef]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN3LEX21save_prep_leaf_tablesEv+0x52)[0x5de072]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z26mysql_multi_update_prepareP3THD+0x790)[0x6a1560]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x2d15)[0x5ef915]
 
sql/log_event.h:1345(Log_event::write_footer())[0x97603e]
 
sql/sql_acl.cc:12061(login_failed_error)[0x976ef7]
 
sql/sql_acl.cc:1453(ACL_ROLE)[0x97910f]
 
sql/sql_acl.cc:13654(old_password_authenticate)[0x979c7c]
 
sql/sql_string.h:346(~String)[0x97b1ed]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x363f)[0x5f023f]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4d5)[0x6079d5]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0x9e)[0x60d9ee]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z22mysql_sql_stmt_executeP3THD+0x149)[0x60e2d9]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x336c)[0x5eff6c]
 
sql/log_event.h:1345(Log_event::write_footer())[0x97603e]
 
sql/sql_acl.cc:12061(login_failed_error)[0x976ef7]
 
sql/sql_acl.cc:1453(ACL_ROLE)[0x97910f]
 
sql/sql_acl.cc:13654(old_password_authenticate)[0x979c7c]
 
sql/sql_string.h:346(~String)[0x97b1ed]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x363f)[0x5f023f]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4d5)[0x6079d5]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0x9e)[0x60d9ee]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld[0x60e5a7]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x28)[0x60e718]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x2355)[0x5fa725]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z10do_commandP3THD+0x165)[0x5fb465]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x235)[0x6e3a55]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(handle_one_connection+0x3f)[0x6e3c1f]
 
sql/sql_repl.cc:2713(mysql_binlog_send(THD*, char*, unsigned long long, unsigned short))[0xa15919]
 
/lib64/libpthread.so.0[0x36d5807aa1]
 
/lib64/libc.so.6(clone+0x6d)[0x36d54e8bcd]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fe5fd9132c0): 
analyze
 
UPDATE qt_entry et                      
  left join if_xximpm_cc_completion_r r
 
  on et.IMP_IF_REC_SEQ = r.REC_SEQ
 
  SET et.DIVISION_CODE = QT_GET_DIVISION_BY_MODEL(r.item_no),
 
      et.SUBSIDIARY_CODE = QT_GET_CORP_BY_ORG_ID(r.OPERATION_UNIT_ID),
 
  et.AU_CODE = QT_GET_AU_OF_CORP(QT_GET_CORP_BY_ORG_ID(r.OPERATION_UNIT_ID)),
 
  et.DECLARATION_YMD = DATE_FORMAT(r.IMPORT_DECLARATION_COMPLT_DATE,'%Y%m%d'),
 
  et.COMPLETE_YMD = DATE_FORMAT(r.CC_COMPLETION_TRANSACTION_DATE,'%Y%m%d'),
 
  et.ITEM_NO = r.ITEM_NO,
 
  et.DESTINATION_PORT  = QT_GET_NTH_FIELD(r.ATTRIBUTE11,1,':'),
 
  et.HOUSE_BL_NO = r.HOUSE_BL_NO,
 
  et.IMPORT_DECLARATION_NO = r.ATTRIBUTE1,
 
  et.INVOICE_NO = r.ATTRIBUTE3,
 
  et.SHIPMENT_LINE_ID = r.CC_SHIPMENT_LINE_ID,
 
  et.FILE_NO = r.FILE_NO,
 
  et.REFRIGERANTS_CODE = QT_GET_MODEL_REFRIG_TYPE(r.item_no),
 
  et.REFRIGERANTS_QTY = QT_GET_MODEL_REFRIG_QTY(r.item_no),
 
  et.CURRENCY_CODE = r.SHIPMENT_CURRENCY_CODE,
 
  et.CC_QTY = r.CUSTOMS_CLEARANCE_QTY,
 
  et.UPDT_DT = NOW(),
 
  et.UPDT_ID = 'RQMS_INF',
 
  et.GWP = (SELECT mm.gwp FROM v_model_master MM
 
                  where MM.MODEL_SUFFIX = r.item_no), -- GWP
 
  et.ORGANIZATION_CODE = r.ORGANIZATION_CODE
 
  WHERE et.IMP_IF_REC_SEQ =  NAME_CONST('rech_REC_SEQ',232772);
 
Connection ID (thread ID): 54346
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,
 
index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,
 
derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,
 
partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,
 
outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,
 
optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,
 
condition_pushdown_for_derived=on

Occurance frequency reduced, but I still cannot determine root cause of this crash.

DB/Table configuration has no problem and the result of analyzing table may have no issue.

######
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 408925 Kbytes = 399.34Mbytes of memory
Hope that's ok; if not, decrease some variables in the equation.

My DB : 128M + (128K + 128K) * 1000 = 128M + 256M = 384M
######

Any suggestion or advice is highly appreciated.

If you want to get more information, please let me know.

Thank you!

(Previously posted issue)
=======================================================
Hello,

Database engine has been crashed and automatically restarted. Problem is being occured 2~3 times per week. I found error details on error log as below.

180921 10:24:04 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed,
 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.2.8-MariaDB-log
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=15
 
max_threads=1002
 
thread_count=22
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 408761 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7ff1e80009a8
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7ff398351e18 thread_stack 0x49000
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(my_print_stacktrace+0x2e)[0xde62fe]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(handle_fatal_signal+0x444)[0x7dbcc4]
 
/lib64/libpthread.so.0[0x36d580f7e0]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN8Item_row10fix_fieldsEP3THDPP4Item+0xd0)[0x8993e0]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN17Item_in_optimizer8fix_leftEP3THD+0x242)[0x820d52]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN17Item_in_optimizer10fix_fieldsEP3THDPP4Item+0x49)[0x822969]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0x19a)[0x82c57a]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x1d4)[0x8659d4]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN13Item_func_not10fix_fieldsEP3THDPP4Item+0x320)[0x82e8d0]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0x19a)[0x82c57a]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z11setup_condsP3THDP10TABLE_LISTR4ListIS1_EPP4Item+0x204)[0x597df4]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN4JOIN7prepareEP10TABLE_LISTjP4ItemjP8st_orderbS5_S3_S5_P13st_select_lexP18st_select_lex_unit+0x5a1)[0x63c8d1]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x9cd)[0x64d43d]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x254)[0x64e884]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld[0x5e6e42]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x1688)[0x5ea218]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z17mysql_open_cursorP3THDP13select_resultPP18Server_side_cursor+0x156)[0x97e046]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN9sp_cursor4openEP3THD+0x1e)[0x96ebfe]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN14sp_instr_copen9exec_coreEP3THDPj+0x30)[0x963250]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0xa2)[0x963b02]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN14sp_instr_copen7executeEP3THDPj+0x63)[0x963e13]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN7sp_head7executeEP3THDb+0x4be)[0x965a6e]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x61d)[0x9692dd]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x25c8)[0x5eb158]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x49c)[0x60393c]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0xb1)[0x609061]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z22mysql_sql_stmt_executeP3THD+0x10e)[0x6097be]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x1e9e)[0x5eaa2e]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x1e)[0x962a8e]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0xa2)[0x963b02]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x51e)[0x96687e]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN7sp_head7executeEP3THDb+0x4be)[0x965a6e]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x61d)[0x9692dd]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x25c8)[0x5eb158]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x49c)[0x60393c]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0xb1)[0x609061]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld[0x6099b6]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x28)[0x609b18]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x20bd)[0x5f619d]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z10do_commandP3THD+0x15d)[0x5f782d]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x235)[0x6db3d5]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld(handle_one_connection+0x3f)[0x6db59f]
 
/engn001/masvc01/RQMSP/mysql/bin/mysqld[0xa01c79]
 
/lib64/libpthread.so.0[0x36d5807aa1]
 
/lib64/libc.so.6(clone+0x6d)[0x36d54e8bcd]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7ff1e903bd90): call QT_CLOSING_PROC
 
Connection ID (thread ID): 17
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
 
 
 
========================================
 
By equation on error log, my DB is satisfying condition.
 
 
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 408761 K 
131072 + (128 + 128) * 1002 = 131072 + 256 * 1002 = 131072 + 256512 = 387584K < 408761K
 
 

Any suggestion or comment is highly appreciated.

Thank you and have a good day!

 Comments   
Comment by Alice Sherepa [ 2019-06-25 ]

Could you please add the output of 

SHOW CREATE TABLE qt_entry;
 
SHOW CREATE TABLE if_xximpm_cc_completion_r;
 
SHOW CREATE TABLE v_model_master;  or (SHOW CREATE VIEW v_model_master; )

and your .cnf file(s)

Comment by SKCHOI [ 2019-06-25 ]

Dear Alice,

I'd like to add more what you requested.

1. show create table qt_entry;
CREATE TABLE `qt_entry` (
`ENTRY_SEQ` BIGINT(22) NOT NULL AUTO_INCREMENT,
`ENTRY_TYPE` VARCHAR(20) NULL DEFAULT '1' COLLATE 'utf8_bin',
`DIVISION_CODE` VARCHAR(4) NULL DEFAULT NULL COLLATE 'utf8_bin',
`SUBSIDIARY_CODE` VARCHAR(8) NULL DEFAULT NULL COLLATE 'utf8_bin',
`AU_CODE` VARCHAR(4) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ORGANIZATION_CODE` VARCHAR(4) NULL DEFAULT NULL COLLATE 'utf8_bin',
`DECLARATION_YMD` VARCHAR(8) NULL DEFAULT NULL COLLATE 'utf8_bin',
`COMPLETE_YMD` VARCHAR(8) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ITEM_NO` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`DESTINATION_PORT` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`HOUSE_BL_NO` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`IMPORT_DECLARATION_NO` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_bin',
`INVOICE_NO` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_bin',
`SHIPMENT_LINE_ID` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`FILE_NO` VARCHAR(13) NULL DEFAULT NULL COLLATE 'utf8_bin',
`REFRIGERANTS_CODE` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
`REFRIGERANTS_QTY` DECIMAL(22,3) NULL DEFAULT NULL,
`GWP` DECIMAL(10,1) NULL DEFAULT NULL,
`CURRENCY_CODE` VARCHAR(15) NULL DEFAULT NULL COLLATE 'utf8_bin',
`CC_QTY` DECIMAL(22,0) NULL DEFAULT NULL,
`REFRIGERANTS_BOOK_PRICE` DECIMAL(22,2) NULL DEFAULT NULL,
`REFRIGERANTS_BOOK_AMT` DECIMAL(22,0) NULL DEFAULT NULL,
`REFRIGERANTS_TRX_PRICE` DECIMAL(22,0) NULL DEFAULT NULL,
`REFRIGERANTS_TRX_AMT` DECIMAL(22,2) NULL DEFAULT NULL,
`EUR_PRICE` DECIMAL(22,2) NULL DEFAULT NULL,
`CRT_DT` DATETIME NOT NULL DEFAULT current_timestamp(),
`CRT_ID` VARCHAR(50) NOT NULL COLLATE 'utf8_bin',
`UPDT_DT` DATETIME NOT NULL DEFAULT current_timestamp(),
`UPDT_ID` VARCHAR(50) NOT NULL COLLATE 'utf8_bin',
`SUB_AGGREGATE_SEQ` BIGINT(20) NULL DEFAULT NULL,
`MODEL_AGGREGATE_SEQ` BIGINT(20) NULL DEFAULT NULL,
`UPLOAD_SEQ` BIGINT(20) NULL DEFAULT NULL,
`COST_S_MQ_GROUP_SEQ` BIGINT(20) NULL DEFAULT NULL,
`IMP_IF_REC_SEQ` BIGINT(20) NULL DEFAULT NULL,
UNIQUE INDEX `UK01` (`ENTRY_SEQ`),
INDEX `N01` (`SUB_AGGREGATE_SEQ`),
INDEX `N02` (`IMP_IF_REC_SEQ`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
AUTO_INCREMENT=34153
;

2. show create table if_xximpm_cc_completion_r;
CREATE TABLE `if_xximpm_cc_completion_r` (
`REC_SEQ` BIGINT(20) NOT NULL AUTO_INCREMENT,
`INTERFACE_ID` BIGINT(22) NULL DEFAULT NULL,
`INTERFACE_TYPE_CODE` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`OPERATION_UNIT_ID` BIGINT(22) NULL DEFAULT NULL,
`ORGANIZATION_ID` BIGINT(22) NULL DEFAULT NULL,
`ORGANIZATION_CODE` VARCHAR(3) NULL DEFAULT NULL COLLATE 'utf8_bin',
`CC_BL_ID` BIGINT(22) NULL DEFAULT NULL,
`CC_SEQS` BIGINT(22) NULL DEFAULT NULL,
`HOUSE_BL_NO` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`LC_NO` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_bin',
`FILE_NO` VARCHAR(13) NULL DEFAULT NULL COLLATE 'utf8_bin',
`FILE_TYPE_CODE` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_bin',
`SHIPMENT_SEQS` BIGINT(22) NULL DEFAULT NULL,
`CC_SHIPMENT_HEADER_ID` BIGINT(22) NULL DEFAULT NULL,
`ARRIVAL_DATE` DATETIME NULL DEFAULT NULL,
`SHIPMENT_DATE` DATETIME NULL DEFAULT NULL,
`SHIPMENT_COUNTRY_CODE` VARCHAR(3) NULL DEFAULT NULL COLLATE 'utf8_bin',
`PRICE_TERMS_CODE` VARCHAR(15) NULL DEFAULT NULL COLLATE 'utf8_bin',
`IMPORT_DECLARATION_COMPLT_DATE` DATETIME NULL DEFAULT NULL,
`CC_COMPLETION_TRANSACTION_DATE` DATETIME NULL DEFAULT NULL,
`CC_SHIPMENT_LINE_ID` BIGINT(22) NULL DEFAULT NULL,
`ITEM_NO` VARCHAR(40) NULL DEFAULT NULL COLLATE 'utf8_bin',
`SUPPLIER_CODE` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`MAKER_CODE` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`AGENT_CODE` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`HS_CODE` VARCHAR(35) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ORIG_CNTRY_CODE` VARCHAR(2) NULL DEFAULT NULL COLLATE 'utf8_bin',
`TAXATION_VALUE` DECIMAL(20,3) NULL DEFAULT NULL,
`SHIPMENT_CURRENCY_CODE` VARCHAR(15) NULL DEFAULT NULL COLLATE 'utf8_bin',
`SHIPMENT_UNIT_PRICE` DECIMAL(20,3) NULL DEFAULT NULL ,
`CUSTOMS_CLEARANCE_QTY` DECIMAL(20,3) NULL DEFAULT NULL,
`PACKING_QTY` DECIMAL(20,3) NULL DEFAULT NULL,
`PACKING_UNIT_CODE` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`CC_UNIT_PRICE` DECIMAL(20,3) NULL DEFAULT NULL,
`FRN_CURR_CC_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CUSTOMS_CLEARANCE_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`TAXATION_EXCHANGE_RATE` DECIMAL(20,3) NULL DEFAULT NULL,
`LOCAL_EXPECTED_FREIGHT_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`INSURANCE_PREMIUM_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`LOCAL_EXPECTED_COST` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX1_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX2_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX3_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX4_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX5_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX6_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX7_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX8_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX9_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX10_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX11_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX12_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX13_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX14_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`CC_TAX15_AMOUNT` DECIMAL(20,3) NULL DEFAULT NULL,
`ATTRIBUTE1` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE2` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE3` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE4` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE5` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE6` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE7` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE8` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE9` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE10` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`TRANSFER_DATE` DATETIME NULL DEFAULT NULL,
`TRANSFER_FLAG` VARCHAR(1) NULL DEFAULT NULL COLLATE 'utf8_bin',
`CREATED_BY` BIGINT(22) NULL DEFAULT NULL,
`CREATION_DATE` DATETIME NULL DEFAULT NULL,
`LAST_UPDATED_BY` BIGINT(22) NULL DEFAULT NULL,
`LAST_UPDATE_DATE` DATETIME NULL DEFAULT NULL,
`GLOBAL_UNIQUE_ID` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_bin',
`BAM_SEQUENCE_ID` BIGINT(22) NULL DEFAULT NULL,
`OLD_GLOBAL_UNIQUE_ID` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE11` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE12` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE13` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE14` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE15` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE16` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE17` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE18` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE19` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE20` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE21` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE22` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE23` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE24` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE25` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE26` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE27` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE28` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE29` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ATTRIBUTE30` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_bin',
`TRX_FLAG` VARCHAR(10) NOT NULL DEFAULT 'N' COLLATE 'utf8_bin',
`TRX_DATE` DATETIME NULL DEFAULT NULL,
`TRX_RESULT` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_bin',
`TRX_ERRM` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_bin',
UNIQUE INDEX `UK01` (`REC_SEQ`),
INDEX `N01` (`TRX_FLAG`),
INDEX `N02` (`CC_COMPLETION_TRANSACTION_DATE`),
INDEX `N03` (`CC_SHIPMENT_LINE_ID`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
AUTO_INCREMENT=248910
;

3. show create table v_model_master;
CREATE ALGORITHM=UNDEFINED
DEFINER="rqms_mgr"@"%" SQL SECURITY DEFINER VIEW "v_model_master"
AS select
"t"."MODEL_SUFFIX" AS "MODEL_SUFFIX",
"t"."MODEL_CODE" AS "MODEL_CODE",
"t"."SUFFIX_CODE" AS "SUFFIX_CODE",
"t"."HQ_ACCOUNTING_UNIT_CODE" AS "HQ_ACCOUNTING_UNIT_CODE",
"t"."DIVISION_CODE" AS "DIVISION_CODE",
"t"."REFRIGERANTS_CODE" AS "REFRIGERANTS_CODE",
"t"."REFRIGERANTS_QTY" AS "REFRIGERANTS_QTY",
(select cast("cm"."ATTR3" as decimal(10,2)) from "cm_code" "cm"
where "cm"."GRP_CD" = 'RQMS_REFRIGERANTS_CODE' and "cm"."CD" = "t"."REFRIGERANTS_CODE") AS "GWP",
"t"."REFRIGERANTS_QTY" *
(select cast("cm"."ATTR3" as decimal(10,2)) from "cm_code" "cm"
where "cm"."GRP_CD" = 'RQMS_REFRIGERANTS_CODE' and "cm"."CD" = "t"."REFRIGERANTS_CODE") AS "CER_QTY",
"t"."PRODUCT_LEVEL1_CODE" AS "PRODUCT_LEVEL1_CODE",
"t"."PRODUCT_LEVEL2_CODE" AS "PRODUCT_LEVEL2_CODE",
"t"."PRODUCT_LEVEL3_CODE" AS "PRODUCT_LEVEL3_CODE",
"t"."PRODUCT_LEVEL4_CODE" AS "PRODUCT_LEVEL4_CODE",
"t"."CRT_DT" AS "CRT_DT","t"."CRT_ID" AS "CRT_ID",
"t"."UPDT_DT" AS "UPDT_DT","t"."UPDT_ID" AS "UPDT_ID" from "t_model_master" "t";

4. my.cnf
[client]
port=4000
socket=/engn001/masvc01/RQMSP/mysql/mysql.sock
default-character-set=utf8

[mysqld]
port=4000
socket=/engn001/masvc01/RQMSP/mysql/mysql.sock
basedir=/engn001/masvc01/RQMSP/mysql
datadir=/data001/masvc01/RQMSP
#tmpdir=/logs001/masvc01/RQMSP/data
tmpdir=/temp001/masvc01/RQMSP

      1. Replication set
        ##Master
        #server-id=1
    2. Slave
      #server-id=2
      #relay-log=/logs001/masvc01/RQMSP/relay/relay-log
      #read_only=1
      #log_slow_slave_statements=1
      #slave_parallel_threads=8
      1. LOG
        log_error=/logs001/masvc01/RQMSP/error/RQMSP.err
        slow_query_log_file=/logs001/masvc01/RQMSP/slow/RQMSP_slow.log
        slow_query_log= ON
        long_query_time=5
        log-slow-verbosity= 'query_plan,explain'
        log-bin=/logs001/masvc01/RQMSP/binary/mysql-bin
        binlog_format=MIXED
        expire_logs_days=7
        max_binlog_size=512M
        binlog-annotate-row-events=0
        log_bin_trust_function_creators=1
        log-warnings=2
        log_output=FILE
        general_log = 1
        general_log_file = /logs001/masvc01/RQMSP/general/RQMSP_general.log
      1. Character set
        #character-set-server=UTF8
        #collation_server=utf8_bin
        #default-character-set=UTF8
        init_connect= SET collation_connection = utf8_bin
        init_connect= SET NAMES utf8
        character_set_server= utf8
        collation-server= 'utf8_bin'
      1. Common set
        max_connections=1000
        max_connect_errors=1000000
        open_files_limit=65535
        thread_cache_size=2000 ## 20180814 100 -> 2000
        max_allowed_packet=1G
        max_heap_table_size=512M
        tmp_table_size=1G
        transaction_isolation=READ-COMMITTED
        event_scheduler=0
        autocommit=0
        lower_case_table_names=1
        read_buffer_size=128K
        read_rnd_buffer_size=128K
        sort_buffer_size=128K
        join_buffer_size=128K
        query_cache_type=0
        query_cache_size=0
        key_buffer_size=128M ## 24M -> 128M 180914 NJH
        symbolic-links= 0
        local-infile= 1
      1. Connection
        wait_timeout=28800
        lock_wait_timeout=31536000
        connect_timeout=10
        table_open_cache=2000
        table-definition-cache=2000
      1. MyISAM Specific options
      1. INNODB Specific options
        innodb_buffer_pool_size=6G
        innodb_flush_method=O_DIRECT
        innodb_data_file_path=ibdata1:1024M;ibdata2:1024M;ibdata3:1024M:autoextend
        innodb_log_buffer_size=128M
        innodb_file_per_table=1
        innodb_data_home_dir=/data001/masvc01/RQMSP
        innodb_write_io_threads=8
        innodb_read_io_threads=8
        innodb_rollback_on_timeout=0
        innodb_rollback_segments=128
        innodb_table_locks=1
        innodb_log_files_in_group=3
        innodb_log_file_size=1024M
        innodb_thread_concurrency=8
        innodb_show_verbose_locks=1
        innodb_print_all_deadlocks=1
        innodb_doublewrite=0
        innodb_thread_sleep_delay=10000
        innodb_large_prefix=0
        innodb_lock_wait_timeout=3600

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_load_now=1
innodb_buffer_pool_dump_now= 1

innodb_buffer_pool_instances=4
innodb_file_format=Barracuda
innodb_file_format_max=Barracuda

sync_binlog=1
performance_schema=1
#plugin-load = server_audit
skip-name-resolve
sql-mode="TRADITIONAL,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI_QUOTES"
#sql-mode=STRICT_TRANS_TABLES,PIPES_AS_CONCAT

      1. Audit set
        server_audit_logging=ON
        server_audit_events=QUERY_DDL

[mysqld_safe]
open-files-limit=65535

[xtrabackup]
default-character-set = 'utf8'

[mysqldump]
max-allowed-packet = 1G
default-character-set = 'utf8'

If you need more data or information, please reply.

Thank you!

Comment by Alice Sherepa [ 2019-06-25 ]

Is it possible for you to check if the crash is repeatable for you with the query, that is reported in the error log?
It looks like prepared statement was executed there. It would be great if you could add the test case to demonstrate the problem.

To try to reproduce the crash on my side I need also 

SHOW CREATE TABLE t_model_master; 
SHOW CREATE TABLE cm_code;
 
SHOW CREATE FUNCTION  QT_GET_DIVISION_BY_MODEL; 
SHOW CREATE FUNCTION  QT_GET_CORP_BY_ORG_ID ;
 
SHOW CREATE FUNCTION  QT_GET_AU_OF_CORP;
 
SHOW CREATE FUNCTION  QT_GET_NTH_FIELD ;
 
SHOW CREATE FUNCTION  QT_GET_MODEL_REFRIG_TYPE ;
 
SHOW CREATE FUNCTION  QT_GET_MODEL_REFRIG_QTY;

Comment by SKCHOI [ 2019-06-26 ]

Dear Alice,

I am sorry but I failed to make crash again because there is no error or crash occured after SQL executed.

(This SQL for update is executed by a part of stored procedure.)

So I post create statement what you requested.

=====================================================================
1. SHOW CREATE TABLE t_model_master;
CREATE TABLE "t_model_master" (
"MODEL_SUFFIX" varchar(60) COLLATE utf8_bin NOT NULL,
"MODEL_CODE" varchar(30) COLLATE utf8_bin NOT NULL,
"SUFFIX_CODE" varchar(20) COLLATE utf8_bin NOT NULL COMMENT,
"HQ_ACCOUNTING_UNIT_CODE" varchar(10) COLLATE utf8_bin DEFAULT NULL,
"DIVISION_CODE" varchar(10) COLLATE utf8_bin DEFAULT NULL,
"REFRIGERANTS_CODE" varchar(10) COLLATE utf8_bin DEFAULT NULL,
"REFRIGERANTS_QTY" decimal(10,3) DEFAULT NULL',
"PRODUCT_LEVEL1_CODE" varchar(2) COLLATE utf8_bin DEFAULT NULL,
"PRODUCT_LEVEL2_CODE" varchar(4) COLLATE utf8_bin DEFAULT NULL,
"PRODUCT_LEVEL3_CODE" varchar(6) COLLATE utf8_bin DEFAULT NULL,
"PRODUCT_LEVEL4_CODE" varchar(8) COLLATE utf8_bin DEFAULT NULL,
"CRT_DT" datetime NOT NULL,
"CRT_ID" varchar(50) COLLATE utf8_bin NOT NULL,
"UPDT_DT" datetime NOT NULL,
"UPDT_ID" varchar(50) COLLATE utf8_bin NOT NULL,
"LAST_REFR_TYPE_IF_DATE" datetime DEFAULT NULL,
"LAST_REFR_QTY_IF_DATE" datetime DEFAULT NULL,
UNIQUE KEY "UK01" ("MODEL_CODE","SUFFIX_CODE"),
UNIQUE KEY "UK02" ("MODEL_SUFFIX")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2. SHOW CREATE TABLE cm_code;
CREATE TABLE "cm_code" (
"GRP_CD" varchar(30) COLLATE utf8_bin NOT NULL,
"CD" varchar(30) COLLATE utf8_bin NOT NULL,
"CD_DESC" varchar(300) COLLATE utf8_bin NOT NULL,
"CAT_TYPE" varchar(30) COLLATE utf8_bin NOT NULL DEFAULT 'SYSTEM',
"COMP_CD" varchar(30) COLLATE utf8_bin NOT NULL DEFAULT '*',
"INV_ORG_ID" decimal(22,0) NOT NULL DEFAULT -1,
"PRNT_CD" varchar(30) COLLATE utf8_bin DEFAULT NULL,
"USE_FLAG" varchar(1) COLLATE utf8_bin DEFAULT NULL,
"DATA_TYPE_CD" varchar(1) COLLATE utf8_bin DEFAULT NULL,
"STRT_DATE" varchar(8) COLLATE utf8_bin NOT NULL,
"END_DATE" varchar(8) COLLATE utf8_bin NOT NULL,
"SORT_ODRG" decimal(10,0) DEFAULT NULL,
"CRT_ID" decimal(22,0) NOT NULL,
"CRT_DT" varchar(14) COLLATE utf8_bin NOT NULL,
"UPDT_ID" decimal(22,0) NOT NULL,
"UPDT_DT" varchar(14) COLLATE utf8_bin NOT NULL,
"FAC_TYPE" varchar(30) COLLATE utf8_bin DEFAULT NULL,
"ATTR1" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR2" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR3" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR4" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR5" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR6" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR7" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR8" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR9" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR10" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR11" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR12" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR13" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR14" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR15" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR16" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR17" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR18" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR19" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"ATTR20" varchar(300) COLLATE utf8_bin DEFAULT NULL,
"DEFAULT_YN" varchar(1) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY ("GRP_CD","CD","COMP_CD","INV_ORG_ID"),
KEY "cm_code_n1" ("USE_FLAG")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3. SHOW CREATE FUNCTION QT_GET_DIVISION_BY_MODEL;
CREATE DEFINER=`rqms_mgr`@`%` FUNCTION `QT_GET_DIVISION_BY_MODEL`(
`P_MODEL_NAME` VARCHAR(50)

) RETURNS varchar(20) CHARSET utf8 COLLATE utf8_bin
SQL SECURITY INVOKER
BEGIN
DECLARE ret VARCHAR(20);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET ret = null;
return ret;
END;

select DIVISION_CODE
INTO ret
from t_model_master mm
where mm.MODEL_SUFFIX = P_MODEL_NAME
;
return ret;
END

4. SHOW CREATE FUNCTION QT_GET_CORP_BY_ORG_ID ;
CREATE DEFINER=`rqms_mgr`@`%` FUNCTION `QT_GET_CORP_BY_ORG_ID`(
`P_OPERATION_UNIT_ID` VARCHAR(50)
) RETURNS varchar(10) CHARSET utf8 COLLATE utf8_bin
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE ret VARCHAR(20);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET ret = null;
return ret;
END;

SELECT CM.CD
INTO ret
FROM cm_code CM
where CM.GRP_CD = 'RQMS_SUBSIDIARY'
and cm.attr3 = P_OPERATION_UNIT_ID
;
return ret;
END

5. SHOW CREATE FUNCTION QT_GET_AU_OF_CORP;
CREATE DEFINER=`rqms_mgr`@`%` FUNCTION `QT_GET_AU_OF_CORP`(
`P_SUBSIDIARY_CODE` VARCHAR(50)
) RETURNS varchar(10) CHARSET utf8 COLLATE utf8_bin
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE ret VARCHAR(20);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET ret = null;
return ret;
END;

SELECT t.attr5
INTO ret
from cm_code t
where t.GRP_CD = 'RQMS_SUBSIDIARY'
– and t.USE_FLAG = 'Y'
and t.cd = P_SUBSIDIARY_CODE
;
return ret;
END

6. SHOW CREATE FUNCTION QT_GET_NTH_FIELD ;
CREATE DEFINER=`rqms_mgr`@`%` FUNCTION `QT_GET_NTH_FIELD`(
`P_IN_STR` VARCHAR(500),
`P_NTH` INT,
`P_SEPARATOR` VARCHAR(50)
) RETURNS varchar(300) CHARSET utf8 COLLATE utf8_bin
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE ret VARCHAR(500);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET ret = null;
return ret;
END;
SELECT REPLACE(REPLACE(SUBSTRING_INDEX(P_IN_STR,P_SEPARATOR,P_NTH), SUBSTRING_INDEX(P_IN_STR,P_SEPARATOR,P_NTH-1),''),P_SEPARATOR,'')
INTO ret;
RETURN ret;
END

7. SHOW CREATE FUNCTION QT_GET_MODEL_REFRIG_TYPE ;
CREATE DEFINER="rqms_mgr"@"%" FUNCTION "QT_GET_MODEL_REFRIG_TYPE"(
`P_MODEL_SUFFIX` VARCHAR(70)

) RETURNS varchar(100) CHARSET utf8 COLLATE utf8_bin
READS SQL DATA
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE ret VARCHAR(20);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET ret = null;
return ret;
END;

SELECT mm.REFRIGERANTS_CODE
INTO ret
FROM v_model_master mm
where mm.MODEL_SUFFIX = P_MODEL_SUFFIX
;
return ret;
END

8. SHOW CREATE FUNCTION QT_GET_MODEL_REFRIG_QTY;
CREATE DEFINER="rqms_mgr"@"%" FUNCTION "QT_GET_MODEL_REFRIG_QTY"(
`P_MODEL_SUFFIX` VARCHAR(70)

) RETURNS decimal(30,3)
READS SQL DATA
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE ret decimal(30,3);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET ret = null;
return ret;
END;

SELECT mm.REFRIGERANTS_QTY
INTO ret
FROM v_model_master mm
where mm.MODEL_SUFFIX = P_MODEL_SUFFIX
;
return ret;
END

I welcome any more request to solve this issue.

Thank you!

Comment by SKCHOI [ 2020-04-22 ]

Hello,

I am not sure the reason or result by this issue is, some update action was little strange.

update a set aa=1, ab=2, ab=3, .... where ...

Update action has been partially done(ex. set aa and ab not finished, but ac succeeded) and I had never met like this situation.

(I think partial update like this case is really Joke)

Does anyone have idea of this case?

Generated at Thu Feb 08 08:54:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.