Docker Compose: MySQL Syntax Error "DELIMITER" On Line 1

Question

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?

Answer 1

The DELIMETER isn't supported by sql API directly, since the support of the statement is defined by the client it works in phpmyadmin, because it supports that. The default mysql client uses delimeter statement, follow suggested steps in the Stored programs defining and update the sql file accordingly ( The CREATE PROCEDURE shall be replaced with your sql statements).

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

According to the script you are using, you have to handle the delimeter differently, than it is now:

Replace

DROP PROCEDURE IF EXISTS `sp_filestatus_get`$$

to

DROP PROCEDURE IF EXISTS `sp_filestatus_get`;

Also, please replace END; to END $$ and add DELIMITER ; at the end of file.

Here is some links which shall be use full for you in finding things to adjust in the init sql script:

• Delimeter issue in mysql 5.6
• Syntax Error using delimiter with MySqlCommand
• Delimeter eror

Answer 2

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.

Answer 3

think I might have stumbled onto a less hacky solution by using /docker-entrypoint-initdb.d/ instead of the init-file=... flag:

docker-compose.yml
  db:
    image: "mysql:5.7.33"
    container_name: "mydb"
    command: "--default-authentication-plugin=mysql_native_password"
    volumes: # Scripts will apparently be executed by ascending order. See https://github.com/docker-library/mysql/issues/395#issuecomment-375409393
    - db-datavolume:/var/lib/mysql
    - ./db/init.sql:/docker-entrypoint-initdb.d/init1.sql
    - ./db/triggers.sql:/docker-entrypoint-initdb.d/init2.sql
    - ./db/seed.sql:/docker-entrypoint-initdb.d/init3.sql
  ...

volumes:
  db-datavolume

After that, in the host machine's ./db/triggers.sql (which mounts to /docker-entrypoint-initdb.d/init2.sql), DELIMETER works just fine.