Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19844

(Reopen) Unexpected crash occured repeatedly

Details

    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!

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment - - edited

            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)

            alice Alice Sherepa added a comment - - edited 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)
            csk6210 SKCHOI added a comment -

            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
              1. 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!

            csk6210 SKCHOI added a comment - 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 Replication set ##Master #server-id=1 Slave #server-id=2 #relay-log=/logs001/masvc01/RQMSP/relay/relay-log #read_only=1 #log_slow_slave_statements=1 #slave_parallel_threads=8 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 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' 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 Connection wait_timeout=28800 lock_wait_timeout=31536000 connect_timeout=10 table_open_cache=2000 table-definition-cache=2000 MyISAM Specific options 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 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!
            alice Alice Sherepa added a comment -

            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;
            

            alice Alice Sherepa added a comment - 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;
            csk6210 SKCHOI added a comment -

            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!

            csk6210 SKCHOI added a comment - 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!
            csk6210 SKCHOI added a comment -

            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?

            csk6210 SKCHOI added a comment - 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?

            People

              Unassigned Unassigned
              csk6210 SKCHOI
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.