Inconsistency Checks

Implemented in Admin panel -> Data Inconsistencies
repo: source:wacko/admin/module/maint_inconsistencies.php


Issue
WackoWiki uses mysql MyISAM as default storage engine and lacks therfore referential integrity constraints with foreign key.


Approach

  1. show / count mismatches / inconsistencies
  2. delete records
  3. assign records to new user / value

1. without user

1.1. group_member without user

SELECT 
  wacko_group_member.*
FROM
  wacko_group_member
  LEFT OUTER JOIN wacko_user ON (wacko_group_member.user_id = wacko_user.user_id)
WHERE
  wacko_user.user_id IS NULL

-> DELETE

1.2. menu without user

SELECT 
  wacko_menu.*
FROM
  wacko_menu
  LEFT OUTER JOIN wacko_user ON (wacko_menu.user_id = wacko_user.user_id)
WHERE
  wacko_user.user_id IS NULL

-> DELETE

1.3. upload without user

SELECT 
  wacko_upload.*
FROM
  wacko_upload
  LEFT OUTER JOIN `wacko_user` ON (wacko_upload.user_id = `wacko_user`.user_id)
WHERE
  `wacko_user`.`user_id` IS NULL

-> DELETE / assign to new user

1.4. usersettings without user

SELECT 
  wacko_user_setting.*
 
FROM
  wacko_user_setting
  LEFT OUTER JOIN wacko_user ON (wacko_user_setting.user_id = wacko_user.user_id)
WHERE
  wacko_user.user_id IS NULL

-> DELETE

1.5. watches without user

SELECT 
  wacko_watch.*
FROM
  wacko_watch
  left JOIN wacko_user ON (wacko_watch.user_id = wacko_user.user_id)
  WHERE wacko_user.user_id is NULL

-> DELETE

2. without page

2.1. acl without page

SELECT 
  wacko_acl.*
FROM
  wacko_acl
  left JOIN wacko_page ON (wacko_acl.page_id = wacko_page.page_id)
WHERE
  wacko_page.page_id IS NULL

-> DELETE

2.2. category_page without page

SELECT wacko_category_page.*
FROM
  wacko_category_page
  LEFT OUTER JOIN wacko_page ON (wacko_category_page.page_id = wacko_page.page_id)
WHERE
  wacko_page.page_id IS NULL

-> DELETE

2.3. link without page

SELECT wacko_link.*
FROM
  wacko_link
  LEFT OUTER JOIN wacko_page ON (wacko_link.from_page_id = wacko_page.page_id)
WHERE
  wacko_page.page_id IS NULL

-> DELETE

2.4. menu without page

SELECT 
  `wacko_menu`.*
FROM
  `wacko_menu`
  LEFT OUTER JOIN wacko_page ON (`wacko_menu`.page_id = wacko_page.page_id)
WHERE
  wacko_page.page_id IS NULL

-> DELETE

2.5. rating without page

SELECT 
  `wacko_rating`.*
FROM
  `wacko_rating`
  LEFT OUTER JOIN wacko_page ON (`wacko_rating`.page_id = wacko_page.page_id)
WHERE
  wacko_page.page_id IS NULL

-> DELETE

2.6. referrer without page

SELECT 
  wacko_referrer.*
FROM
  wacko_referrer
  LEFT OUTER JOIN wacko_page ON (wacko_referrer.page_id = wacko_page.page_id)
WHERE
  wacko_page.page_id IS NULL

-> DELETE

2.7. upload without page and not global

SELECT 
  wacko_upload.*
FROM
  wacko_upload
  LEFT OUTER JOIN wacko_page ON (wacko_upload.page_id = wacko_page.page_id)
WHERE
  wacko_page.page_id IS NULL AND 
  `wacko_upload`.page_id NOT LIKE 0

-> DELETE

2.8. watch without page

SELECT 
  `wacko_watch`.*
FROM
  `wacko_watch`
  LEFT OUTER JOIN wacko_page ON (`wacko_watch`.page_id = wacko_page.page_id)
WHERE
  wacko_page.page_id IS NULL

-> DELETE