I have a java swing application that connects to MySQL database and performs transactions perfectly alright. There are one-up numbers as well that are generated for transactions. Data does get committed most of the times without any issue. But there have been instances where, in some of the cases, the previously committed data gets wiped off from the database altogether and the one up numbers also rollback. For example, if I have done invoices with one up invoice numbers from 1 to 200, the next morning when I check the sales report, it shows transactions with ID from 1 to 100 only. The rest of the data go missing. But since I have printed invoice copies of all invoices from 1 to 200, I am certain that the transactions have indeed taken place. Is there anything in MySQL that I need to watch out for?? Will upgrading to MySQL version 8 help? I feared there is some kind of malware in the machine, so I have even tightened the antivirus but that hasn't helped. I have set a very strong MySQL DB password as well. Nothing seems to have helped prevent this from happening.

1 Answers

2
Stephen C On

For example, if I have done invoices with one up invoice numbers from 1 to 200, the next morning when I check the sales report, it shows transactions with ID from 1 to 100 only. The rest of the data go missing. But since I have printed invoice copies of all invoices from 1 to 200, I am certain that the transactions have indeed taken place.

What you have stated doesn't prove that the transaction committed. One possibility is that the invoice was printed during the transaction; i.e. before you committed it.

We can't eliminate this possibility without examining your code. And there could be any number of other possibilities like that.

Is there anything in MySQL that I need to watch out for?

I am aware of nothing in MySQL that would cause spontaneous roll-back of transactions that have been committed.

Will upgrading to MySQL version 8 help?

It is highly unlikely that it will help. More to the point, you won't really know for sure that it has made any difference ... if you don't know the real cause.


Here's some possible explanations. There are probably others.

  1. Bugs in your code; e.g. see above.
  2. Some scheduled task (that you might have forgotten about or not know about) has bugs in it.
  3. Something is restoring an older version of your database.
  4. A previous employee / developer left a "time bomb" in your code.
  5. A hacker (or a previous employee or a current employee) has access to the system and is "messing with you".
  6. Malware / viruses (though it is unlikely that generic malware would cause this kind of behavior). And note that "tightening" your AV may not help if you have already been infected.
  7. Some strange MySQL bug that I've never heard of.

I would suggest things like:

  • checking all accounts for the system are secure, and close any that shouldn't be there anymore,
  • checking the system access logs, etcetera for signs of unauthorized access or access at unexpected times
  • use MySQL Enterprise Audit - https://dev.mysql.com/doc/refman/8.0/en/audit-log.html
  • implement something that (for example) takes periodic snapshots of certain tables that you can then use for later analysis.