Filesystem issue - number of files in attachements directory / folder - wrapper for $file['saved_name']

[araviski]

Code: Select all

Script URL: Private. Available upon request with Hesk licence.
Version of script: 2.6.8 online - Not solved in 2.7.2 testing purposes
Hosting company: Myself 
URL of phpinfo.php: Private. Available upon request with Hesk licence.
URL of session_test.php: Private. Available upon request with Hesk licence.
What terms did you try when SEARCHING for a solution: directory attachments number folder

When dealing with a lot of tickets and attachments, the filesystem reach his limits (not physically), but in term of number of files. Even if disk partition is able to manage more, having thousands of files in the same folder make it unmanageable (accessing the folder even freeze the explorer).

An easy possible fix is to create a wrapper for each access to file ['saved_name'], then add a folder structure based on the ticket ID such :
29P-6EX-Z4SS_sdfaoihfazofihazokssfklqfhef.jpg
=>
2/9/P/29P-6EX-Z4SS_sdfaoihfazofihazokssfklqfhef.jpg

This can be performed dynamically during upload, or lazily at each file access (download / delete) with one extra disk read/delete attempt if the file is not found (usefull for compatibility).

I can provide a such fix if you are interrested in.

[Klemen]

How many tickets/attachments are you dealing with that this is causing issues and why would you want to manually manage the attachments folder? I have an account with over 1,200,000 tickets and 120,000 attachments and didn't run into any issues yet.

A simple hack to fix the issue without any code modifications to Hesk would be to use your recommended solution and just update the "saved_name" column values in the hesk_attachments table rather than creating a wrapper.

So, a simple PHP (or shell or anything that works on the server...) script to do the following:
1. connect to MySQL
2. loop through the hesk_attachments table
3. for each attachment create the sub-directory structure if necessary and move the file there
4. add the folder structure to the saved_name value

So instead of

Code: Select all

29P-6EX-Z4SS_sdfaoihfazofihazokssfklqfhef.jpg

the saved_name value in the DB would be

Code: Select all

2/9/P/29P-6EX-Z4SS_sdfaoihfazofihazokssfklqfhef.jpg

The folder structure could also be

Code: Select all

YEAR/MONTH/29P-6EX-Z4SS_sdfaoihfazofihazokssfklqfhef.jpg 

This would make it easy to archive/delete old files if you need to save disk space in the future.


If I can find some extra time I will try to write a tool to do this myself. No promises though.

[araviski]

I have 50 GB of attachments, the accurate issue I have is a backup process which is getting in trouble (rsync over NAS that hangs up due to number of files in folder).

Thank you for the hack, I'll implement it. This is much better

[Klemen]

OK, I wrote a simple tool that should achieve this.

Notes:

• it has not been tested extensively. Make 100% sure you have a full Hesk backup before using it, I make no guarantees.
• it relies on "last modified" file property returned by the server; if all files have the same "last modified" date (for example they have been copied from one server to another) it will place them all into the same folder
• it will move files to a "year/month" folder
• it will ignore Knowledgebase attachments
• it will ignore any files with a / in the saved_name database column, so it should be safe to run more than once (periodically)

That said, here are instructions:

1. download the tool here
2. upload it to Hesk "admin" folder
3. login to Hesk with an administrator account
4. in your browser, open structure_attachments.php (it may take a while to complete):

   Code: Select all

   http://example.com/hesk/admin/structure_attachments.php

5. confirm that the files have been moved and attachments still work fine

If you (or anyone else) uses this let me know how it goes.

[bastiaan.c]

Awesome Klemen,

It worked for me on one Ticket System.
My second ticket system says:

Could not move from

../attachments/

to

../attachments/2017/01/

[araviski]

@bastiaan.c are you sure your apache user has the right to create files and folder ?

@Klemen Awesome ! Indeed I performed a copy between servers, I'll implement an SQL query in order to get date of ticket.
Thank you very much (Youtube video like performed )

[bastiaan.c]

@araviski Yes indeed, It made the folder for the year 2015, 2016 and 2017. It will only not do 2017 January

[Klemen]

@bastiaan.c

So the "01" folder does not exist within "2017" but it made month sub-folders for other years?

Do you get any more info if you:

1. enable HESK debug mode, and
2. append "?verbose=1" to the structure_attachments.php file, like this:

Code: Select all

http://example.com/hesk/admin/structure_attachments.php?verbose=1

[araviski]

I'll implement an SQL query in order to get date of ticket.

The SQl query needs to be changed to :

Code: Select all

SELECT `att_id`, `saved_name`, `dt` FROM `".hesk_dbEscape($hesk_settings['db_pfix'])."attachments` a INNER JOIN `".hesk_dbEscape($hesk_settings['db_pfix'])."tickets` t  ON a.ticket_id = t.trackid WHERE `saved_name` NOT LIKE '%/%'

The date of file is retrieved with :

Code: Select all

  // Get file modification time
    $fileDate = explode(' ', $file['dt']);
    list($year, $month, $day) = explode('-', $fileDate[0]);
    //show_log("Y: $year M: $month D: $day<br>\n");

    // Year folder path
    $new_folder = $hesk_settings['attach_dir'] . '/' . $year ;
    prepare_dir($new_folder);

    // Month folder path
    $new_folder .= '/' . $month ;
    prepare_dir($new_folder);

    // Day folder path
    $new_folder .= '/' . $day ;
    prepare_dir($new_folder);

Please not that the day folder path is not mandatory and not exploited natively in the script provided by Klemen.

Please test before use !

[bastiaan.c]

Hi araviski, I did the change in the file and I ran it. It now said: DONE
I thought all would have been good now. However it was not. The months in 2017 are not created and all the files are still in the root.
The are also folder with attachments and text files. When I click on them it opens something like html and it just shows the ticket from the front-end.
It seems that this is all the stuff for 1 ticket in a folder in attachments.
Can you help here? I am not sure if tickets are now dis-functional actually.