[updated 2011-06-10 because a friend pointed out a flaw in my assumption that I needed to enter the password every time – see bottom for updates]
I am architecting a project that is a C# front end, hitting SOAP web services, that communicate with a C# core, and has a MySQL database backend (accessed using a buddy’s custom ORM solution). I use MySQL Workbench to manage the schema and model of my database and I store all DDL and procedures in SVN, along with the seed scripts to put development data into the database.
I need to be able to make changes to the schema, add procedures, or seed data scripts, save them into SVN, and rebuild it on my integration server with a single command. I need other developers to be able to generate the database on their local instances quickly as changes are made. For modularity, each file is a separate script meant to be run, in order, to generate the database, procedures, and seed data. A sample directory listing looks like this:
As you can see – I have a a lot of scripts to run, and the list grows daily as new procedures and seed data are added. Scripts to create the database, scripts for procedures, scripts for seed data… I initially tried to run them all individually:
mysql -h localhost -u root -p < 001_Create.sql mysql -h localhost -u root -p < 002_AppUserProcs.sql mysql -h localhost -u root -p < 003_InterestProcs.sql etc….
The only problem with that is that it asked me for my password on every single execution.
I looked around for ways to put the password “in memory” or to require it only once, but after 15 minutes of Google research my ADHD kicked in and I needed to do something else. What I am about to suggest may not be the only way to accomplish this, but it’s what I came up with. It works, and it’s fast.
The solution: combine all the sql files into a single script file and then execute that single script file. I wrote a DOS batch called generate.bat that combines all of the script files into a single file called sqlrun.txt and then I execute the mysql command line utility against that file. This way, I only get asked a single time for my password. Here is what v1.0 of generate.bat looks like:
@ECHO OFF ECHO Deleting Previous Files DEL sqlrun.txt ECHO Creating script to run FOR %%X IN (*.sql) DO type %%X >> sqlrun.txt ECHO Running script mysql -h localhost -u root -p < sqlrun.txt ECHO Deleting temporary files DEL sqlrun.txt ECHO Finished!
The secret to this is that it loops through every .sql file in the folder and creates a new file called sqlrun.txt, which then gets executed. Because scripts have to be run in a VERY specific order, I utilize the fact that the DOS FOR command will iterate through the folder alphabetically and I name each of my files numerically so it builds sqlrun.txt in the order I want it to. Also, the output file (sqlrun.txt) is a .txt file and not a .sql file so it doesn’t get picked up in the FOR statement.
Viola – problem solved. I am asked only one time for my password and my development team can quickly regenerate the database and seed data on any machine as often as changes are made.
A follower, previous boss, co-worker, and friend, Gary pointed out a flaw in my initial logic. The mysql command line utility will NOT ask you for your password every time if you use it correctly (which I did not). I was putting a space after the -p in the password argument, which is why it did not work for me.
Still, the method I proposed works if you don’t want to hard code the password into your file. If you don’t mind having the password in your file you don’t need to make a single, massive, sql file and you can change your DOS batch file to the following:
@ECHO OFF ECHO Running scripts FOR %%X IN (*.sql) DO mysql -h localhost -u root -pMYPASSWORD < %%X ECHO Finished!
This works VERY well for automated build and deployment scenarios where you push the code automatically and without human intervention. Thanks, Gary, for the correction.