# osTicket attachments migration for huge databases

*https://mbrasile.com/osticket-attachment-migration.html · 2020-05-16 · osTicket*

.Recently I had some issues during the **migration** of files from database to filesystem for an istance of **osTicket 1.12.x**, but I think it's related to all range **1.10-1.14**.

The istance had more than 60.000 files ( **40GB** of data ) pushed as attachments inside the database. And the database was quickly growing by 1GB/week causing problems to the nightly backup procedure on a DB Cloud. Simply, the procedure stopped to work when the database reached 20GB and the provider did not offer any alternative solution. So it was mandatory to proceed with the migration on filesystem to keep it smooth and easy to backup.

After I had enabled the **plugin** and [double checked the procedure](https://github.com/osTicket/osTicket/issues/1243#issuecomment-54715803/) I launched this command in the root of osTicket:

*php manage.php file migrate --verbose --backend D --to F --limit=10000* ***(*)***

***(*)** you could try any limit you want, in my case I just launched the same command 6 times*

I added the **--verbose** option that let you see the flow of files during the "download". And here I had the first issue for 5 attachments, cause I received an **IOError** like this:

*"IOError: /var/www/osticket/attachments/e/..hash..: Unable to open for reading*

checking inside the instance of osTicket, I could see those attachments were really **zero size** files or **corrupted** ones, but after googling on forums it seems no one had really solved this: the migration simply fails to proceed. So I just forced the **creation of empty files on filesystem** with:

*touch /var/www/osticket/attachments/e/..hash..*

and after that I was able to restart and finish correctly the migration (excepting those 5 critical attachments of course).

At the end of the migration all the other attachments were correctly dumped in the target folder and mapped in *%_file* table with the indication of the "filesystem backend" as bk reference, but the *%_file_chunk* table was still 40GB. I have found a [specific thread](https://forum.osticket.com/d/93676-resolved-attachments-are-still-in-the-database-after-migration) on osTicket forum on this problem where someone accidentally resolved just renaming the %_file_chunk table, but this was not acceptable for my situation.

That led me to **analyze the table**: it had exactly the same number of rows it had at the beginning, but with the curious indication of a large amount of **InnoDB data free**, I had never checked before. On [Stackoverflow](https://dba.stackexchange.com/questions/185010/innodb-what-does-data-free-mean), some expert says it is the sum of several areas inside the binary table and suggested to **optimize the table**.

So I just tried this simple query:

*OPTIMIZE TABLE %_file_chunk*

but as anticipated on that thread, in my case it did not change anything. Anyway, I was sure that was the right way, so I launched a **full optimization of all the database** with this command:

***mysqlcheck -o DB_NAME***

and at the end the final size of t**he database was shrinked** from 40GB to 1.1GB!

So it seems this is not really an osTicket issue, but a normal behaviour of MySql (with InnoDB) related to its internal allocation of areas that need to be manually checked for optimization.

---

Massimiliano Brasile — https://mbrasile.com/ — info@mbrasile.com