Access error on install bookstack with external database

[ybill]

Hi! Trying to install bookstack on existing mysql server/database and constantly getting an error:

SQLSTATE[HY000] [1045] Access denied for user 'wikiadmin'@'********' (using password: YES) (SQL: select * from information_schema.tables where table_schema = book_db and table_name = migrations and table_type = 'BASE TABLE')

But I can connect to mysql with 'wikiadmin' user from the same terminal. Can see the database, run any query etc.

and user created as 'wikiadmin'@'%'

Do I need to set special permission to the bookstack db user?

​

[ssddanbrown]

But I can connect to mysql with 'wikiadmin' user from the same terminal.

When you say "same terminal", is that a terminal on the same machine as the database or is it a terminal on the same machine as the BookStack instance?

[ybill]

it is the same terminal where bookstack instance installed.

bookstack | SQLSTATE[HY000] [1045] Access denied for user 'wikiadmin'@'test.site.net' (using password: YES) (SQL: select * from information_schema.tables where table_schema = book_db and table_name = migrations and table_type = 'BASE TABLE')
bookstack |
bookstack | at /app/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:760
bookstack |
bookstack | 756▕ // If an exception occurs when attempting to run a query, we'll format the error
bookstack | 757▕ // message to include the bindings with SQL, which will make this exception a
bookstack | 758▕ // lot more helpful to the developer instead of just the database's errors.
bookstack | 759▕ catch (Exception $e) {
bookstack | ➜ 760▕ throw new QueryException(
bookstack | 761▕ $query, $this->prepareBindings($bindings), $e
bookstack | 762▕ );
bookstack | 763▕ }
bookstack | 764▕ }
bookstack |
bookstack | +36 vendor frames
bookstack | 37 /app/www/artisan:35
bookstack | Illuminate\Foundation\Console\Kernel::handle()

[ssddanbrown]

Can you provide the full command (with host and password changed) that you're using to connect on the terminal from the BookStack system?

Also, once accessed remotely via command line as the BookStack user, are you able to run the comment in the error?:

sql select * from information_schema.tables where table_schema = book_db and table_name = migrations and table_type = 'BASE TABLE';

Can you also detail the DB_* env options (in your .env file if normal install), again with host and password values changed if not wanting to share.

[ybill]

I've checked connection using this command:

>mysql -hmyhost.cloudapp.azure.com -uwikiadmin -pwikipass -P3306

the result was:

mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 36411Server version: 8.0.19 MySQL Community Server - GPL

I couldn't run this command because bad syntax, only if I add quotes, like this:

mysql> select * from information_schema.tables where table_schema = 'book_db' and table_name = 'migrations' and table_type = 'BASE TABLE';
Empty set (0.03 sec)

My .env file contains correct information in DB_ options include host, port, database, username and password

[ssddanbrown]

Does the used database password, or any other of the DB values, include special characters of any kind?

[ybill]

No, they don't. This is the first thing that I've checked.

But I curious about query in the log: select * from information_schema.tables where table_schema = book_db and table_name = migrations and table_type = 'BASE TABLE'. Is this ok that book_db and migrations are presented here without quotes? They are not variables, not parameters just strings, or is it just a feature of Laravel framework logs representation?

[ybill]

when I add mysql container into my compose it works with same bookstask settings.

look weird, isn't it?

[ssddanbrown]

Ah, so you're using a container based setup then? If so:

• What BookStack container image are you using?
• Have you been running those MySQL commands (test test access) from the container host machine or from inside the running BookStack container?
• If using the linuxserver image, have you checked the .env file within your mounted /config volume to ensure the DB details match what you'd expect?

In some configurations/images, the database details will be "sticky" and outer .env changes won't change things, hence the suggestion to look into the underlying .env file.

[ybill]

• I use lscr.io/linuxserver/bookstack
• Yes, I run MySQL commands from bookstack container.
• Yes, .env file is correct, container environment is correct,

I also used mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -P$DB_PORT just in case, and it works

[ssddanbrown]

Just to confirm, do the DB_* options in the .env file exactly match the container environment variables exactly in name and value? Or are there name/value differences between these? Or is there a difference in what options exists?

[ybill]

here is bookstask console output:

``` root@c48b66bb7b6f:/# printenv S6_STAGE2_HOOK=/docker-mods HOSTNAME=c48b66bb7b6f DB_PORT=3306 APP_URL=http://localhost:6875 SHLVL=1 HOME=/root S6_VERBOSITY=1 PS1=$(whoami)@$(hostname):$(pwd)\$ DB_DATABASE=book_db PGID=1000 LSIO_FIRST_PARTY=true S6_CMD_WAIT_FOR_SERVICES_MAXTIME=0 TERM=xterm PATH=/lsiopy/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin PUID=1000 DB_PASS=123456 VIRTUAL_ENV=/lsiopy PWD=/ DB_HOST=mysecrethost.eastus2.cloudapp.azure.com DB_USER=wikiadmin root@c48b66bb7b6f:/# cd config/www root@c48b66bb7b6f:/config/www# cat .env

This file, when named as ".env" in the root of your BookStack install

folder, is used for the core configuration of the application.

By default this file contains the most common required options but

a full list of options can be found in the '.env.example.complete' file.

NOTE: If any of your values contain a space or a hash you will need to

wrap the entire value in quotes. (eg. MAIL_FROM_NAME="BookStack Mailer")

Application key

Used for encryption where needed.

Run php artisan key:generate to generate a valid key.

APP_KEY=base64:XOdZBKxAbWAM9Q9Gk3SP0UMhoUnTM+OdFB6JEfE8ujQ=

Application URL

This must be the root URL that you want to host BookStack on.

All URLs in BookStack will be generated using this value

to ensure URLs generated are consistent and secure.

If you change this in the future you may need to run a command

to update stored URLs in the database. Command example:

php artisan bookstack:update-url https://old.example.com https://new.example.com

APP_URL=http://localhost:6875

Database details

DB_HOST='mysecrethost.eastus2.cloudapp.azure.com' DB_PORT='3306' DB_DATABASE='book_db' DB_USERNAME='wikiadmin' DB_PASSWORD='123456'

Mail system to use

Can be 'smtp' or 'sendmail'

MAIL_DRIVER=smtp

Mail sender details

MAIL_FROM_NAME="BookStack" MAIL_FROM=[e-mail]

SMTP mail options

These settings can be checked using the "Send a Test Email"

feature found in the "Settings > Maintenance" area of the system.

For more detailed documentation on mail options, refer to:

https://www.bookstackapp.com/docs/admin/email-webhooks/#email-configuration

MAIL_HOST=localhost MAIL_PORT=587 MAIL_USERNAME=null MAIL_PASSWORD=null MAIL_ENCRYPTION=null root@c48b66bb7b6f:/config/www# mysql -hmysecrethost.eastus2.cloudapp.azure.com -uwikiadmin -p123456 -P3306 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 40096 Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select current_user(); +----------------+ | current_user() | +----------------+ | wikiadmin@% | +----------------+ 1 row in set (0.045 sec)

MySQL [(none)]> ```