Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.17
-
Ubuntu 22.04
Description
Have some PRI keys which are created as int which must be changed to bigint.
on 10.4.24 I could just disable foreign_key_checks but this no longer works as it is described else where MDEV-31086
table:
| documententries | CREATE TABLE `documententries` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`entryuuid` varchar(64) NOT NULL,
|
`logicaluuid` varchar(64) NOT NULL,
|
`version` int(11) NOT NULL,
|
`patientid` varchar(32) NOT NULL,
|
`patientid_assigningauthorityid` varchar(32) NOT NULL,
|
`patientid_assigningauthoritytype` varchar(16) NOT NULL,
|
`availabilitystatus` varchar(64) NOT NULL,
|
`documententrytype` varchar(64) NOT NULL,
|
`uniqueid` varchar(128) NOT NULL,
|
`classcode_codename` varchar(64) NOT NULL,
|
`classcode_schemename` varchar(64) NOT NULL,
|
`typecode_codename` varchar(64) NOT NULL,
|
`typecode_schemename` varchar(64) NOT NULL,
|
`practicesettingcode_codename` varchar(64) DEFAULT NULL,
|
`practicesettingcode_schemename` varchar(64) DEFAULT NULL,
|
`creationtime` datetime(6) DEFAULT NULL,
|
`servicestarttime` datetime(6) DEFAULT NULL,
|
`servicestoptime` datetime(6) DEFAULT NULL,
|
`healthcarefacilitytypecode_codename` varchar(64) NOT NULL,
|
`healthcarefacilitytypecode_schemename` varchar(64) NOT NULL,
|
`formatcode_codename` varchar(64) NOT NULL,
|
`formatcode_schemename` varchar(64) NOT NULL,
|
`documententrycontentid` int(11) NOT NULL,
|
`creation_time` datetime(6) NOT NULL DEFAULT current_timestamp(6),
|
`last_update_time` datetime(6) DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
|
`migration_pid` int(11) NOT NULL DEFAULT 0,
|
`creationtime_upper` datetime(6) DEFAULT NULL,
|
`servicestarttime_upper` datetime(6) DEFAULT NULL,
|
`servicestoptime_upper` datetime(6) DEFAULT NULL,
|
`deletetrigger_time` datetime(6) GENERATED ALWAYS AS (coalesce(`servicestarttime`,`creation_time`)) STORED,
|
`repository_uniqueid` varchar(64) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `documententrycontentid` (`documententrycontentid`),
|
UNIQUE KEY `documententry_entryuuid_uk` (`entryuuid`),
|
KEY `documententries_patientid_idx` (`patientid`,`patientid_assigningauthorityid`,`patientid_assigningauthoritytype`,`id`),
|
KEY `documententries_uniqueid_idx` (`uniqueid`,`id`),
|
KEY `documententries_entryuuid_idx` (`entryuuid`,`id`),
|
KEY `documententries_logicaluuid_idx` (`logicaluuid`),
|
KEY `documententries_patientid_availabilitystatus_idx` (`patientid`,`patientid_assigningauthorityid`,`patientid_assigningauthoritytype`,`availabilitystatus`,`id`),
|
KEY `documententries_type_code_type_scheme_deletetrigger` (`typecode_codename`,`typecode_schemename`,`deletetrigger_time`),
|
CONSTRAINT `documententry_fk_content` FOREIGN KEY (`documententrycontentid`) REFERENCES `documententrycontent` (`id`) ON DELETE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=70400 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
|
MariaDB [SOLOnxrg]> ALTER TABLE documententries
|
-> DROP CONSTRAINT documententry_fk_content,
|
-> MODIFY COLUMN id BIGINT(11) NOT NULL AUTO_INCREMENT,
|
-> MODIFY COLUMN documententrycontentid BIGINT(11) NOT NULL;
|
Query OK, 26235 rows affected (0.532 sec)
|
Records: 26235 Duplicates: 0 Warnings: 0
|
 |
MariaDB [SOLOnxrg]> ALTER TABLE documententrycontent
|
-> MODIFY COLUMN id BIGINT(11) NOT NULL AUTO_INCREMENT;
|
ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint 'documententry_fk_content' of table 'SOLOnxrg.documententries'
|
For some reason it is not dropped the first time on table documententries.
Dropping it alone without any modify it actually drops it and I can perform the MODIFY.
MariaDB [SOLOnxrg]> ALTER TABLE documententries
|
-> DROP CONSTRAINT documententry_fk_content;
|
Query OK, 0 rows affected (0.013 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
 |
MariaDB [SOLOnxrg]> ALTER TABLE documententrycontent
|
-> MODIFY COLUMN id BIGINT(11) NOT NULL AUTO_INCREMENT;
|
Query OK, 26235 rows affected (0.464 sec)
|
Records: 26235 Duplicates: 0 Warnings: 0
|
 |
MariaDB [SOLOnxrg]> ALTER TABLE documententries ADD CONSTRAINT `documententry_fk_content` FOREIGN KEY (`documententrycontentid`) REFERENCES `documententrycontent` (`id`) ON DELETE CASCADE;
|
Query OK, 0 rows affected (0.009 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
Attachments
Issue Links
- relates to
-
-
- Closed
-