1

I have an application running on a x86 system with MySQL InnoDB database. This application store many rows per day and yesterday, due to a power failure, I experienced a rollback of a one entire day of data. Simply I have a hole of data of the day before the power failure, and I'm sure there were many data before the power failure because I checked backup dumps.

How can a power failure involving data committed and so many rows? Is because of the log structure of innodb? Can I do something in MySQL configuration (expect to avoid power failure)?

My configuration is a standard-default of the mysql installer in Windows x64 7 environment.

  • 1
    What is your machine's configuration? What is the OS? Load on your system? Lots more detail required if we are even going to attempt an answer! Caching occurs at many levels in IT systems - database, OS, disk arrays. Although, I have to say that losing an entire day's data is rather a lot! Are there some batch processes in the background? – Vérace Oct 3 '17 at 11:50
  • I edited the question with some informations, there is no batch, scheduled and so on. What I'm scarried about is the windows automatical restore after a configuration failure. Can this rollback the innofb log file? For this reason I moved the mysq data path away from c:\programData to a custom path c:\mysqldata. But it was not enough – Tobia Oct 3 '17 at 17:18
  • Did you do anything to recover from the failure? Or it simply powered up on its own? – Rick James Oct 7 '17 at 2:17
  • Are all your tables InnoDB? What are the values for %sync% and innodb%? – Rick James Oct 7 '17 at 2:18
  • The user simply powered on the pc, and then found data loss, all tables are innodb. I cannot connect to use db to get that values, but they should be the default for mysql standard community installer for version 5.6.26 – Tobia Oct 9 '17 at 15:43
1

I'm quite sure that the rollback was made by automatic Windows 7 System Restore after the power failure. This restored the mysql data folder. I cannot understand how a OS restore can move files from other applications even becuse I was using a custom path for mysql-data: c:\mysqldata and not the standard c:\programData\Mysql\.... My solution is to disabled the windows restore, but I think can be a better solution.

EDIT

The problem is the IBD data files of Mysql/MariaDB, these are a "monitored extension" and Windows rollbacks them anywhere in the volume.

EDIT2

Seems that switching innodb_file_per_table option to OFF solved the problem because new tables (only new ones) will not use IBD files.

  • Ouch! This makes Windows a really bad OS for running a database?? – Rick James Jan 22 '18 at 12:57
  • Is this a question? – Tobia Jan 22 '18 at 15:09
  • Incredulity.... – Rick James Jan 23 '18 at 2:28
  • @Tobia Does this apply also for windows 10 ? or only 7 ? Thanx u help so – Khalid Ahmed Apr 30 '19 at 8:43
  • I guess it is the same for Win10, but I did not test it yet. – Tobia May 2 '19 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.