Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

Database Setup

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $9.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

Our homepage is busted. Find your terminal and SSH onto the server:

ssh -i ~/.ssh/KnpU-Tutorial.pem ubuntu@54.XXX.XX.XXX

Let's find out what the problem is. I recognize the error page as one that comes from Symfony... which means Symfony is running, and the error will live in its logs.

Check out the var/logs/prod.log file:

cd /var/www/project/current
tail vars/logs/prod.log

Ah, no surprise!

Unknown database "mootube"

So how do we setup the database? Well, it's up to you! This is just a one-time thing... so it doesn't need to be part of your deploy. You could go directly to MySQL and setup it up manually. That's super fine.

Creating the Database

I'm going to do it in Ansible! Add a new task... but move it above the cache tasks, in case the database is needed during cache warm up. Name it: "Create DB if not exists":

... lines 1 - 14
- name: Warm up the cache
command: '{{ release_console_path }} cache:warmup --env=prod'
- name: Create DB if not exists
... lines 19 - 42

We can use the doctrine:database:create command. So, use the command module... and I'll copy one of our other commands. Change it to doctrine:database:create then --if-not-exists so it won't explode if the database already exists:

... lines 1 - 17
- name: Create DB if not exists
command: '{{ release_console_path }} doctrine:database:create --if-not-exists --env=prod'
... lines 20 - 42

If you run this command locally... well... we do have a database already. So it says:

... already exists. Skipped.

Copy that text. This last part is optional: we're going to configure this task to know when it was, or was not changed. Register a variable called create_db_output. Then, add changed_when set to not create_db_output.stdout|search() and paste that text:

... lines 1 - 17
- name: Create DB if not exists
command: '{{ release_console_path }} doctrine:database:create --if-not-exists --env=prod'
register: create_db_output
changed_when: not create_db_output.stdout|search('already exists. Skipped')
... lines 22 - 42

Migrating / Creating the Schema

That'll give us a database. But... we need some schema! Some tables! How do we get those!? Well... you should use migrations in your app. We do have migrations: in app/DoctrineMigrations... and these contain everything. I mean, these have all the queries needed to add all the tables... starting from an empty database. I highly recommend creating migrations that can build from scratch like this.

So, to build the schema - or migrate any new schema changes on future deploys - we just need to run our migrations.

Create a new task: "Run migrations". Then cheat and copy the previous task. This is simple enough: run doctrine:migrations:migrate with --no-interaction, so that it won't interactively ask us to confirm before running the migrations. Interactive prompts are no fun for an automated deploy:

... lines 1 - 17
- name: Create DB if not exists
command: '{{ release_console_path }} doctrine:database:create --if-not-exists --env=prod'
register: create_db_output
changed_when: not create_db_output.stdout|search('already exists. Skipped')
- name: Run migrations
command: '{{ release_console_path }} doctrine:migrations:migrate --no-interaction --env=prod'
... lines 25 - 47

Register another variable - run_migrations_output - and use that below:

... lines 1 - 22
- name: Run migrations
command: '{{ release_console_path }} doctrine:migrations:migrate --no-interaction --env=prod'
register: run_migrations_output
... lines 26 - 47

If you try to migrate and you are already fully migrated, it says:

No migrations to execute.

Let's search for that text: "No migrations to execute":

... lines 1 - 22
- name: Run migrations
command: '{{ release_console_path }} doctrine:migrations:migrate --no-interaction --env=prod'
register: run_migrations_output
changed_when: not run_migrations_output.stdout|search('No migrations to execute')
... lines 27 - 47

Oh, before we try this, make sure you don't have any typos: the variable is create_db_output:

... lines 1 - 17
- name: Create DB if not exists
... line 19
register: create_db_output
changed_when: not create_db_output.stdout|search('already exists. Skipped')
... lines 22 - 47

Ok, try it!

ansible-playbook ansible/deploy.yml -i ansible/hosts.ini --ask-vault-pass

After a bunch of setup tasks... if you watch closely... yea! The migrations ran successfully! We should have a database full of tables.

Go back to the site and refresh! It works! Of course... there's no data, but it works!

Why dev Commands Don't Work

To help bootstrap my data, just this once, I'm going to load my fixtures on production. I'm obviously not going to make this part of my deploy: you won't make any friends if you constantly empty the production database. Believe me.

Find the terminal that is SSH'ed to the server. Move out of the current/ directory and then back in:

cd ..
cd current/

First, try running bin/console without --env=prod:

bin/console

Error! It can't find a bundle! Why? In the dev environment, we use a few bundles - like HautelookAliceBundle - that are in the require-dev section of our composer.json. So, these do not exist inside vendor/ right now!

That is why you must run all commands with --env=prod. But, of course, the fixtures bundle is only available in the dev environment. So, just this one time... manually... let's install the dev dependencies with:

composer install

Now we can load our fixtures:

./bin/console hautelook_alice:doctrine:fixtures:load

Beautiful! And now, we've got some great data to get us started. Next, let's talk more about migrations... because if you're not careful, you may temporarily take your site down! That's not as bad as emptying the production database, but it still ain't great.

Leave a comment!

4
Login or Register to join the conversation

In case you are using newer server (Ubuntu 18.04 here): the root account is bound to root user, and not to password.
To fix, add this tasks to playbook.yml:

    - name: Install mysql-python
      become: true
      apt:
        name: python3-mysqldb
        state: latest
      become: true

    - name: Create MySQL symfony user
      become: true
      mysql_user:
        # change name to anything you want
        name: symfony
        # uncomment if pasword is wanted:
        # password: <somepassword>
        priv: '*.*:ALL'```

And change the username/password in vault.
1 Reply

Hey Ivan,

Thank you for sharing this with others! So you need to create a MySQL user first before start connecting to the DB from PHP scripts (or console commands) - good to know.

Cheers!

Reply

You welcome.
More specifically: Ubuntu 18.04 installation uses auth_socket for the MySQL root user.
Another option is to revert MySQL's root user authentication to the mysql_native_password.
I just choosed to create a new user.

Reply

Hey Ivan,

Yeah, probably the easiest. But good to know about alternative solution as well.
Thanks again!

Cheers!

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

While the fundamentals of Ansistrano haven't changed, this tutorial is built using Symfony 3, which has significant differences versus Symfony 4 and later.

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": ">=5.5.9",
        "doctrine/doctrine-bundle": "^1.6", // 1.6.8
        "doctrine/orm": "^2.5", // v2.7.2
        "incenteev/composer-parameter-handler": "^2.0", // v2.1.2
        "sensio/distribution-bundle": "^5.0.19", // v5.0.20
        "sensio/framework-extra-bundle": "^3.0.2", // v3.0.26
        "symfony/monolog-bundle": "^3.1.0", // v3.1.0
        "symfony/polyfill-apcu": "^1.0", // v1.4.0
        "symfony/swiftmailer-bundle": "^2.3.10", // v2.6.3
        "symfony/symfony": "3.3.*", // v3.3.5
        "twig/twig": "^1.0||^2.0", // v1.34.4
        "doctrine/doctrine-migrations-bundle": "^1.2", // v1.2.1
        "predis/predis": "^1.1", // v1.1.1
        "composer/package-versions-deprecated": "^1.11" // 1.11.99
    },
    "require-dev": {
        "sensio/generator-bundle": "^3.0", // v3.1.6
        "symfony/phpunit-bridge": "^3.0", // v3.3.5
        "doctrine/data-fixtures": "^1.1", // 1.3.3
        "hautelook/alice-bundle": "^1.3" // v1.4.1
    }
}

What Ansible libraries does this tutorial use?

# ansible/requirements.yml
-
    src: DavidWittman.redis
    version: 1.2.4
-
    src: ansistrano.deploy
    version: 2.7.0
-
    src: ansistrano.rollback
    version: 2.0.1
userVoice