Running database upgrade script on mysql 8

[Ruben Gaspar]

Hi,

I do know that mysql is not supported but sadly our IT DB service doesnt offer mariadb.

As postgresql is offered by our central IT I wonder if you have a migration path/script(s) to do so?

My second question is related to the upgrade script when moving from 12 to 13 (I am moving from 12.12 to 13.0): _/usr/share/opencast/docs/upgrade/12_to_13/mariadb.sql_

The script doesnt run on mysql natively as it using specific mariadb syntax. I have tried to translated into Mysql jargon [1]. I am kind of confused about this type of statements:

 ALTER TABLE oc_workflow_operation
  DROP FOREIGN KEY IF EXISTS FK_oc_workflow_operation_workflow_id,
  ADD FOREIGN KEY IF NOT EXISTS IX_oc_workflow_operation_workflow_id (`workflow_id`) REFERENCES `oc_workflow` (`id`);

If you dont set a name for the Foreign Key it will be random one (kind of), is not more advice able to name it, for future upgrades? I take for granted that the IX_oc_workflow_operation_workflow_id doesnt exist in fact, which it's my case.

Thank you,

Ruben

[1]

/* CREATE INDEX IF NOT EXISTS IX_oc_aws_asset_mapping_object_key ON oc_aws_asset_mapping (object_key);
*/
set @var=if((SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
            TABLE_SCHEMA = database() AND
            TABLE_NAME        = 'oc_aws_asset_mapping'  AND
INDEX_NAME='IX_oc_aws_asset_mapping_object_key') = 0,
            'ALTER TABLE oc_aws_asset_mapping ADD Index IX_oc_aws_asset_mapping_object_key (object_key)',
            'select "Nothing to be done"');

prepare stmt from @var;
execute stmt;
deallocate prepare stmt;

/* CREATE INDEX IF NOT EXISTS IX_oc_job_argument_id ON oc_job_argument (id);

*/

set @var=if((SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
            TABLE_SCHEMA = database() AND
            TABLE_NAME        = 'oc_job_argument'  AND
INDEX_NAME='IX_oc_job_argument_id') = 0,
            'ALTER TABLE oc_job_argument ADD Index IX_oc_job_argument_id (id)',
            'select "Nothing to be done"');

prepare stmt from @var;
execute stmt;
deallocate prepare stmt;


ALTER TABLE oc_workflow
  MODIFY COLUMN `description` LONGTEXT DEFAULT NULL;

ALTER TABLE oc_workflow_operation
  MODIFY COLUMN `description` LONGTEXT DEFAULT NULL,
  MODIFY COLUMN `if_condition` LONGTEXT DEFAULT NULL;

DROP PROCEDURE IF EXISTS PROC_DROP_FOREIGN_KEY;
DELIMITER $$
CREATE PROCEDURE PROC_DROP_FOREIGN_KEY(IN tableName VARCHAR(128), IN constraintName VARCHAR(128))
BEGIN
IF EXISTS(
SELECT * FROM information_schema.table_constraints
WHERE
table_schema    = DATABASE()     AND
table_name      = tableName      AND
constraint_name = constraintName AND
constraint_type = 'FOREIGN KEY')
THEN
SET @query = CONCAT('ALTER TABLE ', tableName, ' DROP FOREIGN KEY ', constraintName, ';');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
             
END IF;
END$$
DELIMITER ;


/* ALTER TABLE oc_workflow_configuration
  DROP FOREIGN KEY IF EXISTS FK_oc_workflow_configuration_workflow_id,
  ADD FOREIGN KEY IF NOT EXISTS IX_oc_workflow_configuration_workflow_id (`workflow_id`) REFERENCES `oc_workflow` (`id`);
*/

call PROC_DROP_FOREIGN_KEY('oc_workflow_configuration', 'FK_oc_workflow_configuration_workflow_id');
set @var=if((SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
            TABLE_SCHEMA = database() AND
            TABLE_NAME        = 'oc_workflow_configuration'  AND
INDEX_NAME='IX_oc_workflow_configuration_workflow_id') = 0,
            'ALTER TABLE oc_workflow_configuration ADD CONSTRAINT FK_oc_workflow_configuration_workflow_id FOREIGN KEY (`workflow_id`) REFERENCES `oc_workflow` (`id`)',
            'select "Nothing to be done"');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;

/* ALTER TABLE oc_workflow_operation
  DROP FOREIGN KEY IF EXISTS FK_oc_workflow_operation_workflow_id,
  ADD FOREIGN KEY IF NOT EXISTS IX_oc_workflow_operation_workflow_id (`workflow_id`) REFERENCES `oc_workflow` (`id`);
*/
call PROC_DROP_FOREIGN_KEY('oc_workflow_operation', 'FK_oc_workflow_operation_workflow_id');
set @var=if((SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
            TABLE_SCHEMA = database() AND
            TABLE_NAME        = 'oc_workflow_operation'  AND
INDEX_NAME='IX_oc_workflow_operation_workflow_id') = 0,
            'ALTER TABLE oc_workflow_operation ADD CONSTRAINT FK_oc_workflow_operation_workflow_id FOREIGN KEY (`workflow_id`) REFERENCES `oc_workflow` (`id`)',
            'select "Nothing to be done"');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;


/*
ALTER TABLE oc_workflow_operation_configuration
  DROP FOREIGN KEY IF EXISTS cworkflowoperationconfigurationworkflowoperationid,
  ADD FOREIGN KEY IF NOT EXISTS IX_oc_workflow_operation_configuration_workflow_operation_id (`workflow_operation_id`) REFERENCES `oc_workflow_operation` (`id`);
*/
call PROC_DROP_FOREIGN_KEY('oc_workflow_operation_configuration', 'cworkflowoperationconfigurationworkflowoperationid');
set @var=if((SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
            TABLE_SCHEMA = database() AND
            TABLE_NAME        = 'oc_workflow_operation_configuration'  AND
INDEX_NAME='IX_oc_workflow_operation_configuration_workflow_operation_id') = 0,
            'ALTER TABLE oc_workflow_operation_configuration ADD CONSTRAINT cworkflowoperationconfigurationworkflowoperationid FOREIGN KEY (`workflow_operation_id`) REFERENCES `oc_workflow_operation` (`id`)',
            'select "Nothing to be done"');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;



UPDATE oc_workflow_configuration
SET configuration_value = ''
WHERE configuration_value IS NULL;

UPDATE oc_workflow_operation_configuration
SET configuration_value = ''
WHERE configuration_value IS NULL;

[Lars Kiesow]

Hi Ruben,
looking at the patch introducing those statements, the intention is to
align the index names with those now used in the code when
auto-generating the database structure.

For MariaDB that means dropping and then re-creating the foreign keys
with a different name. I'm not sure how the MySQL equivilent would look
like, but I think you should be able to do that in two steps.


For migrating to PostgreSQL, there are no ready to use scripts since
you would need those for every database version. But here is what I
would do:

- Create the target structure in PostgreSQL. Starting up an empty
Opencast and processing a single file will probably already do the
trick (Opencast created the database tables only if it needs them).
- Remove the data from the PostgreSQL database. You can also export
just the structure using “pg_dump --schema-only …” and create a
clean, new database if you want.
- Use mysqldump to dump your current database including all the data
but remove the database and table creation from the script.
- Try importing the database. You might need to do some adjustments to
the SQL file. But that's probably nothing that cannot be easily
solved by a nice regexp.

Best regards,
Lars

[Ruben Gaspar]

hi Lars,

Thank you for your explanations. I am uncertain since when is Maridadb the only/preferred mysql flavor but I see people still in my situation. if someone has hands-on experience doing such move (Mysql to Postgresql), please could she/he comment about the experience. Lars' hints certainly look ok to me.

For the upgrade script, at least from my setup point of view (a instance of opencast since version 9), for example:

/*
ALTER TABLE oc_workflow_operation_configuration
  DROP FOREIGN KEY IF EXISTS cworkflowoperationconfigurationworkflowoperationid,
  ADD FOREIGN KEY IF NOT EXISTS IX_oc_workflow_operation_configuration_workflow_operation_id (`workflow_operation_id`) REFERENCES `oc_workflow_operation` (`id`);
*/

This index: IX_oc_workflow_operation_configuration_workflow_operation_id doesnt exist so it always create the foreing key, but with a name I cant control, at least on Oracle Mysql, but I guess Mariadb does something very similar. This is what I meant it makes harder to do ugprades of the db in the future as those names are not homogeneous. It's just an opinion. 

This is why I translated above as:

call PROC_DROP_FOREIGN_KEY('oc_workflow_operation_configuration', 'cworkflowoperationconfigurationworkflowoperationid');
set @var=if((SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
            TABLE_SCHEMA = database() AND
            TABLE_NAME        = 'oc_workflow_operation_configuration'  AND
INDEX_NAME='IX_oc_workflow_operation_configuration_workflow_operation_id') = 0,
            'ALTER TABLE oc_workflow_operation_configuration ADD CONSTRAINT cworkflowoperationconfigurationworkflowoperationid FOREIGN KEY (`workflow_operation_id`) REFERENCES `oc_workflow_operation` (`id`)',
            'select "Nothing to be done"');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;

Just creating the foreign key with a name I can referred to. May be this is not that important, but for my quietness of spirit could someone confirm that doing this way it's ok. I dont want to have issues on the db side.

Thanks a lot,

Ruben

[Greg Logan]

Hi Ruben,

While I can't say for sure whether your plan is wise or not, I would try very very hard to stick to the same naming scheme as what's used upstream - to the point of spinning up a small test cluster with populated data and then comparing the table structures.  What I see happening here is that you'll be ok with this migration, but then the *next* one (or some other future migration) will have some name change that will be confused, or missed and then you'll end up with a real mess in your database.

We're not doing anything particularly crazy in the database, nor are we planning to, but we unfortunately don't have the resources to pursue more than one or two databases as test targets.

G

--
To unsubscribe from this group and stop receiving emails from it, send an email to users+un...@opencast.org.

[Ruben Gaspar]

hi Greg, totally aligned with that. Not asking here for support for mysql, just trying to divert as little as possible from upstream till I move to some db (pg) supported.

So you think I shall do then to convert below Mariadb statement:

Mariadb:

/*
ALTER TABLE oc_workflow_operation_configuration
  DROP FOREIGN KEY IF EXISTS cworkflowoperationconfigurationworkflowoperationid,
  ADD FOREIGN KEY IF NOT EXISTS IX_oc_workflow_operation_configuration_workflow_operation_id (`workflow_operation_id`) REFERENCES `oc_workflow_operation` (`id`);
*/

into Mysql8:

call PROC_DROP_FOREIGN_KEY('oc_workflow_operation_configuration', 'cworkflowoperationconfigurationworkflowoperationid');
set @var=if((SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
            TABLE_SCHEMA = database() AND
            TABLE_NAME        = 'oc_workflow_operation_configuration'  AND
INDEX_NAME='IX_oc_workflow_operation_configuration_workflow_operation_id') = 0,

            'ALTER TABLE oc_workflow_operation_configuration ADD CONSTRAINT   IX_oc_workflow_operation_configuration_workflow_operation_id  FOREIGN KEY (`workflow_operation_id`) REFERENCES `oc_workflow_operation` (`id`)',

            'select "Nothing to be done"');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;

I think this is what also Lars suggesting, now that I re-read the whole thread. I missed that the name of the index is in fact the foreign key as it should.

Thank you all for you time/support,

Ruben

[Ruben Gaspar]

Just posting my last version of the db migration script if it might help anyone in the same configuration. If you find any issue please let me know.

Thanks for the explanations,

Cheers,

Ruben

[1]

            'ALTER TABLE oc_workflow_configuration ADD CONSTRAINT  IX_oc_workflow_configuration_workflow_id FOREIGN KEY (`workflow_id`) REFERENCES `oc_workflow` (`id`)',

            'select "Nothing to be done"');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;

/* ALTER TABLE oc_workflow_operation
  DROP FOREIGN KEY IF EXISTS FK_oc_workflow_operation_workflow_id,
  ADD FOREIGN KEY IF NOT EXISTS IX_oc_workflow_operation_workflow_id (`workflow_id`) REFERENCES `oc_workflow` (`id`);
*/
call PROC_DROP_FOREIGN_KEY('oc_workflow_operation', 'FK_oc_workflow_operation_workflow_id');
set @var=if((SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
            TABLE_SCHEMA = database() AND
            TABLE_NAME        = 'oc_workflow_operation'  AND
INDEX_NAME='IX_oc_workflow_operation_workflow_id') = 0,

            'ALTER TABLE oc_workflow_operation ADD CONSTRAINT  IX_oc_workflow_operation_workflow_id FOREIGN KEY (`workflow_id`) REFERENCES `oc_workflow` (`id`)',

            'select "Nothing to be done"');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;


/*
ALTER TABLE oc_workflow_operation_configuration
  DROP FOREIGN KEY IF EXISTS cworkflowoperationconfigurationworkflowoperationid,
  ADD FOREIGN KEY IF NOT EXISTS IX_oc_workflow_operation_configuration_workflow_operation_id (`workflow_operation_id`) REFERENCES `oc_workflow_operation` (`id`);
*/
call PROC_DROP_FOREIGN_KEY('oc_workflow_operation_configuration', 'cworkflowoperationconfigurationworkflowoperationid');
set @var=if((SELECT count(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE
            TABLE_SCHEMA = database() AND
            TABLE_NAME        = 'oc_workflow_operation_configuration'  AND
INDEX_NAME='IX_oc_workflow_operation_configuration_workflow_operation_id') = 0,

            'ALTER TABLE oc_workflow_operation_configuration ADD CONSTRAINT IX_oc_workflow_operation_configuration_workflow_operation_id FOREIGN KEY (`workflow_operation_id`) REFERENCES `oc_workflow_operation` (`id`)',

            'select "Nothing to be done"');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;

UPDATE oc_workflow_configuration
SET configuration_value = ''
WHERE configuration_value IS NULL;

UPDATE oc_workflow_operation_configuration
SET configuration_value = ''
WHERE configuration_value IS NULL;