Recreating Tables Bug - Won't be fixed....

[klh]

So recently we have been transferring everything from an older version of ServiceNow to a new Jakarta instance.

I came across a pretty annoying bug. In summary, when you create a table and realise you did something wrong so delete it, you then can't recreate it properly with the same name as there is a problem with the way the delete works.

The response I got for a HI ticket for this was:

I have completed my investigation to this incident. Here is a summary of my findings;
Issue: For deleted tables that extend configuration item table, recreating them shows erratic behaviour. 
Behaviour 1, when the table is recreated, the field label is not created alongside. The field label now shows as Configuration item rather than the table field label. We can manually add a field label. 
Behaviour 2, on recreation of the table, we do not see the inherited fields on sys_db_object. Navigating to the table though shows the field 
Behaviour 3, Custom Fields created on the table before the deletion are not properly dropped. They do not exist on sys_dictionary but exist on sys_storage_alias and can be added to a list view. 
Most Probable Cause: PRB1189467 - Recreation of custom cmdb_ci extended tables after their deletion fails.
Solution Proposed: After carefully considering the severity and frequency of the issue, and the cost and risk of attempting a fix, it has been decided to not address PRB1189467 in any current or future releases. We do not make this decision lightly, and we apologize for any inconvenience. 
Workaround: The workaround here will be to raise a support incident for us to do a manual cleanup of leftover schema metadata in sys_storage_alias, sys_storage_table_alias, sys_dictionary, and sys_db_object after the delete.
Next steps: As we have a clear path to relief, I will place this incident in a Solution Proposed state. From our last call, I understood you have a number of affected records at this time. I would recommend you take some time to review this information and provide us with a list of all tables and columns currently affected and we will do the cleanup for you as soon as we receive this.

Now correct me if I am wrong, but this isn't the right approach I think. It's a clear bug and the work around involves a manual process within your active instance by ServiceNow. Sure it will fix the issue for us, but we can't be the only people who have encountered this problem, and to say that it won't be addressed in "any current or future releases" doesn't actually inspire me with confidence. What else is lurking under the hood that won't be fixed?

Frankly it's making me worry about doing anything in the platform if something as core as deleting a table isn't handled properly as I can't imagine something like this passing UAT in any systems I have developed.

Does anyone else have any thoughts on this?

[Dave Smith1]

It's a clear bug and the work around involves a manual process within your active instance by ServiceNow. 

When you say "active instance".. do you mean an active developer testbed, or an active production instance?

Frankly it's making me worry about doing anything in the platform if something as core as deleting a table isn't handled properly as I can't imagine something like this passing UAT in any systems I have developed.

UAT normally takes place on a staging system, and is more about exposing failure than proving success.   Generally the final released product will then be transferred over as an Update Set, which may have had several tables recreated/destroyed in its lifetime, but should only be the final schema that's migrated.

I agree that manual cleanup shouldn't be necessary - a deleted object shouldn't influence a recreated object.   But I can see the policy that because the situation usually arises from less-than-diligent change control, the financial cost of fixing the bug outweighs the number of times it should occur so a decision has been left not to address it.

[klh]

Both developer and production. The bug can be reproduced on a new developer instance or in our production, test and dev instances.

It's a pretty fundamental thing, I wouldn't expect a release of MySQL that allowed you to delete a table but that left things there when you did it, sort of floating and acting as blockers to you recreating the table.

Say for example you created table u_test extending cmdb_ci but realised you meant to extend task instead. You can't delete u_test and create a new u_test as the extension doesn't work as no fields are imported and you have to manually recreate your lables as your new table will now be labelled Configuration Item even though it's name is u_test correctly. To be honest if the lables just got orphaned and I just had to recreate those it wouldn't be an issue. It's the extension not working that is the major issue. Especially in a complicated extension model.

This is an issue for us as we are moving some pretty complicated scripting and workflows over from the original instance, which uses the names of the tables, so it would be a massive amount of extra work to find all the references and change them in scripts which otherwise we could happily copy/paste.

I would have responded to your other comment with a link to this topic but I see you found it anyway.

[Dave Smith1]

It's a pretty fundamental thing, I wouldn't expect a release of MySQL that allowed you to delete a table but that left things there when you did it, sort of floating and acting as blockers to you recreating the table.

I wouldn't' expect any release of a RDBMS to work in this way.   But as you've gathered, the issue isn't with MySQL, it's a platform bug - data dictionaries aren't being properly cleaned down thus preventing object reuse.

(having said that, I encountered a similar bug under Cacti with trying to reuse the same RRD file)

Say for example you created table u_test extending cmdb_ci but realised you meant to extend task instead. You can't delete u_test and create a new u_test as the extension doesn't work as no fields are imported and you have to manually recreate your lables as your new table will now be labelled Configuration Item even though it's name is u_test correctly. 

I actually realise the problem isn't just label reuse, it's field reuse, too (looks like I didn't read your problem definition clearly the first time - apologies for that).

This is an issue for us as we are moving some pretty complicated scripting and workflows over from the original instance, which uses the names of the tables, so it would be a massive amount of extra work to find all the references and change them in scripts which otherwise we could happily copy/paste.

If it's simply redundant objects in those tables, just curious how hard it would be to script something that surgically removes just the affected records.   I agree it shouldn't be necessary - it looks like the platform is failing to perform a proper cascade delete (and I'm curious to know how referential integrity is maintained if orphaned foreign keys are possible) but... could this be a workaround?

I would have responded to your other comment with a link to this topic but I see you found it anyway.

Feel free to respond over there with "Dave, you don't fully understand the problem" - because it's clear I didn't when reading that thread.   I do now, based on your experimentation.

[klh]

I wouldn't' expect any release of a RDBMS to work in this way. But as you've gathered, the issue isn't with MySQL, it's a platform bug - data dictionaries aren't being properly cleaned down thus preventing object reuse.

(having said that, I encountered a similar bug under Cacti with trying to reuse the same RRD file)

Sorry I missed out the "for example" in my point about MySQL

When I went through with the ServiceNow help desk on a webex I could see at least some of the orphaned artefacts, which is how they identified the issue on the call. It appears even as admin, you don't have the ability to delete those. This was confirmed as expected behaviour by the ServiceNow rep. It's probably sensible for everyone not to have access to delete on those records, as you might cause some major stability issues if you could remove things from sys_storage_alias. Unfortunately this does put a bit of a damper on any home brew solutions.

What I am a bit more worried about if we could script out a nice cleanup tool, is that we don't quite know if we will catch everything. That would be even if we did have access to delete them.