2

I have a local testing setup where I can create drupal sites easily, all running with a sqlite DB.

I want to (firstly) publish the site to a remote server using a script that calls

drush sql-sync @blah.livesite.local @blah.livesite.com --create-db

The problem i ran into is that drush sql-sync does not like to migrate a sqlite DB to a mysql DB as some structural deficites block the transfer. The error message is:

ERROR 1064 (42000) at line 1: 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 'PRAGMA foreign_keys=OFF' at line 1

I know I maybe can do a sql-dump first, edit some of the lines manually and import that filt into the remote db then. But I need this procedure quite often and have an automation script for the transfer - it should work fast with no manual editing involved.

Is it not possible to sql-sync from sqlite to mysql generally?

Yes, and I tried dbtng-migrate as well, with help of there. It did not work, I ran into all kind of errors, and I don't want to create another mysql db locally and sync then.

All I want is a simple way to sync my local sqlite drupal install to a remote mysql one.

Improve this question
10
  • I'd be surprised if Drush has functionality to convert your data between arbitrary DBMS formats. I suspect you'll need to implement the conversion manually as part of your deployment workflow. Tools do exist to convert between sqlite and mysql, but no, they're not 1-to-1 compatible – Clive♦ Mar 29 '15 at 9:53
  • OK, can you post that as answer so i can mark it as correct... – nerdoc Mar 29 '15 at 10:02
  • But I still find it awkward that drush isn't capable of doing this. Drupal itself is DB agnostic as well, why doesn't drush also use a higher level api for sql-sync? – nerdoc Mar 29 '15 at 11:01
  • Which higher level Api are you referring to? I guess there may be one in existence but cross-converting data between all supported DBMSs doesn't seem like something Drupal (nor Drush) should be expected to do. Drupal supports different DBMSs; I'm not familiar with any claim that it supports converting data between those systems. It's an unusual requirement, one's production and development environments should traditionally be identical – Clive♦ Mar 29 '15 at 11:19
  • Er, I just thought: Drupal DB API The Drupal 7 Database API provides a standard, vendor-agnostic abstraction layer for accessing database servers. The API is designed to preserve the syntax and power of SQL as much as possible, but also... – nerdoc Mar 29 '15 at 11:23
2

Drush sql-sync cannot convert and sync databases in one step; however, you can use drush dbtng migrator to convert the database locally into mysql format, and then use sql-sync to copy it to the final destination.

Improve this answer

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.