How to use placeholders prefix in flyway command line

Originally posted in https://github.com/flyway/flyway/issues/2429

I have an issue (probably a wrong configuration) using flyway placeholders; I can use placeholders for my own variables; but it fails cos one value in an sql query has a similar syntax as flyway placeholder syntax.

Which version and edition of Flyway are you using?

5.2.4 using official docker image

If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)

5.2.4 tag is the latest version in docker hub (https://hub.docker.com/r/boxfuse/flyway/)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Command line thru the docker image

Which database are you using (type & version)?

MySQL Server version: 5.7.26 - MySQL Community Server (GPL) - This is a legacy project

Which operating system are you using?

Linux CentOS 7 x64 (uname -r = 3.10.0-957.5.1.el7.x86_64)

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

I apply flyway to initialize/update a MySQL database; here are a couple of SQL commands.

Here I use placeholders with xxx prefixes:

CREATE USER IF NOT EXISTS '${xxxdbuser}'@'${xxxdbclip}' IDENTIFIED WITH mysql_native_password BY '${xxxdbpass}';
GRANT ALL PRIVILEGES ON ${xxxdbbase}.* TO '${xxxdbuser}'@'${xxxdbclip}';
FLUSH PRIVILEGES;

... then in another SQL script, from a thirdparty app, I insert a content with ${row}. I don't want Flyway to interpret ${row} as a placeholder, only my own vars starting by ${xxx such as ${xxxdbuser}

INSERT INTO `xxx_xxx` (`name`, `template`, `lang`, `group`, `version`, `data`, `size`, `style`, `modified`) VALUES
... ('addressbook.email.rows', '', '', 0, '1.3.001', 'a:1:{i:0;a:6:{ ... \"label\";s:21:\"$row_cont[type_label]\";s:4:\"name\";s:12:\"${row}[type]\";s:5:\"align\";... :{i:0;s:4:\"100%\";}}}', '100%', '', 1150326789), ...

I guess the placeholderPrefix parameter described into https://flywaydb.org/documentation/commandline/info or FLYWAY_PLACEHOLDER_PREFIX env var described into https://flywaydb.org/documentation/envvars#FLYWAY_PLACEHOLDER_PREFIX is for that purpose; but I didn't succeed in using them!

Here is my command using docker:

docker run --rm --network="$(docker network ls --filter name=app_mysql_dev --filter "label=type=app" --format '{{.ID}}')" \
    -v `pwd`/code/Admin/install:/flyway/sql \
    -e FLYWAY_URL=jdbc:mysql://${host}:${port}?useSSL=false \
    -e FLYWAY_SCHEMAS=${base} \
    -e FLYWAY_USER=root \
    -e FLYWAY_PASSWORD=${root_pwd} \
    -e FLYWAY_PLACEHOLDERS_PREFIX="\${xxx" \
    -e FLYWAY_PLACEHOLDERS_XXXDBBASE=${base} \
    -e FLYWAY_PLACEHOLDERS_XXXDBUSER=${user} \
    -e FLYWAY_PLACEHOLDERS_XXXDBPASS=${pass} \
    -e FLYWAY_PLACEHOLDERS_XXXDBCLIP=${clip} \
    -e FLYWAY_PLACEHOLDERS_XXXVHOST=${vhost} \
    -e FLYWAY_PLACEHOLDERS_XXXSCHEME=${scheme} \
    -e FLYWAY_CONNECT_RETRIES=5 \
    boxfuse/flyway:5.2.4 -locations=filesystem:/flyway/sql/custom/ \
    migrate

What did you expect to see?

All ${xxx placeholders should be replaced by their corresponding ENV values; and the ${row} chain in SQL code stay unchanged.

What did you see instead?

Flyway error:

Flyway Community Edition 5.2.4 by Boxfuse
Database: jdbc:mysql://tasks.atlas-mysql:3306 (MySQL 5.7)
ERROR: No value provided for placeholder expressions: ${row}.  Check your configuration!

I guess I did not configure my command correctly... any help, advise and/or command line example would help.

Regrads,

Chris

Solution

I think there are a couple of problems in your command:

-e FLYWAY_PLACEHOLDERS_PREFIX="\${xxx"

should be FLYWAY_PLACEHOLDER_PREFIX (no S), and

-e FLYWAY_PLACEHOLDERS_XXXDBBASE=${base}

should be FLYWAY_PLACEHOLDERS_DBBASE (as XXX is part of the prefix, it's not included in the placeholder name; and analogously for following lines).