Unknown column 'system_type' in oc_adopter_registration

[mh-bht]

I'm wondering when the system_type column was added to the oc_adopter_registration table. I did not originally set up my OC instance, but I updated it from version 9 to version 19, and no SQL migration script added this column during this time. Perhaps the column has always been missing from my table. I see in the code that it has been used since OC 16.
Is there a way to view the full database schema for the latest version of Opencast? I can't find anything like it.

It currently the table looks like this:
CREATE TABLE oc_adopter_registration (
adopter_key varchar(64) NOT NULL,
agreed_to_policy tinyint(1) DEFAULT 0,
allows_error_reports tinyint(1) DEFAULT 0,
allows_statistics tinyint(1) DEFAULT 0,
city varchar(255) DEFAULT NULL,
contact_me tinyint(1) DEFAULT 0,
country varchar(255) DEFAULT NULL,
created datetime NOT NULL,
last_modified datetime NOT NULL,
department varchar(255) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
first_name varchar(255) DEFAULT NULL,
last_name varchar(255) DEFAULT NULL,
organisation varchar(255) DEFAULT NULL,
postal_code varchar(255) DEFAULT NULL,
registered tinyint(1) DEFAULT 0,
statistic_key varchar(255) DEFAULT NULL,
street varchar(255) DEFAULT NULL,
street_no varchar(255) DEFAULT NULL,
delete_me tinyint(1) DEFAULT 0,
terms_version_agreed int(11) DEFAULT 0,
PRIMARY KEY (adopter_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

[mtneug]

The schema is currently automatically generated if not available via EclipseLink (our JPA implementation). You could start a new (local) Opencast instance with an empty database to see which tables are generated. Keep in mind that tables are only generated when they are used. If certain codepaths are not used, those tables are not necessarily available. So, maybe also upload a test video to make sure commonly used tables are generated.

[gregorydlogan]

Looks like we missed adding the relevant column to the table with OC 16.

From a fresh 19.x build:

MariaDB [opencast]> describe oc_adopter_registration;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| adopter_key          | varchar(64)  | NO   | PRI | NULL    |       |
| agreed_to_policy     | tinyint(1)   | YES  |     | 0       |       |
| allows_error_reports | tinyint(1)   | YES  |     | 0       |       |
| allows_statistics    | tinyint(1)   | YES  |     | 0       |       |
| city                 | varchar(255) | YES  |     | NULL    |       |
| contact_me           | tinyint(1)   | YES  |     | 0       |       |
| country              | varchar(255) | YES  |     | NULL    |       |
| created              | datetime     | NO   |     | NULL    |       |
| last_modified        | datetime     | NO   |     | NULL    |       |
| delete_me            | tinyint(1)   | YES  |     | 0       |       |
| department           | varchar(255) | YES  |     | NULL    |       |
| email                | varchar(255) | YES  |     | NULL    |       |
| first_name           | varchar(255) | YES  |     | NULL    |       |
| last_name            | varchar(255) | YES  |     | NULL    |       |
| organisation         | varchar(255) | YES  |     | NULL    |       |
| postal_code          | varchar(255) | YES  |     | NULL    |       |
| registered           | tinyint(1)   | YES  |     | 0       |       |
| statistic_key        | varchar(255) | YES  |     | NULL    |       |
| street               | varchar(255) | YES  |     | NULL    |       |
| street_no            | varchar(255) | YES  |     | NULL    |       |
| system_type          | varchar(255) | YES  |     | NULL    |       |
| terms_version_agreed | int(11)      | YES  |     | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+

@mh-bht you can either add the column on your own, or delete the table and restart Opencast. The restart will re-trigger the registration process, but you will have a clean database. As long as you are only removing that table you should be fine.

[mh-bht]

Thanks! I discovered that my opencast database user, in combination with the host, lacked the “ALTER” privilege—otherwise, Opencast would have added the column itself...