0

Disclaimer: hobbyist programmer, not a professional in any sense, and no skill whatsoever with system admin stuff, especially databases.

Lots of people have the same problem I"m having, but none of the solutions I've seen out there work for me. It seems like the details are different for each case. My system crashed not long ago, but I had a fairly recent backup of everything under my MAMP mysql folder, so I thought I'd be ok. But restoring the mysql folder from the backup doesn't seem to work, which seems to jive with what I've read from everyone else asking the same question. When I run MAMP, mysql simply won't start. The logfile (below) says that I should do something with my.cnf, but I can't find a my.cnf anywhere, and like I say, I just installed MAMP, so I thought it would be there.

Anyway, how can I fix this? Am I simply screwed?

140520 22:01:33 mysqld_safe Starting mysqld daemon with databases from /Applications/MAMP/db/mysql
140520 22:01:33 [Warning] Setting lower_case_table_names=2 because file system for /Applications/MAMP/db/mysql/ is case insensitive
140520 22:01:33 [Note] Plugin 'FEDERATED' is disabled.
140520 22:01:33 InnoDB: The InnoDB memory heap is disabled
140520 22:01:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140520 22:01:33 InnoDB: Compressed tables use zlib 1.2.3
140520 22:01:33 InnoDB: Initializing buffer pool, size = 128.0M
140520 22:01:33 InnoDB: Completed initialization of buffer pool
InnoDB: Error: space header page consists of zero bytes in data file ./ibdata1
140520 22:01:33 InnoDB: Could not open or create data files.
140520 22:01:33 InnoDB: If you tried to add new data files, and it failed here,
140520 22:01:33 InnoDB: you should now edit innodb_data_file_path in my.cnf back
140520 22:01:33 InnoDB: to what it was, and remove the new ibdata files InnoDB created
140520 22:01:33 InnoDB: in this failed attempt. InnoDB only wrote those files full of
140520 22:01:33 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
140520 22:01:33 InnoDB: remove old data files which contain your precious data!
140520 22:01:33 [ERROR] Plugin 'InnoDB' init function returned error.
140520 22:01:33 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140520 22:01:33 [ERROR] Unknown/unsupported storage engine: InnoDB
140520 22:01:33 [ERROR] Aborting

140520 22:01:33 [Note] /Applications/MAMP/Library/bin/mysqld: Shutdown complete

140520 22:01:33 mysqld_safe mysqld from pid file /Applications/MAMP/tmp/mysql/mysql.pid ended
SaganRitual
  • 3,143
  • 2
  • 24
  • 40
  • **After making backups of your data files** can you try in your config file adding `innodb_force_recovery = 1 ` and restarting mysql? – Alex.Ritna May 21 '14 at 05:51
  • @Alex.Ritna which config file is that? I can't find a my.cnf anywhere. Plus mysql won't even run when I put my ibdata and stuff into the mysql db folder. – SaganRitual May 23 '14 at 05:27
  • my.cnf doesn't exist by default in MAMP. I found this which may help you with creating a my.cnf with innodb_force_recovery - http://stackoverflow.com/a/2869707/730315 – Spikes May 23 '14 at 05:36
  • 1
    How did you make your backups? TimeMachine? DBMSes generally keep files open and just copying their binary datafiles will not ensure that the copy is in a consistent state. mysqldump and mysqlhotcopy, are the MySQL tools to get a consistent backup of your data. TimeMachine [isn't the magical tool its GUI suggests](https://discussions.apple.com/thread/1727652?start=0&tstart=0). – Chris Wesseling May 23 '14 at 08:35
  • Does ./ibdata1 exist? If so, what size is it? – akuzminsky May 24 '14 at 15:41
  • Agree with Chris. Just copying files won't make consistent or valid backup. Now the question is how to start MySQL and get data back, if it's important. If not - re-create the database from the scratch – akuzminsky May 24 '14 at 15:45
  • @akuzminsky Yes, it's 10.5MB – SaganRitual May 24 '14 at 16:00
  • Take this config http://pastebin.com/EJbJ6DDB , save it in /etc/my.cnf . Then start MySQL. Try innodb_force_recovery values from 1 to 6 until MySQL starts. When/if MySQL starts take a dump of all databases with mysqldump -A > mysqldb.sql – akuzminsky May 24 '14 at 16:14
  • But if my.cnf already exists on your system just add innodb_force_recovery = 1 under [mysqld] section. – akuzminsky May 24 '14 at 16:16
  • btw, people say my.cnf should be in /Applications/MAMP/conf/my.cnf http://stackoverflow.com/questions/678645/does-mysql-included-with-mamp-not-include-a-config-file?lq=1 . Try either path – akuzminsky May 24 '14 at 16:29
  • @akuzminsky It won't start. All six values result in a crash recovery attempt, but mysql crashes every time, as I can see from the [logfile](http://pastebin.com/M47UebPX). – SaganRitual May 24 '14 at 21:48
  • I don't see MySQL reading your my.cnf. If you set innodb_force_recovery to non-zero it prints "140524 15:19:40 InnoDB: !!! innodb_force_recovery is set to 4 !!!" to the error log – akuzminsky May 24 '14 at 22:20
  • @akuzminsky That's weird. I double-checked that it was reading my.cnf by looking at the logfile both with and without the cnf file. Without, it doesn't say anything about crash recovery, which it does with the cnf file. For values 1-5 it gives the output you see in pastebin. For value 6, it produces [this](http://pastebin.com/VBH0qYP3). – SaganRitual May 25 '14 at 02:10
  • Don't get confused. innodb_force_recovery doesn't force "crash recovery". InnoDB starts crash recovery when it believes MySQL didn't stop gracefully. innodb_force_recovery disables some InnoDB features to let InnoDB start (For example, value 4 disables crash recovery process.) Here's an example of the error log when innodb_force_recovery was set to 4 - http://pastebin.com/xHvLSGuW – akuzminsky May 25 '14 at 02:23
  • Note "140524 15:19:40 InnoDB: !!! innodb_force_recovery is set to 4 !!!" . There is no this message in your log – akuzminsky May 25 '14 at 02:24
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/54387/discussion-between-greatbigbore-and-akuzminsky). – SaganRitual May 25 '14 at 14:50
  • @akuzminsky See my comments in the chat. – SaganRitual May 28 '14 at 14:51

2 Answers2

1

There are four things that you can try out

  1. use database recovery software to get back your data

  2. I found that the below works for me

    lsof -i | grep mysql

    This returns mysqld plus a number (plus other info), in my case the response began

    mysqld 573

    Now type kill plus the number that you got; in my case

    kill 573

  3. try changing your mysql password or port numbers

  4. Set innodb_use_sys_malloc =0 into the my.cnf and restart MySQL.

doublesharp
  • 26,888
  • 6
  • 52
  • 73
Jerin
  • 3,657
  • 3
  • 20
  • 45
0

You can create your own my.cnf file for MAMP server and there you can set paprameters for innodb data folder and also try recovering the innodb tables..

Refer this to create my.cnf file

LINK

Community
  • 1
  • 1
Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81