Wiki source code of Database Administration

Last modified by Vincent Massol on 2024/03/28

Hide last authors
Manuel Smeria 27.2 1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
Ludovic Dubost 1.1 4
Ecaterina Moraru (Valica) 37.4 5 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 guide>>Documentation.AdminGuide.InstallationWAR||anchor="HInstallandconfigureaRelationalDatabase"]] which contains information on how to setup XWiki for multiple databases.
Ludovic Dubost 1.1 6
Thomas Mortagne 43.1 7 = Main limitations =
Thomas Mortagne 42.1 8
Thomas Mortagne 43.1 9 == Short strings ==
Thomas Mortagne 42.1 10
Vincent Massol 45.2 11 All fields dedicated to page references, page title and in general short object properties strings are limlited to 768 characters
Thomas Mortagne 42.1 12
Silvia Macovei 19.2 13 = Indexes =
Jean-Vincent Drean 7.1 14
Vincent Massol 45.3 15 Even though we've configured XWiki to let Hibernate create indexes by default, there can be some limitations on some databases. For example on MySQL, indexes cannot be created automatically for string columns longer than 768 characters ([[MySQL has a limit of 768 utf8mb4 characters>>https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html]]).
Ludovic Dubost 1.1 16
Vincent Massol 40.1 17 Instructions depending on the database you use:
Thomas Mortagne 41.1 18
Vincent Massol 40.1 19 * [[MySQL>>Documentation.AdminGuide.Installation.InstallationWAR.InstallationMySQL.WebHome||anchor="HIndexes"]]
20 * [[Oracle>>Documentation.AdminGuide.Installation.InstallationWAR.InstallationOracle.WebHome||anchor="HIndexes"]]
Vincent Massol 29.1 21
Silvia Macovei 19.2 22 = Sanity Checks =
Ludovic Dubost 1.1 23
jeanvivienmaurice 1.6 24 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.
jeanvivienmaurice 1.7 25
Manuel Smeria 27.2 26 * If you use **MySQL** as your database:
27 ** The following [[sanity check script>>attach:sanitycheck.sql]] has been tested for **MySQL 5.0** (also been reported to work with MS-SQL)
28 ** Another [[script>>attach:sanitycheckMySQL4.sql]] is a variant of the previous script for **MySQL 4.x** replacing some syntax with more ancient one (still reports errors in one command)
29 * If you use **postgresql** as your database:
Silvia Macovei 19.1 30 ** 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.
Vincent Massol 22.2 31 ** pgAdmin is the GUI tool provided with postgresql to access the database. Run pgAdmin, select the ##xwiki## database, and choose the "Query" option in the "Tools" menu. Then just open the script and click on the play icon ("Execute query").
Jean-Vincent Drean 7.1 32
Silvia Macovei 19.2 33 = Database browsing =
Jean-Vincent Drean 7.1 34
Silvia Macovei 19.2 35 == DbVisualizer ==
Jean-Vincent Drean 7.1 36
Manuel Smeria 27.2 37 [[DbVisualizer>>http://www.dbvis.com/download/]] natively supports these DBs:
Jean-Vincent Drean 7.1 38
39 * DB2 for Windows/Linux
40 * JavaDB/Derby
41 * MySQL
42 * PostgreSQL
Manuel Smeria 27.2 43 * [[and more>>http://www.dbvis.com/features/tour/supported-databases/]]
Jean-Vincent Drean 7.1 44
Manuel Smeria 27.2 45 Any DB using the corresponding JDBC driver:
Jean-Vincent Drean 7.2 46
Manuel Smeria 27.2 47 * Example: HSQLDB by using the driver bundled with XWiki

Get Connected