Optimized attachments in vB2.X.X

Hi vB Troopers,

*****************
* Hack Idea:
*****************
This hack is designed to improve 2 things:
1. Prevent duplicate attachments altogether.
2. Speed up the query for duplicates and avoid errors.

What does vB do now?
---------------------------------
If you have "Allow Duplicate Images" set to "No" in your attachments options and your database has a very large amount of attachments, at worst, the server will timeout or the query to check for duplicate attachments takes an extreme amount of time. The duplicates query also checks only for duplicates from a particular user so actually it is possible to have duplicate attachments in the database.

What does vB do differently after the hack?
------------------------
With the hack installed, each attachment receives a hash which is stored in the attachment table. This hash is then used to find duplicate attachments regardless of whether or not the attachment is from the same user or not. So duplicate attachments are not possible.

Why vB works faster with this hack?
------------------------------------
Since the hash field and not the actual file content is being compared, then much less server resources are needed for MySql to run the query. Also the hash field is indexed. This in turn speeds up the saving of the attachments when checking for duplicates.

Please be aware that this hack alters the database and when carried out incorrectly, could cause data loss. Installation is at your own risk.

That said, enjoy!

Scott

This hack was written by pogo.

Also note, a system similiar to this will be part of vB3, so no need to ask if it will be added.

[s.molinari]

[Release vB 2.2/3.x] Optimierung des Speicherns von Anh?ngen
Sinn des Hacks:
Wenn man sehr viele Anh?nge in seiner DB hat und der Server mit einem zu kurzen Timeout versehen ist, passiert es, dass man keine Anh?nge mehr hochladen kann, weil ein 404 Fehler erscheint. Dieser Hack ver?ndert die ?berpr?fung auf doppelte Anh?nge derart, dass sie sehr viel schneller abl?uft.

Wie es im original vBulletin ist:
Wenn man in den Options keine doppelten Anh?nge erlaubt, wird beim Hochladen eines Anhangs ?berpr?ft, ob man diesen schon einmal hochgeladen hat. Es wird nicht ?berpr?ft, ob dergleiche Anhang schon in der DB, sondern nur, ob der augenblickliche Autor diesen Anhang schon gepostet hat.

Wie es mit dem Hack ist:
Es wird ?berpr?ft, ob dergleiche Anhang schon in der DB ist. Ist dies der Fall, wird der Anhang nicht erneut in der DB gespeichert, sondern der bestehende Anhang im Beitrag angezeigt.

Wer braucht den Hack?
Der Hack nimmt vermutlich allen Foren mit vielen Anh?ngen ein wenig (wenn nicht viel) Belastung vom Server.

Die Deutsche Version findest du hier.

[Xenon]

hmm, from what i see, it's looking good.
just one thing might cause trouble (very rarely of course but it could.)

you are using a hash function to decide if an attachment is already in the db or not.
lesson one of hashing is that hashes can be compared faster than the objects, but nevertheless the best hashing function could produce similar hash results with different objects (it's called the birthday problem or something like)

md5 hash spreads the hash result very good, but it is NO injective function, and therfore a bug can occure. (as said rarely but possible)

to solve the problem, you have to compare the full data after selecting it with the hash comparison.

[Dean C]

Wow a hack from a vb dev - this is a rare and special occasion

This hack sounds impressive. Good job pal !

Regards

- miSt

[s.molinari]

Originally posted by Mist
Wow a hack from a vb dev - this is a rare and special occasion

This hack sounds impressive. Good job pal !

Regards

- miSt

hehe! We're not vB devs. Just a future distributor of vB.

@Xenon. Good point but as you said, very rare that something like you mentioned would happen. Thanks for the input.

Scott

[Xenon]

welcome scott
you know, me, always on the search of possible problems
ok, the chance of two matching hashes is 1 to 3.4 * 10^38 but it is there

@Mist: they are just developing the german in the new vb *gg*

[Dean C]

Still developing vbulletin hehe

This is such a good idea. I hope its in vb3?

- miSt

[ManagerJosh]

Well if the bugs get ironed out from what Xenon pointed out, I'll most definitely install

[Xenon]

you can install it, the chance of this bug occurs is nearly 0

if you want to be absolutley sure use instead of this:

PHP Code:

    if (!$allowduplicates) {
      if ($result=$DB_site->query_first("SELECT attachmentid
                                         FROM attachment
                                         WHERE hash = '".addslashes($attachmenthash)."'")) {
        $attachmentid = $result[attachmentid];
      } 


this:

PHP Code:

    if (!$allowduplicates) {
      if ($result=$DB_site->query_first("SELECT attachmentid,filedata
                                         FROM attachment
                                         WHERE hash = '".addslashes($attachmenthash)."'")) {
       if($filestuff==$result[filedata]) {
        $attachmentid = $result[attachmentid];
      } 


and add a } directly before return $attachmentid;

[Erwin]

A hack from molinari is always a good one.