0

I'm having trouble copying data from a production MySQL server to a development SQLite3 file (so that I have real data to play with on development machine). I've found tons of resources around the 'net on how to convert from MySQL to SQLite3, most of which were bash scripts with elaborate sed filters, but none worked (the most common problem was syntax issues upon import).

Anyway, then I stumbled upon YamlDB, and I thought "Why, of course! Let Rails do the conversion for me!" Well, this doesn't work either because all of the NULL fields (which are represented in the YAML file as !!null) end up being imported into the SQLite3 database exactly as "--- !!null" instead of actual NULLs. I seem to be the only person with this problem, as there were no mentions of it in the GitHub issues queue.

I even tried the workaround for using syck instead of psych (found in this SO question), but it made no difference.

So my question is this: does ANYONE know of a SIMPLE way to export data from one rails database for importing into another, regardless of database kind? And by "simple", I mean a few commands at the console, or whatever.

Community
  • 1
  • 1
Matthew Clark
  • 1,885
  • 21
  • 32
  • I would REALLY, REALLY not recommend using two different database back-ends for production and development. As database agnostic as rails is supposed to be, when it comes to data integrity you can still get very different behaviours. This becomes especially true when working with something which supports many different types of constraints (MySQL) and something which has very different constraint/datatype support (SQLite3). – TreyE Dec 07 '11 at 21:22
  • I've heard that recommendation before, and so for a while, I had MySQL running on my development machine. But I really didn't like the overhead of having to start a MySQL server on my MacBook, so I went back to SQLite3 for development. You make a good point, still. But the data copying would only be unidirectional (production -> development), and then used as "play" data during development hacking... – Matthew Clark Dec 07 '11 at 23:19
  • My bigger worry would be that you might see different behaviour during your tests. For example, you might get a crash in production that didn't appear in development/test because of the different data types/constraints. I don't really like it when I have to do SQL Server development and fire up a Windows VM/SQL Server - but the number of bugs I've discovered this way has always outweighed the resource consumption. (And that's really saying something if the thing consuming resources is windows :P). – TreyE Dec 07 '11 at 23:23
  • Also, dunno if it helps, but check out: http://mysqltuner.pl/mysqltuner.pl. Might help you minimize the footprint to a point where it's acceptable. – TreyE Dec 07 '11 at 23:28
  • Well, Trey, you may just have me convinced to re-install MySQL onto my MacBook! You offer some good points -- some I'm already aware of, but just hearing them again is enough to make me think more about what I'm doing. Your input is truly valuable. – Matthew Clark Dec 08 '11 at 19:00
  • Just glad I could help. Subtle production db bugs are really the worst kind. – TreyE Dec 12 '11 at 15:04

1 Answers1

2

Look into taps @ http://github.com/ricardochimal/taps

It will dump your MySQL db into a local sqlite db and is relatively simple to use.

From the comments: If you get an error stating schema parsing returned no columns, table probably doesn't exist then you need to specify an absolute path to the sqlite3 db instead of a relative one

iwasrobbed
  • 46,496
  • 21
  • 150
  • 195
  • This look promising, except it, too, seems to hate me. After it completes a pull form the server, it always crashes with `schema parsing returned no columns, table probably doesn't exist` no matter what I try. Oh well, the search continues... – Matthew Clark Dec 08 '11 at 19:27
  • 1
    To solve the "schema parsing returned no columns" error, specify and absolute path to the sqlite3 db instead of a relative one... – Urkle Feb 07 '12 at 04:38