Final phase produces error: `Fatal error: Uncaught mysqli_sql_exception: Duplicate entry`

[brainchild0]

I have succeeded in running the utility for transfer from local files to S3 in all stages but the last ($TEST = 0).

The tail end of the output, showing the error, appears below. Note that the earlier phases produced no error as such, but did produce the output EXPERIMENTAL, I have not had this problem, so can not test.

Do you have any advice for recovery?

check for canceled uploads in oc_filecache...
=> EXPERIMENTAL, I have not had this problem, so can not test.. => check only!PHP Fatal error:  Uncaught mysqli_sql_exception: Duplicate entry 'object::store:amazon::bucket_name' for key 'storages_id_index' in /root/work/s3/nextcloud-S3-local-S3-migration/localtos3.php:686
Stack trace:
#0 /root/work/s3/nextcloud-S3-local-S3-migration/localtos3.php(686): mysqli->query()
#1 {main}
  thrown in /root/work/s3/nextcloud-S3-local-S3-migration/localtos3.php on line 686

Fatal error: Uncaught mysqli_sql_exception: Duplicate entry 'object::store:amazon::bucket_name' for key 'storages_id_index' in /root/work/s3/nextcloud-S3-local-S3-migration/localtos3.php:686
Stack trace:
#0 /root/work/s3/nextcloud-S3-local-S3-migration/localtos3.php(686): mysqli->query()
#1 {main}
  thrown in /root/work/s3/nextcloud-S3-local-S3-migration/localtos3.php on line 686

[mrAceT]

This is odd..

Is your S3-bucket called 'bucket_name' ?

And you seem to already have this setup? As you get an error for updating the local storage ID to the S3 storage ('object::store:amazon::bucket_name')

You could check the ID already containing that what is mentioned between brackets. And take a look in oc_filecache and search in 'storage' for that number.

PS: when your Nextcloud gets 'bricked', restore the SQL-backup and you should have it working again

[brainchild0]

Would you please provide a concrete suggestion, one which I would be able to follow? I am not understanding your phrasing.

No the bucket is not actually called bucket_name. I substituted the dummy name for the actual one in the output I posted.

I can restore the database from backup, but the restore point would be simply to the state exactly preceding the failed operation. I would need a modified procedure compared to simply the failed operation to expect a different result.

The version of the table oc_storages from the backup, which is the version the migration script is processing when it starts, is not using any value prefixed with object:: for the indexed column storages_id_index. It has two values prefixed with local::, corresponding to two different locations for the data directory that have been used, one current and one previous, and it has several entries prefixed with home::, corresponding to the aliases of users.

[brainchild0]

It appears the script attempts to update all records in oc_storage prefixed with local:: with the same new value, the one prefixed with object::.

Thus, as in my case, if the original column has multiple values prefixed with local::, then the update fails due to violating the key constraint.

I was able to achieve successful completion of the script, by restoring the database from a modified backup, from which was removed a row corresponding to an older data directory for the application.

I am now wondering about cleaning the data folder. The documentation suggests that it may be simply deleted, but I understand it is used for other files such as the default profile and update backups. How may it be cleaned safely, leaving the necessary components, but not including the file data that is not transferred to object storage.

Other than one directory named for each user, those in the data directory include index.html, rainloop-storage, appdata_ocln9zfb4fus, news, updater.log, audit.log, files_encryption, nextcloud.log, updater-ocln9zfb4fus, audit.log.1, files_external, and nextcloud.log.1.

[mrAceT]

Hmm this is odd!

in line 101 I check for this: "SELECT * FROMoc_storagesWHEREid= 'local::$PATH_DATA/'"

Why didn't it catch your 'double' !?

To my knowledge deleting that data in the data folder can be done without risk. When you log and backup in that same data folder do keep the folder itself.. maybe I should make that word "in" bold in the instruction also?

[brainchild0]

in line 101 I check for this: "SELECT * FROMoc_storagesWHEREid= 'local::$PATH_DATA/'"

Why didn't it catch your 'double' !?

I believe because it matches only records corresponding to a single directory, not all record corresponding to all data directories.

To my knowledge deleting that data in the data folder can be done without risk. When you log and backup in that same data folder do keep the folder itself.. maybe I should make that word "in" bold in the instruction also?

I wish to remove the data files from the directory, and also to change the location of the data directory within the local file system. What procedure do you recommend?

[mrAceT]

Could you possibly share your backup of your oc_storages? Then I could investigate.. (you may offcourse obfuscate privacy sensitive parts)

[brainchild0]

The essential structure of the table is as seen below.

By the way, is the bucket name included in the database records that correspond to the data location? If so, what are the implications of later reassigning a different bucket name or host as a new primary storage location?

---

INSERT INTO `oc_storages` VALUES (1,'local::/path/to/old/data/',1,NULL),(2,'home::admin',1,NULL),(3,'home::user1',1,NULL),(4,'home::user2,1,NULL),(6,'local::/path/to/curr/data',1,NULL);

[mrAceT]

is the bucket name included in the database records

In my oc_storages I have "on number one" object::store:amazon::[bucketname]

If you would change to an other AWS-S3 provider, you could do that manually by changing the config.php and if the [bucketname] is different alter the entry in oc_storages

In regards to you other post, do you want to move to 'S3', or do you want to move your "local folder" to an other location?

[brainchild0]

If you would change to an other AWS-S3 provider, you could do that manually by changing the config.php and if the [bucketname] is different alter the entry in oc_storages

If a different bucket name is used for the new provider, or if migration is to a different bucket for the same provider, then is it required to change the string value for the column id in oc_storages?

In regards to you other post, do you want to move to 'S3', or do you want to move your "local folder" to an other location?

The case was that a deployment had already been migrated to a new host, in which a different location was used for data, but the desired final migration would be of files stored in S3.

[mrAceT]

I have never switched S3-provider (did move local data folder, a long time ago), but I am quite confident that this should do the trick: Switching S3 provider:

• change your config.php (login/credentials)
• change oc_storages (bucketname)

Switching local storage

• change your config.php (folder location..)

[brainchild0]

• change oc_storages (bucketname)

Do I understand correctly that the bucket field in config.php, the component of the string value for the id column of the oc_storages table, and the bucket name on the S3 provider, all must match?

[mrAceT]

Again, I haven't tested this, but having some "conversion experience" regarding this subject.. yes I expect that they must all match.