Thanks Roy Kaldung for answer directly. I won't take too much of your
time. Once again, nothing I'm doing here is urgent - the production
instance is fine; 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.
I got the instance upgraded to 3.2 successfully Saturday night. On Sunday
I attempted an upgrade to 3.3 on the same Ubuntu 12.04 box I set up and the
database update failed on constraint creation. The problems on the old box
were a mix of InnoDB and MyIsam storage on different tables, and different
charsets and collations, some of the old latin1_swedish_ci.
So I set up another box with Ubuntu 18.04LTS with MariaDB on it, set InnoDB
as the default storage engine, utf8 as charset for client, connection,
database, results, server, and system, and utf8_general_ci as collation
for connection, database, and server, all verified by showing the variables.
I then restored the post 2.2 upgrade database. Then I proceeded to do a
regular upgrade.
DBUpdate-to-3.3.sql ran clean.
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 info only - None of these tables are involved in the error in
step 7)
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:
----------------------+----------------------------------------------------+------------------------+-----------------------+
| 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.
if this is correct, all I should need is the proper syntax of the one or
two statements, then the database should be good to go.
Kind regards,
Rob Mitchell
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/mailman/listinfo/otrs
