I posted recently Lossless RBR for MySQL 8.0 about a concern I have about moving to minimal RBR in MySQL 8.0. This seems to be the direction that Oracle is considering, but I am not sure it is a good idea as a default setting. I talked about a hypothetical new replication mode lossless RBR and also about … Continue reading The differences between IDEMPOTENT and AUTO-REPAIR mode
The post The differences between IDEMPOTENT and AUTO-REPAIR mode first appeared on Simon J Mudd's Blog.
What are odds MySQL table can be recovered?
This is the most asked question. Every single customer asks if their MySQL table can be recovered. Although it’s not possible to answer that with 100% confidence there are ways to estimate recovery chances. I will describe few tricks.
Generally speaking, if data is on media there are high odds TwinDB data recovery toolkit can fetch it. Where to look for depends on accident type.
Online MySQL data recovery toolkit
On our Data Recovery portal you can upload an .ibd file and check if the InnoDB tablespace contains any good records. The table space may be corrupt. The tool should handle that.
DROP TABLE or DATABASE with innodb_file_per_table=OFF
If …
[Read more]Does your dataset consist of InnoDB tables with large BLOB data such that the data is stored in external BLOB pages? Was the dataset created in MySQL version 5.1 and below and without using the InnoDB plugin, or with InnoDB plugin but with MySQL version earlier than 5.1.55? If the answer to both the questions are "YES" then it could very well be that you have a hidden corruption lying around in your dataset.
The post Beware of MySQL BLOB Corruption in Older Versions appeared first on ovais.tariq.
Sometimes corruption is not the true corruption. Corruption in compressed InnoDB tables may be a false positive.
Compressed InnoDB table may hit false checksum verification failure. The bug (http://bugs.mysql.com/bug.php?id=73689) reveals itself in the error log as follows:
2014-10-18 08:26:31 7fb114254700 InnoDB: Compressed page type (17855); stored checksum in field1 0; calculated checksums for field1: crc32 4289414559, innodb 0, none 3735928559; page LSN 24332465308430; page number (if stored to page already) 60727; space id (if stored to page already) 448 InnoDB: Page may be an index page where index id is 516
InnoDB complains that a stored checksum is zero. If you look closely it’s suspicious that calculated checksum is zero too.
Every InnoDB page stores a checksum in first four bytes. When InnoDB reads a page it compares the checksum, stored in …
[Read more]MySQL 5.5.40 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:
http://dev.mysql.com/downloads/mysql/5.5.html
This release, similar to the last 5.5 release, is mostly uneventful.
There were 0 “Functionality Added or Changed” bugs this time, and 18 bugs overall fixed.
Out of the 18 bugs, most seemed rather minor or obscure, but there are 3 I think are worth noting (all 3 are InnoDB-related, regressions, and serious if you encounter them, so best to be aware of them):
There are at least two cases when it makes sense to take an image from a corrupted hard drive as soon as possible: disk hardware errors and corrupted filesystem. Faulty hard drives can give just one chance to read a block, so there is no time for experiments. The similar picture with corrupted filesystems. Obviously something went wrong, it’s hard to predict how the operating system will behave next second and whether it will cause even more damage.
Save disk image to local storage
Probably the best and fastest way is to plug the faulty disk into a healthy server and save the disk image locally:
# dd if=/dev/sdb of=/path/on/sda/faulty_disk.img conv=noerror
Where /dev/sdb is the faulty disk and faulty_disk.img is the image on the healthy /dev/sda disk.
conv=noerrror tells dd to continue reading even if …
[Read more]The unDROP for InnoDB tool can be used to recover corrupt MySQL database. In this post we will show how to repair MySQL database if its files became corrupted and even innodb_force_recovery=6 doesn’t help.
The corruption of InnoDB tablespace may be caused by many reasons. A dying hard drive can write garbage, thus page checksum will be wrong. InnoDB then reports to the error log:
InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 4.
MySQL is well know for poor start-up script. A simple upgrade procedure may end up with two mysqld processes writing to the same tablespace. That leads to the corruption too. Sometimes power reset corrupts not only InnoDB files, but file system becomes unusable for the operating system.
InnoDB …
[Read more]I ran into a rather obscure bug the other day, but while uncommon, it can cause damage you would not otherwise expect if you use file-level symbolic links. So this is just a warning about that.
Specifically, if you create a table with the .MYI and .MYD files in a different directory, using symbolic links – either manually or using CREATE TABLE .. INDEX DIRECTORY=”" DATA DIRECTORY=”", and then run myisamchk on the table and specify .MYI, you will corrupt the table.
Creating these manually is not so common, but the CREATE TABLE .. INDEX DIRECTORY=”" DATA DIRECTORY=”" is much more common, which creates file-level symbolic links (for the .MYI and .MYD files, respectively) in the datadir and stores the actual file(s) in the location specified. So it leaves you with this setup.
Therefore, if you later run myisamchk on one of these files, do not specify .MYI in the command invocation. If you invoke myisamchk –help, …
[Read more]Ref: Google: Computer memory flakier than expected (CNET DeepTech, Stephen Shankland)
Summary: According to tests at Google, it appears that today’s RAM modules have several thousand errors a year, which would be correctable if it weren’t for the fact that most of us aren’t using ECC RAM.
Previous research, such as some data from a 300-computer cluster, showed that memory modules had correctable error rates of 200 to 5,000 failures per billion hours of operation. Google, though, found the rate much higher: 25,000 to 75,000 failures per billion hours.
This is quite relevant for database servers because they write a lot rather than mainly read (desktop use). In the MySQL context, if a bit gets flipped in RAM, your data could get corrupted, or it’s ok on disk and you’re just reading corrupted data somehow. …
[Read more]There’s a good podcast from Chris Messina and Larry Halff, about what really happened at Ma.gnolia. If you’re at all interested in what happened (i.e. how did they lose all their bookmark data), don’t hesitate to watch the video. I took some quick notes:
