3

I have been researching this issue for 2 days now and all the questions and answers do not solve my problem.

Found! in the mysql_error.log after running it several times:

2020-01-06 16:34:11 0 [ERROR] mysqld.exe: Table '.\mysql\user' is marked as crashed and last (automatic?) repair failed

2020-01-06 16:34:11 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table '.\mysql\user' is marked as crashed and last (automatic?) repair failed

So now I see the issue after numerous times trying to start MySQL and reviewing the log, this time I finally see the "user" table is corrupt.

Can anyone advise how to repair this?

Community
  • 1
  • 1
JRse
  • 93
  • 2
  • 9

1 Answers1

11

First, from XAMPP control panel, open "Config" [mysql] and add the following line under [mysqld] = innodb_force_recovery = 1

then do the following:

Open shell from control panel and start mysql with this command:

mysqld –-console –-skip-grant-tables –-skip-external-locking
Open another shell from control panel and repair database with this command:
mysqlcheck -r --databases mysql --use-frm
Stop mysql, close shells and restart mysql normally.

IF it doesn't work then change innodb_force_recovery = 2 and repeat process, and again if that doesn't work, change to 3, etc... up to 4

You can find this answer useful

Ashish Mishra
  • 430
  • 2
  • 16
  • after running the first line in directory where corrupt table is, it ran and at the end of the run it says: [ERROR] mysqld: Table '.\mysql\user' is marked as crashed and last (automatic?) repair failed - should I have run this in \mysql directory? – JRse Jan 07 '20 at 15:37
  • Hi @JRse, make sure you Stop the MySQL daemon/service by executing the command- `service mysql stop`. then run – `myisamchk -r table_name`. you can follow the link [link] (https://www.stellarinfo.com/blog/mysql-table-is-marked-as-crashed-and-last-repair-failed/). – Ashish Mishra Jan 07 '20 at 16:17
  • It gave me an error when I ran myisamchk -r user..... says File 'user' doesn't exist?? I ran it from dir xampp\mysql. my database is InnoDB could this be why? – JRse Jan 07 '20 at 16:50
  • I went to the site [link you provided] and tried to run some of the commands and it is telling me I do not have permission to use *.MYI? I am at the command prompt, any idea on how to get permission? – JRse Jan 07 '20 at 18:10
  • @Lelio I ran mysqld –-console –-skip-grant-tables –-skip-external-locking and seemed to work, but was unable to do anything at the prompt after that. So I started another shell [command prompt] and ran mysqlcheck –repair mysql user... it returned the following: Failed to SHOW CREATE TABLE 'user' Error: Table '.\mysql\user' is marked as crashed and last (automatic?) repair failed – JRse Jan 07 '20 at 18:45
  • OK... just now looked at XAMPP Control Panel and see MySQL running??? not sure what made that happen.... wow, interesting. I did start it earlier as Admin but did not see it running then. I'm thinking it happened after following the link you provided and ran a few things... wish I had looked up after executing those. I will try to bring up my program to see if all is OK. – JRse Jan 07 '20 at 18:51
  • I pulled this from the XAMPP Control Panel: 10:22:19 AM [mysql] Status change detected: running THIS last line shows the time it came back up – JRse Jan 07 '20 at 18:54
  • OK.. the minute I tried run my program on "Brackets" it failed again and is shutting down again.. – JRse Jan 07 '20 at 18:58
  • @Lelio I ran at the command prompt again: mysqld –-console –-skip-grant-tables –-skip-external-locking, and watched the XAMPP Control Panel, this is what started MySQL. It is running again. But in the log file on one of the lines it shows: 2020-01-07 10:57:45 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=184980007. But the rest of the log does not show anything bad or failing. – JRse Jan 07 '20 at 19:08
  • 1
    OK, I tried to run another program and also phpmyadmin and get the folowing error: This site can’t be reachedlocalhost refused to connect. Search Google for localhost phpmyadmin ERR_CONNECTION_REFUSED. – JRse Jan 07 '20 at 19:16
  • Edit your /etc/my.cnf and add: innodb_force_recovery = 1 . Try [link] (https://serverfault.com/questions/592793/mysql-crashed-and-wont-start-up#). – Ashish Mishra Jan 07 '20 at 22:07
  • @JRse Also see mysql_install_db [options]. Do u have some backup?. – Ashish Mishra Jan 07 '20 at 22:29
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/205562/discussion-between-ashish-mishra-and-jrse). – Ashish Mishra Jan 07 '20 at 22:31