Dockerfile does not create tables from my initialize.sql

Question

I want to do this customize-your-mysql-database-in-docker

I get to the point where I execute the docker container and my database "studienarbeit" is shown to me. But when I use the command show tables it says to me that there are no tables in the database.

So I assume that there is something wrong with my Dockerfile or my initializeDB.sql?

Dockerfile:

FROM mysql
ENV MYSQL_DATABASE studienarbeit
COPY ./sql-scripts/ /docker-entrypoint-initdb.d/

InitializeDB.sql:

CREATE TABLE `buchausleihe` (
  `ausleihnummer` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(45) NOT NULL,
  `Typ` varchar(45) NOT NULL,
  `personid` int(11) NOT NULL,
  `seriennummer` int(11) NOT NULL,
  `Ausleihdatum` varchar(45) NOT NULL,
  `Rückgabedatum` varchar(45) NOT NULL,
  UNIQUE KEY `ausleihnummer_UNIQUE` (`ausleihnummer`),
  UNIQUE KEY `seriennummer_UNIQUE` (`seriennummer`),
  KEY `personid_idx` (`personid`),
  CONSTRAINT `personid` FOREIGN KEY (`personid`) REFERENCES `person` (`personid`) ON DELETE CASCADE,
  CONSTRAINT `seriennummer` FOREIGN KEY (`seriennummer`) REFERENCES `buchexemplar` (`seriennummer`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `buchexemplar` (
  `Seriennummer` int(11) NOT NULL,
  `Buchstatus` varchar(45) NOT NULL,
  `buchid` int(11) NOT NULL,
  PRIMARY KEY (`Seriennummer`),
  KEY `buchid_idx` (`buchid`),
  CONSTRAINT `buchid` FOREIGN KEY (`buchid`) REFERENCES `buchtyp` (`buchid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `buchtyp` (
  `Buchid` int(11) NOT NULL AUTO_INCREMENT,
  `Autor` varchar(45) NOT NULL,
  `Titel` varchar(45) NOT NULL,
  `ISBN` int(11) NOT NULL,
  PRIMARY KEY (`Buchid`),
  UNIQUE KEY `ISBN_UNIQUE` (`ISBN`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `person` (
  `PersonID` int(11) NOT NULL AUTO_INCREMENT,
  `Typ` varchar(45) NOT NULL,
  `Name` varchar(45) NOT NULL,
  `Vorname` varchar(45) NOT NULL,
  `Faku` varchar(45) DEFAULT NULL,
  `Matrikelnr` int(11) DEFAULT NULL,
  `Gruppe` varchar(45) DEFAULT NULL,
  `Straße` varchar(45) DEFAULT NULL,
  `Hausnummer` varchar(45) DEFAULT NULL,
  `Ort` varchar(45) DEFAULT NULL,
  `Plz` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`PersonID`),
  UNIQUE KEY `PersonID_UNIQUE` (`PersonID`),
  UNIQUE KEY `Matrikelnr_UNIQUE` (`Matrikelnr`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `student` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(45) NOT NULL,
  `Gruppe` varchar(45) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Adiii · Accepted Answer · 2019-07-17 14:47:28Z

1

The issue is not the docker, Your SQL file is not valid. you creating the table below at your file and referencing tables at the start of the script.

Checks the logs of your docker container.

use studienarbeit;
CREATE TABLE `buchtyp` (
  `Buchid` int(11) NOT NULL AUTO_INCREMENT,
  `Autor` varchar(45) NOT NULL,
  `Titel` varchar(45) NOT NULL,
  `ISBN` int(11) NOT NULL,
  PRIMARY KEY (`Buchid`),
  UNIQUE KEY `ISBN_UNIQUE` (`ISBN`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `person` (
  `PersonID` int(11) NOT NULL AUTO_INCREMENT,
  `Typ` varchar(45) NOT NULL,
  `Name` varchar(45) NOT NULL,
  `Vorname` varchar(45) NOT NULL,
  `Faku` varchar(45) DEFAULT NULL,
  `Matrikelnr` int(11) DEFAULT NULL,
  `Gruppe` varchar(45) DEFAULT NULL,
  `Straße` varchar(45) DEFAULT NULL,
  `Hausnummer` varchar(45) DEFAULT NULL,
  `Ort` varchar(45) DEFAULT NULL,
  `Plz` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`PersonID`),
  UNIQUE KEY `PersonID_UNIQUE` (`PersonID`),
  UNIQUE KEY `Matrikelnr_UNIQUE` (`Matrikelnr`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `student` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(45) NOT NULL,
  `Gruppe` varchar(45) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `buchexemplar` (
  `Seriennummer` int(11) NOT NULL,
  `Buchstatus` varchar(45) NOT NULL,
  `buchid` int(11) NOT NULL,
  PRIMARY KEY (`Seriennummer`),
  KEY `buchid_idx` (`buchid`),
  CONSTRAINT `buchid` FOREIGN KEY (`buchid`) REFERENCES `buchtyp` (`buchid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `buchausleihe` (
  `ausleihnummer` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(45) NOT NULL,
  `Typ` varchar(45) NOT NULL,
  `personid` int(11) NOT NULL,
  `seriennummer` int(11) NOT NULL,
  `Ausleihdatum` varchar(45) NOT NULL,
  `Rückgabedatum` varchar(45) NOT NULL,
  UNIQUE KEY `ausleihnummer_UNIQUE` (`ausleihnummer`),
  UNIQUE KEY `seriennummer_UNIQUE` (`seriennummer`),
  KEY `personid_idx` (`personid`),
  CONSTRAINT `personid` FOREIGN KEY (`personid`) REFERENCES `person` (`personid`) ON DELETE CASCADE,
  CONSTRAINT `seriennummer` FOREIGN KEY (`seriennummer`) REFERENCES `buchexemplar` (`seriennummer`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

answered Jul 17, 2019 at 14:47

Adiii

61.1k10 gold badges180 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Luciore95 Over a year ago

you are a very nice person for helping me out! Made my Day! Finally its working - thank you really much (i made an export out of mySQL the second you answered and tried my Dockerfile again and boom - its working)

Adiii Over a year ago

u can also accept the other as per your that question

Luciore95 Over a year ago

i have one side question - if i connect to this database inside the docker container now and do some inserts with my java program and stop the container - does it commit those inserts and update them so that i see them if i start the container again?

Adiii Over a year ago

I will recomend mouting just mount mysql direcotry then no worry about database single query lose

Collectives™ on Stack Overflow

Dockerfile does not create tables from my initialize.sql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related