Sophora UGC Update Notes

UGC Service

Version	Timing	Changes
6.0.0		Starting with this version, the JVM proxy settings will be used automatically for connections. Therefore, the configuration parameters sophora-server.proxyHost, sophora-server.proxyPort, sophora-server.proxyUser, and sophora-server.proxyPassword are no longer necessary as of this version and have been removed.
6.0.0		Starting with this version, UGC only supports PostgreSQL. Support for Oracle and MySQL has been discontinued to streamline database operations. If you are currently using Oracle or MySQL, you must migrate your data before upgrading: Data Migration: Use third party tools like pgloader or DBeaver to move your data to a Postgres database. Please refer to their official documentation for the migration process. Sequence Adjustment: After migration, you must manually update your database sequences (or the sequence table) to match the current MAX(id) of your data. Migration tools often don't sync sequence counters. If not updated, the application will attempt to use IDs that already exist, leading to Primary Key conflicts. To ensure high performance you may need to create new indexes. For more information read the Database — Performance Optimization (Indexes) here. Update your configuration parameter database.url in the application.yml of the UGC Webapp.
6.0.0		The configuration parameter database.oracleDB is no loger recognized and needs to be removed from the application.yml.

UGC Multimedia Service 

Version	Timing	Changes
6.0.0		Starting with this version, UGC only supports PostgreSQL. Support for Oracle and MySQL has been discontinued to streamline database operations (see UGC Service table above). If you are currently using Oracle or MySQL, you must migrate your data before upgrading: Data Migration: Use third party tools like pgloader or DBeaver to move your data to a Postgres database. Please refer to their official documentation for the migration process. Sequence Adjustment: After migration, you must manually update your database sequences (or the sequence table) to match the current MAX(id) of your data. Migration tools often don't sync sequence counters. If not updated, the application will attempt to use IDs that already exist, leading to Primary Key conflicts. To ensure high performance you may need to create new indexes. For more information read the Database — Performance Optimization (Indexes) here. Update your configuration parameters in spring.jpa.hibernate and spring.datasource in the application.yml of the UGC Multimedia Service to use PostgreSQL.

Troubleshooting the PostgreSQL migration

If you encounter issues after migrating your data from MySQL or Oracle to PostgreSQL, check the following common pitfalls:

1. "Resource Not Found" or Empty Results

• Symptom: The application starts, but certain data (like Form Data or Comments) is not displayed, or you see ResourceNotFoundException.
• Reason: This is often caused by Case Sensitivity. If your table or column names were imported with capital letters or double quotes (e.g., "VALUE"), JPA might not find them.
• Solution: Ensure all identifiers are lowercase. Use the Normalization Script provided above to rename tables and columns to lowercase.

2. "Duplicate Key" or "Primary Key Violation" on Save

• Symptom: You can read data, but creating new entries fails with a Unique Constraint Violation.
• Reason: PostgreSQL sequences (which handle the id generation) are not automatically updated during a data import. They often remain at 1, while your data already has IDs up to 5000.
• Solution: Run the Sequence Initialization Script to sync all database sequences with your existing record IDs.

3. "Column is of type integer but expression is of type boolean"

• Symptom: Saving data fails with a PSQLException regarding type mismatch for columns like pinned, spam, or legitimate.
• Reason: MySQL uses TINYINT(1) for booleans, which PostgreSQL imports as integer. JPA, however, expects a real boolean type.
• Solution: Cast these columns to boolean using the Data Type Correction Script: ALTER TABLE usercomment ALTER COLUMN spam TYPE boolean USING (spam::boolean);

SQL scripts after database migration

This section contains scripts that may be required after migrating your MySQL database to PostgreSQL. Please review and customize the scripts before execution.

All column and table names need to be lower case:

DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT table_name, column_name
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND column_name <> lower(column_name)
    LOOP
        EXECUTE 'ALTER TABLE public."' || rec.table_name || '" RENAME COLUMN "' || rec.column_name || '" TO ' || lower(rec.column_name);
    END LOOP;

    FOR rec IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
          AND table_type = 'BASE TABLE'
          AND table_name <> lower(table_name)
    LOOP
        EXECUTE 'ALTER TABLE public."' || rec.table_name || '" RENAME TO ' || lower(rec.table_name);
    END LOOP;
END;
$$;

Creating sequence for all tables and set the value to the highest ID to prevent collisions:

DO $$
DECLARE
    row record;
BEGIN
    FOR row IN 
        SELECT table_name 
        FROM information_schema.columns 
        WHERE table_schema = 'public' 
        AND column_name = 'id' 
        AND table_name != 'databasechangelog' 
    LOOP
        EXECUTE 'CREATE SEQUENCE IF NOT EXISTS ' || row.table_name || '_id_seq';
        
        EXECUTE 'ALTER TABLE ' || row.table_name || ' ALTER COLUMN id SET DEFAULT nextval(''' || row.table_name || '_id_seq'')';
        
        EXECUTE 'SELECT setval(''' || row.table_name || '_id_seq'', (SELECT COALESCE(MAX(id), 0) + 1 FROM ' || row.table_name || '), true)';
        
        EXECUTE 'ALTER TABLE ' || row.table_name || ' ALTER COLUMN id TYPE bigint';
        
    END LOOP;
END $$;

Alter columns so that boolean types are used now:

-- User comments & Uploads
ALTER TABLE usercomment ALTER COLUMN spam TYPE boolean USING (spam::boolean);
ALTER TABLE usercomment ALTER COLUMN pinned TYPE boolean USING (pinned::boolean);
ALTER TABLE imageupload ALTER COLUMN spam TYPE boolean USING (spam::boolean);
ALTER TABLE imageupload ALTER COLUMN pinned TYPE boolean USING (pinned::boolean);

-- Results (Quiz, Voting, Forms)
ALTER TABLE formresult ALTER COLUMN legitimate TYPE boolean USING (legitimate::boolean);
ALTER TABLE quizresult ALTER COLUMN legitimate TYPE boolean USING (legitimate::boolean);
ALTER TABLE votingresult ALTER COLUMN legitimate TYPE boolean USING (legitimate::boolean);