Bug#1053757: migration from sqlite to postgresql 15 failed

[Harald Dunkel]

Hi Thomas,

sorry for the late response. Somehow this went out of focus.

I have backported your 2.5.0 package too bookworm. After running
the new xca the sqlite db was updated, next I exported it using
sqlite3, but on importing it psql complained about the first line
in the dump file again:

ERROR:  syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=OFF;
^
BEGIN
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
:


Finally (after about 400 lines) it failed with a syntax error:

:
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR:  syntax error at or near ")"
LINE 1: ...0190307103908Z',replace('obsolete\n','\n',char(10)),2507,1);
 ^
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
:

I am not sure if xca is to blame here. The complete line looks like this:


INSERT INTO items 
VALUES(399,'old_strongSwan_IPsec_client',5,5,'20190307103908Z',replace('obsolete\n','\n',char(10)),2507,1);

There are tons of lines with

replace('sometext\n','\n',char(10))

I wonder how these newlines came into the database at all?


Regards

Harri

Bug#1053757: migration from sqlite to postgresql 15 failed

[Harald Dunkel]

Package: xca
Version: 2.4.0-2+b1

Migrating from sqlite to postgresql 15 (as described on
https://hohnstaedt.de/xca/index.php/documentation/remote-databases)
fails. The first line in the sqlite3 dump "PRAGMA foreign_keys=OFF;"
is not accepted on import. After removing the line (similar to the
mysql procedure) the import fails with

:
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR:  syntax error at or near ")"
LINE 1: ...0190307103908Z',replace('obsolete\n','\n',char(10)),2507,1);
  ^
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
ERROR:  current transaction is aborted, commands ignored until end of 
transaction block
:


This is fatal. My sqlite database got pretty huge. I'd love to move to
postgresql 15 to improve performance.

BTW, xca 2.5.0 was released a few days ago.


Regards

Harri