[Bug]: Conversion of MariaDB to Postgres17 fails on oc_storages_numeric_id_seq sequence

[psych0d0g]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

While converting my mariaDB instance to pgsql i get the following exception:

In ExceptionConverter.php line 62:

  An exception occurred while executing a query: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
  LINE 1: ...nextcloud.oc_storages_numeric_id_seq', (SELECT MAX() FROM ))
                                                                       ^


In Exception.php line 24:

  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
  LINE 1: ...nextcloud.oc_storages_numeric_id_seq', (SELECT MAX() FROM ))
                                                                       ^


In Connection.php line 71:

  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
  LINE 1: ...nextcloud.oc_storages_numeric_id_seq', (SELECT MAX() FROM ))
                                                                       ^

oc_storages in mysql looks like this:

MariaDB [nextcloud]> select * from oc_storages;
+------------+----------------------------+-----------+--------------+
| numeric_id | id                         | available | last_checked |
+------------+----------------------------+-----------+--------------+
|          1 | home::User1                |         1 |         NULL |
|          3 | home::User2                |         1 |         NULL |
|          4 | local::/var/www/html/data/ |         1 |         NULL |
+------------+----------------------------+-----------+--------------+
3 rows in set (0.001 sec)

and:

MariaDB [nextcloud]> explain oc_storages;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| numeric_id   | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| id           | varchar(64) | YES  | UNI | NULL    |                |
| available    | int(11)     | NO   |     | 1       |                |
| last_checked | int(11)     | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

the sequence in postgresql exists despite the exception:

nextcloud=# \d nextcloud.oc_storages_numeric_id_seq
               Sequence "nextcloud.oc_storages_numeric_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: nextcloud.oc_storages.numeric_id

nextcloud=# SELECT * FROM nextcloud.oc_storages_numeric_id_seq;
 last_value | log_cnt | is_called
------------+---------+-----------
          1 |       0 | f
(1 row)

Steps to reproduce

php occ db:convert-type --password="xxxx" pgsql nextcloud nextcloud-psql-primary.nextcloud.svc nextcloud

Expected behavior

have a working PGSQL conversion on the target

Nextcloud Server version

30, 31.0.10, 32.0.1

Operating system

None

PHP engine version

None

Web server

None

Database engine version

None

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

{
    "system": {
        "htaccess.RewriteBase": "\/",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "apps_paths": [
            {
                "path": "\/var\/www\/html\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/www\/html\/custom_apps",
                "url": "\/custom_apps",
                "writable": true
            }
        ],
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***"
        ],
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "31.0.10.2",
        "overwrite.cli.url": "http:\/\/localhost",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "loglevel": 0,
        "maintenance": false,
        "app_install_overwrite": [
            "occweb",
            "passman",
            "news",
            "files_opds",
            "folderplayer"
        ],
        "theme": "",
        "mail_smtpmode": "smtp",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_sendmailmode": "smtp",
        "mail_smtpauth": 1,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "default_phone_region": "DE",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "password": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        }
    }
}

List of activated Apps

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

Additional info

Running docker image via helm chart, version 31.0.10, also version 30 was affected.
after upgrading nextcloud to version 32.0.1 this issue is still present.

[psych0d0g]

i got nextcloud working with pgsql anyways even despite those errors in the migration script,
for that i had to fix all sequence numbers in the new pgsql tables:

DO $$
DECLARE
    rec RECORD;
    seq_name TEXT;
    max_id BIGINT;
BEGIN
    FOR rec IN
        SELECT
            table_schema,
            table_name,
            column_name
        FROM
            information_schema.columns
        WHERE
            column_default ILIKE 'nextval%'
            AND table_schema = 'nextcloud'
    LOOP
        -- Find the sequence name
        seq_name := pg_get_serial_sequence(
            format('%I.%I', rec.table_schema, rec.table_name),
            rec.column_name
        );

        IF seq_name IS NOT NULL THEN
            -- Find the max ID from the table
            EXECUTE format('SELECT MAX(%I) FROM %I.%I',
                           rec.column_name, rec.table_schema, rec.table_name)
            INTO max_id;

            -- FIXED LINE: Cast max_id to text for printing
            RAISE NOTICE 'Checking: %.%. Column: %. Max ID: %.',
                rec.table_schema,
                rec.table_name,
                rec.column_name,
                COALESCE(max_id::text, 'NULL (empty)');

            IF max_id IS NOT NULL THEN
                -- Table has data, set sequence to max_id, mark as 'used' (true)
                PERFORM setval(seq_name, max_id, true);
            ELSE
                -- Table is empty, set sequence to 1, mark as 'not used' (false)
                PERFORM setval(seq_name, 1, false);
            END IF;

        END IF;
    END LOOP;
    RAISE NOTICE 'All sequences checked and synchronized.';
END $$;

[joshtrichards]

Any chance the target database wasn't empty? i.e. from a prior migration attempt.

Also what's the output of the following?

SELECT column_default FROM information_schema.columns WHERE table_name = 'oc_storages' AND column_name = 'numeric_id';

[psych0d0g]

since the migration worked and i was able to get it up and running using my psql fix stated above, i have no mysql reference DB anymore, the target database was a freshly spawned postgresql using Crunchy's PGO operator inside of k8s

[rustyscottweber]

I also have run into this problem, but I was not so lucky in getting my migrated DB working.
What I gather is that a query is being made for the max ID so that the counter can be set.
The query that get's executed is

  LINE 1: ...('nextcloud.oc_circles_circle_id_seq', (SELECT MAX() FROM ))  

However, the query that should be getting executed should be more like :

SELECT MAX(id) FROM nextcloud.oc_circles_circle;

Or.. if you realy want to get fancy and work around tables that don't have any rows to count a max then

SELECT setval(
    'nextcloud.oc_circles_circle_id_seq',
    COALESCE((SELECT MAX(id) FROM nextcloud.oc_circles_circle), 1),
    false
);

It looks like there is a bug causing both the id and the table name to be either null or omitted from the query string.

Here is a more complete stack trace of the error:

  An exception occurred while executing a query: SQLSTATE[42601]: Syntax erro  
  r: 7 ERROR:  syntax error at or near ")"                                     
  LINE 1: ...('nextcloud.oc_circles_circle_id_seq', (SELECT MAX() FROM ))      
                                                                       ^       
                                                                               

Exception trace:
  at /var/www/html/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:62
 Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert() at /var/www/html/3rdparty/doctrine/dbal/src/Connection.php:1976
 Doctrine\DBAL\Connection->handleDriverException() at /var/www/html/3rdparty/doctrine/dbal/src/Connection.php:1918
 Doctrine\DBAL\Connection->convertExceptionDuringQuery() at /var/www/html/3rdparty/doctrine/dbal/src/Connection.php:1111
 Doctrine\DBAL\Connection->executeQuery() at /var/www/html/lib/private/DB/Connection.php:420
 OC\DB\Connection->executeQuery() at /var/www/html/lib/private/DB/PgSqlTools.php:63
 OC\DB\PgSqlTools->resynchronizeDatabaseSequences() at /var/www/html/core/Command/Db/ConvertType.php:411
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/html/core/Command/Db/ConvertType.php:199
 OC\Core\Command\Db\ConvertType->execute() at /var/www/html/3rdparty/symfony/console/Command/Command.php:326
 Symfony\Component\Console\Command\Command->run() at /var/www/html/3rdparty/symfony/console/Application.php:1078
 Symfony\Component\Console\Application->doRunCommand() at /var/www/html/3rdparty/symfony/console/Application.php:324
 Symfony\Component\Console\Application->doRun() at /var/www/html/3rdparty/symfony/console/Application.php:175
 Symfony\Component\Console\Application->run() at /var/www/html/lib/private/Console/Application.php:187
 OC\Console\Application->run() at /var/www/html/console.php:90
 require_once() at /var/www/html/occ:33

In Exception.php line 24:
                                                                           
  [Doctrine\DBAL\Driver\PDO\Exception (7)]                                 
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"     
  LINE 1: ...('nextcloud.oc_circles_circle_id_seq', (SELECT MAX() FROM ))  
                                                                       ^   
                                                                           

Exception trace:
  at /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php:24
 Doctrine\DBAL\Driver\PDO\Exception::new() at /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php:76
 Doctrine\DBAL\Driver\PDO\Connection->query() at /var/www/html/3rdparty/doctrine/dbal/src/Connection.php:1106
 Doctrine\DBAL\Connection->executeQuery() at /var/www/html/lib/private/DB/Connection.php:420
 OC\DB\Connection->executeQuery() at /var/www/html/lib/private/DB/PgSqlTools.php:63
 OC\DB\PgSqlTools->resynchronizeDatabaseSequences() at /var/www/html/core/Command/Db/ConvertType.php:411
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/html/core/Command/Db/ConvertType.php:199
 OC\Core\Command\Db\ConvertType->execute() at /var/www/html/3rdparty/symfony/console/Command/Command.php:326
 Symfony\Component\Console\Command\Command->run() at /var/www/html/3rdparty/symfony/console/Application.php:1078
 Symfony\Component\Console\Application->doRunCommand() at /var/www/html/3rdparty/symfony/console/Application.php:324
 Symfony\Component\Console\Application->doRun() at /var/www/html/3rdparty/symfony/console/Application.php:175
 Symfony\Component\Console\Application->run() at /var/www/html/lib/private/Console/Application.php:187
 OC\Console\Application->run() at /var/www/html/console.php:90
 require_once() at /var/www/html/occ:33

In Connection.php line 71:
                                                                           
  [PDOException (42601)]                                                   
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"     
  LINE 1: ...('nextcloud.oc_circles_circle_id_seq', (SELECT MAX() FROM ))  
                                                                       ^   
                                                                           

Exception trace:
  at /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php:71
 PDO->query() at /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php:71
 Doctrine\DBAL\Driver\PDO\Connection->query() at /var/www/html/3rdparty/doctrine/dbal/src/Connection.php:1106
 Doctrine\DBAL\Connection->executeQuery() at /var/www/html/lib/private/DB/Connection.php:420
 OC\DB\Connection->executeQuery() at /var/www/html/lib/private/DB/PgSqlTools.php:63
 OC\DB\PgSqlTools->resynchronizeDatabaseSequences() at /var/www/html/core/Command/Db/ConvertType.php:411
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/html/core/Command/Db/ConvertType.php:199
 OC\Core\Command\Db\ConvertType->execute() at /var/www/html/3rdparty/symfony/console/Command/Command.php:326
 Symfony\Component\Console\Command\Command->run() at /var/www/html/3rdparty/symfony/console/Application.php:1078
 Symfony\Component\Console\Application->doRunCommand() at /var/www/html/3rdparty/symfony/console/Application.php:324
 Symfony\Component\Console\Application->doRun() at /var/www/html/3rdparty/symfony/console/Application.php:175
 Symfony\Component\Console\Application->run() at /var/www/html/lib/private/Console/Application.php:187
 OC\Console\Application->run() at /var/www/html/console.php:90
 require_once() at /var/www/html/occ:33

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

[rustyscottweber]

Fairly certain that this is the offending code:

		foreach ($conn->createSchemaManager()->listSequences() as $sequence) {
			$sequenceName = $sequence->getName();
			$sqlInfo = 'SELECT table_schema, table_name, column_name
				FROM information_schema.columns
				WHERE column_default = ? AND table_catalog = ?';
			$result = $conn->executeQuery($sqlInfo, [
				"nextval('$sequenceName'::regclass)",
				$databaseName
			]);
			$sequenceInfo = $result->fetchAssociative();
			$result->free();
			/** @var string $tableName */
			$tableName = $sequenceInfo['table_name'];
			/** @var string $columnName */
			$columnName = $sequenceInfo['column_name'];
			$sqlMaxId = "SELECT MAX($columnName) FROM $tableName";
			$sqlSetval = "SELECT setval('$sequenceName', ($sqlMaxId))";
			$conn->executeQuery($sqlSetval);
		}

When I tested the first query, I got back a null set, when I added some additional escape characters, I got back the data I was expecting. Given that there was no data in the second query makes me think that the first query is not returning what it is supposed to.

nextcloud=# SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_default = 'nextval(oc_circles_circle_id_seq::regclass)'
  AND table_catalog = 'nextcloud';
 table_schema | table_name | column_name 
--------------+------------+-------------
(0 rows)

nextcloud=# SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_default = 'nextval(''oc_circles_circle_id_seq''::regclass)'
  AND table_catalog = 'nextcloud';
 table_schema |    table_name     | column_name 
--------------+-------------------+-------------
 nextcloud    | oc_circles_circle | id
(1 row)

[rustyscottweber]

The root cause of this issue is how the query is executed and how the series is formed.
The series does not include the next cloud pre-fix.
so...

nextcloud=# SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_default = 'nextval(''nextcloud.oc_circles_circle_id_seq''::regclass)'
  AND table_catalog = 'nextcloud';
 table_schema | table_name | column_name 
--------------+------------+-------------
(0 rows)

while another query that omits the nextcloud prefix returns what is expected.

nextcloud=# SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_default = 'nextval(''oc_circles_circle_id_seq''::regclass)'
  AND table_catalog = 'nextcloud';
 table_schema |    table_name     | column_name 
--------------+-------------------+-------------
 nextcloud    | oc_circles_circle | id
(1 row)

[rustyscottweber]

Fix being worked on here:
#56575