Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.24
-
None
-
None
-
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
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
- relates to
-
-
- Closed
-
Activity
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
- Replication set
- 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
- 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'
- Character set
-
-
-
- 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
- Common set
-
-
-
- Connection
wait_timeout=28800
lock_wait_timeout=31536000
connect_timeout=10
table_open_cache=2000
table-definition-cache=2000
- Connection
-
-
-
- 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 Specific options
-
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
- Audit set
-
[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!
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;
|
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!
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?
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)