Microsoft MVP성태의 닷넷 이야기
글쓴 사람
정성태 (techsharer at outlook.com)
홈페이지
첨부 파일
 

Windows - MySQL zip 파일 버전의 "mysqld --skip-grant-tables" 실행 시 비정상 종료

mysql의 zip 파일 버전을 다운로드해 압축을 풀고, "mysqld --initialize"를 실행 후 root 계정의 암호 변경을 위해 "start mysqld --skip-grant-tables" 명령어를 실행했더니, 그냥 새 창이 뜨고는 잠시 후에 없어져 버립니다. 이때 이벤트 로그를 보면 다음의 내용이 남는데요,

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: 

...[생략]...

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: 

한마디로 crash가 발생한 것입니다. "procdump -i" 명령어로 뜬 덤프 파일을 windbg로 검사해 보면 다음의 호출 스택을 볼 수 있습니다.

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::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

검색해 보면, 위의 내용으로 몇몇 글을 볼 수 있지만 사실상 해결책이 없습니다.




혹시나 환경을 타나 싶어서 Windows 10, Windows Server 2019, Windows Server 2012 R2에서 테스트했으나 증상이 동일했습니다. "mysqld --skip-grant-tables" 명령이 실행돼야 root 계정의 암호를 설정할 수 있는데 ^^; 저것부터 안 되니 "mysql -uroot" 명령어도 그냥 이렇게 오류가 발생합니다.

D:\mysql-8.0.25-winx64\bin> mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

막막하군요. ^^; 일단, "mysqld --console"을 실행하면 다음과 같이 잘 서비스가 됩니다.

D:\mysql-8.0.25-winx64\bin> mysqld --console 

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.

혹시나 싶어 --console 옵션과 함께 --skip-grant-tables 옵션을 주니, 화면에 로그가 출력이 됩니다.

D:\mysql-8.0.25-winx64\bin> mysqld --console --skip-grant-tables
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.

그래도 crash가 된다는 점에서는 변함이 없습니다. ^^; 그런데, 중간에 보면 다음과 같은 메시지가 있습니다.

[ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS

따라서, "--shared-memory" 옵션과 함께 사용하면,

D:\mysql-8.0.25-winx64\bin> mysqld --console --skip-grant-tables --shared-memory
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.

실행이 잘 되는군요. (여기서 좀 이해가 안 됩니다. 어차피 알고 있는 거면 애당초 Windows OS에서 실행되는 경우 --shared-memory를 스스로 적용하면 될 문제 아니었던가요? ^^;)

어쨌든 실행이 되니, 이 상태에서 mysql 연결도 정상적으로 되고 암호 변경도 잘 됩니다.

D:\mysql-8.0.25-winx64\bin> mysql -uroot
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> flush privileges;
Query OK, 0 rows affected (0.10 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Query OK, 0 rows affected (0.17 sec)

재미있는 것은 "flush privileges;"를 실행하지 않은 상태에서 명령어를 입력하면 이런 식으로 오류가 발생합니다.

// 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

도대체가 신비로운 mysql의 비밀번호 설정 방법이군요. ^^;




[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]

[연관 글]






[최초 등록일: ]
[최종 수정일: 7/17/2021]

Creative Commons License
이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.
by SeongTae Jeong, mailto:techsharer at outlook.com

비밀번호

댓글 작성자
 



2022-07-16 04시03분
[마린이] 와 저랑 똑같은 오류였는데 감사합니다!
[손님]

1  2  3  4  5  [6]  7  8  9  10  11  12  13  14  15  ...
NoWriterDateCnt.TitleFile(s)
12991정성태3/2/2022929.NET Framework: 1170. C# - ffmpeg(FFmpeg.AutoGen)를 이용한 transcode_aac.c 예제 포팅
12990정성태3/2/2022932오류 유형: 797. msbuild - The BaseOutputPath/OutputPath property is not set for project '[...].vcxproj'
12989정성태3/2/2022908오류 유형: 796. mstest.exe - System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.VisualStudio.QualityTools.Tips.WebLoadTest.Tip
12988정성태3/2/2022748오류 유형: 795. CI 환경에서 Docker build 시 csproj의 Link 파일에 대한 빌드 오류
12987정성태3/1/2022895.NET Framework: 1169. C# - ffmpeg(FFmpeg.AutoGen)를 이용한 demuxing_decoding.c 예제 포팅
12986정성태2/28/2022915.NET Framework: 1168. C# -IIncrementalGenerator를 적용한 Version 2 Source Generator 실습
12985정성태2/28/2022990.NET Framework: 1167. C# -Version 1 Source Generator 실습
12984정성태2/24/2022850.NET Framework: 1166. C# - ffmpeg(FFmpeg.AutoGen)를 이용한 filtering_video.c 예제 포팅
12983정성태2/24/2022839.NET Framework: 1165. .NET Core/5+ 빌드 시 runtimeconfig.json에 설정을 반영하는 방법
12982정성태2/24/2022817.NET Framework: 1164. HTTP Error 500.31 - ANCM Failed to Find Native Dependencies
12981정성태2/23/2022863VC++: 154. C/C++ 언어의 문자열 Literal에 인덱스 적용하는 구문 [1]
12980정성태2/23/20221085.NET Framework: 1163. C# - 윈도우 환경에서 usleep을 호출하는 방법 [2]
12979정성태2/22/20221344.NET Framework: 1162. C# - 인텔 CPU의 P-Core와 E-Core를 구분하는 방법파일 다운로드1
12978정성태2/21/2022827.NET Framework: 1161. C# - ffmpeg(FFmpeg.AutoGen)를 이용한 resampling_audio.c 예제 포팅
12977정성태2/21/20222065.NET Framework: 1160. C# - ffmpeg(FFmpeg.AutoGen)를 이용한 qsv 디코딩
12976정성태2/21/20221282VS.NET IDE: 174. Visual C++ - "External Dependencies" 노드 비활성화하는 방법
12975정성태2/20/20221134.NET Framework: 1159. C# - ffmpeg(FFmpeg.AutoGen)를 이용한 qsvdec.c 예제 포팅파일 다운로드1
12974정성태2/20/2022836.NET Framework: 1158. C# - SqlConnection의 최소 Pooling 수를 초과한 DB 연결은 언제 해제될까요?
12973정성태2/16/20221065개발 환경 구성: 639. ffmpeg.exe - Intel Quick Sync Video(qsv)를 이용한 인코딩 [3]
12972정성태2/16/2022891Windows: 200. Intel CPU의 내장 그래픽 GPU가 작업 관리자에 없다면? [4]
12971정성태2/15/20221798.NET Framework: 1157. C# - ffmpeg(FFmpeg.AutoGen)를 이용한 muxing.c 예제 포팅 [7]파일 다운로드1
12970정성태2/15/20221021.NET Framework: 1156. C# - ffmpeg(FFmpeg.AutoGen): Bitmap으로부터 h264 형식의 파일로 쓰기 [1]파일 다운로드1
12969정성태2/14/2022855개발 환경 구성: 638. Visual Studio의 Connection Manager 기능(Remote SSH 관리)을 위한 명령행 도구 - 두 번째 이야기파일 다운로드1
12968정성태2/14/2022630오류 유형: 794. msbuild 에러 - error NETSDK1005: Assets file '...\project.assets.json' doesn't have a target for '...'.
12967정성태2/14/2022841VC++: 153. Visual C++ - C99 표준의 Compund Literals 빌드 방법 [4]
12966정성태2/13/2022827.NET Framework: 1155. C# - ffmpeg(FFmpeg.AutoGen): Bitmap으로부터 yuv420p + rawvideo 형식의 파일로 쓰기파일 다운로드1
1  2  3  4  5  [6]  7  8  9  10  11  12  13  14  15  ...