Access denied for user 'root'@'localhost' with mariadb 10.4.8 docker container using docker compose and issue while attaching external volume

Question

I am new to Docker, I was trying to crate docker container of mariadb for my application but when I start running mariadb container it shows Access denied for user 'root'@'localhost' (using password: YES) dockerfile

Following is the docker compose I am using.

version: '3'

services:
  mysql:
    image: mariadb
    container_name: mariadb
    volumes:
      - dbvolume:/var/lib/mysql
      - ./AppDatabase.sql:/docker-entrypoint-initdb.d/AppDatabase.sql
    environment:
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_ROOT_USER: root
      MYSQL_USER: root
      MYSQL_PASSWORD: root123
      MYSQL_DATABASE: appdata
    ports:
      - "3333:3306"

volumes:
  dbvolume:

After trying for multiple times by referring few links I was able to connect my application to docker container but it failed to import AppDatabase.sql script at the time of creating docker container.

But now by using same docker compose file I am not able to connect mariadb to my application and I think even it's not importing SQL script to the database (based on logs I have observed).

Following is the docker log generated while running docker compose:

$ docker logs 3fde358ff015
2019-09-24 17:40:37 0 [Note] mysqld (mysqld 10.4.8-MariaDB-1:10.4.8+maria~bionic) starting as process 1 ...
2019-09-24 17:40:37 0 [Note] InnoDB: Using Linux native AIO
2019-09-24 17:40:37 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-09-24 17:40:37 0 [Note] InnoDB: Uses event mutexes
2019-09-24 17:40:37 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-09-24 17:40:37 0 [Note] InnoDB: Number of pools: 1
2019-09-24 17:40:37 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-09-24 17:40:37 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
2019-09-24 17:40:37 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
2019-09-24 17:40:37 0 [Note] InnoDB: Completed initialization of buffer pool
2019-09-24 17:40:37 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-09-24 17:40:37 0 [Note] InnoDB: Upgrading redo log: 2*50331648 bytes; LSN=21810033
2019-09-24 17:40:38 0 [Note] InnoDB: Starting to delete and rewrite log files.
2019-09-24 17:40:38 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
2019-09-24 17:40:38 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes
2019-09-24 17:40:38 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2019-09-24 17:40:38 0 [Note] InnoDB: New log files created, LSN=21810033
2019-09-24 17:40:38 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-09-24 17:40:38 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-09-24 17:40:38 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-09-24 17:40:38 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-09-24 17:40:38 0 [Note] InnoDB: Waiting for purge to start
2019-09-24 17:40:38 0 [Note] InnoDB: 10.4.8 started; log sequence number 21810033; transaction id 14620
2019-09-24 17:40:38 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-09-24 17:40:38 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-09-24 17:40:38 0 [Note] Server socket created on IP: '::'.
2019-09-24 17:40:38 0 [Warning] 'proxies_priv' entry '@% root@c980daa43351' ignored in --skip-name-resolve mode.
2019-09-24 17:40:38 0 [Note] InnoDB: Buffer pool(s) load completed at 190924 17:40:38
2019-09-24 17:40:38 0 [Note] Reading of all Master_info entries succeeded
2019-09-24 17:40:38 0 [Note] Added new Master_info '' to hash table
2019-09-24 17:40:38 0 [Note] mysqld: ready for connections.
Version: '10.4.8-MariaDB-1:10.4.8+maria~bionic'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

SQL Script I am trying to import:

create database appdata;
use appdata;

CREATE TABLE `appdatadetails` (
  `Name` varchar(8) NOT NULL,
  `appIndex` int(11) NOT NULL,
  `connector` varchar(16) DEFAULT NULL,
  `intName` varchar(12) DEFAULT NULL,
  `intIndex` int(11) DEFAULT NULL,
  PRIMARY KEY (`Name`,`appIndex`)
) 

Please help me to understand what I am doing wrong, I have tried all possible solution posted on different blogs.

---

Update:

---

Latest Update:

I was able to up and running mariadb docker image with 10.1. But if I attach volume then still I am facing issue.

Docker Compose:

version: '3'

services:
  mysql:
    image: mariadb:10.1
    container_name: mariadb
    volumes:
      - container-volume:/var/lib/mysql
      - ./AppDatabase.sql:/docker-entrypoint-initdb.d/AppDatabase.sql
    environment:
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_DATABASE: appdata
    ports:
      - "3333:3306"

volumes:
  container-volume:

And the log error message, If I attach container-volume volume.

Creating mariadb ... done
Attaching to mariadb
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] mysqld (mysqld 10.1.41-MariaDB-1~bionic) starting as process 1 ...
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: Using mutexes to ref count buffer pool pages
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: The InnoDB memory heap is disabled
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: Compressed tables use zlib 1.2.11
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: Using Linux native AIO
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: Using SSE crc32 instructions
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: Initializing buffer pool, size = 256.0M
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: Completed initialization of buffer pool
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] InnoDB: Highest supported file format is Barracuda.
mariadb  | InnoDB: No valid checkpoint found.
mariadb  | InnoDB: A downgrade from MariaDB 10.2.2 or later is not supported.
mariadb  | InnoDB: If this error appears when you are creating an InnoDB database,
mariadb  | InnoDB: the problem may be that during an earlier attempt you managed
mariadb  | InnoDB: to create the InnoDB data files, but log file creation failed.
mariadb  | InnoDB: If that is the case, please refer to
mariadb  | InnoDB: http://dev.mysql.com/doc/refman/5.6/en/error-creating-innodb.html
mariadb  | 2019-09-25  6:56:26 140542855440384 [ERROR] Plugin 'InnoDB' init function returned error.
mariadb  | 2019-09-25  6:56:26 140542855440384 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
mariadb  | 2019-09-25  6:56:26 140542855440384 [Note] Plugin 'FEEDBACK' is disabled.
mariadb  | 2019-09-25  6:56:26 140542855440384 [ERROR] Unknown/unsupported storage engine: InnoDB
mariadb  | 2019-09-25  6:56:26 140542855440384 [ERROR] Aborting
mariadb  | 
mariadb exited with code 1

If I remove container-volume then It is importing .sql script and running well and good.

---

Updated with working script: Before I was using mariadb 10.4.8 or latest and facing issue(s) to access DB and attaching external volume.

Now I have downgraded (As suggested by @Adiii) and tried. It runs perfectlly and we no need to specify external: true in volumes service

version: '3'

services:
  mysql:
    image: mariadb:10.1
    container_name: mariadb
    volumes:
      - ./dbvolume:/var/lib/mysql
      - ./AppDatabase.sql:/docker-entrypoint-initdb.d/AppDatabase.sql
    environment:
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_DATABASE: appdata
    ports:
      - "3333:3306"

Answer 1

It will affect the new user and keys even if you create fresh container.

Remove mount location, as it will pick a user name and password from this location, also will not run your DB init script too.

    volumes:
      - dbvolume:/var/lib/mysql

Also you do not need

create database appdata;

As database already created at

      MYSQL_ROOT_PASSWORD: root123
      MYSQL_ROOT_USER: root
      MYSQL_DATABASE: appdata

this step.

update:

remove the user root, as root already defined. you can try with

version: '3.7'
services:

  mysql:
    environment:
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_DATABASE: appdata
    image: mariadb

Or the second user should be different

version: '3.7'

services:

  mysql:
    environment:
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_DATABASE: appdata
      MYSQL_USER: test
      MYSQL_PASSWORD: root123
    image: mariadb

you can try with

docker exec -it container_name bash -c "mysql -u test -proot123"

or

docker exec -it mysql bash -c "mysql -u root -proot123"

If still issue, remove the DB image, pull a new one.

Or try image tag 10.1

If I remove container-volume then It is importing .sql script and running well and good.

if mount the location then the init script will not run as the container is expecting that there is already DB or try to remove named volume and create new one.

So mount the location, import the DB using MySQL command and use the mount location for next time.