The MySQL-Docker installation variant seems unfit for Subwikis

Help / Discuss
,
#1

Hello, I am currently trying to install an XWiki 10.1 (Standard Flavor) instance from the official xwiki:10-mysql-tomcat docker image using the provided docker-compose files. Adding Subwikis does not work out of the box:
19

The Wiki by default uses the “xwiki” MySQL user, which is (I assume) created during the installation process by using the MySQL root user (XWiki also knows the MySQL root password).

After setup, the xwiki MySQL user has the following grants:

±-------------------------------------------------+
| Grants for xwiki@% |
±-------------------------------------------------+
| GRANT USAGE ON . TO ‘xwiki’@’%’ |
| GRANT ALL PRIVILEGES ON xwiki.* TO ‘xwiki’@’%’ |
±-------------------------------------------------+

In a default MySQL installation, this new “xwiki” user seems to not be able to create new databases. This is however required for creating subwikis (on MySQL, Subwikis must exist in separate databases, as XWiki documentation suggests). Creating a new Wiki through the Wiki index therefore fails.

unsuccessfull solution attempts:

  • Granting CREATE (or even ALL) permissions to the xwiki user does not help, as the user then might be able to create a database, but won’t be allowed to access it. According to this blog post, enabling a non-root user to create and populate databases would require updating the createdb stored procedure. This feels hacky…
  • Creating the database for the new wiki beforehand and granting permissions to the xwiki MySQL user does not work. The Subwiki creation process complains that the database already exists.

A workaround I have found is to start xwiki with the MySQL root user.
Is there something wrong with my setup, or is there currently no clean way to create subwikis in my configuration?

#2

Would be great if you could create a jira issue since this use case should work OOB: http://jira.xwiki.org/browse/XDOCKER

#3

Doc at http://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Installation/InstallationWAR/InstallationMySQL/ says to execute:

mysql -u root -e "grant all privileges on *.* to xwiki@localhost identified by 'xwiki'"

But that seems similar to what you’re showing above. I don’t have the answer right now, will need to spend some time on googling/trying stuff out.

If you find a solution, I’ll happily implement it in the default XWiki docker image (or even better you can submit a PR ;)).

Thanks for reporting this!

#4

Any news on this? It still does not seem to be fixed.

#5

Just checked and @blablub44 (or anyone else) has not created the jira issue that I mentioned above (at least I can’t find it at http://jira.xwiki.org/browse/XDOCKER), and thus, this has not been tasked and no work is being done on it by anyone.

Having a jira issue is the first step. Second step is finding someone to provide a pull request for it.

As I said above:

Unfortunately @blablub44 has not answered so I don’t know the command I mentioned worked or not.

Could you try it yourself @pitwegner?

#6

I tried starting the docker container with root mysql user as well as your suggestion. Both unfortunately did not work. Of course I can open a Jira issue, just wanted to make sure I don’t report anything that’s already being worked on.
EDIT: Starting with mysql root worked after actually changing it in the config…

#7

Hi, I just wanted to ask if you could get rid of this problem.

I ran into the same problem and now I have to put the user “root” into the hibernate.cfg.xml, otherwise I’m not allowed to create a database like in your initial post here.

Access denied for user ‘xwiki’@’%’ to database 'SubWiki’

But I dont like it to use root as a user in a config :frowning:
I also changed “root” to “xwiki” as a user after the database creation but than ran into the same “Access denied” Problem.

Should I open an issue or is there already one open ?

#8

Yes, please report a jira issue at http://jira.xwiki.org/browse/XDOCKER and I’ll try to find a solution. It’s weird that grant all privileges on *.* to xwiki@localhost identified by 'xwiki' is not working. I’ll try it myself. Thanks.

#9

FWIW this is how the Debian packaging does it: https://github.com/xwiki/xwiki-platform/blob/41ad63e84618a2e79f404e2e16263c53eeea1c57/xwiki-platform-distribution/xwiki-platform-distribution-debian/xwiki-platform-distribution-debian-mysql-common/src/deb/control/postinst#L59

#10

Thanks for the very fast reply.

I opened an Jira Issue

https://jira.xwiki.org/browse/XDOCKER-118

Hope it will help to fix the problem, as the docker container is perfect, also for prod environments.
Everything but the subwiki is running out of the box without the need to configure XX Applications and Configurations :-):slightly_smiling_face:

#11

This means the user xwiki accessing MySQL using localhost host, maybe XWiki is using a different host to access the docker based MySQL server ? That’s what the error pasted by @Lange in the Jira issue suggests (it’s reffering to %).

#12

I’ve now tested and documented it at https://github.com/xwiki-contrib/docker-xwiki/blob/master/README.md#using-docker-run

See also https://jira.xwiki.org/browse/XDOCKER-118?focusedCommentId=103662&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-103662

#13

Wow, thanks for this instant issue fix, I’m overwhelmed :star_struck:
Today we’re quite busy at work, so I hope I can try it right tomorrow.

Had a quick look and going to read it as soon as I can.

Would you then recommend it to use it with docker run or is it also possible to apply that changes you made for the docker compose file ?

#14

Yes, I’m on it (for the compose file change).

#15

Committed and tested the changes for the compose files too! :slight_smile:

Enjoy!

#16

omg, @vmassol you are awesome !!!

I’ll try it as soon as possible and give feedback.

Do I need to deploy everything new or am I able to take that new compose file to use it in my current installation ? Not sure if the database gets affected badly with it.

#17

It should work with your existing DB. Now you’ll need to docker exec -it <containerid> bash -l into your running DB container and make sure you have the permissions set correctly: mysql -u root -p xwiki and then select * from mysql.user, and verify you have Y set for the xwiki user for all permissions (except the grant one).

#18

Thank you so much vmassol !!!
It works perfectly.

The only issue I had was, that mysql, I don’t know why, wasn’t accepting my password.
Maybe it was to complex ?

It was Zrtq,K{hUrtK}P9f (no worries , I already changed it completly).

Similar to the root password…

The root password worked, but the xwiki password not (I connected to the container within the shell and tried to mysql -uxwiki -pZrtq,K{hUrtK}P9f).

And got the ERROR 1045 (28000): Access denied for user ‘xwiki’@‘localhost’ (using password: YES)

Then I changed the password to test (just for testing) in:

  • within the docker container as root I changed the xwiki user pw
  • docker-compose.yml
  • init.sql
  • /var/lib/docker/volumes/xwikidocker_xwiki-data/_data/data/hibernate.cfg.xml

Rebooted with Portainer and then it worked.
I’m now trying it with a more complex password. Maybe mysql had a problem with the length or with some escaping character things…

#19

great

that would be strange. I googled quickly and found no limitations on mysql passwords.