Time Machine restore issues (macOS)

All MAMP discussions around troubleshooting and anything related to MAMP. Be as detailed as possible here when posting an issue.
Post Reply
Site Admin
Posts: 129
Joined: Wed Jan 20, 2021 3:06 am

Time Machine restore issues (macOS)

Post by mampsupportmod »

Recently had to restore my MAMP web server from backup using Time Machine. In the past, this worked perfectly with restoring my macOS system with MAMP installs.

MAMP Version: 6.3.2 w/ MySQL v5
macOS: 10.15.7

After restoring from Time Machine and firing up MAMP Pro, MySQL misbehaved. It would allow me to hit some sites until I had to create a login authentication and suddenly MySQL died. The problem is, Time Machine isn't actually doing a snapshot backup of files, rather incremental. This causes the all important IB* log files to not backup efficiently with the same times. So, when you go to restore macOS and MAMP Pro from Time Machine, you are almost guaranteed to face this issue. The workaround is to take consistent snapshots of your MAMP Pro install using MAMP's function AND include Time Machine backups. I will cover doing this in a later post.

Note: Before trying the below suggestion to fix this, I would recommend to attempt to start MySQL normally. Let it attempt to run and watch your MySQL error log. It will likely try to fix all the mismatched log sequence numbers and while it does your sites may not function. In our case, this took a few hours. Then, once the MySQL error log stops and your sites are look good (MySQL may not show running in MAMP Pro during this process!) get backups of all your DB's. Then stop/start and monitor your logs again. If things look good, you dodged a bullet!

Here's the logs

Code: Select all

[ERROR] InnoDB: Page [page id: space=573, page number=3] log sequence number 108946610081 is in the future! Current system log sequence number 103805991611.
[ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
[Warning] InnoDB: A transaction id in a record of table  is newer than the system-wide maximum.
[Warning] InnoDB: A transaction id in a record of table  is newer than the system-wide maximum.
0x700001f94000  InnoDB: Assertion failure in thread 123145335422976 in file trx0rec.ic line 109
InnoDB: Failing assertion: next_undo_offset > undo_offset
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
15:48:42 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 184800 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fa305040800
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 700001f93f50 thread_stack 0x40000
0   mysqld                              0x000000010fd64c9a my_print_stacktrace + 58
1   mysqld                              0x000000010fcbd820 handle_fatal_signal + 704
2   libsystem_platform.dylib            0x00007fff6a28a5fd _sigtramp + 29
3   mysqld                              0x00000001108b6a48 _ZZL18innodb_show_statusP10handlertonP3THDPFbS2_PKcmS4_mS4_mEE13truncated_msg + 116616
4   libsystem_c.dylib                   0x00007fff6a160808 abort + 120
5   mysqld                              0x00000001102756e1 _Z23ut_dbg_assertion_failedPKcS0_m + 161
6   mysqld                              0x000000011025b655 _Z25trx_undo_get_undo_rec_lowyP16mem_block_info_tb + 549
7   mysqld                              0x000000011025bdb7 _Z27trx_undo_prev_version_buildPKhP5mtr_tS0_P12dict_index_tPmP16mem_block_info_tPPhS7_PPK8dtuple_tm + 1815
8   mysqld                              0x0000000110233cc3 _Z34row_vers_build_for_consistent_readPKhP5mtr_tP12dict_index_tPPmP8ReadViewPP16mem_block_info_tSA_PPhPPK8dtuple_t + 195
9   mysqld                              0x0000000110213f05 _ZL31row_sel_get_clust_rec_for_mysqlP14row_prebuilt_tP12dict_index_tPKhP9que_thr_tPS4_PPmPP16mem_block_info_tPPK8dtuple_tP5mtr_t + 1317
10  mysqld                              0x00000001102173b6 _Z15row_search_mvccPh15page_cur_mode_tP14row_prebuilt_tmm + 11590
11  mysqld                              0x0000000110136ffe _ZN11ha_innobase10index_readEPhPKhj16ha_rkey_function + 734
12  mysqld                              0x000000010f6459ac _ZN7handler18index_read_idx_mapEPhjPKhm16ha_rkey_function + 76
13  mysqld                              0x000000010f63ecde _ZN7handler21ha_index_read_idx_mapEPhjPKhm16ha_rkey_function + 158
14  mysqld                              0x000000010fb833fe _ZL10read_constP5TABLEP12st_table_ref + 302
15  mysqld                              0x000000010fb82f02 _Z21join_read_const_tableP8JOIN_TABP11st_position + 306
16  mysqld                              0x000000010fbb87b3 _ZN4JOIN29extract_func_dependent_tablesEv + 675
17  mysqld                              0x000000010fbaf949 _ZN4JOIN14make_join_planEv + 777
18  mysqld                              0x000000010fbab3b0 _ZN4JOIN8optimizeEv + 2496
19  mysqld                              0x000000010fc0000c _ZN13st_select_lex8optimizeEP3THD + 76
20  mysqld                              0x000000010fbffefb _Z12handle_queryP3THDP3LEXP12Query_resultyy + 587
21  mysqld                              0x000000010fbcae83 _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 819
22  mysqld                              0x000000010fbc54e7 _Z21mysql_execute_commandP3THDb + 3111
23  mysqld                              0x000000010fbc3eb4 _Z11mysql_parseP3THDP12Parser_state + 964
24  mysqld                              0x000000010fbc2bef _Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command + 4895
25  mysqld                              0x000000010fbc38b9 _Z10do_commandP3THD + 505
26  mysqld                              0x000000010fc9e694 handle_connection + 468
27  mysqld                              0x00000001102d5cc7 pfs_spawn_thread + 311
28  libsystem_pthread.dylib             0x00007fff6a296109 _pthread_start + 148
29  libsystem_pthread.dylib             0x00007fff6a291b8b thread_start + 15

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa305044030): SELECT id, password
WHERE username=''
Connection ID (thread ID): 1459

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

The culprit was:
InnoDB: Page [page id: space=573, page number=3] log sequence number 108946610081 is in the future! Current system log sequence number 103805991611.

After reading online, the InnoDB log has a sequence mismatch. It's important to fix this for your DB's or MySQL won't work (in this version anyway). Here's what I had to.

1. Add this to the [mysqld] section in the my.cnf file:

Code: Select all

innodb-force-recovery = 6

2. Restart MySQL / MAMP

3. Verify the recovery in your MySQL log file in MAMP. You should see this:

InnoDB: !!! innodb_force_recovery is set to 6 !!!

4. Make a dump of all SQL DB's. You can do this from MAMP tools but you can also do it from Terminal via MAMP MySQL:

Code: Select all

/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot > dumpme.sql

5. Stop MySQL in MAMP Pro.

6. Comment innodb-force-recovery = 6 line from my.cnf file and move ibdata*, ib_logfile* and all database folders to a backup location. The location of these files is:

Code: Select all

/Library/Application Support/appsolute/MAMP PRO/db

7. Start MySQL. This will create the new ibdata and ib_logfiles.

8. Restore your SQL Dump back:

Code: Select all

/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot < dumpme.sql

That's it. You should have your working MAMP Pro webserver back to normal. If this did not work for you please comment below. Hopefully when MAMP is released withy MySQL 8 this is article is meaningless.
Post Reply