Queue DB Index Creation manual procedure

[fiskaltrust-ckr]

Get a DB Procedure or script where Index creation (MW 1.3.37 change) can be run manually

[volllly]

Hey 😁, you can find that in the MW MySQL migrations https://github.com/fiskaltrust/middleware/blob/main/queue/src/fiskaltrust.Middleware.Storage.MySQL/Migrations/005_Indices.mysql

ALTER TABLE ftQueueItem MODIFY cbReceiptReference VARCHAR(450);
CREATE INDEX idx_ftQueueItem_Timestamp ON ftQueueItem (TimeStamp);
CREATE INDEX idx_ftQueueItem_cbReceiptReference ON ftQueueItem (cbReceiptReference);
CREATE INDEX idx_ftActionJournal_Timestamp ON ftActionJournal (TimeStamp);
CREATE INDEX idx_ftReceiptJournal_Timestamp ON ftReceiptJournal (TimeStamp);
CREATE INDEX idx_ftJournalAT_Timestamp ON ftJournalAT (TimeStamp);
CREATE INDEX idx_ftJournalDE_Timestamp ON ftJournalDE (TimeStamp);
CREATE INDEX idx_ftJournalFR_Timestamp ON ftJournalFR (TimeStamp);

[StefanKert]

Do we have any insights on the time it takes to execute the first statement?

ALTER TABLE ftQueueItem MODIFY cbReceiptReference VARCHAR(450);

And another question:

Are we able to perform this migration even though the indices already exist? We are thinking about executing this script in a bulk job to performance these updates without having to start the middleware and the question is if we can then perform the migration with everything already being done.

[volllly]

No that is not (easily) possible as mysql does not support the if not exists syntax 🥲

but when you include this in the script SET SQL_SAFE_UPDATES = 0;UPDATE ftDatabaseSchema SET CurrentVersion = "005_Indices";SET SQL_SAFE_UPDATES = 1; the migrator will not attempt the index creation on queue startup again. (I have not tried this yet. also you should probably check beforehand if the 004 migrations are alerady performed or they will be skipped after setting the version to 005)

[volllly]

Hey, so mysql supports index creation without downtime since v5.6 https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-syntax-notes

The table remains available for read and write operations while the index is being created. The [CREATE INDEX](https://dev.mysql.com/doc/refman/8.0/en/create-index.html) statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.

Here you can find the SQL script which creates the indexes for a Database.

You will need to run this on all your affected databases. The database name is the queueid in lowercaps with dashes - removed.

[volllly]

Here you can find the script to verify the indexes were created successfully.

The result of this script should be a row with the following data:

ftQueueItem_cbReceiptReference_dataType	idx_ftQueueItem_Timestamp	idx_ftQueueItem_cbReceiptReference	idx_ftActionJournal_Timestamp	idx_ftReceiptJournal_Timestamp	idx_ftJournalAT_Timestamp	idx_ftJournalDE_Timestamp	idx_ftJournalFR_Timestamp	ftDatabase_schema_version
1	1	1	1	1	1	1	1	1

If theres a 0 in any of the columns this means something went wrong.