How to seed a mysql database?

[Christian McHugh]

Greetings,

I am creating a mysql database with the salt mysq_database.present, creating a user with mysql_user.present, and now need to seed the initial schema. 

I have data in a text file provided by the application, and would normally run mysql -h servername -u root -psomepass < provided_file.sql

The salt option appears to be the mysql_query.run state:

query_id:
  mysql_query.run
    - database: my_database
    - query:    "SELECT * FROM table;"
    - output:   "/tmp/query_id.txt"

Since query seems to be the only interface allowing me to interact with mysql, I thought I might be able to import my sql file, and pass that into the query like:

{%- import_text "provided-schema.sql" as schema %}

import_sql_schema:

  mysql_query.run:

    - database: 'some_db'

    - query: {{ schema }}

But this seems to throw lots of errors based on the contents of the sql file. 

    Data failed to compile:

----------

    Rendering SLS 'base:ztest.schema' failed: Illegal tab character; line 7

I figure I can't be the first person to want to import data into mysql with salt, so what is everyone else doing? 

Thanks much!

[Christian McHugh]

Welp, made some progress and hit some snags.

I was able to work around the illegal tab character in the query section by adding in the striptags filter

{%- import_text "provided-schema.sql" as schema %}

import_sql_schema:

  mysql_query.run:

    - database: 'some_db'

    - query: {{ schema | striptags() }}

And this seems to have worked! However, when doing this with additional sql files, some of which include # in the data, I now get the error:

Rendering SLS 'base:ztest.schema' failed: Jinja syntax error: Missing end of comment tag;

This error is generated from the {%- import_text "provided-schema.sql" as schema %} part itself. So since the import_text seems to be validating the input and not just storing it, I think I'm unable to progress further.

Is there an alternate way to get the contents of a text file into a variable in jinja for use in the mysql_query.run state, or am I trying to do something crazy? 

[Christian McHugh]

I came up with one last way to try to force this, rather than try to use jinja to save the sql into a variable, I could just use salt to read in the file directly in the query. 

sql_data:

  mysql_query.run:

    - database: 'zabbix_db'

    - query: |

          {{ salt['file.grep']('/tmp/provided-data.sql', '*')['stdout'] }}

Unfortunately, this too seems to choke on something in the file with an error:

    Data failed to compile:

----------

    Rendering SLS 'base:ztest.schema' failed: could not found expected ':'; line 8

So after all of this, I'm still left wondering how anyone manages to seed a mysql data with a sql file without having to resort to installing the mysql client (with all the associated overhead of mapping package names for each OS) and shelling out. For trivial files, the above seems to work just fine, but when exposed to larger sql datasets, I see errors.

Does anyone have any thoughts?

[Christian McHugh]

To close this out, there didn't seem to be a good solution for this, so I worked up a file based mysql query, which has been accepted. 

As of the next release (nitrogen) you should now be able to fully provision a mysql db without having to resort to installing the mysql client. 

init_db:
  mysql_database.present:
    - name: dbname
    - character_set: utf8
    - collate: utf8_bin
  mysql_grants.present:
    - grant: all privileges
    - database: dbname.*
    - user: dbuser
    - host: '%'
  mysql_user.present:
    - name: dbuser
    - host: '%'
    - password: somepass

import_schema:
  mysql_query.run_file:
    - database: dbname
    - query_file: /tmp/schema.sql