Mercurial > p > roundup > code

annotate doc/mysql.txt @ 8211:513942bca8d2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fix(web): issue2551381 - update changelog.
author John Rouillard <rouilj@ieee.org>
date Sun, 15 Dec 2024 00:42:18 -0500
parents 3071db43bfb6
children 3d7292d222d1
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
6167
81ae33038ec5 more index entries.
John Rouillard <rouilj@ieee.org>
parents: 6163
diff changeset
1 .. index:: mysql; deployment notes
6163
c2fd254c9257 Doc updates/indexing.
John Rouillard <rouilj@ieee.org>
parents: 5756
diff changeset
2
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
3 =============
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
4 MySQL Backend
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
5 =============
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
6
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
7 This notes detail the MySQL backend for the Roundup issue tracker.
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
8
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
9
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
10 Prerequisites
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
11 =============
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
12
1706
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
13 To use MySQL as the backend for storing roundup data, you also need
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
14 to install:
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
15
5756
e48b039b0ec0 issue2550966: fix suboptimal links in docs.
John Rouillard <rouilj@ieee.org>
parents: 5610
diff changeset
16 1. MySQL RDBMS 4.0.18 or higher - https://www.mysql.com/. Your MySQL
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
17 installation MUST support InnoDB tables (or Berkeley DB (BDB) tables
3929
a472391156ae mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents: 2921
diff changeset
18 if you have no other choice). If you're running < 4.0.18 (but not <4.0)
1913
d929c31a3620 clarified minimum version required for MySQL backend (4.0.16)
Richard Jones <richard@users.sourceforge.net>
parents: 1912
diff changeset
19 then you'll need to use BDB to pass all unit tests. Edit the
d929c31a3620 clarified minimum version required for MySQL backend (4.0.16)
Richard Jones <richard@users.sourceforge.net>
parents: 1912
diff changeset
20 ``roundup/backends/back_mysql.py`` file to enable DBD instead of InnoDB.
5510
e2978ed3b550 update link to new mysqlclient module and recommend update in upgrading.txt
Christof Meerwald <cmeerw@cmeerw.org>
parents: 5137
diff changeset
21 2. Python MySQL interface - https://pypi.org/project/mysqlclient/
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
22
5137
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
23
7719
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
24 Preparing the Database
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
25 ======================
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
26
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
27 The Roundup user expects to be able to create and drop its database
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
28 when using ``roundup_admin init``.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
29
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
30 In the examples below, replace ``roundupuser``, ``rounduppw`` and
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
31 ``roundupdb`` with suitable values.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
32
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
33 This assumes you are running MySQL on the same host as you are
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
34 running Roundup. If this is not the case, setting up remote
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
35 credentials, SSL/TLS etc. is beyond the scope of this documentation.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
36 However examples are welcome on the wiki or mailing list.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
37
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
38 These references may be helpful:
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
39 https://dev.mysql.com/doc/refman/8.0/en/create-user.html and
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
40 https://dev.mysql.com/doc/refman/8.0/en/grant.html.
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
41
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
42 Creating a Role/User
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
43 --------------------
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
44
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
45 The following command will create a ``roundupuser`` with the ability
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
46 to create the database::
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
47
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
48 mysql -u root -e 'CREATE USER "roundupuser"@"localhost" IDENTIFIED WITH mysql_native_password BY "rounduppw"; GRANT ALL on roundupuser.* TO "roundupuser"@"localhost";'
3071db43bfb6 feat: issue2550852 - support using a specified PostgreSQL db schema
John Rouillard <rouilj@ieee.org>
parents: 7409
diff changeset
49
5137
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
50 Other Configuration
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
51 ===================
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
52
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
53 If you are indexing large documents (e.g attached file contents)
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
54 using MySQL, you may need to increase the max_allowed_packet size.
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
55 If you don't you can see the error::
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
56
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
57 'MySql Server has gone away (2006)'
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
58
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
59 To do this edit /etc/my.conf and change::
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
60
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
61 [mysqld]
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
62 max_allowed_packet = 1M
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
63
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
64 the 'max_allowed_packet' value from '1M' to '64M' or
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
65 larger.
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
66
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
67 Alternatively you can install an alternate indexer (whoosh, xapian
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
68 etc.) and force the tracker to use it by setting the ``indexer``
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
69 setting in the tracker's ``config.ini``.
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
70
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
71 This fix was supplied by telsch. See issue
5610
0df5f9eeefd4 Changed references to http://issues.roundup-tracker.org to https now
John Rouillard <rouilj@ieee.org>
parents: 5510
diff changeset
72 https://issues.roundup-tracker.org/issue2550743 for further info or if
5137
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
73 you are interested in developing a patch to roundup to help work
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
74 around this issue.
98fdc1f98194 issue2550743 - Reindex with MySQL Server failed. It looks like
John Rouillard <rouilj@ieee.org>
parents: 4557
diff changeset
75
1706
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
76 Running the MySQL tests
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
77 =======================
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
78
1706
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
79 Roundup tests expect an empty MySQL database. Two alternate ways to provide
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
80 this:
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
81
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
82 1. If you have root permissions on the MySQL server, you can create
7312
fc9e16fe3991 typo fix.
John Rouillard <rouilj@ieee.org>
parents: 6167
diff changeset
83 the necessary database entries using the following SQL sequence. Use
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
84 ``mysql`` on the command line to enter::
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
85
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
86 CREATE DATABASE rounduptest;
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
87 USE rounduptest;
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
88 GRANT ALL PRIVILEGES ON rounduptest.* TO rounduptest@localhost
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
89 IDENTIFIED BY 'rounduptest';
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
90 FLUSH PRIVILEGES;
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
91
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1706
diff changeset
92 2. If your administrator has provided you with database connection info,
4513
6a32a2fb95b4 Docs update for mysql and postgresl: Mentioned test/db_test_base.py because
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 3929
diff changeset
93 see the config values in 'test/db_test_base.py'
6a32a2fb95b4 Docs update for mysql and postgresl: Mentioned test/db_test_base.py because
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 3929
diff changeset
94 about which database connection, name and user will be used.
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
95
2409
Richard Jones <richard@users.sourceforge.net>
parents: 2067
diff changeset
96 The MySQL database should not contain any tables. Tests will not
1706
5a28eea9a33c rewrite to fix english.
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1705
diff changeset
97 drop the database with existing data.
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
98
7409
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 7312
diff changeset
99 Note that ``rounduptest`` is a well known account. You should delete
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 7312
diff changeset
100 it and the ``rounduptest`` database and create a new user/database for
b21ebabeb2ef Add warning rounduptest user: pg;mysql; add db account creation pg
John Rouillard <rouilj@ieee.org>
parents: 7312
diff changeset
101 production use.
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
diff changeset
102
2067
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
103 Showing MySQL who's boss
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
104 ========================
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
105
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
106 If things ever get to the point where that test database is totally hosed,
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
107 just::
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
108
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
109 $ su -
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
110 # /etc/init.d/mysql stop
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
111 # rm -rf /var/lib/mysql/rounduptest
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
112 # /etc/init.d/mysql start
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
113
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
114 and all will be better (note that on some systems, ``mysql`` is spelt
dbb78664e427 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 1913
diff changeset
115 ``mysqld``).
2921
ad4fb8a14a97 more doc updates, not so many TODOs any more
Richard Jones <richard@users.sourceforge.net>
parents: 2860
diff changeset
116
Roundup Issue Tracker: http://roundup-tracker.org/