Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got an interesting issue last week that after shutting down an instance of mine, all data were back in 3 months (like a big rollback), even though there is no sign of crashing, recovery crash process, anything indeed.

All data was restored using the latest backup and bin logs, which somehow it was fully updated with all recent transactions.

I've tried to reproduce the error cloning the instance and doing a shutdown (halt command) process cleanly, however no issue occurred. As far as I know, when halt command used without arguments and the system is running normally and also this system is running in a run level different of 0 or 6, the OS sends a SIGTERM to all process in order to let them to save all work and terminating correctly.

Looking at MySQL runtime status and global variables I found that:

  • InnoDB dirty pages are being flushed correctly as Fast Shutdown is turned on, and looking at MySQL error log it shows that shutdown process was completed normally.
  • innodb_flush_log_at_trx_commit is set to 1, which keeps MySQL ACID compliant.
  • innodb_file_per_table is also enabled, so there's "no risk" to inflate ibdata and somehow get corrupted, which is not the case as not recovery crash process was initiated.
  • InnoDB runtime status shows that there's no pending writes (e.g LRU):

    Pending writes: LRU 0, flush list 0, single page 0.

    • InnoDB Log buffer size is not that big - 8M only

    So, if there's any information that it may be needed to help solving that problem I will be providing, because I really need someone to shed a light on this as I run out of tests/probabilities to figure out what exactly happened, please.

    PS: MySQL running version is 5.1.62, but we do have plans to upgrade ASAP, however having that issue recently made us uncertain about what if that happens again.

    Thank you guys a lot in advance.

UPDATE 07/06 - InnoDB global variables

+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 16777216               |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 4294967296             |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | ON                     |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     | O_DIRECT               |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 120                    |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 8388608                |
| innodb_log_file_size                    | 268435456              |
| innodb_log_files_in_group               | 3                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_method                     | nulls_equal            |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+
share|improve this question
    
Do you have autocommit=0 in your my.cnf ? –  RolandoMySQLDBA Jun 6 '13 at 16:43
    
Hi Rolando, Thanks for responding. No, it's turned on by default: mysql> show global variables like '%commit'; | Variable_name | Value | | autocommit | ON | –  Heitor Lessa Jun 7 '13 at 8:47
    
Please run SHOW GLOBAL VARIABLES LIKE 'innodb%'; and post all the variables in the question body. –  RolandoMySQLDBA Jun 7 '13 at 11:29
    
Updated the question body as requested –  Heitor Lessa Jun 7 '13 at 16:12
    
"back in 3 months" means... what, exactly? After the restart, all changes for the past 3 months had disappeared? –  Michael - sqlbot Jun 7 '13 at 16:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.