search

linuxserver / docker-mariadb

GNU General Public License v3.0
210 stars 45 forks source link

custom sql files on init not working #40

Closed netwrkx closed 5 years ago

netwrkx commented 5 years ago

linuxserver.io

If you are new to Docker or this application our issue tracker is ONLY used for reporting bugs or requesting features. Please use our discord server for general support.

Expected Behavior

Load custom sql files on init

Current Behavior

custom sql files wont load

Steps to Reproduce

  1. docker system prune -a
  2. docker-compose build
  3. docker-compose up -d

DOCKER-COMPOSE.YAML

version: '3'

services:
  mariadb:
    build:
      context: ./db
      dockerfile: mariadb.Dockerfile
    container_name: db-mariadb
    restart: unless-stopped
    ports:
      - "3306:3306"
    environment:
      TZ: Europe/London
      MYSQL_USER: jukebox
      MYSQL_DATABASE: ubox2
      MYSQL_PASSWORD: QoX12mvf
      MYSQL_ROOT_PASSWORD: B9lleTTy
      REMOTE_SQL: http://labs.linkstack.xyz/ubox2.sql
    volumes:
      - ${PWD}
    networks:
      - app-network

DOCKERFILE //I TRIED THIS
# ---- Base Node ----
FROM linuxserver/mariadb

DOCKERFILE //I TRIED THIS ALSO
# ---- Base Node ----
FROM linuxserver/mariadb
COPY ./ubox2.sql /config/initdb.d/ubox2.sql

Environment

*OS:RASPBERRY 3+*

Command used to create docker container (run/create/compose/screenshot)

  1. docker system prune -a
  2. docker-compose build
  3. docker-compose up -d

Docker logs

root@DietPi:/home/node_project# docker-compose build
Building mariadb
Step 1/2 : FROM linuxserver/mariadb
latest: Pulling from linuxserver/mariadb
Digest: sha256:95c9ead6d2d0d45db01e0489447e818d05189cd0890576f3999f44727dcf86e1
Status: Downloaded newer image for linuxserver/mariadb:latest
 ---> 9b351c6d2a78
Step 2/2 : COPY ./ubox2.sql /config/initdb.d/ubox2.sql
 ---> a1bf80b0b2d3
Successfully built a1bf80b0b2d3
Successfully tagged node_project_mariadb:latest
root@DietPi:/home/node_project#
root@DietPi:/home/node_project# docker-compose up -d
Creating network "node_project_app-network" with driver "bridge"
Creating volume "node_project_certbot-etc" with default driver
Creating volume "node_project_certbot-var" with default driver
Creating volume "node_project_web-root" with local driver
Creating volume "node_project_node-root" with local driver
Creating db-mariadb ... done
root@DietPi:/home/node_project#
root@DietPi:/home/node_project# docker logs db-mariadb
[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] 10-adduser: executing...
usermod: no changes

-------------------------------------
          _         ()
         | |  ___   _    __
         | | / __| | |  /  \
         | | \__ \ | | | () |
         |_| |___/ |_|  \__/

Brought to you by linuxserver.io
We gratefully accept donations at:
https://www.linuxserver.io/donate/
-------------------------------------
GID/UID
-------------------------------------

User uid:    911
User gid:    911
-------------------------------------

[cont-init.d] 10-adduser: exited 0.
[cont-init.d] 30-config: executing...
[cont-init.d] 30-config: exited 0.
[cont-init.d] 40-initialise-db: executing...
Setting Up Initial Databases
Installing MariaDB/MySQL system tables in '/config/databases' ...
2019-10-29  9:40:43 1995569984 [Note] /usr/sbin/mysqld (mysqld 10.1.41-MariaDB-0ubuntu0.18.04.1) starting as process 263 ...
OK
Filling help tables...
2019-10-29  9:41:28 1995406144 [Note] /usr/sbin/mysqld (mysqld 10.1.41-MariaDB-0ubuntu0.18.04.1) starting as process 293 ...
OK
Creating OpenGIS required SP-s...
2019-10-29  9:41:31 1996282688 [Note] /usr/sbin/mysqld (mysqld 10.1.41-MariaDB-0ubuntu0.18.04.1) starting as process 323 ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h 8c5f7ed68b67 password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/config/databases'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

2019-10-29  9:41:35 1995815744 [Note] mysqld (mysqld 10.1.41-MariaDB-0ubuntu0.18.04.1) starting as process 351 ...
Database Setup Completed
[cont-init.d] 40-initialise-db: exited 0.
[cont-init.d] 99-custom-scripts: executing...
[custom-init] no custom files found exiting...
[cont-init.d] 99-custom-scripts: exited 0.
[cont-init.d] done.
[services.d] starting services
[services.d] done.
191029 09:41:40 mysqld_safe Logging to syslog.
191029 09:41:41 mysqld_safe Starting mysqld daemon with databases from /config/databases
thelamer commented 5 years ago

Your sql dump is of a single database, it has no use ubox2 statement in it so the statements essentially go nowhere. There are different ways to dump sql but using mysqldump --databases test > dump.sql will include create and use stanzas in the dump and will work based on the database name passed test in this case.

Also as far as building a local docker image to include files that is not how you use the custom files. Your compose file arbitrarily mounts your PWD while we specifically state all data is stored in /config in both the command examples and the example of how to load custom sql on boot.

You want to bind mount a folder that already has a initdb.d/ folder in it with sql files to config ie:

/home/myuser/mariadata:/config

Where mariadata has an initdb.d folder in it.

I am going to close this issue but you can still ping me here if you are having problems with anything I explained.

netwrkx commented 5 years ago

@thelamer thanks for your response but what other ways can I export my database in this scenario

thelamer commented 5 years ago

I have not used phpmyadmin in some time , but I remember there being a bunch of options for data and structure when creating a dump there should be options to include creates and use stanzas not sure their option names in the web ui though.

netwrkx commented 5 years ago

@thelamer i've added the following to my sql file, is there any other thing i supposed to add Captureo

CREATE DATABASE IF NOT EXISTS `ubox2` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE `ubox2`;
netwrkx commented 5 years ago

@thelamer i've updated my docker-compose file as follow mariadata contains initdb.d which also contains ubox2.sql

dockerfile

# ---- Base Node ----
FROM linuxserver/mariadb

docker-compose.yaml

version: '3'

services:
  mariadb:
    build:
      context: ./db
      dockerfile: mariadb.Dockerfile
    container_name: db-mariadb
    restart: unless-stopped
    ports:
      - "3306:3306"
    environment:
      TZ: Europe/London
      MYSQL_USER: jukebox
      MYSQL_DATABASE: ubox2
      MYSQL_PASSWORD: QoX12mvf
      MYSQL_ROOT_PASSWORD: B9lleTTy
    volumes:
      - /home/node_project/db/mariadata:/config
    networks:
      - app-network

root@DietPi:/home/node_project# docker logs db-mariadb
[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] 10-adduser: executing...
usermod: no changes

-------------------------------------
          _         ()
         | |  ___   _    __
         | | / __| | |  /  \
         | | \__ \ | | | () |
         |_| |___/ |_|  \__/

Brought to you by linuxserver.io
We gratefully accept donations at:
https://www.linuxserver.io/donate/
-------------------------------------
GID/UID
-------------------------------------

User uid:    911
User gid:    911
-------------------------------------

[cont-init.d] 10-adduser: exited 0.
[cont-init.d] 30-config: executing...
[cont-init.d] 30-config: exited 0.
[cont-init.d] 40-initialise-db: executing...
[cont-init.d] 40-initialise-db: exited 0.
[cont-init.d] 99-custom-scripts: executing...
[custom-init] no custom files found exiting...
[cont-init.d] 99-custom-scripts: exited 0.
[cont-init.d] done.
[services.d] starting services
[services.d] done.
191029 19:29:53 mysqld_safe Logging to syslog.
191029 19:29:53 mysqld_safe Starting mysqld daemon with databases from /config/databases
root@DietPi:/home/node_project# docker exec -it db-mariadb mysql -ujukebox -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.41-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ubox2              |
+--------------------+
2 rows in set (0.01 sec)

MariaDB [(none)]> use ubox2
Database changed
MariaDB [ubox2]> show tables;
Empty set (0.00 sec)

MariaDB [ubox2]>
MariaDB [ubox2]> exit
Bye
root@DietPi:/home/node_project# docker exec -it db-mariadb /bin/sh
# ls
app  bin  boot  config  defaults  dev  docker-mods  etc  home  init  lib  libexec  media  mnt  opt  proc  root  run  sbin  srv  sys  tmp  usr  var
# cd onfig
/bin/sh: 2: cd: can't cd to onfig
# cd config
# ls
custom.cnf  databases  initdb.d  log
# cd initdb.d
# ls
ubox2.sql
#
thelamer commented 5 years ago

The problem you are running into now is the DB is already started and populated as it sees it, none of the init logic runs if mysql folderexists in the config folder the specific check for all the init logic is this: https://github.com/linuxserver/docker-mariadb/blob/master/root/etc/cont-init.d/40-initialise-db#L16

You would need a clean /config folder with just the initdb.d folder in it for it to bootstrap again.

netwrkx commented 5 years ago

@thelamer i've clear my config folder leaving only initdb.d, in initdb.d is my ubox2.sql. database ubox2 was created but its empty, no table or data below is the latest log

root@DietPi:/home/node_project# docker logs db-mariadb
[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] 10-adduser: executing...
usermod: no changes

-------------------------------------
          _         ()
         | |  ___   _    __
         | | / __| | |  /  \
         | | \__ \ | | | () |
         |_| |___/ |_|  \__/

Brought to you by linuxserver.io
We gratefully accept donations at:
https://www.linuxserver.io/donate/
-------------------------------------
GID/UID
-------------------------------------

User uid:    911
User gid:    911
-------------------------------------

[cont-init.d] 10-adduser: exited 0.
[cont-init.d] 30-config: executing...
[cont-init.d] 30-config: exited 0.
[cont-init.d] 40-initialise-db: executing...
Setting Up Initial Databases
Installing MariaDB/MySQL system tables in '/config/databases' ...
2019-10-29 21:03:09 1995664192 [Note] /usr/sbin/mysqld (mysqld 10.1.41-MariaDB-0ubuntu0.18.04.1) starting as process 263 ...
OK
Filling help tables...
2019-10-29 21:03:28 1996348224 [Note] /usr/sbin/mysqld (mysqld 10.1.41-MariaDB-0ubuntu0.18.04.1) starting as process 292 ...
OK
Creating OpenGIS required SP-s...
2019-10-29 21:03:32 1995901760 [Note] /usr/sbin/mysqld (mysqld 10.1.41-MariaDB-0ubuntu0.18.04.1) starting as process 322 ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h a54d1e35c4ef password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/config/databases'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

2019-10-29 21:03:36 1996254016 [Note] mysqld (mysqld 10.1.41-MariaDB-0ubuntu0.18.04.1) starting as process 350 ...
Database Setup Completed
[cont-init.d] 40-initialise-db: exited 0.
[cont-init.d] 99-custom-scripts: executing...
[custom-init] no custom files found exiting...
[cont-init.d] 99-custom-scripts: exited 0.
[cont-init.d] done.
[services.d] starting services
[services.d] done.
191029 21:03:41 mysqld_safe Logging to syslog.
191029 21:03:41 mysqld_safe Starting mysqld daemon with databases from /config/databases
root@DietPi:/home/node_project#
root@DietPi:/home/node_project#
root@DietPi:/home/node_project# docker exec -it db-mariadb /bin/sh
# cd /config/initdb.d
# ls
ubox2.sql
#
#
# mysql -ujukebox -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.41-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ubox2              |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use ubox2
Database changed
MariaDB [ubox2]> show tables;
Empty set (0.00 sec)

MariaDB [ubox2]>

what am i missing

thelamer commented 5 years ago

If you have use before the table creates than it should just be working please pastebin the full SQL file so I can test it locally

netwrkx commented 5 years ago

@thelamer here is my ubox2.sql

thelamer commented 5 years ago

looks like your issue is here: https://jira.mariadb.org/browse/MDEV-20912

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Looks like some kind of regression as this was an old character set introduced in MySQL 5.7. you can dump in like mysql 4.0 compatibility mode to just use UTF8. (under format specific options)

netwrkx commented 5 years ago

@thelamer thanks, finally resolved it. i had to manually replace utf8mb4_0900_ai_ci with utf8_general_ci replace utf8mb4 with utf8

switching to mysql4.0 compatibility mode didnt help. thanks