Header image

Symfony Finland
Random things on PHP, Symfony and web development

Migrating Bolt CMS from SQLite to MySQL (MariaDB)

Bolt CMS documentation site does not describe migrating from SQLite to MySQL. I thought I'd write down the steps.

You'll need to be confortable with the cli / terminal, but not an expert.

It is assumed you already have MySQL / MariaDB running on localhost and have Python installed.

1. Install SQLite 3

  • Debian Linux: apt-get install sqlite3
  • OS X: brew install sqlite3 (you'll need Brew)

2. Download SQLite 3 MySQL converter script

An updated version a python script to convert SQLite dumps to MySQL dumps. Place it to your app/database/ directory and make it executable (e.g. chmod 755 sqlite3-to-mysql.py).

Note that some versions no not convert CLOB fields to LONGTEXT, resulting in error similar to:


ERROR 1064 (42000) at line 3264 in file: 'dump.sql': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CLOB NOT NULL, html CLOB NOT NULL, geolocation CLOB NOT NULL, image VARCHAR(256)' at line 1

3. Create a new database

Login to MySQL and execute creation and permission commands:

  • CREATE DATABASE bolt;
  • GRANT ALL ON bolt.* TO 'bolt'@'localhost' IDENTIFIED BY 'SOMEPASSWORD';
  • FLUSH PRIVILEGES;

4. Export and convert SQLite database to MySQL dump

  • cd app/database/
  • sqlite3 bolt.db .dump | ./sqlite3-to-mysql.py > bolt.sql

5. Import MySQL dump to your server

Execute:

  • mysql -ubolt -pSOMEPASSWORD bolt
  • source bolt.sql

6. Change Bolt CMS Configuration

Edit the configuration file: app/config/config.yml

Comment out the SQLite configuration:


#database:

# driver: sqlite

# databasename: bolt

Add the MySQL configuration:


database:

driver: mysql

username: metropolitan

password: metropolitan1

databasename: metropolitan

host: 127.0.0.1

7. Clear caches and update DB

Execute:

  • php app/nut cache:clear
  • php app/nut database:check
  • php app/nut database:update
  • php app/nut cache:clear

8. Test and verify your configuration change

  • Open your site and it opens
  • Go to the admin and create some content
  • Open a MySQL console and verify new rows appear when you enter content (e.g. select id from bolt_entries;)

I your site does not work you can revert your DB configuration to SQLite to keep your site working.

More on SQLite MySQL migrations


Written by Jani Tarvainen on Thursday July 2, 2015
Permalink - Tags: php, symfony, silex, bolt, mysql, mariadb, sqlite

Leave a comment

comments powered by Disqus

« How to expose your Routes and Translations to your Front End JavaScript in Symfony - Web Components will fulfil the promise of Modular XHTML »