|
|
1 |
|
|
|
2 |
|
|
|
3 |
1 Database Issue |
|
|
4 |
XWiki uses by default a database to store its data. |
|
|
5 |
{toc} |
|
|
6 |
|
|
|
7 |
1.1 Online Documentation |
|
|
8 |
|
|
|
9 |
[DatabaseInstallation>http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Installation#HInstallandconfigureaRelationalDatabase] |
|
|
10 |
|
|
|
11 |
[Database Administration > http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Database+Administration] |
|
|
12 |
|
|
|
13 |
[XWiki Database Schema > http://platform.xwiki.org/xwiki/bin/view/DevGuide/DatabaseSchema] (updated to version 1.8) |
|
|
14 |
|
|
|
15 |
|
|
|
16 |
1.1 Database tools |
|
|
17 |
|
|
|
18 |
|
|
|
19 |
1.1.1 Mysqlcheck |
|
|
20 |
|
|
|
21 |
The MySql tool for database integrity |
|
|
22 |
{code} |
|
|
23 |
sudo mysqlcheck -c xwiki |
|
|
24 |
{code} |
|
|
25 |
|
|
|
26 |
Last run: *18-03-2010* |
|
|
27 |
|
|
|
28 |
Result: *All Tables structure are OK* |
|
|
29 |
|
|
|
30 |
|
|
|
31 |
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) |
|
|
51 |
|
|
|
52 |
|
|
|
53 |
1.1.1 Indexes |
|
|
54 |
|
|
|
55 |
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} |
|
|
61 |
|
|
|
62 |
|
|
|
63 |
|
|
|
64 |
|
|
|
65 |
1.1 Exploring xWiki database (v. 1.2.2) |
|
|
66 |
|
|
|
67 |
|
|
|
68 |
{code} |
|
|
69 |
sudo mysqlshow |
|
|
70 |
|
|
|
71 |
oppure ... |
|
|
72 |
sudo mysql xwiki |
|
|
73 |
mysql> show databases; |
|
|
74 |
|
|
|
75 |
+--------------------+ |
|
|
76 |
| Database | |
|
|
77 |
+--------------------+ |
|
|
78 |
| information_schema | |
|
|
79 |
| alice | |
|
|
80 |
| apice | |
|
|
81 |
| blojsom | |
|
|
82 |
| mysql | |
|
|
83 |
| publication | |
|
|
84 |
| xwiki | |
|
|
85 |
+--------------------+ |
|
|
86 |
7 rows in set (0.00 sec) |
|
|
87 |
|
|
|
88 |
|
|
|
89 |
SHOW TABLES; |
|
|
90 |
|
|
|
91 |
+---------------------------+ |
|
|
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 | |
|
|
104 |
| xwikiclasses | |
|
|
105 |
| xwikiclassesprop | |
|
|
106 |
| xwikicomments | |
|
|
107 |
| xwikidateclasses | |
|
|
108 |
| xwikidates | |
|
|
109 |
| xwikidblistclasses | |
|
|
110 |
| xwikidbversion | |
|
|
111 |
| xwikidoc | |
|
|
112 |
| xwikidoubles | |
|
|
113 |
| xwikifloats | |
|
|
114 |
| xwikiintegers | |
|
|
115 |
| xwikilargestrings | |
|
|
116 |
| xwikilinks | |
|
|
117 |
| xwikilistitems | |
|
|
118 |
| xwikilists | |
|
|
119 |
| xwikilock | |
|
|
120 |
| xwikilongs | |
|
|
121 |
| xwikinumberclasses | |
|
|
122 |
| xwikiobjects | |
|
|
123 |
| xwikipreferences | |
|
|
124 |
| xwikiproperties | |
|
|
125 |
| xwikircs | |
|
|
126 |
| xwikirecyclebin | |
|
|
127 |
| 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 |
| XWO_ID | XWO_NUMBER | XWO_NAME | XWO_CLASSNAME | |
|
|
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) |
|
|
190 |
di cui 117 Tesi |
|
|
191 |
{code} |
|
|
192 |
|
|
|
193 |
|
|
|
194 |
Objects and properties are linked by |
|
|
195 |
|
|
|
196 |
<code>xwikiproperties.XWP_ID = xwikiobjects.XWO_ID</code> |
|
|
197 |
|
|
|
198 |
|
|
|
199 |
{code} |
|
|
200 |
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 |
| XWO_ID | XWO_NUMBER | XWO_NAME | XWO_CLASSNAME | |
|
|
229 |
+------------+------------+-----------------------+--------------------+ |
|
|
230 |
| -966466718 | 0 | Theses.SemanticTuCSoN | Theses.ThesisClass | |
|
|
231 |
+------------+------------+-----------------------+--------------------+ |
|
|
232 |
1 row in set (0.00 sec) |
|
|
233 |
{code} |
|
|
234 |
|
|
|
235 |
|
|
|
236 |
1.1 To do List |
|
|
237 |
|
|
|
238 |
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;" |