TransWikia.com

Cannot access MYSQL safe mode using skip-grant-tables on Windows

Database Administrators Asked by Augustus Buckman on October 28, 2021

I am running a MySQL server 8.0.21 on a Windows Server system.

Recently I ran into an issue where my root user lost all its privileges.
I am trying to gain access to the MySQL server using:

    mysqld --skip-grant-tables --skip-networking --enable-named-pipe

First I stopped the MySQL service through the services window, but after I ran the command, nothing happened. The MySQL service doesn’t start up.

Later I accessed the my.ini file and placed skip-grant-tables after the [mysqld] line.

Then I started my service. I noticed that the service starts and then stops immediately which does not allow me connect to the MySQL server.

I need help in granting all privileges to my root user.
What am I doing wrong?

my my.ini file

port=3306

[mysql]
no-beep=

# default-character-set=

# SERVER SECTION
# ----------------------------------------------------------------------
# 
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.=
# 
# server_type=2
[mysqld]

skip-grant-tables
# The next three options are mutually exclusive to SERVER_PORT below.
#skip-networking
#enable-named-pipe
# shared-memory=

# shared-memory-base-name=MYSQL

# The Pipe the MySQL Server will use
# socket=MYSQL

# The TCP/IP Port the MySQL Server will listen on
port=3306 

err file

2020-07-20T12:38:19.931040Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2020-07-20T12:38:19.937256Z 0 [System] [MY-010116] [Server] C:Program FilesMySQLMySQL Server 8.0binmysqld.exe (mysqld 8.0.21) starting as process 1680
2020-07-20T12:38:19.992428Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-07-20T12:38:21.120912Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-07-20T12:38:21.737782Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'
2020-07-20T12:38:21.912489Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-07-20T12:38:21.914252Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-07-20T12:38:21.977620Z 0 [System] [MY-010931] [Server] C:Program FilesMySQLMySQL Server 8.0binmysqld.exe: ready for connections. Version: '8.0.21'  socket: ''  port: 0  MySQL Community Server - GPL.
2020-07-20T12:38:21.980334Z 0 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS
2020-07-20T12:38:21.981840Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-07-20T12:38:22.878424Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: trx0sys.cc:636:UT_LIST_GET_LEN(trx_sys->mysql_trx_list) == 0 thread 7744
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.
12:38:22 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...
7ff737347402    mysqld.exe!?my_errno@@YAHXZ()
7ff8a7f3da2d    ucrtbase.dll!raise()
7ff8a7f3e901    ucrtbase.dll!abort()
7ff73755fd80    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff7374501dd    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff7374e434e    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff7374e3a24    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff737429e0e    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff7364a4a67    mysqld.exe!?ha_finalize_handlerton@@YAHPEAUst_plugin_int@@@Z()
7ff7364c2cea    mysqld.exe!?memcached_shutdown@@YAXXZ()
7ff7364c8332    mysqld.exe!?push_back@?$Prealloced_array@PEAUst_plugin_int@@$0BA@@@QEAA_NAEBQEAUst_plugin_int@@@Z()
7ff7364c7041    mysqld.exe!?plugin_shutdown@@YAXXZ()
7ff736467fea    mysqld.exe!?check_valid_arguments_processor@Item_func@@UEAA_NPEAE@Z()
7ff7364792f6    mysqld.exe!?type@LogEvent@@QEAAAEAV1@W4enum_log_type@@@Z()
7ff736478534    mysqld.exe!?setup_conn_event_handler_threads@@YAXXZ()
7ff73647b298    mysqld.exe!?win_main@@YAHHPEAPEAD@Z()
7ff736475775    mysqld.exe!?mysql_service@@YAHPEAX@Z()
7ff736475db1    mysqld.exe!?mysqld_main@@YAHHPEAPEAD@Z()
7ff737949e08    mysqld.exe!?set_timespec_nsec@@YAXPEAUtimespec@@_K@Z()
7ff8a8f77974    KERNEL32.DLL!BaseThreadInitThunk()
7ff8abc5a261    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.

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP