How can i change the path of the music library?

[Bob Arctor]

Hello,

at first i want to thank everyone for this wonderful software! I had many great hours with my favourite music that would not have been possible without ampache!

With that said now to my problem:

The path of my music library changed. The harddrive i stored my music on was full and i migrated it to another harddrive.

Obviously ampache didn't play music after that so I changed the path of the local catalog with mysql workbench and updated the catalog in the webinterface.

But now when i search for an album for example every song is listed twice and just one of those is playing. So i assume there are now the songs with the old path and the ones with the new path both in the database.

Does anyone know how to fix that? Is it possible to keep the songratings etc. of the users?

What would have been the correct way to tell ampache that the path of the music library has changed?

Thank you very much for your help!

Best regards

Bob

[Bob Arctor]

Sorry, i forgot to mention that I am runnung ampache with apache 2 and mysql on an ubuntu 16.04 server.

[Ernie D]

Hello Bob,

The following assumes you have a working  knowledge of SQL.  The
procedure might have been simpler if you asked the question before
making any changes. :-)

First, I suggest you backup the current database with the Workbench or
mysqldump on the command line.

Because you updated the catalog and didn't create a new one, the
"addition _time" of the song table will have to be used. Notice that it
is a "Unix time stamp" which is a simple integer.

Look for a large gap where the new song items were added.  There is an
online converter at http://www.onlineconversion.com/unix_time.htm to
convert them to human readable form.  An SQL query can be used to remove
all files above that timestamp(newest files).

Now comes the hard part:

It is necessary to  get the "file" column of the song table to agree
with the "path" column in the catalog_local table. This could be done
using Mysql regular expressions
(https://dev.mysql.com/doc/refman/5.7/en/regexp.html#regexp-syntax).

I hope this helps, but it seems to be the only way to preserve ratings
and other user statistics.


73 From AA1AD

Ernie D
Federal Way, WA | CN87ug

> --
> You received this message because you are subscribed to the Google
> Groups "Ampache" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to ampache+u...@googlegroups.com
> <mailto:ampache+u...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ampache/89109350-b9a8-4021-bae6-961f5d7c3c91%40googlegroups.com
> <https://groups.google.com/d/msgid/ampache/89109350-b9a8-4021-bae6-961f5d7c3c91%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

[Bob Arctor]

Hi Ernie!

Thank you very much for your help!

I backed up my current database using mysqldump and opend the database with mysql-workbench. Unfortunately when i open the song table and sort it by "addition_time" the newest and the oldest entry are are both very recently (thanks for the link for converting the integers!). So there is no big time gap... Is it possible that the old entries got deleted by updating the database?

If thats the case it's not as bad because i found an old .sql backup file, about two month old.
When i import that backup what would be the steps to change the path in that case?
Should i change the 'path' column and update all entries of the 'file' column of the song table aswell or is there another way for that scenario?

Thanks a lot!

Bob

[Ernie D]

ampache.cfg.php has a switch: no_symlinks = "false". I'm wondering if you change it to true
and recreate the old path with a symlink pointing to the new catalog path, that might be a quick fix.

Otherwise...

Remember, the workbench by default limits the query to only 1000. It can be set to a max of 50000. That my be why you didn't see a large time gap.

If you didn't do a clean, the old files should still be there. Also a query of the count of the song
table should show about double the count shown on Ampache's web interface. If you remember the day that you did the update, a delete from that timestamp
to the last record would do the trick.

The problem with using a backup, is that the song id column number will not be the same as the original. That means that
 ratings and even statistics may be different for the users.

I understand that the current catalog path points to the actual location of the media files. Only the song 'file' column must be changed to point to the
new catalog path.

Also, I suggest replicating the database and use that to experiment on by changing the database name in ampache.cfg.php.

This is a purly "mental construct" for me so if you have any more questions, let me know.:-)

73 From AA1AD
Ernie D
Federal Way, WA | CN87ug

[Ernie D]

Sometimes it takes me awhile to get to the "KISS" answer: It just occurred to me that if you do a select on the new song.file path in Workbench, the new files can be removed very quickly.

Of course you want to remove the newer files before doing the following to change the song file path.

I used the workbench to work out the proper SQL statement:

Assuming the catalog path was changed to /media/<new location>/Music from /media/backup/Music:

USE `ampache`;
UPDATE `song` SET `file` = (SELECT REPLACE(file, 'media/backup/Music','media/<new location>/Music'));

73 From  AA1AD
Ernie D
Federal Way, WA | CN87ug

[Bob Arctor]

Hi,

thank you very much for your help!

Yes, I still have a few more questions :)

I set the limit in the MySQL Workbench to don't Limit. Can that be a Problem?

Also when i look at 'Show Catalogs' in the Web interface under the coloumn 'Last Clean' is a recent Date.

According to the GitHub ampache Wiki 'Clean' is part of the 'Update' function. Does that mean the old songs where deleted?

Also i am not shure what the correct song number in the web interface is. When i go to 'Home' - 'Statistics' I see two numbers for 'Songs'.

One is in a table that shows also ''Connected Users", "Total Users" etc. This number says 181 339 Songs.

The other One is below that table wich also lists the 'Name' of the catalog, 'Path' wich is empty in my case, 'Last Verify' etc. The 'Songs' number in that table says 68 767. When i look at my harddrive that should be the correct number of songs in my library. 

In the workbench when i look at the info of the song table it says at 'Table rows' 181 339, too.

When I sort the unlimited table by addition_time the lowest and the highest number are both after the update.

This date was about 12th of feb. 2018.

So would it be possible to use the backup that i made about two month ago in december? I mean would just the user statistics that where made in those two month be gone but everything before the date of the backup would still be there?

In that backup of course there is still the old path of the music library. Would it be possible import that old backup, change the path and have the correct user statistics of december afterwards? Or would the ratings and statistics be completely different for the users?

Thanks!

Best regards,

Bob

[Bob Arctor]

Sorry i didn't read your second post before posting my previous one.

The problem is that i can't figure out wich songs are old and wich are the new ones. The addition_time of all songs seems to be after the path changed. Also under the 'file' coloumn for every entry points to the new path.

Also i just figured out why there are so much more entrys in that table. There are also entrys that point to other folders on that new harddrive, despite i just set the path at the 'catalog_local' to the music folder.

[Ernie D]

Yes, the old songs are probably gone from the db.  I forgot that the catalog id didn't change because you changed only the the path.

For your question below: probably not. The other tables use the song.id column as "object_id". If the newly imported song table data matched the old table id for id, then the user ratings/statistics would be the same.

The problem is that if songs were removed from the catalog before the backup, importing the table would give incorrect ratings/statistics for the user . The song table would have to be truncated to return the song.id to 0 and hope that the first id of the backed up song data started at  0 in the old table.  The ratings/statics might not be zero, but would probably be very different from the original values.

73 From  AA1AD
Ernie D
Federal Way, WA | CN87ug

[Bob Arctor]

Thanks so much for your help!
I got it now back to normal as it was in december.
I dropped (deleted) the current ampache database. Recreated it empty, restored the backup of december, changed the path in catalog_local table and changed the path for every song in the 'file' column of the song table using your commands.

Tanks and best regards,

Bob

[Ernie D]

Glad I could help.

73 From  AA1AD
Ernie D
Federal Way, WA | CN87ug