skip to Main Content

I’ve been working with a simple MySQL table using Docker Compose that only included ID and NAME column. I attempted to update my myDb.sql file that initially creates the table like this:

CREATE TABLE `Person` (
  `id` int(11) NOT NULL,
  `firstName` varchar(50) NOT NULL, // updated this column
  `lastName` varchar(50) NOT NULL  // added this column
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I updated the NAME column to firstName, and added a lastName column.

I then stopped the Docker containers by running DOCKER-COMPOSE STOP.

I then restarted the Docker containers by running DOCKER-COMPOSE UP. I even tried DOCKER-COMPOSE RESTART.

The error message I was able to print in the console was this:

SELECT '', id, `firstName` From Person<br>Unknown column 'firstName' in 'field list'

This leads me to believe that I did not restart Docker Compose correctly.

How do I restart Docker Compose so that it runs the CREATE TABLE command?

Edit

Here is my docker-compose.yml file:

version: "3.7"
services:
www:
    build: .
    ports: 
        - "8001:80"
    volumes:
        - ./www:/var/www/html/
    links:
        - db
    networks:
        - default
db:
    image: mysql:5.7.13
    ports: 
        - "3306:3306"
    environment:
        MYSQL_DATABASE: myDb
        MYSQL_USER: user
        MYSQL_PASSWORD: test
        MYSQL_ROOT_PASSWORD: test
    volumes:
        - ./dump:/docker-entrypoint-initdb.d
        - persistent:/var/lib/mysql
    networks:
        - default
phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links: 
        - db:db
    ports:
        - 8000:80
    environment:
        MYSQL_USER: user
        MYSQL_PASSWORD: test
        MYSQL_ROOT_PASSWORD: test
volumes:
    persistent:

My Dockerfile looks like this:

FROM php:7.0.30-apache 
RUN docker-php-ext-install mysqli

3

Answers


  1. Chosen as BEST ANSWER

    So I was thinking that restarting docker-compose would automatically run the CREATE TABLE query in the myDb.sql file. As indicated above, I changed the name of 'name' column to 'firstName' and added a column called 'lastName'.

    Again, I am not sure if there was a command that actually does this, but in the end, I was able to alter the table in phpmyadmin. Once I altered the table there, now I am getting data back to the page with no errors.


  2. The docker-entrypoint-initdb.d mechanism only runs the first time a database container starts up, with an empty database. You’ll need to explicitly docker-compose rm your containers after you docker-compose stop them to cause the current database to be deleted, and then a new empty table will be created in a new empty database.

    If you need to preserve the data in an existing database, you are looking for a mechanism called a migration. The various Docker database images don’t directly have migration support; this is almost always something that is packaged with your application-level database library (Ruby on Rails and Python’s SQLAlchemy both have migration facilities, for instance).

    Once you have a migration system anyways, it’s probably better to just use that to create the initial database tables. docker-entrypoint-initdb.d makes a little more sense for database-level setup like creating initial users or loading a seed database dump, but in practice you will always need a migration system for changes like what you’re describing.

    Login or Signup to reply.
  3. Do docker-compose down whenever you update your schema, it will remove containers and docker network and do docker-compose up to bring your environment with your new schema. Hope it helps. If not, try updating to latest mysql image, the image you are using is almost 3 years old.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search