occ memories:places-setup do not work with no table prefix

[diroots]

Describe the bug
occ memories places:setup do not work with a nextcloud installation with empty table prefix.

our installation is done with this parameter in config.php : 'dbtableprefix' => '',

when launching occ memories places:setup, we get this result :

server:/path/to/nextcloud# occ memories:places-setup
Attempting to set up reverse geocoding

In Places.php line 42:
                                                                                     
  Database table prefix is not set. Cannot use database extensions (dbtableprefix).  
                                                                                     

memories:places-setup

it's a bit like #632, except my nc installation is with empty table prefix, and i cannot change it,...

To Reproduce

• install nextcloud with empty table prefix
• install memories
• run occ memories:places-setup

Platform:

• OS: Ubuntu 20.04 LTS, docker image
• Browser: not applicable
• Memories Version: [5.1.0]
• Nextcloud Version: [25.0.6]
• PHP Version: [8.0]

Additional context
Add any other context about the problem here.

• Any errors in the JS console? not applicable
• Any errors in the Nextcloud server logs?

[diroots]

also i don't get the purpose of this check : https://github.com/pulsejet/memories/blob/master/lib/Service/Places.php#L41

as when checking where is used this $prefix variable the the whole app, it's only called there :

root@server:/path/to/nextcloud/html/custom_apps/memories# grep -nR --exclude-dir=exiftool-bin '$prefix'
lib/Service/Places.php:40:        $prefix = $this->config->getSystemValue('dbtableprefix', '') ?: '';
lib/Service/Places.php:41://        if ('' === $prefix) {
root@server:/path/to/nextcloud/html/custom_apps/memories# 

so it's defined, then checked for not being empty, but then it's not used anywhere.

why is this check needed, then?
and it should be tested that it is defined correctly, but it can be empty, as nextcloud install can have prefix empty (https://github.com/nextcloud/server/blob/master/config/config.sample.php#L145).

[pulsejet]

Unfortunately there's no easy way around this. Doctrine doesn't play well with unknown column types when calculating the schema, so adding a geometry column in the same namespaces causes it to crash. Putting nextcloud's tables inside a prefix allows placing the geometry table in a separate memories_ namespace, which Doctrine then ignores.

[pulsejet]

The sample config has been corrected now: nextcloud/server#38321

[diroots]

but what happens with a nc instance already set up without prefix (running for years)? we cannot install memories? like there is no other choice?

[akhil1508]

Putting nextcloud's tables inside a prefix allows placing the geometry table in a separate memories_ namespace, which Doctrine then ignores.

@pulsejet In an installation which had dbtableprefix => 'oc_', I could see tables like oc_memories_places and oc_memories. How does Doctrine ignore these?

Update: NVM, I got it. Only the memories_planet_geometry is outside the prefix.

[pulsejet]

but what happens with a nc instance already set up without prefix (running for years)? we cannot install memories? like there is no other choice?

Places is an optional feature, so you should be able to use everything else just fine. If you really need places support, you'll need to manually migrate the DB to a non blank prefix (basically rename all tables to have this prefix, then update config.php)

[bendschs]

so i guess this is why i am getting the following error message?

Attempting to set up reverse geocoding
Database support was detected
Download planet data to temporary file...
Inserting planet data into database...

In Places.php line 442:
                                                                               
  Failed to create database tables: An exception occurred while executing a q  
  uery: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key  
   was too long; max key length is 1000 bytes                                  
                                                                               

[pulsejet]

@bendschs that seems unrelated. You might need to upgrade / set some options in your DB. What DB is this anyway?

[bendschs]

it's MariaDB 10.5.8. reading over various issues regarding the planet database it seems almost like this feature is still a bit experimental (not to be taken as criticism)?

thanks for the amazing work you have done at this point, it's really quite impressive how much progress you made with this app, it's already so much better than the official photos app.

[pulsejet]

it's MariaDB 10.5.8. reading over various issues regarding the planet database it seems almost like this feature is still a bit experimental (not to be taken as criticism)?

It's quite stable actually. Two issues that are common:

1. Memory limits: batch insertions are faster but they end up creating some trouble on low memory systems.
2. This (occ memories:places-setup do not work with no table prefix #648) issue, which nothing can be done about (it's a Doctrine limitation). Fortunately, any Nextcloud setup from the last five years or so likely shouldn't have trouble.

Regarding the error you got (first time I'm seeing this), it's likely a database configuration mismatch; what's happening here is MariaDB is refusing to create the spatial index. A couple of things to try:

1. occ maintenance:repair
2. You could verify some db/table settings
   • You are using InnoDB
   • Row format is dynamic
   • Collation is utf8mb4_general_ci

thanks for the amazing work you have done at this point, it's really quite impressive how much progress you made with this app, it's already so much better than the official photos app.

🎉