Bug #1891
quasselcore: migration sqlite > postgresql failed
0%
Description
I've got it running without the migration. I spent quite a bit of time on it and the errors I'm seeing tend to make me think that they're related to a compatibility issue with the version of postgresql and the script because they appear [to me] to be sql syntax errors.
$ quasselcore --version; psql --version; neofetch --fastfetch=off
quassel v0.15-pre (unknown revision)
psql (PostgreSQL) 16.4 (Ubuntu 16.4-1)
Rhino Linux rolling release based on
OS: Rhino Linux 2024.2~RC (server) x86_64
Host: Gigabyte Technology Co., Ltd. Z77X-UD3H
Kernel: Linux 6.10.4-061004-generic
Uptime: 1d 2h 3m
Shell: bash 5.2.32
This is what the errors look like when I captured them at the start of trying to fix it and while I would not swear in the name of the Flying Spaghetti Monster that the errors were exactly the same the second snippet I was able to find [2×below] sure looks mostly alike... just hours later
$ quasselcore --configdir=/var/lib/quassel --select-backend=PostgreSQL
2024-08-29 11:42:41 [Warn ] SslServer: Failed to open certificate file /var/lib/quassel/quasselCert.pem error: 5
2024-08-29 11:42:41 [Warn ] SslServer: Unable to set certificate file
Quassel Core will still work, but cannot provide SSL for client connections.
Please see https://quassel-irc.org/faq/cert to learn how to enable SSL support.
2024-08-29 11:42:41 [Warn ] SslServer: Failed to open certificate file /var/lib/quassel/quasselCert.pem error: 5
2024-08-29 11:42:41 [Warn ] No storage backend selected!
Default values are in brackets
Username [quassel]:
Password []:
Hostname [localhost]:
Port [5432]:
Database [quassel]:
2024-08-29 11:42:47 [Error] unhandled Error in QSqlQuery!
2024-08-29 11:42:47 [Error] last Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2024-08-29 11:42:47 [Error] executed Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2024-08-29 11:42:47 [Error] bound Values:
2024-08-29 11:42:47 [Error] Error Code: 42601
2024-08-29 11:42:47 [Error] Error Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601) QPSQL: Unable to create query
2024-08-29 11:42:47 [Error] Driver Message: QPSQL: Unable to create query
2024-08-29 11:42:47 [Error] DB Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601)
2024-08-29 11:42:47 [Error] unhandled Error in QSqlQuery!
2024-08-29 11:42:47 [Error] last Query:
SELECT MAX(version) FROM coreinfo
2024-08-29 11:42:47 [Error] executed Query:
SELECT MAX(version) FROM coreinfo
2024-08-29 11:42:47 [Error] bound Values:
2024-08-29 11:42:47 [Error] Error Code: 42601
2024-08-29 11:42:47 [Error] Error Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601) QPSQL: Unable to create query
2024-08-29 11:42:47 [Error] Driver Message: QPSQL: Unable to create query
2024-08-29 11:42:47 [Error] DB Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601)
2024-08-29 11:42:47 [Error] Storage Schema is missing!
2024-08-29 11:42:47 [Info ] PostgreSQL storage backend is ready. Schema version: 31
2024-08-29 11:42:47 [Error] Could not save backend settings, probably a permission problem.
2024-08-29 11:42:47 [Warn ] Switched storage backend to: PostgreSQL
2024-08-29 11:42:47 [Warn ] No currently active storage backend. Skipping migration...
2024-08-29 11:42:47 [Warn ] New storage backend does not support migration: PostgreSQL
Add a new user:
Username: ...
⋮
024-08-29 14:38:57 [Error] unhandled Error in QSqlQuery!
2024-08-29 14:38:57 [Error] last Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2024-08-29 14:38:57 [Error] executed Query:
SELECT value FROM coreinfo WHERE key = 'schemaversion'
2024-08-29 14:38:57 [Error] bound Values:
2024-08-29 14:38:57 [Error] Error Code: 42601
2024-08-29 14:38:57 [Error] Error Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601) QPSQL: Unable to create query
2024-08-29 14:38:57 [Error] Driver Message: QPSQL: Unable to create query
2024-08-29 14:38:57 [Error] DB Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601)
2024-08-29 14:38:57 [Error] unhandled Error in QSqlQuery!
2024-08-29 14:38:57 [Error] last Query:
SELECT MAX(version) FROM coreinfo
2024-08-29 14:38:57 [Error] executed Query:
SELECT MAX(version) FROM coreinfo
2024-08-29 14:38:57 [Error] bound Values:
2024-08-29 14:38:57 [Error] Error Code: 42601
2024-08-29 14:38:57 [Error] Error Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601) QPSQL: Unable to create query
2024-08-29 14:38:57 [Error] Driver Message: QPSQL: Unable to create query
2024-08-29 14:38:57 [Error] DB Message: ERROR: syntax error at end of input
LINE 1: EXECUTE
^
(42601)
History
#1 Updated by iconoclasthero 4 months ago
I don't remember where I left off with this.
As of now, it seems that quasselcore is storing data in the postgresql table I created since the last modificaiton of that table was moments ago.
I have not been able to convert the old messages I had in the sqlite table to postgresql and I've largely given up on that [for now], however I'd eventually like to migrate to that.
#2 Updated by dona168lee 5 days ago
iconoclasthero wrote:
I've got it running without the migration. I spent quite a bit of time on it and the errors I'm seeing tend to make me think that they're related to a compatibility issue with the version of postgresql and the script because they appear [to me] to be sql syntax errors.
[...]
This is what the errors look like when I captured them at the start of trying to fix it and while I would not swear in the name of the Flying Spaghetti Monster that the errors were exactly the same the second snippet I was able to find [2×below] sure looks mostly alike... just hours later
[...]
[...]
you're facing SQL syntax errors during the migration from SQLite to PostgreSQL for QuasselCore. These errors often occur due to differences in SQL syntax between the two databases. Here are some steps you can take to troubleshoot and resolve the issue:
Possible Solutions:
Check PostgreSQL Version Compatibility: Ensure that the version of PostgreSQL you are using is compatible with the version of QuasselCore. Upgrading or downgrading PostgreSQL might help resolve compatibility issues.
Review and Modify Migration Scripts:
Review the migration script for any SQL syntax that may not be compatible with PostgreSQL.
Modify the script to adhere to PostgreSQL syntax. Common issues include differences in how data types, constraints, and functions are handled.
Schema Initialization:
Ensure that the PostgreSQL database schema is initialized correctly.
Check if there are any missing tables or columns that should be present before running the migration.
Enable Detailed Logging:
Enable detailed logging for PostgreSQL and QuasselCore to capture more information about https://www.hmfusafinance.com the errors.
This can help identify the exact location and cause of the syntax errors.