Ads error 1823

Thanks Archie,

Runtime systems was set to 1. In the PLC tab of the twincat system properties, No. 1 was ticked for the boot project and for the Load/store retain data. I deselected the Load/store tab and the system will stop and start now with no problems. Would this be expected?

I now have a soft PLC that I can start/stop and restart. I can upload the program from my remote PC and Login and the PLC is in run mode.

I have added a new ladder routine in the PLC. I called it from another routine. The routine name is not greyed out so it should be getting executed I think. I created 2 bools, Test_In: BOOL; and Test_Out: BOOL;.

I paralled a NO and a NC of Test_In at the start of the rung and put an output at the end Test_Out. The output should have come on but it didnt change. I can force the values usinf ctrl F7. The PLC does not seem to be executing the code.

In the System manager, I disabled all the IO nodes. I have a profibus card in the PC and I will connect up a new node to this for testing. I have the node and IO but do not yet have the KL9010 terminator which I think I need for the configuration to be correct.

Is it expected that the plc would be in run mode but that the program would not be executing?

Thanks for your help so far!

I’m updating a database to correct the foreign key behaviour on update and delete. However, on one table in particular this has been unexpectedly difficult.

On most tables, I’ve been doing the following without any issues:

1. Drop the foreign key constraint
2. Drop the corresponding index
3. Add a new, updated foreign key

One table, however, is causing some trouble I can’t quite understand. Trying to do the process above returns the following error (I’ve redacted the DB name to db_name):

SQLSTATE[HY000]: General error: 1025 Error on rename of 
'./<db_name>/#sql-5e1_1c5' to './<db_name>/doctors' 
(errno: 150 - Foreign key constraint is incorrectly formed)
(SQL: alter table `doctors` drop index `doctors_id_unique`)

The relevant part of the table is as follows, initially

  UNIQUE KEY `doctors_id_unique` (`id`),
  UNIQUE KEY `doctors_code_unique` (`code`),
  KEY `doctors_updated_by_foreign` (`updated_by`),
  CONSTRAINT `doctors_id_foreign` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `doctors_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I want to change id to a primary key and foreign key, so I do the following:

ALTER TABLE doctors DROP FOREIGN KEY doctors_id_foreign;
ALTER TABLE doctors DROP INDEX doctors_code_unique;
ALTER TABLE doctors DROP INDEX doctors_id_unique;

On that 3rd statement, however, I get the following error:

Error on rename of './<db_name>/#sql-5e1_2' to './<db_name>/doctors' 
(errno: 150 - Foreign key constraint is incorrectly formed)

I can’t figure out how to fix that. I try dropping the updated_by constraint and key as well as the code unique key first, but it doesn’t change anything. I try disabling foreign key checks, but no cigar.

I try changing the SQL dump I had been using for tests. Changing it to the following made it work as I had intended, at least apparently:

  PRIMARY KEY (`id`),
  UNIQUE KEY `doctors_code_unique` (`code`),
  KEY `doctors_updated_by_foreign` (`updated_by`),
  CONSTRAINT `doctors_id_foreign` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `doctors_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 

However, I would rather update the table programatically so I can make the migration more easily and reliably. So I try a different approach and just rename the table, copy it sans keys to a new one, and add the keys there:

SET FOREIGN_KEY_CHECKS=0;
RENAME TABLE `doctors` TO `old_doctors`;
CREATE TABLE `doctors` SELECT * FROM `old_doctors`;
ALTER TABLE `doctors` ADD PRIMARY KEY (`id`);
ALTER TABLE `doctors` ADD UNIQUE INDEX `doctors_code_unique` (`code`);
ALTER TABLE `doctors` ADD CONSTRAINT `doctors_id_foreign` FOREIGN KEY `doctors_id_foreign` (`id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `doctors` ADD CONSTRAINT `doctors_updated_by_foreign` FOREIGN KEY `doctors_updated_by_foreign` (`updated_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE SET NULL;
SET FOREIGN_KEY_CHECKS=1;

And now I get the following error:

SQLSTATE[HY000]: General error: 1823 Failed to add the foreign key constraint
'<db_name>/doctors_id_foreign' to system tables (SQL: ALTER TABLE `doctors`
ADD CONSTRAINT `doctors_id_foreign` FOREIGN KEY `doctors_id_foreign` (`id`) 
REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE)

So I just can’t get it to work, and neither error seems to be common: I couldn’t find any good information on how to deal with either of them.

This has been so time-consuming that I’m probably going to put the servers down, dump the DB, fix the dump manually and import the fixed dump back; however, I’d really like to know if this is some bug or if I’m just missing something. Which is highly likely, because I’m hardly an experienced programmer.

For context: this is all on a Laravel (5.7.13) project, but the errors happen both using Laravel migrations (with Schema and Blueprint) and directly through the SQL console. MySQL version 5.7.22.

tl;dr: I just can’t change a table’s foreign key’s behaviour on update/delete, despite doing so successfully on various other tables on the same DB. I tried two vastly different approaches to fixing it, and both returned (distinct) errors, resolutions of which I couldn’t find anywhere. Is it a bug or am I missing something?