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.

Improve this question
6
  • 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). Commented Dec 7, 2011 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... Commented Dec 7, 2011 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). Commented Dec 7, 2011 at 23:23
  • Also, dunno if it helps, but check out: mysqltuner.pl/mysqltuner.pl. Might help you minimize the footprint to a point where it's acceptable. Commented Dec 7, 2011 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. Commented Dec 8, 2011 at 19:00

1 Answer 1

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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...
To solve the "schema parsing returned no columns" error, specify and absolute path to the sqlite3 db instead of a relative one...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.