Steps

When a wiki misbehaves in a way that looks like damaged data rather than a slow query, a sanity check tells you whether the database holds rows XWiki itself could not have written: an object whose page was deleted, a property in the table of the wrong type, a translation with no original. It is a read-only report and it repairs nothing, so it is the first step of an investigation, not a fix; for how XWiki stores its data see Store.

1. Take the script for your database. Each one is a long list of SELECT statements, none of which should return a row.
   • sanitycheck.sql for MySQL and MariaDB, also reported to work on MS-SQL.
   • sanitycheck-postgresql.sql for PostgreSQL.

   Read the comment block at the top of whichever you downloaded before running it. It records the XWiki version the script was last checked against and the three checks whose result needs interpreting on a current instance.

2. Run it as the wiki's own database user, against the wiki's database. Nothing is written, so it is safe on a running instance, though on a large wiki it is a long sequence of full table scans and is better run when the wiki is quiet.

   mysql -u xwiki -p xwiki < sanitycheck.sql

   On PostgreSQL, pass -a. The script labels its checks with SQL comments rather than with SELECT statements, so without that flag psql prints a column of anonymous result sets and nothing saying which check produced which.

   psql -U xwiki -d xwiki -a -f sanitycheck-postgresql.sql

3. Read the output against the three checks that need interpreting rather than acting on every row. Two of them report on a healthy instance, and the third depends on a setting.

   Check	What a row means today
   attachment with no attachment content and attachment with no attachment archive	Nothing, on a default instance. Since XWiki 10.5 attachment content and its history live on the filesystem, so these two tables are empty by design and the checks select every attachment the wiki has. They only mean something where xwiki.store.attachment.hint is set to hibernate.
   bad characters in document name	Little, unless your wiki restricts page names. The check predates the Name Strategy feature and flags ?, +, &, # and a trailing space, none of which XWiki forbids; the standard flavor forbids only the backslash and the slash, and removes them silently.
   similar document with different identifier	A genuine duplicate: two rows for the same reference and language. These are worth investigating.

4. Decide what the result means. A clean run prints the labels and no rows under them, which is the outcome to expect on a healthy wiki.

   XWIKI SANITY CHECK
   THIS SCRIPT SHOULD NOT SELECT ANY ROW
   
   DOCUMENTS
   checking for data with bad characters in document name
   
   OBJECTS
   checking for objects where the document does not exist anymore
   
   ATTACHMENTS
   checking for attachment where the document does not exist

   A row under a label is a thing to investigate with a backup in hand, not a thing to delete: the script names the table and the identifier, and nothing more. Take a backup before touching anything it reports.

FAQ

Does an empty result prove the database is consistent?

No. It proves these checks found nothing. The script is a list of the inconsistencies someone has run into before, not a model of what XWiki considers valid, so a clean run is reassuring rather than conclusive.

Are the two scripts equivalent?

No, despite what the old documentation said. The PostgreSQL one is missing eight of the MySQL one's checks, including all four translation and duplicate-document checks at its end. Its header comment lists them; run those by hand if they matter.

Why does it report every attachment in the wiki?

Because the default attachment store has been the filesystem since XWiki 10.5, which leaves xwikiattachment_content and xwikiattachment_archive empty, and two of the checks look for attachments missing from them. Both are listed in the script's header for that reason.

Can it be run while the wiki is up?

Yes. Every statement is a SELECT, so there is nothing to lock beyond what a read takes. On a wiki large enough for that to matter, run it off-hours: it scans whole tables more than a hundred times.

What fixes a row it reports?

Nothing in this page, deliberately. Each row needs reading on its own terms, because an orphaned row and a deliberately unusual page name look the same from SQL. Start from a backup, work out how the row got there, and change the database only when you know what wrote it.

Related

• Store
• Performance
• Backup