Large Database Category Confusion 1.9 - 3.2

[treat2day]

I really want to upgrade before my database gets any larger.

I have been working on upgrading 1.9.2.11 to 3.2.39

Some information about my install.

I have attempted 4 installs and changed the 297 category ids in 3.2 to match 1.9 
All 3527 files could not be found and new ones were added with new file ids.

Question: 1

Is this database to large for jdownloads?

• 66.9 GB (71,841,411,072 bytes)
• (includes, mp4 zip, mp3 zip, eBooks in PDF, EPUB files) 
  

Step 1- installing 

• First I could not get the database to install online with dedicated server without stopping and landing on a white page  - failed
• So I moved the jdownloads folder to my computer localhost extra Desktop Hard Disk Drive - 7200 RPM SATA 6 Gb/s 128MB Cache - failed again


• Finally, I moved the install to my SSD drive and the database installed without going to a white page and landed in jdownloads showing the statistics of the install   - SUCCESSFUL

Step 2 - verify categories and files
 

• Some files were not found and 3.2 automatically added.
• The files were there but the install did not find them.

Step 3 - delete not found items

• matched categories and files to match 1.9 - these showed the number of downloads and were not published
• deleted newly automatically added - these showed 0 downloads with new files ids

 
Note. I turned off monitoring and publishing automatically

Step 4 - category id changes in 3.2

• First, I tried changing the category ids in the 3.2 backup, then restored using jdownloads restoration upload - failed
• Then I tried changing all the category ids in phpmyadmin, ran the check downloads - all files from 1.9 not found and new files add automatically with new file ids - failed again

The problem the confusion with getting the right category ids to match without file ids to change from the 1.9.

Step 5 - changed phpmyadmin setting UTF-8 character encoding

This may be of no use but I was willing to check out all possible scenarios.

• Accuracy 
  utf8mb4_unicode_ci is based on the Unicode standard for sorting and comparison, which sorts accurately in a very wide range of languages.
  utf8mb4_general_ci fails to implement all of the Unicode sorting rules, which will result in undesirable sorting in some situations, such as when using particular languages or characters.
   
• Performance
  utf8mb4_general_ci is faster at comparisons and sorting, because it takes a bunch of performance-related shortcuts.
  On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today's computers.
  utf8mb4_unicode_ci, which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'.
   

Question: 2

Where is it suggested in making category id changes so the 3.2 will recognize the changes and not reproduce new file ids and unpublished the old 1.9?

Note: I did not delete backup tables

[ColinM]

Hi
First thanks for comprehensive notes.

Is this database to large for jdownloads?

No. I think you say there are only 3527 files and 257 categories.  The files themselves are not stored in the database, it is only the information relating to them that is stored in the database.  The files are stored on the server file system.  The absolute size of each Download is a disk useage resource, not a database resource.  There are numerous instances of sites with tens of thousands of Downloads.

I presume you read the article How to upgrade jDownloads component from 1.9.2.11 to latest 3.x version?


Step 1.  Creating Downloads, especially when automatically making thumbnail images, is an intensive cpu process.  With a large number of files it is almost certain that one will run out of the time allowed  by a regular php.ini file.  On a general server it is usually not possible to increase this time.  Hence it is recommended that with a large number of Downloads being created one carries this out on a local host, as you did, making sure you set the php.ini parameter max_execution_time to a suitable large integer value.  The usual php default is 30 seconds.  There is also another time limit in Apache based servers have a time limit directive which is generally about 300 seconds.  I suspect that you left the default time so that it only worked with the ssd drive on your local pc.

Note Turning off automonitoring, as you did, is recommended in situations when a large number of files are presented. This is due to the timing issue as above.

Step 2. It is possible that the process actually timed out when most things were done but not all files yet processed.  That is our experience is that the migration process runs OK and finds all all of the files and makes all the categories (directories).  I would suggest that you extend the time limit and start yet again. It may well prove to be better in the long term.

Step 3.  The migration process from jD 1.9.2.x retains the file ids but may have changes to the file ids. This is mentioned in the above article.  The reasons for the category ids being possible changed are somewhat long and obscure.  Maybe Arno could advise here.

Of course if you have turned off auto publishing then they will not be published.

Step 4.  Changing the category ids in the database table is feasible but beware any interactions with Joomla! permissions.  Presumably you want to do this because of plugin references.  It might actually be better to change the links - tiresome!  Another thought which has just occurred to me, so no guarantee of success, is to change the category ids using php-myadmin (export, save, edit, re-import) just before setting permissions when doing the migration. (around steps 12 and 13 in above doc)

Step 5.  I do not understand!!  I am not totally conversant with such matters with UTF-8.  It does do a lot for many foreign language stuff and after a few initial hiccups seems to work OK.  Maybe you could explain a bit more for me please.

Colin

[treat2day]

The usual php default is 30 seconds.  There is also another time limit in Apache based servers have a time limit directive which is generally about 300 seconds.  I suspect that you left the default time so that it only worked with the ssd drive on your local pc.

Step 1.
These are my settings

max_execution_time = 99999999
max_input_time = 100000
memory_limit = 3072M

upload_max_filesize = 10000000M
post_max_size = 10005000M

Did I miss something?

I presume you read the article How to upgrade jDownloads component from 1.9.2.11 to latest 3.x version?

Yes!! And double checked each time I attempted the install. This is why I thought changing the category ids was the only work involved because the file ids were not changing.


Step 4.

It might actually be better to change the links - tiresome!

This is where I am stuck.

I have 4 external domains with links to these downloads as it's main source of file sharing. It would be a nightmare to change the links. I did not use the content plugin, only direct links on the hosting domain.

The only thing left to do is redirect the 357 categories and 4000 files to a new install link. 

I have never dealt with a database for downloads before now.

I have made the changes phpmyadmin on the last attempt. this created a duplicate of each file "automatically added" and could not find the old files which had not be moved.

Is this an unusual procedure as an upgrade from 1.9 to 3.2. ?

[ColinM]

OK
I understand why you want the locations identical so that your external inks work.  And your were obviously aware of the resource limit.
Yes your situation is a little unusual so I will consult with Arno.  It may need a copy by PM of your 1.9 database backup.  presumeably however your live site is still working on jD1.9.2.11 so maybe admin & ftp access to that might be better.

Colin

[Arno]

Hi,

Step 3.  The migration process from jD 1.9.2.x retains the file ids but may have changes to the file ids. This is mentioned in the above article.  The reasons for the category ids being possible changed are somewhat long and obscure.  Maybe Arno could advise here.

This should be:
  The migration process from jD 1.9.2.x retains the file ids but changes the category ids.
The reason is the support for the Joomla access permissions. For this it is required to create a new base (root) category. This must be the new parent (root) category for all other categories. So we get as result new category ids. This is inevitable.

So i think in your case can you only change the old links with a special db query. For this you need a list with the old category ID and the new ID. What also can be much work. But I have to think about it.

[treat2day]

So i think in your case can you only change the old links with a special db query. For this you need a list with the old category ID and the new ID. What also can be much work. But I have to think about it.

Okay thanks.

I will attempt any suggestions no matter how much work is involved on my end. 

I can easily have the online live site open on one monitor and the localhost updating version on the 2nd monitor.

[Arno]

Maybe can i add a simple log function in the migration script. Then we have an automaticly created category id list. I will see what i can do here. But i need a little bit time for it.

[treat2day]

 But i need a little bit time for it.

I am only attempting to upgrade because I have plenty of time to wait during this time of the year. 

I have a total of 3 upgrades that will benefit from the success of this one.

• archive contains currently 914 downloads in 47 categories.
• archive contains currently 1568 downloads in 127 categories.
• archive contains currently 2916 downloads in 274 categories.

Thank you for all your efforts.

[treat2day]

I just completed manually linking all the articles to the new links on my localhost. 

Download Configuration areas are all empty when restoring the site on my server. 

The backup restoration 
Error! Cannot store the backup file in the /_tempzipfiles folder.

Status: NOT WRITABLE
I filled everything into to the directory path and folders, my permissions are set to 755. However. each time I try to save I get an error.

Error! Can not rename folder:

I tried using the same setting on from another domain and still no luck.

Any suggestions. What am I doing wrong?

[treat2day]

Solved

I uninstalled and reinstalled the new setup from my localhost. It took five times before jdownloads opened to control panel because I have a large database (1846 items). Each time it will time out and default the root front end. However, once the component finished, all files and folders were perfectly in place without errors.

Note:

I get double files when adding if jdownloads isn't active in the browser window ONLY.

It works perfectly if I have an session active in the component.

Thank you for all that you do.