Error in DBUpdate-to-3.3.pl

[p3isys]

Greetings:
I'm upgrading an old instance that's been running for 7 years. The prod box is OTRS 3.0.11 on an out-of-support Ubuntu 10.04 OS. I built a 12.04 downlevel box to begin the upgrades on so as to not move too far from the base. Upgraded successfully to 3.1 and then 3.2. The old MySQL instance had MyISAM as default engine and (thanks Michael Widenius) default charset of latin1 and collation of latin1_swedish_ci, which caused the upgrade to 3.3 to fail at the DBUpdate-to-3.3.pl script.
I built a new instance on Ubuntu 18.04 LTS, current and supported and deployed MariaDB. It comes already with InnoDB as the default storage engine and I set all the default charsets and collations to utf8 and utf8_general_ci instead of utf8mb4, as I was tipped to a tech article on an incompatibility between one of the fields in OTRS and utf8mb4. I then restored the post 3.2 upgrade database. Then I proceeded to do a regular upgrade.
DBUpdate-to-3.3.sql ran clean.

Note: I'm working on a dev box and I make a database dump before and after each upgrade, so I can blow it up as many times as I want and can always roll back with no worries.

After running DBUpdate-to-3.3.sql the resulting DB shows InnoDB for all tables and utf8_general_ci as the collation for all the tables except for the following, which I checked and are explicitly created with utfmb4_unicode_ci collation from the dump script create table statements.
(this is for background info only - none of these tables is involved in the error I'm asking about )
article
pm_activity
pm_activity_dialog
pm_entity
pm_entity_sync
pm_process
pm_transition
pm_transition_action
sessions

Once everything else checked out I attempted once more to run DBUpdate-to-3.3.pl
Steps 1-6 done - no issues.
Steps 8-13 done - no issues (except the Subroutine Load redefined - no need to worry message in 13 of 13).

Back to step 7 - here are results
******************** results start *****************
Step 7 of 13: Updating Queue Standard Template relations table...
Cleaning queue_standard_template table
Creating new Foreign Keys for queue_standard_template table

--- Note: ---
If you have already run this script before then the Foreign Keys are already set and you might see errors regarding 'duplicate key' or 'constrain already exists', that's fine, no need to worry!
---

ALTER TABLE queue_standard_template ADD CONSTRAINT FK_queue_standard_template_standard_template_id_id FOREIGN KEY (standard_template_id) REFERENCES standard_template (id)
ALTER TABLE queue_standard_template ADD CONSTRAINT FK_queue_standard_template_queue_id_id FOREIGN KEY (queue_id) REFERENCES queue (id)
[Mon Feb 18 15:09:28 2019] DBUpdate-to-3.3.pl: DBD::mysql::db do failed: Cannot add or update a child row: a foreign key constraint fails (`otrs33`.`#sql-337c_22`, CONSTRAINT `FK_queue_standard_template_queue_id_id` FOREIGN KEY (`queue_id`) REFERENCES `queue` (`id`)) at /opt/otrs/Kernel/System/DB.pm line 493.
ERROR: OTRS-DBUpdate-to-3.3-10 Perl: 5.26.1 OS: linux Time: Mon Feb 18 15:09:28 2019

Message: Cannot add or update a child row: a foreign key constraint fails (`otrs33`.`#sql-337c_22`, CONSTRAINT `FK_queue_standard_template_queue_id_id` FOREIGN KEY (`queue_id`) REFERENCES `queue` (`id`)), SQL: 'ALTER TABLE queue_standard_template ADD CONSTRAINT FK_queue_standard_template_queue_id_id FOREIGN KEY (queue_id) REFERENCES queue (id)'

Traceback (13267):
Module: main::_AddQueueStandardTemplateForeignKeys (unknown version) Line: 556
Module: scripts/DBUpdate-to-3.3.pl (unknown version) Line: 132

ERROR: OTRS-DBUpdate-to-3.3-10 Perl: 5.26.1 OS: linux Time: Mon Feb 18 15:09:28 2019

Message: Error during execution of 'ALTER TABLE queue_standard_template ADD CONSTRAINT FK_queue_standard_template_queue_id_id FOREIGN KEY (queue_id) REFERENCES queue (id)'!

Traceback (13267):
Module: main::_AddQueueStandardTemplateForeignKeys (unknown version) Line: 559
Module: scripts/DBUpdate-to-3.3.pl (unknown version) Line: 132
done.
******************** results end ********************
I ran Show Create Table queue_standard_template; to see if the FK Constraints took, with the following results:

| queue_standard_template | CREATE TABLE `queue_standard_template` (
`queue_id` int(11) NOT NULL,
`standard_template_id` int(11) NOT NULL,
`create_time` datetime NOT NULL,
`create_by` int(11) NOT NULL,
`change_time` datetime NOT NULL,
`change_by` int(11) NOT NULL,
KEY `FK_queue_standard_response_queue_id_id` (`queue_id`),
KEY `FK_queue_standard_response_standard_response_id_id` (`standard_template_id`),
KEY `FK_queue_standard_response_create_by_id` (`create_by`),
KEY `FK_queue_standard_response_change_by_id` (`change_by`),
CONSTRAINT `FK_queue_standard_template_standard_template_id_id` FOREIGN KEY (`standard_template_id`) REFERENCES `standard_template` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


Just to verify I ran a select statement to return all constraints:

MariaDB [otrs33]> select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
-> from information_schema.KEY_COLUMN_USAGE
-> where TABLE_NAME = 'queue_standard_template';
+----------------------+----------------------------------------------------+------------------------+-----------------------+
| COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_COLUMN_NAME | REFERENCED_TABLE_NAME |
+----------------------+----------------------------------------------------+------------------------+-----------------------+
| standard_template_id | FK_queue_standard_template_standard_template_id_id | id | standard_template |
+----------------------+----------------------------------------------------+------------------------+-----------------------+
1 row in set (0.00 sec)

If I'm reading the results correctly I'm missing one constraint on the queue_id table. The first constraint , FK_queue_standard_template_standard_template_id_id appeared to apply correctly.
But the second constraint here did not apply:
ALTER TABLE queue_standard_template ADD CONSTRAINT FK_queue_standard_template_queue_id_id FOREIGN KEY (queue_id) REFERENCES queue (id)

If correctly applied the results should read (I think at least - please correct me if I'm wrong):
----------------------+----------------------------------------------------+------------------------+-----------------------+
| COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_COLUMN_NAME | REFERENCED_TABLE_NAME |
+----------------------+----------------------------------------------------+------------------------+-----------------------+
| standard_template_id | FK_queue_standard_template_standard_template_id_id | id | standard_template |
+----------------------+----------------------------------------------------+------------------------+-----------------------+
| standard_template_id | FK_queue_standard_template_queue_id_id | id | queue |
+----------------------+----------------------------------------------------+------------------------+-----------------------+

Bottom line:
It looks like I either need to simply add the second constraint or I need to add the Key FK_queue_id and then add the constraint, which means I can fix the issue with one or two SQL statements.
Is this correct? If so could I get some help with fixing this one missing constraint - because that seems to be only thing wrong with the upgraded database. Thanks in advance for help.
The end goal is to continue the upgrade process to OTRS 4, 5, then 6 and get everything up to date.
Kind regards,
Rob Mitchell

[skullz]

3.0.11 -> 3.1.x -> 3.2.x -> 3.3.x -> 4.0.x -> 5.0.x ->6.0.x

[p3isys]

Yes, I understand that. I already upgraded from 3.011>3.1, then 3.1 to 3.2 successfully, as I stated in my post.
On each of those two upgrades I also got my ITSM and other packages updated successfully, and used the Support Assessment applet in the Admin page to make sure I was green all the way across the board before going forward. At each stage I have a backup of the opt/otrs directory and the database.

This error is from 3.2>3.3

[crythias]

https://stackoverflow.com/questions/500 ... s/39679270

Paraphrasing from the most popular answer:
You're getting this error because you're trying to add/update a row to #sql-337c_22 (maybe a temp table) that does not have a valid value for the id field based on the values currently stored in queue.

Basically, there's an expected queue.id or queue_standard_template.queue_id that the foreign key constraint can't match up. Typically this would be a missing or deleted queue.id

The original queues:
https://github.com/OTRS/otrs/blob/e3b1c ... l.sql#L236

Example queue_standard_template:
https://github.com/OTRS/otrs/blob/e3b1c ... l.sql#L270