3.4GB Attachment Table - Trouble moving from DB to FS

[rick]

I have been attempting to upgrade my VB to VB3RC3 with marginal success. After several attempts I was able to complete the upgrade scripts and completed the upgrade to vb3rc3 over about 9 hours time. The attachment table ALTER statements are the time killer.

Once I had completed the upgrade my next move was to move the attachments out of the database ONCE AND FOR ALL! I successfully moved all attachments to the filesystem and I could see that all directories and files were created successfully. I went to follow the next step to confirm the move and that is where I ran into problems.

Can anyone tell me exactly what is happening on this last step, and possibly why it might fail on an attachment table 3.4GB in size? As I mentioned, all of the ALTER statements worked, just took a long time.

Would it be possible for me to run this last SQL statement from SQL shell (command line)? If so, can anyone please help me with the final SQL statement to execute?

Thanks for your assistance, almost got these attachments out of the DB...

Rick

[Freddie Bingham]

This last step is doing the following:

- Clearing the attachments from the database (This part doesn't take a long time)
- Optimizing the attachment table to reclaim space (This is probably what is taking a long time).

What you can do is remove the following line in admincp/attachment.php

$DB_site->query("OPTIMIZE TABLE " . TABLE_PREFIX . "attachment");

You can also choose to wait it out as it should come back at some point. If you remove the line and run the process again, you'll probably find that your database is still consuming a large amount of space even though the attachments are no longer there.

If you are still sitting at the screen waiting for it to finish and want to try this, remove the line as mentioned above then hit the back button on your browser a couple time so that the attachment move process goes back to moving the last few attachments again. It is safe to do this and will save you the time of having to run the whole process again.

[rick]

thanks for your help, Freddie.

Im not running the upgrade right now, had to scratch my last attempt. I'll edit the file admincp/attachment.php as you suggest and run the upgrade again.

Another quick question, If I want to reclaim the space taken by the attachment table, is there a command I can run for SQL shell (command line) to get it back after I move the attachments to FS via admincp,
ie. "OPTIMIZE TABLE attachemnt" ?

Also, I assume that I should also comment out the line right below the line you suggested I comment out, $DB_site->reporterror = 1; ???

Thanks for your help, I'll give it another shot.

Rick

[Freddie Bingham]

This only pertains to the process of moving attachments from the DB to the FS. It has nothing to do with the upgrade process, which you said you had completed.

Only remove the line I listed, nothing else.

Yes that would be the query to run from the command line but it is going to take just as long there as if you let the script do it.

[rick]

Copy that -

The upgrade had completed, but I had to scrap it because of the failure at the finalize step. No biggie, im working on a backup board.

I'll only comment out the line you suggest.

The optimize query doesnt scare me from the command line - just have bad luck with the browser sometimes.

Thanks again for your help, Freddie, much appreciated.

Rick

[rick]

Well, 11 hours after the process began, I am finding that I am in the same spot I originally was. Images that are posted as attachments in threads do not show up (shows as broken image when clicked). I moved all of the images to the Filesystem via the admincp and I commented out the optimize command. When I selected yes to finalize the page timed out and returned a blank, white page.

Looking into the attachment table I can see that all blob fields are now 0 bytes. I can see that my attachment directory is full of all of the attachments. However, when bringing up a thread with attachments only presents the broken image.

im really not sure where to go from here. like I said, I have a directory with 3.4 GB of attachments that were dumped from the db in web space. It looks like all of the blob fields are 0 bytes in size. Filehash field is filled in...

When I post a new thread with an attachment, that attachment DOES work, it's just the attachments prior to the upgrade that are not showing up.

I can give root access to the linux box if it would help, Freddie?

thanks- I've been up all night on this and it looks like it is going to be a long day as well.

[rick]

one other thing - when I go into admincp and look under attachment storage and type, it still says "Attachments are currently being stored in the database". However the blob field sizes are all zero K ???

[Freddie Bingham]

[ -- removed -- ]

[Freddie Bingham]

Actually, this isn't going to work. Let me think about it for a moment.

[Freddie Bingham]

Run this query:

UPDATE setting SET value = '2' WHERE varname = 'attachfile'

Then go to vBulletin Options, open any category and then press the submit button to save the options.

[rick]

While trying to figure out the problem, I have run accross this query;

$DB_site->query("UPDATE " . TABLE_PREFIX . "setting SET value = '2' WHERE varname = 'attachfile'");

Problem is that when I look for a varname called "attachfile", I cant find one!?! This query is most likely failing and not throwing an error that I can see.

so, question is, should I have a varname in the setting table called attachfile? and if so, can someone help me to create it?

Thanks,
Rick

[rick]

scratch that - I found the attachfile varname

[Freddie Bingham]

The attachfile query did not fail as it is as basic as a query can be. If you really did remove the optimize line then the query that timed out was the query that removes the attachment data from the attachment table.

[rick]

ok, I have ran the following query on the db;

UPDATE `setting` SET `value` = '2' WHERE `varname` = 'attachfile'

This did not appear to make a difference. I still get broken images on attached files ???

[rick]

hrm, I found this function, build_options(); right under the query that I performed above. Is there a way to manually build options? Is this part of the problem?