Database Issue

Database Issue

XWiki uses by default a database to store its data. {toc}

Online Documentation

DatabaseInstallation

Database Administration

XWiki Database Schema (updated to version 1.8)

Database tools

Mysqlcheck

The MySql tool for database integrity

sudo mysqlcheck -c xwiki

Last run: 18-03-2010

Result: All Tables structure are OK

There is also an application on code.xwiki.org to run this script automatically on XE directly from xwiki.")

Sanity Checks

To verify the consistency of the data of XWiki database run the sanity check script. Download SQL script 

Script will select rows that are inconsistent with the logics of XWiki.\ If no rows are selected, it means the script itself can not detect any error.

There are different version MySQL and postgresql

 Official online documentation 

Last run: 18 03 2009\ Result: Database inconsistent, 

Fixed many elements: (in attach the reports)

Indexes

To improve XWiki performance when running with many documents there's a script that creates indexes for database. (Tested for MySQL 4.x and 5.0. )

Last run: 18-03-2010*\ Result: in attach (log in to view)

SQL script 

Exploring xWiki database (v. 1.2.2)

sudo mysqlshow

oppure ...   
sudo mysql xwiki
mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| alice              |
| apice              |
| blojsom            |
| mysql              |
| publication        |
| xwiki              |
+--------------------+
7 rows in set (0.00 sec)


SHOW TABLES;

+---------------------------+
| Tables_in_xwiki           |
+---------------------------+
| feeds_aggregatorgroup     |
| feeds_aggregatorurl       |
| feeds_aggregatorurlgroups |
| feeds_feedentry           |
| feeds_feedentrytags       |
| feeds_keyword             |
| xwikiattachment           |
| xwikiattachment_archive   |
| xwikiattachment_content   |
| xwikibooleanclasses       |
| xwikiclasses              |
| xwikiclassesprop          |
| xwikicomments             |
| xwikidateclasses          |
| xwikidates                |
| xwikidblistclasses        |
| xwikidbversion            |
| xwikidoc                  |
| xwikidoubles              |
| xwikifloats               |
| xwikiintegers             |
| xwikilargestrings         |
| xwikilinks                |
| xwikilistitems            |
| xwikilists                |
| xwikilock                 |
| xwikilongs                |
| xwikinumberclasses        |
| xwikiobjects              |
| xwikipreferences          |
| xwikiproperties           |
| xwikircs                  |
| xwikirecyclebin           |
| xwikislistclasses         |
| xwikistatsdoc             |
| xwikistatsreferer         |
| xwikistatsvisit           |
| xwikistringclasses        |
| xwikistrings              |
+---------------------------+
39 rows in set (0.00 sec)

Some of the tables above are documented in the page containing The XWiki Database Schema (updated to version 1.8)

How to access data

 XWiki 1.1M4, xwikiclasses table is not populated.
The XWiki engine retieves the class of an object using xwikiobjects.XWO_CLASSNAME in table xwikiobjects instead. It accesses the document where xwikidoc.XWD_FULLNAME matches xwikiobjects.XWO_CLASSNAME, and then reads the XML description of the class from wikidoc.XWD_CUSTOM_CLASS_XML. The table xwikiobjects is still populated, of course.

mysql> SELECT DISTINCT XWO_CLASSNAME FROM xwikiobjects;
+-------------------------------+
| XWO_CLASSNAME                 |
+-------------------------------+
| Blog.Categories               |
| Courses.CourseClass           |
| Panels.PanelClass             |
| Publications.---              |
| Publications.PublicationClass |
| SaraMontagna.---              |
| Talks.TalkClass               |
| Theses.ThesisClass            |
| XWiki.ArticleClass            |
| XWiki.Mail                    |
| XWiki.PhotoAlbumClass         |
| XWiki.PresentationClass       |
| XWiki.SchedulerJobClass       |
| XWiki.SlideClass              |
| XWiki.TagClass                |
| XWiki.WatchListClass          |
| XWiki.XWikiComments           |
| XWiki.XWikiGlobalRights       |
| XWiki.XWikiGroups             |
| XWiki.XWikiPreferences        |
| XWiki.XWikiRights             |
| XWiki.XWikiSkins              |
| XWiki.XWikiUsers              |
+-------------------------------+
23 rows in set (0.00 sec)

select * from xwikiobjects where XWO_CLASSNAME = 'Theses.ThesisClass';
+-------------+------------+---------------------------------------------------------------+--------------------+
| XWO_ID      | XWO_NUMBER | XWO_NAME                                                      | XWO_CLASSNAME      |
+-------------+------------+---------------------------------------------------------------+--------------------+
|  1387325302 |          0 | Theses.ThesisClassTemplate                                    | Theses.ThesisClass |
.......
|    -2545831 |          0 | Theses.InternethingsTamagnini09                               | Theses.ThesisClass |
|  1048205775 |          0 | Theses.ProvaTesi                                              | Theses.ThesisClass |
+-------------+------------+---------------------------------------------------------------+--------------------+
118 rows in set (0.00 sec)
di cui 117 Tesi

Objects and properties are linked by 

xwikiproperties.XWP_ID = xwikiobjects.XWO_ID
select * from xwikiproperties where XWP_ID = -966466718;
+------------+-----------------------+-------------------------------------------+
| XWP_ID     | XWP_NAME              | XWP_CLASSTYPE                             |
+------------+-----------------------+-------------------------------------------+
| -966466718 | abstract              | com.xpn.xwiki.objects.LargeStringProperty |
| -966466718 | availabilityDate      | com.xpn.xwiki.objects.DateProperty        |
| -966466718 | category              | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | contact               | com.xpn.xwiki.objects.LargeStringProperty |
| -966466718 | coSupervisors         | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | description           | com.xpn.xwiki.objects.LargeStringProperty |
| -966466718 | endDate               | com.xpn.xwiki.objects.DateProperty        |
| -966466718 | externalCosupervisors | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | externalSupervisor    | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | language              | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | startDate             | com.xpn.xwiki.objects.DateProperty        |
| -966466718 | status                | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | studentMail           | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | studentName           | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | studentUserName       | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | supervisor            | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | title                 | com.xpn.xwiki.objects.StringProperty      |
| -966466718 | translatedAbstract    | com.xpn.xwiki.objects.LargeStringProperty |
| -966466718 | translatedTitle       | com.xpn.xwiki.objects.StringProperty      |
+------------+-----------------------+-------------------------------------------+
19 rows in set (0.00 sec)

select * from xwikiobjects where XWO_ID = -966466718;
+------------+------------+-----------------------+--------------------+
| XWO_ID     | XWO_NUMBER | XWO_NAME              | XWO_CLASSNAME      |
+------------+------------+-----------------------+--------------------+
| -966466718 |          0 | Theses.SemanticTuCSoN | Theses.ThesisClass |
+------------+------------+-----------------------+--------------------+
1 row in set (0.00 sec)

To do List

  1. Check inconsistent 
select * from xwikiproperties where XWP_ID IN (-367288557,338839292,-1659694622);
  1. Check inconsistent 
select * from xwikiproperties,xwikilargestrings where xwp_classtype='com.xpn.xwiki.objects.StringListProperty' and xwp_name=xwl_name and xwp_id=xwl_id;
  1. Check inconsistent "Publications.-" and "SaraMontagna.-" got by query "SELECT DISTINCT XWO_CLASSNAME FROM xwikiobjects;"