성태의 닷넷 이야기
홈 주인
모아 놓은 자료
프로그래밍
질문/답변
사용자 관리
사용자
메뉴
아티클
외부 아티클
유용한 코드
온라인 기능
MathJax 입력기
최근 덧글
[정성태] Java - How to use the Foreign Funct...
[정성태] 제가 큰 실수를 했군요. ^^; Delegate를 통한 Bein...
[정성태] Working with Rust Libraries from C#...
[정성태] Detecting blocking calls using asyn...
[정성태] 아쉽게도, 커뮤니티는 아니고 개인 블로그입니다. ^^
[정성태] 질문이 잘 이해가 안 됩니다. 우선, 해당 소스코드에서 ILis...
[양승조
] var대신 dinamic으로 선언해서 해결은 했습니다. 맞는 해...
[양승조
] 또 막혔습니다. ㅠㅠ var list = props[i].Ge...
[양승조
] 아. 감사합니다. 어제는 안됐던것 같은데....정신을 차려야겠네...
[정성태] "props[i].GetValue(props[i])" 코드에서 ...
글쓰기
제목
이름
암호
전자우편
HTML
홈페이지
유형
제니퍼 .NET
닷넷
COM 개체 관련
스크립트
VC++
VS.NET IDE
Windows
Team Foundation Server
디버깅 기술
오류 유형
개발 환경 구성
웹
기타
Linux
Java
DDK
Math
Phone
Graphics
사물인터넷
부모글 보이기/감추기
내용
<div style='display: inline'> <h1 style='font-family: Malgun Gothic, Consolas; font-size: 20pt; color: #006699; text-align: center; font-weight: bold'>Windows - MySQL zip 파일 버전의 "mysqld --skip-grant-tables" 실행 시 비정상 종료</h1> <p> mysql의 zip 파일 버전을 다운로드해 압축을 풀고, "mysqld --initialize"를 실행 후 root 계정의 암호 변경을 위해 "start mysqld --skip-grant-tables" 명령어를 실행했더니, 그냥 새 창이 뜨고는 잠시 후에 없어져 버립니다. 이때 이벤트 로그를 보면 다음의 내용이 남는데요,<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > Log Name: Application Source: Windows Error Reporting Date: 2021-07-10 오후 8:05:16 Event ID: 1001 Task Category: None Level: Information Keywords: Classic User: N/A Computer: TESTPC Description: Fault bucket 2155725182767660306, type 4 Event Name: APPCRASH Response: Not available Cab Id: 0 Problem signature: P1: mysqld.exe P2: 8.0.25.0 P3: 6082efcd P4: mysqld.exe P5: 8.0.25.0 P6: 6082efcd P7: 80000003 P8: 0000000001091d62 P9: P10: ...[생략]... </pre> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > Log Name: Application Source: Application Error Date: 2021-07-10 오후 8:05:13 Event ID: 1000 Task Category: (100) Level: Error Keywords: Classic User: N/A Computer: TESTPC Description: Faulting application name: mysqld.exe, version: 8.0.25.0, time stamp: 0x6082efcd Faulting module name: mysqld.exe, version: 8.0.25.0, time stamp: 0x6082efcd Exception code: 0x80000003 Fault offset: 0x0000000001091d62 Faulting process id: 0x27d4 Faulting application start time: 0x01d77a326db8d323 Faulting application path: D:\mysql-8.0.25-winx64\bin\mysqld.exe Faulting module path: D:\mysql-8.0.25-winx64\bin\mysqld.exe Report Id: 99070596-edff-4556-b746-5c00773b64fd Faulting package full name: Faulting package-relative application ID: </pre> <br /> 한마디로 crash가 발생한 것입니다. "<a target='tab' href='https://www.sysnet.pe.kr/2/0/12051'>procdump -i</a>" 명령어로 뜬 덤프 파일을 windbg로 검사해 보면 다음의 호출 스택을 볼 수 있습니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > mysqld!my_errno+0x22 ucrtbase!raise+0x1e1 ucrtbase!abort+0x31 mysqld!binary_log::transaction::compression::Zstd_comp::set_compression_level+0x1f9c82 mysqld!binary_log::transaction::compression::Zstd_comp::set_compression_level+0x1f7a33 mysqld!binary_log::transaction::compression::Zstd_comp::set_compression_level+0x15e48f mysqld!binary_log::transaction::compression::Zstd_comp::set_compression_level+0xb5d94 mysqld!ha_finalize_handlerton+0x47 mysqld!memcached_shutdown+0x1d5a mysqld!Prealloced_array<st_plugin_int * __ptr64,16>::push_back+0x352 mysqld!plugin_shutdown+0xd8 mysqld!Item_func::check_valid_arguments_processor+0x321 mysqld!LogEvent::type+0x236 mysqld!setup_conn_event_handler_threads+0x755 mysqld!win_main+0x1af2 mysqld!mysql_service+0xa5 mysqld!mysqld_main+0x5cc mysqld!set_timespec_nsec+0x6bf88 kernel32!BaseThreadInitThunk+0x14 ntdll!RtlUserThreadStart+0x21 </pre> <br /> 검색해 보면, 위의 내용으로 몇몇 글을 볼 수 있지만 사실상 해결책이 없습니다.<br /> <br /> <hr style='width: 50%' /><br /> <br /> 혹시나 환경을 타나 싶어서 Windows 10, Windows Server 2019, Windows Server 2012 R2에서 테스트했으나 증상이 동일했습니다. "mysqld --skip-grant-tables" 명령이 실행돼야 root 계정의 암호를 설정할 수 있는데 ^^; 저것부터 안 되니 "mysql -uroot" 명령어도 그냥 이렇게 오류가 발생합니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > D:\mysql-8.0.25-winx64\bin> <span style='color: blue; font-weight: bold'>mysql -uroot</span> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) </pre> <br /> 막막하군요. ^^; 일단, "mysqld --console"을 실행하면 다음과 같이 잘 서비스가 됩니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > D:\mysql-8.0.25-winx64\bin> <span style='color: blue; font-weight: bold'>mysqld --console</span> 2021-07-10T14:32:44.652882Z 0 [System] [MY-010116] [Server] D:\mysql-8.0.25-winx64\bin\mysqld.exe (mysqld 8.0.25) starting as process 880 2021-07-10T14:32:44.763559Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-07-10T14:32:46.796149Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2021-07-10T14:32:48.098412Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060 2021-07-10T14:32:48.681719Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2021-07-10T14:32:48.690977Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2021-07-10T14:32:49.038021Z 0 [System] [MY-010931] [Server] D:\mysql-8.0.25-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.25' socket: '' port: 3306 MySQL Community Server - GPL. </pre> <br /> 혹시나 싶어 --console 옵션과 함께 --skip-grant-tables 옵션을 주니, 화면에 로그가 출력이 됩니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > D:\mysql-8.0.25-winx64\bin> <span style='color: blue; font-weight: bold'>mysqld --console --skip-grant-tables</span> 2021-07-10T14:27:37.440754Z 0 [System] [MY-010116] [Server] D:\mysql-8.0.25-winx64\bin\mysqld.exe (mysqld 8.0.25) starting as process 3336 2021-07-10T14:27:37.563356Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-07-10T14:27:41.256156Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2021-07-10T14:27:42.170809Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible' 2021-07-10T14:27:43.651229Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2021-07-10T14:27:43.656797Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2021-07-10T14:27:43.747028Z 0 [System] [MY-010931] [Server] D:\mysql-8.0.25-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.25' socket: '' port: 0 MySQL Community Server - GPL. 2021-07-10T14:27:43.756320Z 0 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS 2021-07-10T14:27:43.762431Z 0 [ERROR] [MY-010119] [Server] Aborting 2021-07-10T14:27:45.720592Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: trx0sys.cc:585:UT_LIST_GET_LEN(trx_sys->mysql_trx_list) == 0 thread 8488 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 14:27:46 UTC - mysqld got exception 0x80000003 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. Thread pointer: 0x0 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... 2021-07-10T14:28:37.982684Z 0 [System] [MY-013105] [Server] D:\mysql-8.0.25-winx64\bin\mysqld.exe: Normal shutdown. 7ff7c5771d62 mysqld.exe!my_sigabrt_handler()[my_thr_init.cc:372] 7ffdc2d6d167 ucrtbase.DLL!raise() 7ffdc2d6dff1 ucrtbase.DLL!abort() 7ff7c5999b42 mysqld.exe!ut_dbg_assertion_failed()[ut0dbg.cc:99] 7ff7c59978f3 mysqld.exe!trx_sys_close()[trx0sys.cc:585] 7ff7c58fe34f mysqld.exe!srv_shutdown()[srv0start.cc:3648] 7ff7c5855c54 mysqld.exe!innodb_shutdown()[ha_innodb.cc:1410] 7ff7c474ff47 mysqld.exe!ha_finalize_handlerton()[handler.cc:724] 7ff7c477c8aa mysqld.exe!plugin_deinitialize()[sql_plugin.cc:1116] 7ff7c4781f42 mysqld.exe!reap_plugins()[sql_plugin.cc:1187] 7ff7c4780c18 mysqld.exe!plugin_shutdown()[sql_plugin.cc:2034] 7ff7c471e8f1 mysqld.exe!clean_up()[mysqld.cc:2504] 7ff7c4730f76 mysqld.exe!unireg_abort()[mysqld.cc:2372] 7ff7c472fb85 mysqld.exe!setup_conn_event_handler_threads()[mysqld.cc:3199] 7ff7c4732d62 mysqld.exe!win_main()[mysqld.cc:7591] 7ff7c472caa5 mysqld.exe!mysql_service()[mysqld.cc:7687] 7ff7c472d0ec mysqld.exe!mysqld_main()[mysqld.cc:7903] 7ff7c5da3078 mysqld.exe!__scrt_common_main_seh()[exe_common.inl:288] 7ffde7cf1412 KERNEL32.DLL!BaseThreadInitThunk() 7ffdea2c54f4 ntdll.dll!RtlUserThreadStart() The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. </pre> <br /> 그래도 crash가 된다는 점에서는 변함이 없습니다. ^^; 그런데, 중간에 보면 다음과 같은 메시지가 있습니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS </pre> <br /> 따라서, "--shared-memory" 옵션과 함께 사용하면,<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > D:\mysql-8.0.25-winx64\bin> <span style='color: blue; font-weight: bold'>mysqld --console --skip-grant-tables --shared-memory</span> 2021-07-10T14:39:33.422973Z 0 [System] [MY-010116] [Server] D:\mysql-8.0.25-winx64\bin\mysqld.exe (mysqld 8.0.25) starting as process 4920 2021-07-10T14:39:33.482257Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2021-07-10T14:39:37.529997Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2021-07-10T14:39:39.063351Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible' 2021-07-10T14:39:40.103310Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2021-07-10T14:39:40.111012Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2021-07-10T14:39:40.316717Z 0 [System] [MY-010931] [Server] D:\mysql-8.0.25-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.25' socket: '' port: 0 MySQL Community Server - GPL. </pre> <br /> 실행이 잘 되는군요. (여기서 좀 이해가 안 됩니다. 어차피 알고 있는 거면 애당초 Windows OS에서 실행되는 경우 --shared-memory를 스스로 적용하면 될 문제 아니었던가요? ^^;)<br /> <br /> 어쨌든 실행이 되니, 이 상태에서 mysql 연결도 정상적으로 되고 암호 변경도 잘 됩니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > D:\mysql-8.0.25-winx64\bin> <span style='color: blue; font-weight: bold'>mysql -uroot</span> Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> <span style='color: blue; font-weight: bold'>flush privileges;</span> Query OK, 0 rows affected (0.10 sec) mysql> <span style='color: blue; font-weight: bold'>ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';</span> Query OK, 0 rows affected (0.17 sec) </pre> <br /> 재미있는 것은 "flush privileges;"를 실행하지 않은 상태에서 명령어를 입력하면 이런 식으로 오류가 발생합니다.<br /> <br /> <pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' > // https://stackoverflow.com/questions/36099028/error-1064-42000-you-have-an-error-in-your-sql-syntax-want-to-configure-a-pa mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('new_password')' at line 1 mysql> SET PASSWORD FOR 'root' = PASSWORD('new_password'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('new_password')' at line 1 mysql> UPDATE user SET authentication_string=password('new_password') WHERE user='root'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('new_password') WHERE user='root'' at line 1 mysql> UPDATE user SET password=password('new_password') WHERE user='root'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('new_password') WHERE user='root'' at line 1 mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement </pre> <br /> 도대체가 신비로운 mysql의 비밀번호 설정 방법이군요. ^^;<br /> </p><br /> <br /><hr /><span style='color: Maroon'>[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]</span> </div>
첨부파일
스팸 방지용 인증 번호
1088
(왼쪽의 숫자를 입력해야 합니다.)