SQLLite to MySQL conversion?

[Rich Mulvey]

I've got a couple of years worth of the sqllite data in my archive.  Now that I'm playing around with Weewx2, is there a utility to migrate the data to a MySql DB?  I'm not seeing any, offhand.

Thanks!

- Rich

[Thomas Keffer]

It's a great question.

I originally wrote the config_database.py utility to allow reconfiguring the database from a general source to a general destination. It was proving a little too complex and I could see it leading to a  support headache. So, I restricted it to its original intent: allowing new types to be added to the schema and to change the standard unit system used by the database.

In the end, I decided that using a dump command is the simplest and, after all, what it is intended for:

> sqlite3 /home/weewx/weewx.sdb

sqlite> .output /home/weewx/weewx.sql

sqlite> .dump

Then reload into MySQL

> mysql -u weewx --password=password weewx <weewx.sql

You will probably have to edit the dump file a bit to reflect the differing SQL dialects.

-tk

- Rich

--
 
 

--

Tom Keffer

kef...@threefools.org
+1 541-386-8891 (h)
+1 541-490-9507 (c)

Skype: tkeffer

[Richard Mulvey]

I did that, and the load appeared to work fine.  I've been running  under MySQL for two days now.

However.... I'm not sure that this is related, but my weekly rainfall is wrong.  See:  http://weather.mulveyfamily.com/week.html

The corresponding template fragment is:

              <tr class="odd">

                <td class="stats_label_wide">Rain Total</td>

                <td class="stats_data">$week.rain.sum</td>

              </tr>

              <tr class="odd">

                <td class="stats_label_wide">Max Rain Rate</td>

                <td class="stats_data">$week.rainRate.max $week.rainRate.maxtime</td>

              </tr>

Any chance this is a MySQL issue?

- Rich

--
 
 

--
ri...@mulveyfamily.com

[Thomas Keffer]

That's great news! Did you have to edit the dump much in order to get MySQL to accept it?

Regarding rain: let's make sure we are talking about the same thing. $week.rain.sum returns the rain so far this week. That is, since midnight Sunday. Seeing as how this is only Monday I would expect this to be a small number. However, I noticed that your website is annotated "Last seven days."

If you really want the last seven days, it would be easy enough to do: just provide a custom search list. There are directions in the Customizing guide to doing this.

-tk

[Richard Mulvey]

IIRC, the only thing I edited was to get rid of the table creation DDL at the start of the dumpfile, and I needed to whack any ' chars around the table name in the insert statements.

As for the rainfall - Doh!  You're absolutely correct.  I was having a brain cramp about what the number represents.

I've updated to the b12 version, and have it running now.

- Rich

--
 
 

--
ri...@mulveyfamily.com

[Thomas Keffer]

<grin> I suspect you guys on the east coast are going to get a good rainfall test over the next few days!

-tk

[Richard Mulvey]

lol  Yeah, shouldn't be too bad here up near Lake Ontario in upstate NY - we're only supposed to get 3-4 inches, tops, along with some high winds.  Downstate is another story entirely.

--
 
 

--
ri...@mulveyfamily.com

[Peter Finley]

I found the performance of the import to be abysmal until I added this setting to the first line of the dump file:

SET autocommit=0;

I also had to change the 'BEGIN TRANSATION' to 'START TRANSATION', add ticks around the interval column (-> `interval`) and remove the quotes from around the "archive" of all of the inserts (can be easily done using VIM via :%s/"archive"/archive/). With these changes I was able to import over 250,000 rows in less than a minute (as opposed to probably hours with autocommit enabled).

Peter

--
 
 

[Mark F]

These tips worked for me.  Issues I ran into on OS X Server 10.8.0 which may apply to others:

• There was a permissions error for user weewx accessing a parent of /usr/local/mysql/lib, which is where my shared libraries reside.   (Error was "...libmysqlclient_r.16.dylib: stat() failed with errno=13").  I tried symlinking it but found the default permissions error and that fixed it.
• Pointing to localhost under [Databases] in weewx.conf resulted in: "wxengine: Caught database OperationalError: (2002, "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)")".  Pointing to 127.0.0.1 fixed this.  Or you can symlink this to wherever your mysql.sock resides.

Other than that, it all went smoothly and I'm running on MySQL right now.

[pterodaktil]

>>You will probably have to edit the dump file a bit to reflect the differing SQL dialects.

In attachment script to convert dump from sqllite to mysql

[Mark Jenks]

2 days to late.  :)  I just did it manually.

-Mark

[Mark Jenks]

I just took it for a test run and it needs some tweaking. 

1) Please add "SET autocommit=0;" at the beginning (Faster import)
2) Change the code from "archive" > `archive', to "archive" > archive. (strip quotes)   It didn't work for me at all with the `.

DOCS:

> sqlite3 /home/weewx/archive/weewx.sdb

sqlite> .output /home/weewx/archive/weewx.sql

sqlite> .dump

Convert it  (unless I'm missing the straight push into the DB?  If that is the case, weewx shouldn't be running if it is)

> ./sqlite3-to-mysql.py /home/weewx/archive/weewx.sql > /home/weewx/archive/weewx.sql.import

Create tables via weewx or weewx.sql.import

If you already changed weewx to mysql and started it, all the tables will already be created, and the import will fail.  If you already did this, remove the TABLE CREATE section from the .import file. 

Then reload into MySQL

> mysql -u weewx --password=password weewx < /home/weewx/archive/weewx.sql.import

[Thomas Keffer]

Here's another alternative. It's dirt simple, but requires the weewx libraries. Adjust 'user', 'password', 'database', etc., as necessary. It must be run from the weewx/bin subdirectory (or, adjust PYTHONPATH as appropriate).

It's not particularly fast, but it does everything in one step (no dump, edit, then import). It helps if you have a multi-core CPU, because one core will run the python program, the other mysql. It took 13 minutes wall clock time transferring my 550,000 record (5+ years) database.

from weewx.archive import Archive

from user.schemas import defaultArchiveSchema

old_archive_dict = {'driver' : 'weedb.sqlite',

                    'database' : '/home/weewx/archive/weewx.sdb'}

new_archive_dict = {'driver'   : 'weedb.mysql',

                    'database' : 'weewx',

                    'host'     : 'localhost',

                    'user'     : 'weewx',

                    'password' : 'weewx'}

with Archive.open(old_archive_dict) as old_archive:

    with Archive.open_with_create(new_archive_dict, defaultArchiveSchema) as new_archive:

        # This is very fast because it is done in a single transaction context:

        new_archive.addRecord(old_archive.genBatchRecords())

-tk

On Tue, Jan 1, 2013 at 8:25 AM, pterodaktil <pterod...@mail.ru> wrote:

>>You will probably have to edit the dump file a bit to reflect the differing SQL dialects.

In attachment script to convert dump from sqllite to mysql

--
 
 

[Nirica]

Tom, I used the below script at it worked great to migrate from sqlite --> mysql.  Do you have a similar script to migrate the stats db, or is there a way to force weewx to re-create the stats db?

Thanks

[Thomas Keffer]

If it doesn't exist, Weewx will automatically regenerate the stats database. Just follow the directions in the User's Guide.

-tk

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Nirica]

Worked perfectly, thanks for quick reply.

[Michiel Kanis]

Hi Tom,

I just created a sql database (stats and weewx) on my NAS Synology server and changed weewx.conf to use those databases. I checked the databases and saw all tables are created. At this time I would implement the sdb database. How do I execute below script? I thought I go to /home/weewx/bin directory, then execute sqlite3 and use your script to execute? But nothing happend. I changed your script into:

from weewx.archive import Archive

from user.schemas import defaultArchiveSchema

old_archive_dict = {'driver' : 'weedb.sqlite',

                    'database' : '/home/weewx/archive/weewx.sdb'}

new_archive_dict = {'driver'   : 'weedb.mysql',

                    'database' : 'weewx',

                    'host'     : '192.168.178.10',

                    'user'     : 'weewx',

                    'password' : 'XXXXXXXXX'}

with Archive.open(old_archive_dict) as old_archive:

    with Archive.open_with_create(new_archive_dict, defaultArchiveSchema) as new_archive:

        # This is very fast because it is done in a single transaction context:

        new_archive.addRecord(old_archive.genBatchRecords())

Thanks in advanced

Michiel

Op zaterdag 5 januari 2013 19:24:09 UTC+1 schreef Tom Keffer:

[Michiel Kanis]

Sorry Tom,

I executed your script in Python and seems succesfull because have weather data back from a week ago!

Regards Michiel

Op vrijdag 28 november 2014 11:21:09 UTC+1 schreef Michiel Kanis:

[Tarmo]

Could you please help me to get the script working with weewx v3.1.0. 

[Thomas Keffer]

The instructions in the Wiki apply to V3.

-tk