create tables on docker start

Question

I am new to docker, i have created my application and i want to send to another developer but i cant seem to run it running docker.

It says tables dont exist. I have read the docker doc but i dont get it.

The table are not created.

WHAT AM I DOING WRONG?

my dockerfile

FROM php:7.1-apache

RUN docker-php-ext-install pdo pdo_mysql

COPY ./dump.sql /docker-entrypoint-initdb.d/

my docker-composer

version: '2'

volumes:
    logs:
        driver: local

services:
    slim:
        build: .
        working_dir: /var/www
        command: php -S 0.0.0.0:8080 -t public
        environment:
            docker: "true"
        depends_on:
          - db-mysql
        ports:
            - 80:8080
        volumes:
            - .:/var/www
            - logs:/var/www/logs
        links:
          - db-mysql

    db-mysql:
      image: mysql
      restart: always
      container_name: db-mysql
      ports:
        - "3307:3306"
      environment:
        MYSQL_DATABASE: path
        MYSQL_ROOT_PASSWORD: root
        MYSQL_USER: root
        MYSQL_PASSWORD: root
      volumes:
        - ./mysql_init:/docker-entrypoint-initdb.d
        - ./dump.sql:/docker-entrypoint-initdb.d

and my dump.sql has

CREATE TABLE IF NOT EXIST `paths` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lat` double DEFAULT NULL,
  `long` double DEFAULT NULL,
  `token` varchar(225) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

I think the `dump.sql` file is supposed to be copied in the `mysql` image, not the `php:7.1-apache`. — tgogos, Mar 30 '18 at 10:59
I've written an answer here: [Dockerize MySQL with database and tables](https://stackoverflow.com/questions/44239766/dockerize-mysql-with-database-and-tables/44240369#44240369). — tgogos, Mar 30 '18 at 11:02
ok but can i have 2 froms in my Dockerfile like FROM php and FROM mysql — Schnecke, Mar 30 '18 at 11:04
You can define different `Dockerfiles` for each service. But this means that you will have to change `build: .` and use actual paths to Dockerfiles. — tgogos, Mar 30 '18 at 11:07

score 9 · Accepted Answer · edited Feb 28 '21 at 09:48

You have to add your entrypoint script to your database. Not to your application (the dockerfile is not clear for me). For more detail you can look to this answer.

docker-compose.yml (basic without persistency)

version: '3.1'

services:
  mysql:
   image: mysql
   restart: always
   container_name: db-mysql
   ports:
    - 3307:3306
   environment:
     MYSQL_DATABASE: path
     MYSQL_ROOT_PASSWORD: root
     MYSQL_USER: testuser
     MYSQL_PASSWORD: testpassword
   volumes:
    - ./dump:/docker-entrypoint-initdb.d

first check the users. A root user does already exist by default so give the mysql_user a different name. secondly I mount a directory, not a file. I have a dir dump which contains my dump.sql (very basic too):

CREATE TABLE paths (
  id int(11)
)

Tree looks like this:

docker-compose.yml
dump/

dump/ contains dump.sql

During container startup this is dump/ directory mounted inside the mysql container.

$ docker-compose up -d

docker exec -it db-mysql bash

Authenticate (you can choose, root_user of normal user) I use the normal one:

root@a110fe08e4b6:/# mysql -u testuser -p
Enter password:

Check db's:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| path               |
+--------------------+
2 rows in set (0.00 sec)

use path and check tables:

mysql> use path;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_path |
+----------------+
| paths          |
+----------------+
1 row in set (0.00 sec)

show columns from paths table:

mysql> show columns from paths;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

what's the error? I've tested it (printed the outputs) so it should work. — lvthillo, Mar 30 '18 at 11:41
no errors just this mysql> use path; Database changed mysql> show tables; Empty set (0.00 sec) — Schnecke, Mar 30 '18 at 11:43
Did you copy past my docker-compose.yml file? and the same dump dir and dump.sql. Start from there. When it works you can update the dump.sql etc. — lvthillo, Mar 30 '18 at 11:46
when i run your code i get this:yaml.parser.ParserError: while parsing a block mapping in "./docker-compose.yml", line 8, column 5 expected , but found '' in "./docker-compose.yml", line 24, column 6 — Schnecke, Mar 30 '18 at 11:49
It's a valid yaml (check here: http://yaml-online-parser.appspot.com/) I'm sorry. I can't help you copy pasting. — lvthillo, Mar 30 '18 at 11:52
in my case, error is due to sql syntax. please check the docker log may helpful. — msmaromi, Feb 08 '20 at 09:09

create tables on docker start

1 Answers1