Changes for page Database Administration

Summary

• Page properties (3 modified, 0 added, 0 removed)
• Objects (2 modified, 0 added, 0 removed)
  • XWiki.XWikiComments[1]
  • XWiki.XWikiComments[2]

Details

Page properties

Author

...	...	@@ -1,1 +1,1 @@
1		-XWiki.VincentMassol
	1	+XWiki.SilviaRusu

Syntax

...	...	@@ -1,1 +1,1 @@
1		-XWiki 1.0
	1	+XWiki 2.0

Content

...	...	@@ -1,16 +1,18 @@
1		-1 Database Administration
	1	+= Database Administration =
2	2
3		-XWiki uses by default a database to store its data. You will find on this page tips and tricks to administer and tune your database setup. For Installation please check the [Installation>Installation#HInstallandconfigureaRelationalDatabase] page which contains information on how to setup XWiki for multiple databases.
	3	+XWiki uses by default a database to store its data. You will find on this page tips and tricks to administer and tune your database setup. For Installation please check the [[Installation>>Installation#HInstallandconfigureaRelationalDatabase]] page which contains information on how to setup XWiki for multiple databases.
4	4
5		-#toc("" "" "")
	5	+{{toc start="" depth="" numbered=""/}}
6	6
7		-1.1 Indexes
	7	+== Indexes ==
8	8
9	9	To improve your XWiki instance when running with many documents you should run the following script to create indexes for your database. This has been tested for MySQL 4.x and 5.0.
10	10
11		-#info("There is now [an application on code.xwiki.org>code:Applications.CheckConfigAndIndexesApplication] to run this script automatically on XE and XEM directly from your wiki.")
	11	+{{info}}
	12	+There is now [[an application on code.xwiki.org>>code:Applications.CheckConfigAndIndexesApplication]] to run this script automatically on XE and XEM directly from your wiki.
	13	+{{/info}}
12	12
13		-{code}
	15	+{{code}}
14	14	create index xwd_name on xwikidoc (xwd_name);
15	15	create index xwd_fullname on xwikidoc (xwd_fullname);
16	16	create index xwd_web on xwikidoc (xwd_web);
...	...	@@ -60,22 +60,22 @@
60	60	create index xwv_unique_id on xwikistatsvisit (XWV_UNIQUE_ID);
61	61	create index xwv_classname on xwikistatsvisit (XWV_CLASSNAME);
62	62	create index xwv_number on xwikistatsvisit (XWV_NUMBER);
63		-{code}
	65	+{{/code}}
64	64
65		-1.1 Sanity Checks
	67	+== Sanity Checks ==
66	66
67	67	To verify the consistency of your XWiki database you can run the sanity check script. The 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.
68		-* If you use *MySQL* as your database :
69		-** The following {attach:sanity check script|sanitycheck.sql} has been tested for *MySQL 5.0*.
70		-** Another {attach:script|sanitycheck_MySQL4.sql} is a variant of the previous script for *MySQL 4.x* replacing some syntax with more ancient one (still reports error in one command, please some MySQL guru to fix it...).
71	71
72		-* If you use *postgresql* as your database :
73		-** Here is a {attach:modified version of the sanity check script|sanitycheck_postgresql.sql}, to be run in the "query" tool of pgAdmin. It is the same as the script for MySQL, except that the syntax for SQL comments is different.
74		-** pgAdmin is the GUI tool provided with postgresql to access the database. Run pgAdmin, select the <code>xwiki</code> database, and choose the "Query" option in the "Tools" menu. Then just open the script and click on the play icon ("Execute query").
	71	+* If you use **MySQL** as your database :
	72	+** The following [[sanity check script>>attach:sanitycheck.sql]] has been tested for **MySQL 5.0**.
	73	+** Another [[script>>attach:sanitycheck_MySQL4.sql]] is a variant of the previous script for **MySQL 4.x** replacing some syntax with more ancient one (still reports error in one command, please some MySQL guru to fix it...).
	74	+* If you use **postgresql** as your database :
	75	+** Here is a [[modified version of the sanity check script>>attach:sanitycheck_postgresql.sql]], to be run in the "query" tool of pgAdmin. It is the same as the script for MySQL, except that the syntax for SQL comments is different.
	76	+** pgAdmin is the GUI tool provided with postgresql to access the database. Run pgAdmin, select the {{html clean="false" wiki="true"}}<code>xwiki</code>{{/html}} database, and choose the "Query" option in the "Tools" menu. Then just open the script and click on the play icon ("Execute query").
75	75
76		-1.1 Database browsing
	78	+== Database browsing ==
77	77
78		-1.1.1 DbVisualizer
	80	+=== DbVisualizer ===
79	79
80	80	http://www.dbvis.com/products/dbvis/download/install.jsp
81	81
...	...	@@ -85,7 +85,7 @@
85	85	* JavaDB/Derby
86	86	* MySQL
87	87	* PostgreSQL
88		-* [more>http://www.dbvis.com/products/dbvis/features/features.jsp?page=matrix]
	90	+* [[more>>http://www.dbvis.com/products/dbvis/features/features.jsp?page=matrix]]
89	89
90	90	Any DB using the corresponding JDBC driver :
91	91

XWiki.XWikiComments[1]

Comment

...	...	@@ -1,1 +1,1 @@
1		-Please note that some of the above indexes, in oracle are on CLOB column types. These do not work under oracle, one needs to create indexes based on the Oracle Text packages. Just thought I would let you oracle users know :)
	1	+Please note that some of the above indexes, in oracle are on CLOB column types. These do not work under oracle, one needs to create indexes based on the Oracle Text packages. Just thought I would let you oracle users know :)

XWiki.XWikiComments[2]

Comment

...	...	@@ -4,17 +4,13 @@
4	4
5	5	Take an example, for the following query:
6	6
	7	+{{velocity filter="none"}}{{html clean="false" wiki="true"}}
7	7	## this should not return lines. This shows data in large string table that should be in largestring table
8		-select * from xwikiproperties,xwikilargestrings where xwp_classtype='com.xpn.xwiki.objects.StringListProperty' and xwp_name=xwl_name and xwp_id=xwl_id
9	9
10		-+-------------+---------------------+------------------------------------------+-------------+---------------------+-------------------------------------------------
11		-| XWP_ID | XWP_NAME | XWP_CLASSTYPE | XWL_ID | XWL_NAME | XWL_VALUE
12		-+-------------+---------------------+------------------------------------------+-------------+---------------------+-------------------------------------------------
13		-| -1877168607 | subscribedDocuments | com.xpn.xwiki.objects.StringListProperty | -1877168607 | subscribedDocuments | Main.WebSearch|Main.Categories
14		-| 261169163 | subscribedDocuments | com.xpn.xwiki.objects.StringListProperty | 261169163 | subscribedDocuments | Service Measurements.WebHome|IMProcess.WebHome
15		-| 683557787 | subscribedWebs | com.xpn.xwiki.objects.StringListProperty | 683557787 | subscribedWebs | Replication
16		-| 683557787 | subscribedDocuments | com.xpn.xwiki.objects.StringListProperty | 683557787 | subscribedDocuments | IIS.WebHome
	10	+{{/html}}{{/velocity}}select * from xwikiproperties,xwikilargestrings where xwp_classtype='com.xpn.xwiki.objects.StringListProperty' and xwp_name=xwl_name and xwp_id=xwl_id
17	17
	12	++--~-~-~-~-~-~--+--~-~-~-~-~-~-~-~-~-~-~-~-~-~--+--~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-----+--~-~-~-~-~-~--+--~-~-~-~-~-~-~-~-~-~-~-~-~-~--+--~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-- | XWP_ID | XWP_NAME | XWP_CLASSTYPE | XWL_ID | XWL_NAME | XWL_VALUE +--~-~-~-~-~-~--+--~-~-~-~-~-~-~-~-~-~-~-~-~-~--+--~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-----+--~-~-~-~-~-~--+--~-~-~-~-~-~-~-~-~-~-~-~-~-~--+--~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-- | -1877168607 | subscribedDocuments | com.xpn.xwiki.objects.StringListProperty | -1877168607 | subscribedDocuments | Main.WebSearch|Main.Categories | 261169163 | subscribedDocuments | com.xpn.xwiki.objects.StringListProperty | 261169163 | subscribedDocuments | Service Measurements.WebHome|IMProcess.WebHome | 683557787 | subscribedWebs | com.xpn.xwiki.objects.StringListProperty | 683557787 | subscribedWebs | Replication | 683557787 | subscribedDocuments | com.xpn.xwiki.objects.StringListProperty | 683557787 | subscribedDocuments | IIS.WebHome
	13	+
18	18	I've got couple hundred lines of records like this. If I delete all these records, what kind of impact will be to my content?
19	19
20	20	Thank you very much for your help