By FrenchHope on

With Drupal 7 it seems easy to choose and install Drupal on an SQLite database, but what about an existing Drupal website ?

After a Drupal 6 to Drupal 7 upgrade, will I be able to convert a Drupal MySQL database to a Drupal SQLite database ?

Thanks.

⋅ Categories: Drupal 7.x

Comments

LaurentAjdnik’s picture

LaurentAjdnik commented

Hi,

there might be a more efficient solution, but here is what I would do:

- upgrade from DP6 to DP7
- convert the mySQL database to SQLite (there are many ways to do that: google "mysql sqlite migration" for example)
- change the $databases array in settings.php to redirect it from mySQL to SQLite

Let me know the result if you ever try it :c)

hplc’s picture

hplc commented

I tried. No good luck to migrate.
Used several scripts to convert MySQL to SQLite, output many errors, finally there's only 31 tables exist in SQLite, while there are 88 in MySQL.
Lost many tables!

I found there's a project:
http://drupal.org/project/dbtng_migrator
But it still in developing.

I'll try it later.

hplc’s picture

hplc commented

No good luck for me to use dbtng_migrator, and I can't find any detailed documentation, maybe it's still in developing.

But I find this project is great:
https://gist.github.com/943776

But the original version(while writing this post) still need a bit adjusting to fit our situation, below is the one that works for Drupal 7.

In my case: Drupal 7.7, MySQL 5.0, SQLite 3.7

This script works fine for me(Based on https://gist.github.com/943776):
http://525183.serverbox.org/drupal7/mysql2sqlite.sh

After converting, adjust site setting.php file properly. Clean all caches, and refresh site page several times, it will all works fine.

midoriconcept’s picture

midoriconcept commented

Hi I did the following.

1) Export the MySql database from my host
2) Imported locally in my machine
3) Run the script ./mysql2sqlite.sh --no-data -u user -p password databasename | sqlite3 database.sqlite
4) I get only a message 'memory' and the script does basically nothing...

any suggestions?

(I am running linux mint 10 if can help, but I tried on windows with Cygwin)

Thanks

Sakrecoer’s picture

Sakrecoer commented

Subscribing, while hunting for info.

open your mind open the source
express yourself, share your tools

Ronni-1’s picture

Ronni-1 commented

I want to convert my site's database from MySQL to SQLite. So i got the DBTNG-Migrator and tried using it. I changed the settings.php files as follows -

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'driver' => 'mysql',
      'database' => 'db_name',
      'username' => 'root',
      'password' => 'password',
      'host' => 'localhost',
      'port' => '',
      'prefix' => '',
    ),
  ),
  'dest' => array (
        'default' =>
          array (
            'database' => 'drupal/sqlite_db/db_name',
              'driver' => 'sqlite',
              'prefix' =>'',
            ),
        ),
);

I had changed the permissions of the folder sqlite_db so that it is writable by others. Then i go to the configuration page of DBTNG and go to Migrator database check. There i choose the destination as sqlite and mysql as origin. When i hit check i get a lot of warnings and this error-

block does not existing in dest
PDOException: SQLSTATE[HY000]: General error: 1 no such table: block: SELECT     
b.* FROM {block} b WHERE (b.theme = :db_condition_placeholder_0) AND          
(b.status = :db_condition_placeholder_1) ORDER BY b.region ASC, b.weight       
ASC, b.module ASC; Array ( [:db_condition_placeholder_0] => gtt               
[:db_condition_placeholder_1] => 1 ) in _block_load_blocks() (line 727 of       
/var/www/drupal/modules/block/block.module).

I have 184 tables in the db. How can i solve these issues.

jaskho’s picture

jaskho commented

I've hit a number of edge cases this script doesn't handle. A useful technique for debugging is to break up the steps, and use a a text editor to tweak things as necessary.

  • Run the script, exporting to a file instead of piping to sqlite3:

    ./mysql2sqlite -u root -pMySecretPassWord myDbase > sqliteimport.sql

  • Season to taste (edit sqliteimport.sql), then...
  • Import into sqlite:

    sqlite3 database.sqlite < sqliteimport.sql

I've also found it useful to break out the initial dump (so I can see what's coming from mysql versus what's coming out of mysql2sqlite.sh), like this:

  • Dump from mysql, using the params that mysql2sqlite.sh uses:
    mysqldump -u root pMySecretPassWord --compatible=ansi --skip-extended-insert --compact temp_for_mysql2sqlite > mysqldump.sql
  • Edit mysql2sqlite.sh so you can pass in the dump file:

    Replace this line: 

    mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \

    With this one:

    cat $1 | \
  • Call the script, passing in the dump file:

    ./mysql2sqlite.sh mysqldump.sql

elvis2’s picture

elvis2 commented

I know this is a 12 year old post, but for those who are looking for a sane way to migrate from a drupal mysql db to sqlite, I highly recommend this project: https://github.com/techouse/mysql-to-sqlite3

I was able to migrate a drupal 9.5 (with many fields and views) to a sqlite db in a matter of seconds!

ameliasarah’s picture

ameliasarah commented

Work-around technique · Run the script, exporting to a file instead of piping to sqlite3: ./mysql2sqlite -u root -pMySecretPassWord myDbase i tried to migrate the db of my d7 website from  spacebar counter  mysql to sqlite. I changed the settings.php file as follows  If it's possible to make joins between the Drupal database and your source database (e.g., under MySQL, if they are on the same server, your connections ... I want to convert my site's database from MySQL to SQLite. So i got the DBTNG-Migrator and tried using it. I changed the settings.php files ...Drupal now has the ability to migrate an entire database from one server type to another! Thats right, Drupal can migrate itself from MySQL to PostgreSQL and ...