I had a customer's computer shutdown/crash due to power going out and when mysql tried to boot they got. After I looked in data folder for database and there was just .frm files and no .idb files.
2016-05-18 18:22:57 108 [Note] InnoDB: Database was not shutdown normally!
2016-05-18 18:22:57 108 [Note] InnoDB: Starting crash recovery.
2016-05-18 18:22:57 108 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-05-18 18:23:07 108 [Note] InnoDB: Restoring possible half-written data pages
2016-05-18 18:23:07 108 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 172727488
2016-05-18 18:23:08 108 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
2016-05-18 18:23:08 108 [Note] InnoDB: 128 rollback segment(s) are active.
2016-05-18 18:23:08 108 [Note] InnoDB: Waiting for purge to start
2016-05-18 18:23:09 108 [Note] InnoDB: 5.6.23 started; log sequence number 172727488
2016-05-18 18:23:09 108 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2016-05-18 18:23:09 108 [Note] - '127.0.0.1' resolves to '127.0.0.1';
2016-05-18 18:23:09 108 [Note] Server socket created on IP: '127.0.0.1'.
2016-05-18 18:23:13 108 [Note] Event Scheduler: Loaded 0 events
2016-05-18 18:23:13 108 [Note] C:\PHPPOS\mysql\bin\mysqld.exe: ready for connections.
Version: '5.6.23' socket: '' port: 3306 MySQL Community Server (GPL)
160518 19:46:36 [Note] Plugin 'FEDERATED' is disabled.
160518 19:46:37 InnoDB: Initializing buffer pool, size = 8.0M
160518 19:46:37 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file .\ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
160518 19:46:37 [ERROR] Plugin 'InnoDB' init function returned error.
160518 19:46:37 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160518 19:46:37 [ERROR] Do you already have another mysqld server running on port: 3306 ?
160518 19:46:37 [ERROR] Aborting
I copied their data directory to an identical server and am trying to recover.
The files ib_logfile0,ib_logfile1 are exactly 48M each so I tried the following to recover.
Add my.ini value to the mysqld section: (size of log file)
innodb_log_file_size=48M
Results on restart.
2016-05-19 14:16:45 7116 [Note] InnoDB: The log sequence numbers 170685510 and 170685510 in ibdata files do not match the log sequence number 172823340 in the ib_logfiles!
2016-05-19 14:16:45 7116 [Note] InnoDB: Database was not shutdown normally!
2016-05-19 14:16:45 7116 [Note] InnoDB: Starting crash recovery.
2016-05-19 14:16:45 7116 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-05-19 14:16:45 7116 [Note] InnoDB: Restoring possible half-written data pages
2016-05-19 14:16:45 7116 [Note] InnoDB: from the doublewrite buffer...
2016-05-19 14:16:45 7116 [ERROR] InnoDB: Table bitnami_phppos/FTS_0000000000000064_0000000000000141_INDEX_1 in the InnoDB data dictionary has tablespace id 87, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-05-19 14:16:45 7116 [ERROR] InnoDB: Table bitnami_phppos/FTS_0000000000000064_0000000000000141_INDEX_2 in the InnoDB data dictionary has tablespace id 88, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
It seems like it is looking for the .ibd files; but as I said they are not at ALL in the database anymore.
Here is settings for innodb:
+------------------------------------------+------------------------+
| Variable_name | Value |
+------------------------------------------+------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_flush_neighbors | 1 |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_lru_scan_depth | 1024 |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_dirty_pages_pct_lwm | 0 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 2000 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 5.6.23 |
| innodb_write_io_threads | 4 |
+------------------------------------------+------------------------+
119 rows in set (0.00 sec)
Any other steps I can try to recover? (I have tried many things including changing innodb_force_recovery to 6 and also trying innodb_fast_shutdown to 0. Binlogs are not available.
Is this customer out of luck?
.frmfiles present? Did you haveinnodb_file_per_table=ONwhen each table was created? If not, then there were no .ibd files, and all the data is still inibdata1. – Rick James May 29 '16 at 17:20innodb_file_per_table=ONwas on. I couldn't believe it either; but a lot of strange things has happened to this clients computer including not being able to run some installers; router resettings..etc. It could be a bad employee; but it happened right after a hard crash. – Chris Muench May 30 '16 at 22:41innodb_file_per_table=ONwas this way from the server setup, before creating any tables? – ypercubeᵀᴹ Sep 13 '16 at 21:08