localtoS3 fails updating oc_storages

[Weishaupt]

Hey there,

I've setup a new instance to test out your script. I've added SSE-C support (will create a seperate pull request) and it ran smoothly on $TEST = 2 and $TEST = 1. However the script fails executing an SQL query on the $TEST = 0 run:

#########################################################################################
check for canceled uploads in oc_filecache...
=> EXPERIMENTAL, I have not had this problem, so can not test.. => check only![12-Jun-2024 17:52:29 Europe/Berlin] PHP Fatal error:  Uncaught mysqli_sql_exception: Duplicate entry 'object::user:marvin' for key 'storages_id_index' in /home/whnx/nxmigration/localtos3.php:706
Stack trace:
#0 /home/whnx/nxmigration/localtos3.php(706): mysqli->query('UPDATE `oc_stor...')
#1 {main}
  thrown in /home/whnx/nxmigration/localtos3.php on line 706

The affected line is

$mysqli->query("UPDATE `oc_storages` SET `id`=CONCAT('object::user:', SUBSTRING_INDEX(`oc_storages`.`id`,':',-1)) WHERE `oc_storages`.`id` LIKE 'home::%'");

Any idea what is going on?

[mrAceT]

Great to hear my work gets used :)

will create a seperate pull request

Great! Improvements are always welcome!

Lets analyze the output:

######################################################################################### check for canceled uploads in oc_filecache... => EXPERIMENTAL, I have not had this problem, so can not test.. => check only!

That's line 636 to 691. No error there, so the challenge starts at line 694 and on. Which sounds logical since 694 starts with "if (empty($TEST)).."

the interesting part, the error given:

PHP Fatal error: Uncaught mysqli_sql_exception: Duplicate entry 'object::user:marvin' for key 'storages_id_index' in localtos3.php:706

Your line 706 is my line 698; the query that gives you the problem:

UPDATE oc_storages SET id=CONCAT('object::user:', SUBSTRING_INDEX(oc_storages.id,':',-1)) WHERE oc_storages.id LIKE 'home::%'

What this query does is convert the local storage target 'home..' to AWS-S3 storage 'object..' Apparently you already have an 'object target' for user 'marvin' AND a 'home target' for user 'marvin' !

This can't be good.. In my (cleaned up) setup the 'oc_storages' contains one 'object::store:' and one entry starting with 'object::user:' for each user.

I have tried and used different things before I ended up building these scripts and several tables within nextcloud got 'dirty'.. I think something similar has happend at your end.

I suggest you take a look at the PRE-migration table 'oc_storages' and write down the 'nummeric_id' linked to the already existing 'object::user:marvin' Then search for that number in 'oc_filecache':

SELECT * FROM oc_filecache WHERE storage = [number]

When you are lucky you get a zero result. Then you can simply remove the 'object::user:marvin' from 'oc_storages' (if I remember correctly!). If you do get entries then you probably know where those files are, if it's old "orphaned data" or... ?

In any case, you need to get rid of all the data linked to that oc_storage-id to be able to migrate that user to AWS-S3

Do test this thoroughly.. you are going to "mess around" in the tables.. one (big) mistake and you're in a lot of trouble.. backups, backups.. ;)

Does this help? Let me know how it goes!

[Weishaupt]

Hey @mrAceT ,

thank you for the thourough explanation. I know exactly what the issue was now.

First I added the new object storage configuration to nextcloud config directly, instead of using the seperate storage.config.php. Afterwards I logged in with the user to test. This must have been resulting in the newly created object::user:marvin

There were only two entries in the oc_filecache:

MariaDB [nextcloud]> SELECT * FROM oc_filecache WHERE storage = 10;
+--------+---------+-------+----------------------------------+--------+-------+----------+----------+------+------------+---------------+-----------+------------------+---------------+-------------+----------+
| fileid | storage | path  | path_hash                        | parent | name  | mimetype | mimepart | size | mtime      | storage_mtime | encrypted | unencrypted_size | etag          | permissions | checksum |
+--------+---------+-------+----------------------------------+--------+-------+----------+----------+------+------------+---------------+-----------+------------------+---------------+-------------+----------+
|  68637 |      10 |       | d41d8cd98f00b204e9800998ecf8427e |     -1 |       |        2 |        1 |    0 | 1718106189 |    1718106185 |         0 |                0 | 6668384d288e1 |          31 | NULL     |
|  68645 |      10 | files | 45b963397aa40d4a0063e0d85e4fe7a1 |  68637 | files |        2 |        1 |    0 | 1718106189 |    1718106189 |         0 |                0 | 6668384d256a2 |          31 | NULL     |
+--------+---------+-------+----------------------------------+--------+-------+----------+----------+------+------------+---------------+-----------+------------------+---------------+-------------+----------+
2 rows in set (0.022 sec)

These were probably the automatically created base folder structrue.

Also, I checked the source (as I'm working on a copy to test everything out) and it is missing the entry entierly.