Reliable Switch to MySQL?

[Cycle London]

Hi,

I've been running my station for just over a year, and would like to switch from sqilite to MySQL on a different server.   To be clear, what I mean by that is: I want to use a 'production' MySQL instance that is on a 'live' server.  

A couple of questions, if I may. 

1.  What 'granularity' of data is available inside the database?  I ask because today for example, 21 January, I'd like to know what the temperature was like last year on the same date.  In the weewx GUI, I can see that the coldest day of the month of January 2018 was the 30th, but I can't see what the temperature was on the 21st.   Is this inside the DB?   I know nothing of sqlite, so even if it's in there, I can't get it out.   

2.  Is there an easy, foolproof way of switching to MySQL?   I can do Linux, I can do MySQL but I don't know python or sqlite.  A quick method of switching would be great.  

Many thanks. 

[Andrew Milner]

The database granularity, contents and structure is identical for sqlite3 and MySQL.

The archive records are stored at 'archive interval' granularity.

There are also 'daily' tables for each of the reading type which give max and min for each calendar day.

If you can use MySQL then you should also be capable of using sqlite3.

How would you plan to retrieve the data from MySQL?

Use the same method/approach to retrieve it from sqlite3

(Granted, you may need to install sqlite3 on your weewx computer first)

I guess I did not fully understand how/why going to MySQL would enable you to get information from the database easier than from sqlite3.  The select statements are nearly identical - albeit a little different for date\time fields.

[Cycle London]

Hi.  Sorry, I wasn't clear.  

I want to use MySQL as I'm MySQL DBA trained.  OK, so I was, about 5 years ago.   And I'm a little too long in the tooth to learn new stuff.  

[gjr80]

Regards transferring from SQLite to MySQL have you ready through the Transfer from SQLite to MySQL page of the wiki (https://github.com/weewx/weewx/wiki/Transfer%20from%20sqlite%20to%20MySQL) and the Configuring MySQL section of the User's Guide (http://weewx.com/docs/usersguide.htm#configuring_mysql) ?

Those two areas have about everything you need for the transfer.

Gary

[Andrew Milner]

LOL - I'm 68 and still learning.  As I said though MySQL and SQLite are close enough to being the same for data retrieval select statements - if you were going to retrieve using sql select statements - and it is never too late to learn python.  Still not sure what/where/how you were planning on retrieving and displaying/outputting the data once retrieved.

IF you were going to use MySQL interactively then you cn do the same with sqlite3

[Cycle London]

On Monday, 21 January 2019 10:54:24 UTC, Andrew Milner wrote:

LOL - I'm 68 and still learning.  As I said though MySQL and SQLite are close enough to being the same for data retrieval select statements - if you were going to retrieve using sql select statements - and it is never too late to learn python.  Still not sure what/where/how you were planning on retrieving and displaying/outputting the data once retrieved.

IF you were going to use MySQL interactively then you cn do the same with sqlite3

Fair point.  :-)   

I planned to use mysql to get the data I need from the db.   It's really just for my own interest.  Maybe I'll get around to doing a simple PhP interface to it at some point.  

[Andrew Milner]

well as I keep on saying, as far as I know you can do exactly the same with sqlite3

[Cycle London]

Hi Andrew,

Doesn't look similar to me... 

[cyclelondon@weather ~]$ sqlite3

SQLite version 3.7.17 2013-05-20 00:56:22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> show databases ;

Error: near "show": syntax error

sqlite> show tables ;

Error: near "show": syntax error

--
You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/nlfPiicnLsw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Cycle London]

CHeers.  Those links aren't really clear.  Maybe i'm too old.  Or too dim.   

Anyway, time to gird my loins, snapshot the VM .. and just do it. 

[Andrew Milner]

you are being very obscure

SQLite only has one database in use at one time - the one you open

usage - eg - sqlite3 archive.sdb

then you can 

show tables;

[Cycle London]

Hi Andrew,

Please forgive me, I'm not being deliberately thick, and I'm not trying to annoy anyone.   Tried the above - even tried to remove the space between the command the semicolon, just in case sqllite handled white space differently than MySQL ... 

[cyclellondon@weather ~]$ sqlite3 archive.sdb

SQLite version 3.7.17 2013-05-20 00:56:22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> show tables ;

Error: near "show": syntax error

sqlite> show tables;

Error: near "show": syntax error

[Thomas Keffer]

Try either

.table

or

SELECT tbl_name FROM sqlite_master WHERE type='table';

-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.

[Cycle London]

The thing is that I work in IT but I hate it.  I have a set of skills (RHEL, Citrix, MySQL, OpenVMS..), but have little desire to increase that.  I do what I do because it pays the bills, and lets me buy nice toys like the weather station, like my Nikon D850, and I get to go on nice holidays.  But when I think of extracting data for the weather last year, I think "why bother to learn sqlite when I already know MySQL?". 

If that makes me sound ungrateful for the effort Thomas has put in, I'm really not.  

[Cycle London]

Hi Thomas,

I'll give you a screenshot this time, so you know I'm not actually lying about what I'm typing and what result I'm getting.... 

[Thomas Keffer]

That's what you get if you try it on an empty file. Try it on weewx.sdb, the file that WeeWX uses.

-tk

[Thomas Keffer]

The thing is that I work in IT but I hate it.  I have a set of skills (RHEL, Citrix, MySQL, OpenVMS..), but have little desire to increase that.  I do what I do because it pays the bills, and lets me buy nice toys like the weather station, like my Nikon D850, and I get to go on nice holidays.  But when I think of extracting data for the weather last year, I think "why bother to learn sqlite when I already know MySQL?". 

That's unfortunate. Life is way too short to work on something you don't love.

"Live as if you were to die tomorrow. Learn as if you were to live forever." --- Gandhi

-tk

[Cycle London]

Oh, you are so right, man.  

If I could, I'd be travelling the world as a landscape photographer.  Or flying in the Royal Air Force.  But although I'm 'not bad' with a camera, the world is full of people who are 'not bad'.  And my eyesight was too bad for the RAF to accept me.  

Anyway, I digress.  In response to your other post, I note that the weewx.sdb file in /home/weewx is owned by root but is empty.   Inside ./archive there is another file of the same name.   .table works on that file.  Curiously, however.. 

[root@weather weewx]# pwd && grep sdb weewx.conf

/home/weewx

        database_name = weewx.sdb

Can't figure out how, if the weewx.conf file is pointing to what is essentially an empty file, how I'm getting data on the GUI.  

--

[Thomas Keffer]

The database location is /home/weewx/archive/weewx.sdb. 

I'd guess you're trying /home/weewx/weewx.sdb.

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.

[Andrew Milner]

to answer that one it may help to know how you installed weewx in the first place!!  Different installation methods put things in different places.

what exactly do you mean by 'getting data on the GUI'??

the likely explanation is that the webpages are being generated from a version of weewx.sdb which is located in a different place - and weewx itself is possibly using a version of weewx.conf which is not the one you looked at!!

[Andrew Milner]

To avoid conflicting with Tom's input I shall make a departure at this point!!

Just remember - the documentation is always a place of first resort, and .help screens are there for a purpose!!

On Monday, 21 January 2019 15:16:01 UTC+2, Cycle London wrote:

[Cycle London]

Apologies - by 'the GUI' I mean the weewx web frontend.  

I reckon it's about time I moved to  MySQL.  That involves raising a change - which in my case, means asking my wife if she has anything planned the weekend after next.  :-) 

[Greg Troxel]

Cycle London <cycle.l...@gmail.com> writes:

> Doesn't look similar to me...
>
> [cyclelondon@weather ~]$ sqlite3
> SQLite version 3.7.17 2013-05-20 00:56:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> show databases ;
> Error: near "show": syntax error
> sqlite> show tables ;
> Error: near "show": syntax error

Standard sql is the same. nonstandard extensions are different, but you
can do the same things.

Each database is a file, so there is no notion of show databases.

"man sqlite3" will explain many things. But ".tables" will do what you
want. In all seriousness, if you understand mysql, you can figure out
how to do what you want in sqlite in well under an hour.

If sqlite is adequate in terms of size, which it certainly is a year in,
then it is vastly simpler. Think about reliability and weewx being
able to store new data as archive records happen. So you really want a
local db, that is guaranteed to be up if weewx is up.

[Cameron D]

It won't take you long at all.  I have done more complex transfers from sqlite3 to mysql and I haven't been trained in any IT system - sorry, I was taught Fortran IV, but I don't think that counts.

Mysql has a far richer function and capability set so if you intend doing anything more than what weewx can do already then I would think you will be better off with mysql/Mariadb.

If the command line syntax is a bit obscure, the DB Browser for sqlite (http://sqlitebrowser.org/) is a fine multiplatform gui.

Export the table definitions to file, check over and import into mysql.

then export data, check over and then import.

For the other database I converted, I needed to edit around several incompatibilities between the sql syntax, but a few cycles of import, crash, edit fixed that.  The weewx db is so simple I don't expect there will be any compatibility problems.

Cameron.

[Cycle London]

Hi Cameron,

Thanks for this.  Just snapshotting my VMs as we speak, and then going to try the flip.   One thing ... 

***

irst, verify that the MySQLdb python package is installed:

python -c "import MySQLdb"

If this results in an import error

ImportError: No module named MySQLdb

then install the MySQLdb package.

***

Is that on the weewx VM or on the database VM?  

Neither actually has it.  Both are CentOS.  I'll need to install but on which? 

I actually downloaded the DB Browser for SQL lite.  A curious thing happened.  Quite interested to see all the fields that are not being used.  Soil temperature, etc.  I think I might need to invest in some new kit... :-) 

[Cameron D]

I replied off-list by mistake, no need for anybody else to follow up.

[Cycle London]

Hi Cameron,

Thanks for the tips.  I tried to respond direcrly to the address whence your mail originated, but it bounced.