DB Convertion from SQLITE to Postgresql

ℹ️ Support
, ,
1

Nextcloud version (eg, 24.0.1): 30.0.8
Talk Server version (eg, 14.0.2): 20.1.5
Custom Signaling server configured: yes, 2.0.2
Custom TURN server configured:yes
Custom STUN server configured: yes

The issue you are facing:
The issue happens when trying to convert the SQLITE DB to MARIADB
command used:
occ db:convert-type --password=“****” --port=“3306” --clear-schema --all-apps mysql {db_user} {db_host} {db_name}

It gives an error:

 - oc_talk_rooms
  0/58 [>---------------------------]   0% < 1 sec/< 1 sec
In ExceptionConverter.php line 67:
                                                                                                                                          
  An exception occurred while executing a query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'last_activity' in 'INSERT INTO'  
                                                                                                                                          
In Exception.php line 24:
                                                                                           
  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'last_activity' in 'INSERT INTO'  
                                                                                           
In Statement.php line 130:
                                                                                           
  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'last_activity' in 'INSERT INTO'  
                                                                                           
db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

So at some point it can’t find a column last_activity and doesn’t finalise the conversion. Tried with postgresql and it’s a similar error.

Does anybody know how to fix it?

2

I’ve tried to convert it to postgresql

and it seems to be even more errors coming up:
errors like these:

  0/58 [>---------------------------]   0% < 1 sec/< 1 sec
In ExceptionConverter.php line 68:
                                                                                                                                                               
  An exception occurred while executing a query: SQLSTATE[42703]: Undefined column: 7 ERROR:  column "object_type" of relation "oc_talk_rooms" does not exist  
  LINE 1: ..."active_guests", "last_activity", "last_message", "object_ty...                                                                                   
                                                               ^                                                                                               
                                                                                                                                                               

In Exception.php line 24:
                                                                                                                
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "object_type" of relation "oc_talk_rooms" does not exist  
  LINE 1: ..."active_guests", "last_activity", "last_message", "object_ty...                                    
                                                               ^                                                
                                                                                                                

In Statement.php line 130:
                                                                                                                
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "object_type" of relation "oc_talk_rooms" does not exist  
  LINE 1: ..."active_guests", "last_activity", "last_message", "object_ty...                                    
                                                               ^                                                
                                                                                                                

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

it seems that adding the missing tables into postgresql may fix it, but i tested on the test-version only, not sure if it is safe to run this in the production:

ALTER TABLE oc_talk_rooms ADD COLUMN last_activity TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE oc_talk_rooms ADD COLUMN last_message BIGINT NULL DEFAULT 0;
ALTER TABLE oc_talk_rooms ADD COLUMN lobby_state INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE oc_talk_rooms ADD COLUMN lobby_timer TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE oc_talk_rooms ADD COLUMN object_type CHARACTER VARYING(64) DEFAULT '' NOT NULL;
ALTER TABLE oc_talk_rooms ADD COLUMN object_id CHARACTER VARYING(64) DEFAULT '' NOT NULL;
ALTER TABLE oc_talk_rooms ADD COLUMN read_only INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE oc_talk_rooms ADD COLUMN assigned_hpb INTEGER DEFAULT NULL;

Is it ok that there are so many tables missing in the newly created postgresql schema by db:convert-type tool?

Is it safe to manually adding those columns, will it not brake the postgresql db integrity?

3

There is a bug report:

In the discussion, they mention a fix that will also be back-ported to NC 30:

(it will be in 30.0.9)

If you do not want to wait and test, you can apply the patch manually:

In this case it is nice to give feedback if it has been working/not working for you.

1 Like
4

Hello!

Thank you a lot for your information. Then we’ll probably wait for the official release of 30.0.9 or 31.0.3.

In a meantime, as a test I manually altered postgresql and it also worked. I used my code like this:

ALTER TABLE oc_talk_rooms
ADD COLUMN last_activity TIMESTAMP NULL DEFAULT NULL,
ADD COLUMN last_message BIGINT NULL DEFAULT 0,
ADD COLUMN lobby_state INTEGER DEFAULT 0 NOT NULL,
ADD COLUMN lobby_timer TIMESTAMP NULL DEFAULT NULL,
ADD COLUMN object_type CHARACTER VARYING(64) DEFAULT '' NULL,
ADD COLUMN object_id CHARACTER VARYING(64) DEFAULT '' NULL,
ADD COLUMN read_only INTEGER DEFAULT 0 NOT NULL,
ADD COLUMN assigned_hpb INTEGER DEFAULT NULL;
1 Like