MySQL Forums
Forum List  »  InnoDB

Power failure and innodb
Posted by: Tobia Bea
Date: January 27, 2014 09:45AM

Hello, I already wrote about a problem like this, but now I restart a new topic because I'm sure this depends on power failure.

I'm using innodb for an intranet web application, this computer works as server and client.

Due to user's ignorance this workstation suffered of many power failures, I mean at least 2 per month. However innodb could works for many months until the last week. Here happends a strange data lost (at last!) because the database rollbacked to many days before. All tables were working, the only problem was that data inside them was exactly like many days before. This rollback has happend during a normal shutdown and bootup and not after a power failure. Moreover mysqldump backup of the day before has all data.

My question is: why this data lost (I think due to many power failure in this months) after a GOOD shoutdown and start? I can understand data lost just after a power failure, but why after few days? Is this due log corruption? In this case, why mysqldump the day before could backup all data?
How can I prevent it adjusting innodb configuration?

I know that your answer will be "you might configure your user, not innodb".. I agree :-) but I can not choose my users and I want to do everything I can to prevent this also because if the user doesn't notice the data lost everything will go forward with a data gap...

Thank you for you help

In this instance there was the default "server machine" configuration

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_server_stopword_table          |
innodb_ft_sort_pll_degree                | 2
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_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.11
innodb_write_io_threads                  | 4

Options: ReplyQuote


Subject
Views
Written By
Posted
Power failure and innodb
8137
January 27, 2014 09:45AM
2717
January 28, 2014 08:42AM
2760
February 24, 2014 06:21AM
2216
March 05, 2014 08:15AM
2071
March 05, 2014 10:42PM
1937
March 07, 2014 07:46AM
1438
February 02, 2015 09:33AM
1413
February 02, 2015 11:43AM
1520
February 03, 2015 05:09AM
1472
February 03, 2015 07:04PM
1890
February 04, 2015 01:03PM
1160
June 04, 2015 07:35AM
2590
March 31, 2014 06:16AM
1455
March 18, 2015 11:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.