Database corruption

The Problem

An inconsistent database is rare when using MDriven Server and Turnkey.

However, it can happen. If it happens, it's almost always because of an evolve that went wrong somehow and objects were left behind.

Link-classes with incorrect link information are especially problematic. Because link-objects only exist when two connected objects exist, they can't be loaded into memory if the linked objects are missing.

This needs to be corrected at the SQL server level.

As MDriven users, we don't want to spend any time on SQL-related things; to create the SQL that looks up problems, we use the model debugger on the model itself.

Start the model debugger (right-click model surface -> Extras -> Open model debugger...) and run the following OCL.

(Note: The generated OCL does not work for child-mapped classes right away. You need to manually add the class names of the child-mapped classes instead of the parent class, usually an abstract class.)

How to Correct the Problem

For link-classes

Association.allInstances->
  select(a|a.Class.notNull and a.Class.isPersistent)->
    collect(a|a.Class.Name,
      let ae = a.AssociationEnd->at(1) in
        let c1 = ae.Participant in
          if c1.TaggedValue->exists(tv | (tv.Tag='Eco.TableMapping') and (tv.Value = 'Parent')) then
            c1.Superclass.Name
          else
            c1.Name
          endif->collect(classname|if classname='User' then 'User_TBL' else classname endif)->first,
      let ae = a.AssociationEnd->at(1) in
        ae.Name,
      let ae = a.AssociationEnd->at(1) in
        ae.TaggedValue->select(tv | tv.Tag='Eco.InnerLinkName')->first.Value,
      let ae = a.AssociationEnd->at(2) in
        let c1 = ae.Participant in
          if c1.TaggedValue->exists(tv | (tv.Tag='Eco.TableMapping') and (tv.Value = 'Parent')) then
            c1.Superclass.Name
          else
            c1.Name
          endif->collect(classname|if classname='User' then 'User_TBL' else classname endif)->first,
      let ae = a.AssociationEnd->at(2) in
        ae.Name,
      let ae = a.AssociationEnd->at(2) in
        ae.TaggedValue->select(tv | tv.Tag='Eco.InnerLinkName')->first.Value
)->orderBy(x|x.Name)->
collect(d | 
'select * from ' + d.Name + ' where ' + if d.Part4.isNullOrEmpty then d.Part3 else d.Part4 endif + 'ID not in (select ' + d.Part2 + 'ID from ' + d.Part2 + ')\r\n'+
'select * from ' + d.Name + ' where ' + if d.Part7.isNullOrEmpty then d.Part6 else d.Part7 endif + 'ID not in (select ' + d.Part5 + 'ID from ' + d.Part5 + ')'
)->asSeparatedList('\r\n')
Running SQL

The OCL above will produce a long string similar to this for every link class in your model:

select * from SysUserRole where SysUserID not in (select SysUserID from SysUser)
select * from SysUserRole where SysRoleID not in (select SysRoleID from SysRole)

Which simply looks up any link from a link class to non-existent objects.

To correct the problem, change select * to delete and run the query again. Be careful!!!

For Single-links

AssociationEnd.allinstances->
  select(ae|ae.IsSingleLink and ae.Association.IsPersistent and ae.Association.Class.isNull and
  not ae.Association.TaggedValue->exists(tv | (tv.Tag='derived') and (tv.Value = 'True')) and
  not ae.TaggedValue->exists(tv | (tv.Tag='Eco.Embed') and (tv.Value = 'False')) and
  ae.Participant.IsPersistent and
  ae.OtherEnd.Participant.IsPersistent)->
    collect(ae|
      let c1 = ae.OtherEnd.Participant in
      if c1.TaggedValue->exists(tv | (tv.Tag='Eco.TableMapping') and (tv.Value = 'Parent')) then
        c1.Superclass.Name
      else
        c1.Name
      endif->collect(classname|if classname='User' then 'User_TBL' else classname endif)
           ->collect(classname|if classname='Group' then 'Group_TBL' else classname endif)->first.replace('-', '_'),
      ae.Name.replace('-', '_'),
      let c1 = ae.Participant in
        if c1.TaggedValue->exists(tv | (tv.Tag='Eco.TableMapping') and (tv.Value = 'Parent')) then
          c1.Superclass.Name
        else
          c1.Name
      endif->collect(classname|if classname='User' then 'User_TBL' else classname endif)
           ->collect(classname|if classname='Group' then 'Group_TBL' else classname endif)->first.replace('-', '_')
    )->orderBy(x|x.Part1)
     ->collect(d | 'select * from ' + d.Part1 + ' where ' + d.Part2 + 'ID is not null and ' + d.Part2 + 'ID not in (select ' + d.Part3 + 'ID from ' + d.Part3 + ')')
     ->asSeparatedList('\r\n')
Running SQL

The OCL above will produce a long string similar to this for every link class in your model:

select * from SysUserRole where SysUserID not in (select SysUserID from SysUser)
select * from SysUserRole where SysRoleID not in (select SysRoleID from SysRole)

Which simply looks up any a single-link from an object to non-existent objects.

To correct the problem, change select * to update <table-name> set <link name>=null and run the query again. Be careful!!!

Tags

Check database error, Database validation, Generate script for database check

This page was edited 63 days ago on 03/26/2024. What links here