1

We recently installed a server dedicated to unit tests, which deploys updates automatically via Jenkins when commits are done, and sends mails when a regression is noticed
> This requires our database to always be up-to-date

Since the database-schema-reference is our MWB, we added some scripts during deploy, which export the .mwb to a .sql (using python) This worked fine... but still has some issues

Our main concern is that the functions attached to the schema are not exported at all, which makes the DB unusable.

We'd like to hack into the python code to make it export scripts... but didn't find enough informations about it. Here is the only piece of documentation we found. It's not very clear for us. We didn't find any information about exporting scripts.
All we found is that a db_Script class exists. We don't know where we can find its instances in our execution context, nor if they can be exported easily. Did we miss something ?

For reference, here is the script we currently use for the mwb to sql conversion (mwb2sql.sh).
It calls the MySqlWorkbench from command line (we use a dummy x-server to flush graphical output.)
What we need to complete is the python part passed in our command-line call of workbench.

# generate sql from mwb
# usage: sh mwb2sql.sh {mwb file} {output file}
# prepare: set env MYSQL_WORKBENCH

if [ "$MYSQL_WORKBENCH" = "" ]; then
  export MYSQL_WORKBENCH="/usr/bin/mysql-workbench"
fi

export INPUT=$(cd $(dirname $1);pwd)/$(basename $1)
export OUTPUT=$(cd $(dirname $2);pwd)/$(basename $2)

"$MYSQL_WORKBENCH" \
  --open $INPUT \
  --run-python "
import os
import grt
from grt.modules import DbMySQLFE as fe
c = grt.root.wb.doc.physicalModels[0].catalog
fe.generateSQLCreateStatements(c, c.version, {})
fe.createScriptForCatalogObjects(os.getenv('OUTPUT'), c, {})" \
  --quit-when-done
set -e

note: posted on SO last week, but with 13 views in 4 days (and 1 up!), I doubt it was the right place.
I'll remove it from SO if I get answers here


@Rick James's comment :

MySQLDump was our previous option, but we'd like something which doesn't require an action from the developper anymore.
It doesn't only save the time taken to export / commit the .sql, but more importantly time spent due to mistakes, debugging problems which happen only because one forgot to update the .sql.

  • An other "alternative" could be to manually export the .mwb via the GUI, since it features a checkbox to "also export scripts", but again, it's not mistake-proof, and we know it will cause problems
  • We might also end-up with an export of the functions in a separate (manually created) .sql, and import separately both structure and scripts files, since our functions are rarely modified... but this isn't the safest / more efficient way either

We thought MySQL Workbench would be easier to use with command line. It's quite disappointing to see that such tool is missing accessibility for features... which are accessible via the GUI !

1

Switch to mysqldump; it is aimed at scripting, whereas Workbench is aimed at UI.

Use the option --routines in addition to whatever else you need.

--no-data gives you just the schema.

  • Thanks for the suggestion, but in fact, we are precisely trying to switch from manual processes, which are source of mistakes, to fully automated ones. I updated my question with some details about the other alternatives we have thought about. – Balmipour May 2 '16 at 22:52
  • I'm confused. What about mysqldump prevents it from being completely scripted -- hands off. – Rick James May 2 '16 at 23:11
  • Say a developer needs to add a new column. Currently, he adds it in the .mwb and synchronizes his local schema to add the column. He could use mySqlDump to create a .sql, of course, but the experience demonstrated that the more step in processes, the more mistakes are made. That's why we'd like to have a single entry point for every modification : the .mwb. Tee dev modifies the schema to update his local DB, and automating the export prevents any de-sync between the schema and the .sql export created. (Maybe I should have given the precision that devs can't access the server ?) – Balmipour May 2 '16 at 23:25
  • OK, I see your point. I assumed that Jenkins (etc) were trying to pull the latest schema whenever they run. Instead, you have the subject-to-human-forgetfulness of having the Devs push. – Rick James May 3 '16 at 1:20
  • Yes. To be clear, the only thing we want on our repository is the .mwb. – Balmipour May 3 '16 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.