Reputation: 5757

How can I solve "Error: MySQL shutdown unexpectedly"?

When I open XAMPP and click the start MySQL button, it gives me an error. I had started it just before, but now it isn't working.

12:19:12 PM [mysql] Attempting to start MySQL app...
12:19:12 PM [mysql] Status change detected: running
12:19:13 PM [mysql] Status change detected: stopped
12:19:13 PM [mysql] Error: MySQL shutdown unexpectedly.
12:19:13 PM [mysql] This may be due to a blocked port, missing dependencies,
12:19:13 PM [mysql] improper privileges, a crash, or a shutdown by another method
12:19:13 PM [mysql] Press the Logs button to view error logs and check
12:19:13 PM [mysql] the Windows Event Viewer for more clues
12:19:13 PM [mysql] If you need more help, copy and post this
12:19:13 PM [mysql] entire log window on the forums

Here is the contents of the error log:

2013-08-02 12:19:12 4536 [Note] Plugin 'FEDERATED' is disabled.
2013-08-02 12:19:12 f64 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-08-02 12:19:12 4536 [Note] InnoDB: The InnoDB memory heap is disabled
2013-08-02 12:19:12 4536 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-08-02 12:19:12 4536 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-08-02 12:19:12 4536 [Note] InnoDB: Not using CPU crc32 instructions
2013-08-02 12:19:12 4536 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2013-08-02 12:19:12 4536 [Note] InnoDB: Completed initialization of buffer pool
2013-08-02 12:19:12 4536 [Note] InnoDB: Highest supported file format is Barracuda.
2013-08-02 12:19:12 4536 [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1616798 in the ib_logfiles!
2013-08-02 12:19:12 4536 [Note] InnoDB: Database was not shutdown normally!
2013-08-02 12:19:12 4536 [Note] InnoDB: Starting crash recovery.
2013-08-02 12:19:12 4536 [Note] InnoDB: Reading tablespace information from the .ibd files...

The most important error message is here:

2013-08-02 12:19:12 4536 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: .\mysql\innodb_table_stats.ibd. Cannot open tablespace xat/payments which uses space ID: 1 at filepath: .\xat\payments.ibd

The rest of the log:

InnoDB: Error: could not open single-table tablespace file .\xat\payments.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

What is the cause of these errors and how can I rectify them?

Upvotes: 572

Answers (30)

Alohe

Reputation: 450

I had the same problem for a while. While most suggestions worked, I had to reinstall XAMPP and copy the files and table folders from xampp\mysql\data to the new installation.

However, I found a way easier fix.

I noticed the issue was linked to how I exited XAMPP. Shutting down my PC without stopping the services first caused problems.

When reinstalling XAMPP, only two files were present ibdata1 and my.ini before starting the service. After starting it, temp files were generated.

When the issue recurred a week or so later, I deleted all temp files except the original two ibdata1 and my.ini, and just like that XAMPP started working, without the need to restart and without copy and pasting any files.

Upvotes: 5

Karthik

Reputation: 41

In my case Mysql could not start due to the below issue (mysql_error.txt):

2024-03-27 19:42:10 0 [ERROR] mysqld.exe: Aria recovery failed. Please run aria_chk -r on all Aria tables and delete all aria_log.######## files 2024-03-27 19:42:10 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.

Solution:

Open Shell from xampp control panel.
run aria_chk -r
Del all aria_log.######## files

After following the above steps:

Upvotes: 2

ticky

Reputation: 383

Delete every file from folder c:\xampp\mysql\data, except my.ini.

Upvotes: 1

Ajith jojo

Reputation: 427

Find my.ini file inside xampp/mysql/bin, then find [mysqld] section and add this line after error log:

innodb_force_recovery = 6

Then start MYSQL, once it starts, you can remove the code from it and save it back to normal.

Upvotes: -3

Charles Thembo

Reputation: 636

Recovering a crashed Database

Instead, first try using the MySQL backup folder which is included with XAMPP. So do the next steps:

Rename folder mysql/data to MySQL/data_old

Make a copy of MySQL/backup folder and name it as MySQL/data

Copy all your database folders from MySQL/data_old into mysql/data (except MySQL, performance_schema, and phpmyadmin folders)

Copy MySQL/data_old/ibdata1 file into mysql/data folder

Start MySQL from the XAMPP control panel

Upvotes: 23

Nikunj

Reputation: 77

To answer to this error

This may be due to a blocked port, missing dependencies
improper privileges, a crash, or a shutdown by another method.
Press the Logs button to view error logs and check
the Windows Event Viewer for more clues
If you need more help, copy and post this
entire log window on the forums

In windows

Just simply open Task manager and open Services section and then start again the MYSQL server it'll work perfectly no need to do anything else.

This simple trick worked for me, I have done it more than 20 times.

Upvotes: 1

pmatatias

Reputation: 4404

I wrote this answer as reminder for me too.

Blocked Port: Ensure that port 3306 (MySQL's default port) is not being used by another application. netstat -ano | findstr :3306 in the command prompt (run as administrator) to check for any conflicts.

In my case, i have 2 running on 3306

to solve this: open taskmanager and search for mysqld

I have 2 running in the background. these the reaseon why i cant run MySQL from xampp. since its already running from other resources.

Solutions:

stopping the process directly from the Task Manager and then start MySQL from XAMPP.
end task in the taskmanager
start from xampp

solved.

Upvotes: -1

Pigeon DevPH

Reputation: 21

Go to mysql/data then rename(preferable) or delete mysql.pid.. then start mysql in xampp panel... works like charm

Upvotes: 0

Soubhagya Kumar Barik

Reputation: 2595

I have tried all of the previous answers, but it didn't work for me. So finally I tried the below approach and it worked 100% for me.

If you are worried about following the below steps, you can take a backup of the entire XAMPP folder for the safe side.

Step 1: Rename the c:\xampp\mysql\data folder to c:\xampp\mysql\data_bkp

Step 2: Create a new folder c:\xampp\mysql\data.

Step 3: Copy the content, that is present inside c:\xampp\mysql\backup to the new c:\xampp\mysql\data folder.

Step 4: Copy all your database folders c:\xampp\mysql\data_bkp to c:\xampp\mysql\data (Note: Don't copy mysql, performance_schema, and phpmyadmin folders).

Step 5: Copy the ibdata1 file from c:\xampp\mysql\data_bkp and replace it inside the c:\xampp\mysql\data folder.

Step 6: Start MySQL from the XAMPP control panel.

Upvotes: 24

Priyantha

Reputation: 5083

I also get the same issue. Solution was kill process Find the PID for port (3306)

netstat -a -n -o | find "3306"

You will see everything you need.

0.0.0.0:3306           0.0.0.0:0              LISTENING       8120   TCP
0.0.0.0:33060          0.0.0.0:0              LISTENING       8120   TCP
[::]:3306              [::]:0                 LISTENING  8120   TCP
[::]:33060             [::]:0                 LISTENING  8120

PID is 8120

Next run the following command

taskkill /PID 8120 /F

Start MySQL again. It will be fine.

Upvotes: 6

Brijesh Chauhan

Reputation: 111

Error: MySQL shutdown unexpectedly

This is the best answer

Rename the folder mysql/data to mysql/data_old (you can use any name)
Create a new folder mysql/data
Copy the content that resides in mysql/backup to the new mysql/data folder
Copy all your database folders that are in mysql/data_old to mysql/data (skipping the mysql, performance_schema, and phpmyadmin folders from data_old)
Finally copy the ibdata1 file from mysql/data_old and replace it inside mysql/data folder
Start MySQL from XAMPP control panel

Upvotes: 4

DevWL

Reputation: 18840

I had the same issue.

Step 1

backup folder xampp/mysql/data (saved it as `xampp/mysql/_old_data`).

Step 2

Go to xampp/mysql/data and deleted all the files but not folders! With databases.

Step 3

At this stage your database will not work. You need to go to your backup folder `xampp/mysql/_old_data` and copy over "`ibdata1`" file to `xampp/mysql/data`.

After that everything work as expected including phpmyadmin and console.

Step 4

If this does not help. You can remove xampp/mysql/data folder and rename xampp/mysql/_old_data back to xampp/mysql/data. (ROLLBACK) and try different things.

Upvotes: 6

mir pekiro

Reputation: 105

Go to: C: C:\xampp\mysql

Rename the folder "data" to "data_old" (you can use any name).
Create a new folder "data".
Copy the content of "backup" folder to the new "data" folder.
Copy all content of "data_old" to "data" (skip those folder "mysql", "performance_schema", and "phpmyadmin") *** without remplacing the file in the destination (skip theses files)**
restart XAMPP
And it will work

Upvotes: 4

Rohit Saini

Reputation: 987

Working solution: Follow the below listed steps

Step 1. Rename the folder C:\xampp\mysql\data to C:\xampp\mysql\data_bkp (you can use any name).

Step 2. Create a new folder, C:\xampp\mysql\data.

Step 3. Copy the content that resides in mysql\backup to the new mysql\data folder.

Step 4. Copy all your database folders that are in mysql\data_bkp to mysql\data (skipping the mysql, performance_schema, and phpmyadmin folders from mysql\data_bkp).

Important note: Please do not replace the existing files while pasting (click skip for these files)

Step 5. Finally copy the ibdata1 file from mysql\data_bkp and replace it inside the mysql\data folder.

Step 6. Start MySQL from XAMPP control panel.

And, it's done. No databases were lost, no ports changed, no run as administrator, no force recovery, no kill mysqld process, no restoring from previous versions, and no more errors.

Upvotes: 82

Leonardo Cerchioni

Reputation: 17

I use this command thought the shell of xampp:

mysqld --console --skip-grant-tables --skip-external-locking

Upvotes: 1

saber tabatabaee yazdi

Reputation: 4959

This PowerShell script automated the solution for you:

Rename-Item -Path "./data" -NewName "./data_old"

# Create new data directory
Copy-Item -Path "./backup" -Destination "./data" -Recurse
Remove-Item "./data/test" -Recurse
$dbPaths = Get-ChildItem -Path "./data_old" -Exclude ('mysql', 'performance_schema', 'phpmyadmin') -Recurse -Directory
Copy-Item -Path $dbPaths.FullName -Destination "./data" -Recurse
Copy-Item -Path "./data_old/ibdata1" -Destination "./data/ibdata1"

# Notify user
Write-Host "Finished repairing MySQL data"
Write-Host "Previous data is located at ./data_old"

https://gist.github.com/josemmo/24e35f2b4984a4370ce2c164f5956437

Upvotes: -1

Unique631

Reputation: 101

I open and empty the file multi-master.info which exists in the data folder. This worked for me.

Upvotes: 5

Yasir Ijaz

Reputation: 1043

Simple solution: Open the shell from the XAMPP Control Panel:

mysqld --console --skip-grant-tables --skip-external-locking

Then again open another shell and run

mysqlcheck -r --databases mysql --use-frm

Now close both shells and restart the XAMPP.

Upvotes: 50

Sahib Khan

Reputation: 602

I had the same problem.

My XAMPP control panel will start Apache, but not MySQL. The problem is in iblogfile. This happens when you are running xampp/mysql and your PC restarts without properly shutting down the InnoDB engine. Go to xampp/mysql and delete:

ib_logfile0

and

ib_logfile1

Now restart MySQL and it should work.

Upvotes: 2

Thirosh Madhusha

Reputation: 275

Step 01: If you have an error with starting XAMPP MySQL, follow these steps to easily solve this problem.

Step 02: Stop the Server and go to Config.

Step 03: Open this my.ini file.

Step 04: Find these two port numbers in your notepad text.

Step 05: Change these two port numbers, 3306 → 3308 or any other number.

After saving the text file, using Ctrl + S

Step 07: Go to your XAMPP installation folder and open the XAMPP folder.

Step 08: Find the phpMyAdmin folder.

Step 09: Open the phpMyAdmin folder and find the config.inc.php file.

Step 10: Change the port number here that previous you changed in Step 05. (Like 3008) and Save this text file.

Step 11: After closing all files and going to your XAMPP server.

Step 12: Start the MySQL server again.

Step 13: Open your web browser and type in the URL → localhost

Step 14: Go to PhpMyAdmin

Step 15: phpMyAdmin is working properly now.

If you have any error in starting the Apache server,

Step 01: Stop the server, and open the configuration file.

Step 02: Find this Listen number in your text file.

Step 03: Change that 80 to 8000 or any other value. And save the text file.

Step 04: After saving the text file, open your XAMPP server and Start the Apache server again.

Step 05: After opening your web browser.

Step 06: Type localhost:8000

Step 07: Now you can open your phpMyAdmin.

Note: After changing the Apache port number, you must add in to that after the localhost: in your URL.

Upvotes: 2

Akram Elhaddad

Reputation: 314

Remember: Never delete this file (ibdata1), because all your data will be deleted

stop all running XAMPP services (Apache, MySQL, etc.)
rename the xampp folder to xampp-old
install a fresh XAMPP
Restore databases by copying these three files (ibdata1, ib_logfile0, and ib_logfile1), and your database folders you created from xampp-old from this path:
```
xampp-old\mysql\data
```
to
```
xampp\mysql\data
```
and accept to replace files
Restore the coded files by transferring xampp-old\htdocs to xampp\htdocs

Now start XAMPP. It will work with all of your data.

Note: if you need to edit the Apache or MySQL .ini files, do this again

Upvotes: 0

A.Jain

Reputation: 80

Follow these steps:-

Go into C:\xampp\mysql
Rename data folder by data_old
Create a new folder empty data folder
Copy all files from data_old and paste them into the data folder
Copy all files from the backup folder and paste them in the data folder and replace all files
Copy ibdata1 files from data_old and paste and replace them in the data folder.
Restart Server

Upvotes: 4

csgeek

Reputation: 801

I faced the same issue. MySQL stops as soon as I turn it on.

In the logs it said:-

10:31:21 [mysql] Problem detected! 10:31:21 [mysql] Port 3306 in use by "Unable to open process"! 10:31:21 [mysql] MySQL WILL NOT start without the configured ports free!
10:31:21 [mysql] You need to uninstall/disable/reconfigure the blocking application
10:31:21 [mysql] or reconfigure MySQL and the Control Panel to listen on a different port

I stopped the running service on port 3306 wamp(in my case), and it worked fine after that. Changing the port in config settings should also work.

Upvotes: 1

Boston Kenne

Reputation: 818

i comment this statement in mysql/bin/my.ini

'innodb_additional_mem_pool_size=2M'

and it solve my problem.

Upvotes: 1

insoftservice

Reputation: 840

Rename below files from mysql/data ib_logfile0 ib_logfile1 ibdata1

my.cnf innodb_buffer_pool_size to 200M as per your ram innodb_log_buffer_size to 32M

Restart your apache server

Upvotes: 0

Rasul Ebrahimzadeh

Reputation: 306

Never delete this file (ibdata1), because all your data will be deleted!!!

I suggest three ways:

Exit from the XAMPP control panel.
Rename the folder mysql/data to mysql/data_old (you can use any name)
Create a new folder, mysql/data
Copy the content that resides in mysql/backup to the new mysql/data folder
Copy all your database folders that are in mysql/data_old to mysql/data (skipping the mysql, performance_schema, and phpmyadmin folders from data_old)
Finally copy the ibdata1 file from mysql/data_old and replace it inside mysql/data folder
Restart your system.