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
  • 11

3 Answers3

15

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
  • 510
  • 4
  • 18
  • 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
  • Just wanted to let you know that these two commands were successful for me. `mysqlcheck` fixed lots of wrong CRCs on the `mysql.db` datapages. – abaumg Feb 08 '23 at 15:13
1

I saw this answer in an unclear form here on stackOverflow which helped me, hope this answer helps too

1 - open the directory "c:/xampp/mysql/data" and remove the folder "mysql"

2 - now you will copy the "mysql" folder from "c:/xampp/mysql/backup" and then paste it in the "c:/xampp/mysql/data" directory

Hope this answer helps!

Gladson Simões
  • 11
  • 2
  • This will only help if the problem is in the mysql database only. If other databases are corrupt it won't. – theking2 Feb 15 '23 at 07:31
0

As XAMPP forcefully shutsdown the database process sooner or later this is bound to happen.

Considering you are running windows (from the \ I deduce you are) there are a couple of steps you could try to repair this.

  1. rename c:\xampp\mysql\data to something else (data_YYMMDD)
  2. copy c:\xampp\mysql\backup to c:\xampp\mysql\data
  3. try to see of MariaDB starts (it should) and stop it.
  4. copy over InnoDB folders from c:\xampp\mysql\data_YYMMDD to c:\xampp\mysql\data
  5. run c:\xampp\mysql\mysqlcheck.exe --auto-repair --all-databases -uroot

Btw. any user accounts you created will be lost and will have to be recreated.

theking2
  • 2,174
  • 1
  • 27
  • 36
  • *"As XAMPP forcefully shutsdown the database process sooner or later this is bound to happen.*" - Out of curiosity, because I didn't know about that: Is this a known fact? Could you provide a source for this statement? – abaumg Feb 08 '23 at 15:17
  • 1
    The code is avaible as batch shell commands. It locates the process ID and performs a taskkill. Not something I would do to a running database process – theking2 Feb 09 '23 at 19:08
  • Thanks for pointing this out. So, XAMPP is indeed using `taskkill /F` (=forced) to stop the MySQL process... WOW. For the records: the above mentioned batch files are `mysql_stop.bat` and `killprocess.bat` in the XAMPP root folder. – abaumg Feb 10 '23 at 09:02