Physically writing the file full - mysql Error

Question

I am using xampp , everytime I start mysql , I get following error in xampp

Error: MySQL shutdown unexpectedly.
13:16:14  [mysql]   This may be due to a blocked port, missing dependencies, 
13:16:14  [mysql]   improper privileges, a crash, or a shutdown by another method.
13:16:14  [mysql]   Press the Logs button to view error logs and check

I checked the error logs and below is the output , I even changed port , but that is not helping.Previously I deleted ibdata by seeing some other stackoverflow answer and whole database was lost , so dont want to do that mistake.Any guide to safely fix this error ?

2019-08-07 13:16:01 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2019-08-07 13:16:01 0 [Note] InnoDB: Uses event mutexes
2019-08-07 13:16:01 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-08-07 13:16:01 0 [Note] InnoDB: Number of pools: 1
2019-08-07 13:16:01 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-08-07 13:16:01 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2019-08-07 13:16:01 0 [Note] InnoDB: Completed initialization of buffer pool
2019-08-07 13:16:01 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-08-07 13:16:01 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-08-07 13:16:01 0 [Note] InnoDB: Setting file 'D:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-08-07 13:16:01 0 [Note] InnoDB: File 'D:\xampp\mysql\data\ibtmp1' size is now 12 MB.
2019-08-07 13:16:01 0 [Note] InnoDB: Waiting for purge to start
2019-08-07 13:16:01 0 [Note] InnoDB: 10.3.16 started; log sequence number 1951328; transaction id 1068
2019-08-07 13:16:01 0 [Note] InnoDB: Loading buffer pool(s) from D:\xampp\mysql\data\ib_buffer_pool
2019-08-07 13:16:01 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-08-07 13:16:01 0 [Note] Server socket created on IP: '::'.
2019-08-07 13:16:01 0 [Note] InnoDB: Buffer pool(s) load completed at 190807 13:16:01

I get following error in windows events

The description for Event ID 100 from source MySQL cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event: 

Slave I/O: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine, Internal MariaDB error code: 1932

Where are the log lines between 13:16:01 and 13:16:14 ? Have you also checked the logs for SystemD and kernel ? — IVO GELOV, Aug 07 '19 at 10:09
Hi , I just copy pasted that error later , whatever I posted on 13:16:01 is what appears again and again — Gracie williams, Aug 07 '19 at 10:22
As I said - look at `/var/log/messages` and run `journalctl -xe` — IVO GELOV, Aug 07 '19 at 10:24
I am using windows and xampp , I believe you are telling about linux ? — Gracie williams, Aug 07 '19 at 10:25
Ah, sorry - my oversight! Okay, have you ruled out the obvious possible reasons like blocked port and/or missing dependencies ? Have you checked ControlPanel -> EventViewer ? — IVO GELOV, Aug 07 '19 at 10:26
yes I have changed port and all dependencies are there... It was working fine , until i copied one table structure to another in phpmyadmin , suddenly i got error , no access to user for this table .. and thats it , from that time I am unable to start mysql in xampp — Gracie williams, Aug 07 '19 at 10:33
Look at the last sentence in the EventLog - this is the cause of the error (Table 'mysql.gtid_slave_pos' doesn't exist) — IVO GELOV, Aug 07 '19 at 10:48
This is the way https://stackoverflow.com/questions/18022809/how-to-solve-error-mysql-shutdown-unexpectedly — Michał Korzeniowski, Oct 29 '22 at 12:55

Clomp · Answer 1 · 2022-07-25T03:02:27.857

If you want a faster & easier way of fixing this problem, instead of re-installing XAMPP & reconfiguring everything, then look in your /xampp/mysql/data/ folder for any files, which have these prefixes & really long file names. They will almost all have a plethora of repeating "@" symbols in their file names, like so:

master-...@...@...@..@...
multi-...@...@...@..@...
mysql-relay-bin-...@...@...@..@...
relay-log-...@...@...@..@...

If you find those files - which are not standard short table names, but rather crash dump files - then make a /backup folder & simply move them into that folder. Then you can restart MySQL through the XAMPP control panel, without it failing. That's an easy & fast fix, which I just discovered & used to solve the same problem!

Addendum: 7-24-2022 If MySQL fails to startup after deleting those files, then delete this file too:

multi-master.info

Then MySQL should startup, plus remain up & running in XAMPP.

Magic! It is working. Please explain – Rajib Oct 25 '22 at 07:11 — Rajib, Oct 25 '22 at 07:11

score 5 · Accepted Answer · answered Jul 02 '20 at 18:35

A simple and effective solution is:

To copy your database files ibdata1 and database folders from c:\xamp\mysql\data to a safe place as backup
Copy all your website files from c:\xamp\htdocs to a safe place as backup
Head to applications (MAC) or control panel (Windows) and uninstall the current xampp version
Download the latest xampp version and install.
Copy back your files to their respective places
Done. Your applications should be back normal and working fine. I hope it helps someone.

score 0 · Answer 3 · edited Mar 13 '23 at 04:43

I think the best solution to do this is by editing your /mysql/bin/my.conf.

innodb_data_home_dir="C:/xampp/mysql/data"

innodb_log_group_home_dir="C:/xampp/mysql/data"
#innodb_log_arch_dir = "C:/xampp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size=1G
innodb_data_file_path=ibdata1:12M:autoextend:max:500M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_buffer_size=16M
innodb_log_file_size=20M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50

If that doesn't work initially, you can try copying files from your backups folder to the data folder before attempting this. But please keep a backup of your data folder!

Physically writing the file full - mysql Error

3 Answers3

Linked