[FIXED] Docker Compose: MySQL Syntax Error "DELIMITER" On Line 1

Issue

I have the Problem that I want Docker Compose to directly import my Database.sql file after setting up MySQL. But it always throws me the Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER |' at line 1

But somehow when I import it through phpMyAdmin in Browser it works perfectly fine.

This is my docker-compose.yml:

version: "3.2"
services:
  php:
    build: './php/'
    networks:
      - backend
    volumes:
      - ./www/:/var/www/html/
  apache:
    build: './apache/'
    depends_on:
      - php
      - mysql
    networks:
      - frontend
      - backend
    ports:
      - "8081:80"
    volumes:
      - ./www/:/var/www/html/
  mysql:
    image: mysql:5.7
    volumes:
      - ./mysql:/tmp/database
    command: mysqld --max_allowed_packet=32505856 --user=root --init-file="/tmp/database/schema.sql"
    networks:
      - backend
    environment:
      - MYSQL_ROOT_PASSWORD=root
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
        - mysql
    ports:
      - '8082:80'
    environment:
      MYSQL_USERNAME: root
      MYSQL_ROOT_PASSWORD: root
      PMA_HOST: mysql
    networks:
      - backend
networks:
  frontend:
  backend:

This is an Example Snippet of my .sql File which gets executed:

-- phpMyAdmin SQL Dump
-- version 4.8.2
-- https://www.phpmyadmin.net/
--
-- Host: mysql
-- Generation Time: Aug 20, 2018 at 08:30 AM
-- Server version: 5.7.23
-- PHP Version: 7.2.6
DELIMITER $$

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `database`
--
CREATE DATABASE IF NOT EXISTS `databasetest` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `databasetest`;

--
-- Procedures
--
DROP PROCEDURE IF EXISTS `sp_filestatus_get`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_filestatus_get` (IN `filename_name` VARCHAR(500), IN `kampagne_name` VARCHAR(100), IN `outlet_name` VARCHAR(100))  NO SQL

... Some more stuff

END;

Did I miss something in the configuration? Or why doesnt it work with compose but directly in phpMyAdmin it works?

Solution

If you really need your DELIMITER statement, you could change your command for mysql to

bash -c "service mysql start && echo 'xxxxxxxxxxxxxxxxx' && mysql --max_allowed_packet=32505856 -u root -proot < /tmp/database/schema.sql && while true; do sleep 1; done"

then it should work without any other change.

So your mysql-section should look like this:

  mysql:
    image: mysql:5.7
    volumes:
      - ./mysql:/tmp/database
    command: bash -c "service mysql start && echo 'xxxxxxxxxxxxxxxxx' && mysql --max_allowed_packet=32505856 -u root -proot < /tmp/database/schema.sql && while true; do sleep 1; done"
    networks:
      - backend
    environment:
      - MYSQL_ROOT_PASSWORD=root

---

Explanation why it works:

Before, you were starting the mysql-server using the mysqld command and specifying the init-script. Now it is started as a service. This brings one advantage and one disadvantage.

• Advantage: You can now connect to your mysql-server using mysql command.
• Disadvantage: After you've specified the mysql command, the container will be stopped by docker.

To handle the disadvantage, I've added while true; do sleep 1; done as last command. This causes the container to run until it gets stopped. The echo ist just for you to see that the commands get executed.

Answered By - Halko Karr-Sajtarevic