Database Issue

There is also [an application on code.xwiki.org>http://code.xwiki.org/xwiki/bin/view/Applications/CheckConfigAndIndexesApplication] to run this script automatically on XE directly from xwiki.")

32

33

34

1.1.1 Sanity Checks

35

36

To verify the consistency of the data of XWiki database run the sanity check script. {attach:Download SQL script |file=MYSQLsanitycheck.sql|title=Download script}

37

38

39

Script will select rows that are inconsistent with the logics of XWiki.\\

40

If no rows are selected, it means the script itself can not detect any error.

41

42

There are different version *MySQL* and *postgresql*

43

44

45

[Official online documentation > http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Database+Administration#HSanityChecks]

46

47

Last run: *18 03 2009*\\

48

Result: *Database inconsistent*,

49

50

Fixed many elements: (in attach the reports)

1.1.1 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. )

56

57

Last run: *18-03-2010**\\

58

Result: in attach (log in to view)

59

60

{attach:SQL script |file=createIndexes.sql|title=Download script}

1.1 Exploring xWiki database (v. 1.2.2)

{code}

sudo mysqlshow

oppure ...

sudo mysql xwiki

mysql> show databases;

74

75

+--------------------+

76

| Database |

77

+--------------------+

78

| information_schema |

| alice |

| apice |

| blojsom |

| mysql |

| publication |

| xwiki |

+--------------------+

86

7 rows in set (0.00 sec)

SHOW TABLES;

+---------------------------+

92

| Tables_in_xwiki |

93

+---------------------------+

94

| feeds_aggregatorgroup |

95

| feeds_aggregatorurl |

96

| feeds_aggregatorurlgroups |

97

| feeds_feedentry |

98

| feeds_feedentrytags |

99

| feeds_keyword |

100

| xwikiattachment |

101

| xwikiattachment_archive |

102

| xwikiattachment_content |

103

| 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 |

128

| xwikistatsdoc |

129

| xwikistatsreferer |

130

| xwikistatsvisit |

131

| xwikistringclasses |

132

| xwikistrings |

133

+---------------------------+

134

39 rows in set (0.00 sec)

135

{code}

136

137

Some of the tables above are documented in the page containing [The XWiki Database Schema > http://platform.xwiki.org/xwiki/bin/view/DevGuide/DatabaseSchema] (updated to version 1.8)

138

139

140

1.1 How to access data

141

142

#warning(" XWiki 1.1M4, xwikiclasses table is not populated.")

143

144

#info("The XWiki engine retieves the class of an object using xwikiobjects.XWO_CLASSNAME in table xwikiobjects instead.

145

It accesses the document where xwikidoc.XWD_FULLNAME matches xwikiobjects.XWO_CLASSNAME,

146

and then reads the XML description of the class from wikidoc.XWD_CUSTOM_CLASS_XML.

147

The table xwikiobjects is still populated, of course.")

148

149

{code}

150

mysql> SELECT DISTINCT XWO_CLASSNAME FROM xwikiobjects;

151

+-------------------------------+

152

| XWO_CLASSNAME |

153

+-------------------------------+

154

| Blog.Categories |

155

| Courses.CourseClass |

156

| Panels.PanelClass |

157

| Publications.--- |

158

| Publications.PublicationClass |

159

| SaraMontagna.--- |

160

| Talks.TalkClass |

161

| Theses.ThesisClass |

162

| XWiki.ArticleClass |

163

| XWiki.Mail |

164

| XWiki.PhotoAlbumClass |

165

| XWiki.PresentationClass |

166

| XWiki.SchedulerJobClass |

167

| XWiki.SlideClass |

168

| XWiki.TagClass |

169

| XWiki.WatchListClass |

170

| XWiki.XWikiComments |

171

| XWiki.XWikiGlobalRights |

172

| XWiki.XWikiGroups |

173

| XWiki.XWikiPreferences |

174

| XWiki.XWikiRights |

175

| XWiki.XWikiSkins |

176

| XWiki.XWikiUsers |

177

+-------------------------------+

178

23 rows in set (0.00 sec)

179

180

select * from xwikiobjects where XWO_CLASSNAME = 'Theses.ThesisClass';

181

+-------------+------------+---------------------------------------------------------------+--------------------+

182

183

+-------------+------------+---------------------------------------------------------------+--------------------+

184

| 1387325302 | 0 | Theses.ThesisClassTemplate | Theses.ThesisClass |

185

.......

186

| -2545831 | 0 | Theses.InternethingsTamagnini09 | Theses.ThesisClass |

187

| 1048205775 | 0 | Theses.ProvaTesi | Theses.ThesisClass |

188

+-------------+------------+---------------------------------------------------------------+--------------------+

189

118 rows in set (0.00 sec)

di cui 117 Tesi

{code}

Objects and properties are linked by

195

196

<code>xwikiproperties.XWP_ID = xwikiobjects.XWO_ID</code>

{code}

select * from xwikiproperties where XWP_ID = -966466718;

201

+------------+-----------------------+-------------------------------------------+

202

| XWP_ID | XWP_NAME | XWP_CLASSTYPE |

203

+------------+-----------------------+-------------------------------------------+

204

| -966466718 | abstract | com.xpn.xwiki.objects.LargeStringProperty |

205

| -966466718 | availabilityDate | com.xpn.xwiki.objects.DateProperty |

206

| -966466718 | category | com.xpn.xwiki.objects.StringProperty |

207

| -966466718 | contact | com.xpn.xwiki.objects.LargeStringProperty |

208

| -966466718 | coSupervisors | com.xpn.xwiki.objects.StringProperty |

209

| -966466718 | description | com.xpn.xwiki.objects.LargeStringProperty |

210

| -966466718 | endDate | com.xpn.xwiki.objects.DateProperty |

211

| -966466718 | externalCosupervisors | com.xpn.xwiki.objects.StringProperty |

212

| -966466718 | externalSupervisor | com.xpn.xwiki.objects.StringProperty |

213

| -966466718 | language | com.xpn.xwiki.objects.StringProperty |

214

| -966466718 | startDate | com.xpn.xwiki.objects.DateProperty |

215

| -966466718 | status | com.xpn.xwiki.objects.StringProperty |

216

| -966466718 | studentMail | com.xpn.xwiki.objects.StringProperty |

217

| -966466718 | studentName | com.xpn.xwiki.objects.StringProperty |

218

| -966466718 | studentUserName | com.xpn.xwiki.objects.StringProperty |

219

| -966466718 | supervisor | com.xpn.xwiki.objects.StringProperty |

220

| -966466718 | title | com.xpn.xwiki.objects.StringProperty |

221

| -966466718 | translatedAbstract | com.xpn.xwiki.objects.LargeStringProperty |

222

| -966466718 | translatedTitle | com.xpn.xwiki.objects.StringProperty |

223

+------------+-----------------------+-------------------------------------------+

224

19 rows in set (0.00 sec)

225

226

select * from xwikiobjects where XWO_ID = -966466718;

227

+------------+------------+-----------------------+--------------------+

228

229

+------------+------------+-----------------------+--------------------+

230

| -966466718 | 0 | Theses.SemanticTuCSoN | Theses.ThesisClass |

231

+------------+------------+-----------------------+--------------------+

232

1 row in set (0.00 sec)

{code}

1.1 To do List

1. Check inconsistent {code}select * from xwikiproperties where XWP_ID IN (-367288557,338839292,-1659694622);{code}

239

1. Check inconsistent {code}select * from xwikiproperties,xwikilargestrings where xwp_classtype='com.xpn.xwiki.objects.StringListProperty' and xwp_name=xwl_name and xwp_id=xwl_id;{code}

240

1. Check inconsistent "Publications.\-\-\-" and "SaraMontagna.\-\-\-" got by query "SELECT DISTINCT XWO_CLASSNAME FROM xwikiobjects;"

Wiki source code of Database Issue