4

I have a situation with a particular table that now thinks it contains 4 Petabytes of data. I know that sounds cool, but I assure you, it is only on a 60GB partition.

This table has 9 fields in it. One of them is a domain_id field. It is the best field to identify the rows by, as there are only approximately 6300 of them. The only other field option to match has over 2 million records, and that's just more difficult.

I cannot do a straight mysqldump because it will attempt to output all 4PB of data and fill the drive long before it gets close to that, so I need to surgically remove the good stuff, destroy the db, and recreate it.

I believe if I can do a dump for each domain_id record, then I will get most of the usable data out of it. This is what I am trying to use:

mysqldump -u root --skip-opt -q --no-create-info --skip-add-drop-table \
 --max_allowed_packet=1000000000 database table --where="domain_id=10" \
 > domains10.sql

Using this I expect every row with the domain_id 10 to be exported.

However, when I check the export, I am only getting 1 row, when however I look at the db, there are many many rows. It is as though the operator just finds one, then gives up.

I have tried various operators. Using the < or > I am able to get more of the data, but the export stops short at certain rows where the data has been compromised. With over 6000 to go through, I can't narrow down which rows are being affected in the export easily enough.

So, what I need is an operator that will basically do what I thought = would do, simply give me an export of all records that match the specific field.

Also note, the only way I got this DB even accessible is through an innodb force recovery 3. So I need to get this right, because after this is done, I have to drop the db in order to make mysql functional again.

Looking forward to any helpful answers.

1
  • I have given up.. the more I tried the worse it got to access the info. I eventually had to drop it all, including the mysql install. I managed to get a dump of the other tables before all this though using an innodb forced recover 3 level. It is helpful to get data out of a db you otherwise couldn't even start. The mysterious 4 Petabyte table will now forever remain a mystery. Feb 19, 2010 at 20:48

4 Answers 4

1

From what you write it appears that database has been corrupted (thinking 4PB instead of 60GB is sort of a giveaway).

I doubt you can get any assurance of retrieved information reliability, unless you repair db first. Have you tried this?

Otherwise, what happens if you do "-f" key -- to continue even if errors encountered?

2
1

How big do you think the table should actually be ?

You could try converting it to myisam:

alter table ggg engine=myisam;

However, it sounds like you have a corrupted database.

Best plan might be to contact the innodb guys for support.

http://www.innodb.com/

0

I'm not a database admin, so maybe this idea is totally wrongheaded, but is there data in the dump that should be consistent in all the records with a text string? I wondered if it's possible for you to do a dump of your "4 petabyte" database and redirect it through a grep/strings filter so that if the data that's corrupted isn't a valid string, it won't get written to disk. This would depends on whether the corrupted data was just incomprehensible garbage though...

Otherwise someone else here will have to suggest a repair tool to try fixing the database.

1
  • This isn't an option as the db contains blob data, which is binary. Part of my problem is that I get so far and then I have an error like this come up: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table table at row: 52739 When I start a different domain_id points, I will get different rows.. always consistent. I know this isn't a timeout issue like most would think from this error, as the timeout settings are set very high. Feb 19, 2010 at 11:07
0

Try adding --skip-extended-insert. It's possible that things are getting mangled when writing to the file.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .