Thanks, Theo, for your digging and, thanks to you, I learned something. In general, we don't mention these things in the weewx documentation, as they are part of the MySQL / Maria admin world. Indeed, there are many other such database admin comments we could add, such as the use of triggers, timeouts, character sets, etc. If the user is going to be using MySQL, we expect him/her to be conversant in its administration.
-tk On Wed, Mar 15, 2017 at 5:34 AM, Theophanis Kontogiannis <[email protected]> wrote: > Hi Tom, > > I read through this: http://mysql-python.sourceforge.net/MySQLdb.html#id6 > <http://mysql-python.sourceforge.net/MySQLdb.html#id6> > > And I think that the magic words are: > > *Okay, so you want to use _mysql anyway. Here are some examples.* > > *The simplest possible database connection is:* > > *import _mysql* > > *db=_mysql.connect()* > > > > *This creates a connection to the MySQL server running on the local > machine* *using the standard UNIX socket (or named pipe on Windows),** > your login name (from the USER environment variable), no password, and does > not USE a database. Chances are you need to supply more information.:* > > *db=_mysql.connect("localhost","joebob","moonpie","thangs")* > > > > *This creates a connection to the MySQL server running on the local > machine via a UNIX socket (or named pipe), the user name "joebob", the > password "moonpie", and selects the initial database "thangs".* > > *We haven't even begun to touch upon all the parameters connect() can > take. For this reason, I prefer to use keyword parameters:* > > *db=_mysql.connect(host="localhost",user="joebob",* > * passwd="moonpie",db="thangs")* > > > > *This does exactly what the last example did, but is arguably easier to > read. But since the default host is "localhost", and if your login name > really was "joebob", you could shorten it to this:* > > *db=_mysql.connect(passwd="moonpie",db="thangs")* > > > > *UNIX sockets and named pipes don't work over a network, so if you specify > a host other than localhost, TCP will be used, and you can specify an odd > port if you need to (the default port is 3306):* > > *db=_mysql.connect(host="outhouse",port=3307,passwd="moonpie",db="thangs")* > > > > *If you really had to, you could connect to the local host with TCP by > specifying the full host name, or 127.0.0.1.* > > > > This is the standard behavior. To connect to the standard unix socket > which is /var/lib/mysql/mysql.sock > > As my socket is in /mnt/services/DBs/mysql/mysql.sock there is no config > in MySQLdb to point it to that path. > > It is black white according to the document above. > > Use 'locahost' and point MySQLdb to the standard socket (I do not use > standard socket) or use 127.0.0.1 and go for TCP/IP (on port 3306). > > Hence my thought on explicitly mentioning that in weewx docs cause people > do use non-standard sockets. > > My python knowledge is minimal so I am digging info based on my SysAdm > experience. > > Hope it helps and shorts things out. > > BR > Theo > > > > On Tuesday, March 14, 2017 at 10:50:59 PM UTC+2, Tom Keffer wrote: >> >> I think you're right. >> >> >>> from MySQLdb import connect >> >>> # No port number specified: >> >>> conn = connect(host='localhost', user='weewx',passwd='weewx') >> >>> conn.get_host_info() >> 'Localhost via UNIX socket' >> >>> conn.close() >> >>> # Add a port number: >> >>> conn = connect(host='localhost', port=3306, user='weewx', >> passwd='weewx') >> >>> conn.get_host_info() >> 'Localhost via UNIX socket' >> >>> conn.close() >> >>> conn = connect(host='127.0.0.1', user='weewx',passwd='weewx') >> >>> conn.get_host_info() >> '127.0.0.1 via TCP/IP' >> >> Sorry for questioning you. Somewhere along the line when I was developing >> weedb, I came under the impression that it was the presence or absence of >> the port that controlled whether a TCP/IP or socket connection was used. >> >> Unfortunately, I still can't help you on why you're unable to connect >> with the socket. There does not seem to be any MySQLdb function that >> returns what socket it expects to be communicating with. Or, at least, I >> can't find it. >> >> You can try the exercise above to see what communication protocol your >> installation is using. I'd be interested to see if it's different from mine. >> >> -tk >> >> On Tue, Mar 14, 2017 at 1:16 PM, Theophanis Kontogiannis < >> [email protected]> wrote: >> >>> This is not what I got after testing it. >>> >>> Please read in line the per use case test result (with the new mysql.py >>> code) >>> >>> On Tuesday, March 14, 2017 at 10:02:12 PM UTC+2, Tom Keffer wrote: >>>> >>>> Let me try again: >>>> >>>> - If hostname is localhost *or 127.0.0.1*, but no port is supplied: >>>> use sockets. >>>> >>>> No. >>> If it is localhost it uses socket. >>> If it is 127.0.0.1 it uses TCP/IP. >>> >>>> >>>> - If hostname is localhost* or 127.0.0.1*, but a port is supplied: >>>> use TCP/IP. >>>> >>>> No. >>> If it is 127.0.0.1+port it uses TCP/IP >>> If it is localhost+port it uses socket. >>> >>>> >>>> - If hostname is anything else, use TCP/IP. >>>> >>>> Correct >>> >>> >>>> If you want to use sockets, you should *not* specify a port number. It >>>> only makes sense: you are not using TCP/IP, so why would it need a port >>>> number? >>>> >>> >>> For testing the various use cases. >>> >>> In my case however I am using non standard location for the socket, but >>> weedb does not honour the MySQL configuration for that. >>> >>> >>>> >>>> As far as why your server cannot connect to the mysql.sock socket, I >>>> can't say. That's a MariaDB / MySQLDB question. >>>> >>> >>> Correct. >>> >>> >>>> I assume something has to be set in the configuration file. >>>> >>> >>> It has, but for some reason it gets ignored by weewx or weedb. Probably >>> because the MySQL config files are parsed only by native clients. >>> >>> Hence my suggestion to explicitly document the completely agnostic >>> config with only three simple cases of a local SQL data base: >>> >>> a) Standard socket? Use localhost >>> b) Non standard socket? Use 127.0.0.1 >>> c) Non standard socket AND non standard port? Use 127.0.0.1 + port >>> >>> BR >>> TK >>> >>> >>> Or, perhaps your server was started up with a flag that tells it to >>>> communicate with TCP/IP only? I dunno. This is an area outside of my >>>> expertise. >>>> >>>> -tk >>>> >>>> >>>> >>> On Tue, Mar 14, 2017 at 12:04 PM, Theophanis Kontogiannis < >>>> [email protected]> wrote: >>>> >>>>> Negative Tom >>>>> >>>>> Replaced with new code. >>>>> weewx does not complain any more for the port number not being >>>>> integer, but still tries to access the DB via the standard socket and not >>>>> via TCP/IP >>>>> Would it not be more simple to just document the two cases? >>>>> 1) socket ---> host=localhost >>>>> 2) TCP/IP---> host=127.0.0.1 >>>>> >>>>> >>>>> >>>>> Mar 14 21:00:45 tweety.example.net weewx[10419]: engine: Database >>>>> OperationalError exception: (2002, "Can't connect to local MySQL server >>>>> through socket '/var/lib/mysql/mysql.sock' (2)") >>>>> Mar 14 21:00:45 tweety.example.net weewx[10419]: **** Waiting 2 >>>>> minutes then retrying... >>>>> >>>>> >>>>> >>>>> [[MySQL]] >>>>> driver = weedb.mysql >>>>> # The host where the database is located >>>>> host = localhost >>>>> port = 3306 >>>>> >>>>> >>>>> BR >>>>> Theo >>>>> >>>>> >>>>> >>>>> On Tuesday, March 14, 2017 at 3:05:31 PM UTC+2, Tom Keffer wrote: >>>>>> >>>>>> You did provide the right syntax. The problem is that the driver did >>>>>> not convert the option to an integer before using it. Fixed in commit >>>>>> 163d7da >>>>>> <https://github.com/weewx/weewx/commit/163d7dac6dbc44a181468a9b0f0473cd29df97b2> >>>>>> . >>>>>> >>>>>> Replace your copy of weedb/mysql.py with this copy >>>>>> <https://raw.githubusercontent.com/weewx/weewx/163d7dac6dbc44a181468a9b0f0473cd29df97b2/bin/weedb/mysql.py>. >>>>>> Or wait until V3.7.1. >>>>>> >>>>>> -tk >>>>>> >>>>>> On Tue, Mar 14, 2017 at 3:32 AM, Theophanis Kontogiannis < >>>>>> [email protected]> wrote: >>>>>> >>>>>>> Hi. >>>>>>> >>>>>>> Changed the config to: >>>>>>> >>>>>>> [[MySQL]] >>>>>>> driver = weedb.mysql >>>>>>> # The host where the database is located >>>>>>> * host = localhost* >>>>>>> * port = 3306 * >>>>>>> # The user name for logging in to the host >>>>>>> user = weewx >>>>>>> # The password for the user name. Put in quotes to guard >>>>>>> against parsing errors. >>>>>>> password = ****** >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> and it fails: >>>>>>> >>>>>>> [root@tweety ~]# systemctl status -l weewx >>>>>>> ● weewx.service - SYSV: start and stop the weewx weather system >>>>>>> Loaded: loaded (/etc/rc.d/init.d/weewx; bad; vendor preset: >>>>>>> disabled) >>>>>>> Active: active (exited) since Tue 2017-03-14 12:29:12 EET; 999ms >>>>>>> ago >>>>>>> Docs: man:systemd-sysv-generator(8) >>>>>>> Process: 1305 ExecStop=/etc/rc.d/init.d/weewx stop (code=exited, >>>>>>> status=0/SUCCESS) >>>>>>> Process: 1314 ExecStart=/etc/rc.d/init.d/weewx start >>>>>>> (code=exited, status=0/SUCCESS) >>>>>>> >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** File >>>>>>> "/usr/share/weewx/weedb/mysql.py", line 38, in guarded_fn >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** >>>>>>> return fn(*args, **kwargs) >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** File >>>>>>> "/usr/share/weewx/weedb/mysql.py", line 118, in __init__ >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** >>>>>>> db=database_name, **kwargs) >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** File >>>>>>> "/usr/lib64/python2.7/site-packages/MySQLdb/__init__.py", line 81, >>>>>>> in Connect >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** >>>>>>> return Connection(*args, **kwargs) >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** File >>>>>>> "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line >>>>>>> 193, in __init__ >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** >>>>>>> super(Connection, self).__init__(*args, **kwargs2) >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** >>>>>>> TypeError: an integer is required >>>>>>> Mar 14 12:29:13 tweety.example.net weewx[1320]: **** Exiting. >>>>>>> >>>>>>> >>>>>>> Did I provide the correct "host/port" syntax? >>>>>>> >>>>>>> Thank you >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Tuesday, March 14, 2017 at 3:05:32 AM UTC+2, Theophanis >>>>>>> Kontogiannis wrote: >>>>>>>> >>>>>>>> Yeap looks correct. >>>>>>>> >>>>>>>> So basically in my 3.7.x case, setting "*host=127.0.0.1*" (replace >>>>>>>> localhost with the IP for the localhost) did the trick to force TCP/IP >>>>>>>> connection. >>>>>>>> >>>>>>>> Will test the localhost/port combination to check if it gives the >>>>>>>> same result. >>>>>>>> >>>>>>>> We are in tune. >>>>>>>> >>>>>>>> Thank you >>>>>>>> Theo >>>>>>>> >>>>>>>> >>>>>>>> On Tuesday, March 14, 2017 at 1:05:56 AM UTC+2, Tom Keffer wrote: >>>>>>>>> >>>>>>>>> Here's how I understand it: >>>>>>>>> >>>>>>>>> - If hostname is localhost, but no port is supplied: use >>>>>>>>> sockets. >>>>>>>>> - If hostname is localhost, but a port is supplied: use TCP/IP. >>>>>>>>> >>>>>>>>> In version 3.6.x, you were in situation #2. With version 3.7.x, >>>>>>>>> you are in #1. >>>>>>>>> >>>>>>>>> -tk >>>>>>>>> >>>>>>>>> On Mon, Mar 13, 2017 at 3:58 PM, Theophanis Kontogiannis < >>>>>>>>> [email protected]> wrote: >>>>>>>>> >>>>>>>>>> Hi Tom, >>>>>>>>>> >>>>>>>>>> Thank you for the follow up. >>>>>>>>>> >>>>>>>>>> Actually the standard MySQL/MariaDB way is to use sockets if the >>>>>>>>>> hostname is localhost. >>>>>>>>>> TCP/IP will only be used if the hostname is not localhost, or if >>>>>>>>>> an IP is provided. >>>>>>>>>> >>>>>>>>>> So basically it works now as expected (non standard dirs etc) but >>>>>>>>>> had to provide the ip address for the localhost (127.0.0.1) to force >>>>>>>>>> TCP/IP >>>>>>>>>> connection. >>>>>>>>>> >>>>>>>>>> Should it be documented? >>>>>>>>>> >>>>>>>>>> Best regards, >>>>>>>>>> >>>>>>>>>> Theo >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Tuesday, March 14, 2017 at 12:54:35 AM UTC+2, Tom Keffer wrote: >>>>>>>>>>> >>>>>>>>>>> Yes, the code did change. >>>>>>>>>>> >>>>>>>>>>> Before, it always passed in the hostname and port, which caused >>>>>>>>>>> MySQL to always use a TCP/IP connection. Now it adds the port only >>>>>>>>>>> if the >>>>>>>>>>> hostname is 'localhost' or '127.0.0.1'. >>>>>>>>>>> >>>>>>>>>>> I suspect that you were connecting via TCP/IP all along. The >>>>>>>>>>> change just exposed the problem. But, I'm not a MySQL (let alone >>>>>>>>>>> MariaDB) >>>>>>>>>>> expert! >>>>>>>>>>> >>>>>>>>>>> Try Googling "Can't connect to local MySQL server through socket >>>>>>>>>>> '/var/lib/mysql/mysql.sock' " and see what you come up with. You >>>>>>>>>>> may have >>>>>>>>>>> to configure your installation explicitly to use >>>>>>>>>>> /var/lib/mysql/mysql.sock. >>>>>>>>>>> >>>>>>>>>>> -tk >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Mon, Mar 13, 2017 at 3:01 PM, Theophanis Kontogiannis < >>>>>>>>>>> [email protected]> wrote: >>>>>>>>>>> >>>>>>>>>>>> Hello All, >>>>>>>>>>>> >>>>>>>>>>>> I have a mariadb on CEntOS 7.3 with non-standard datadir and >>>>>>>>>>>> socket. >>>>>>>>>>>> >>>>>>>>>>>> The configuration is implemented via drop in files. >>>>>>>>>>>> >>>>>>>>>>>> [root@tweety ~]# cat /etc/my.cnf.d/tweety.cnf >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> [mysqld] >>>>>>>>>>>> datadir=/mnt/services/DBs/mysql >>>>>>>>>>>> socket=/mnt/services/DBs/mysql/mysql.sock >>>>>>>>>>>> >>>>>>>>>>>> [client] >>>>>>>>>>>> >>>>>>>>>>>> socket=/mnt/services/DBs/mysql/mysql.sock >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> The weewx configuration is: >>>>>>>>>>>> >>>>>>>>>>>> [[MySQL]] >>>>>>>>>>>> driver = weedb.mysql >>>>>>>>>>>> # The host where the database is located >>>>>>>>>>>> host = *localhost* >>>>>>>>>>>> # The user name for logging in to the host >>>>>>>>>>>> user = weewx >>>>>>>>>>>> # The password for the user name. Put in quotes to >>>>>>>>>>>> guard against parsing errors. >>>>>>>>>>>> password = ***** >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> I keep on getting an error on weewx launch: >>>>>>>>>>>> >>>>>>>>>>>> engine: Database OperationalError exception: (2002, "Can't >>>>>>>>>>>> connect to local MySQL server through socket >>>>>>>>>>>> '/var/lib/mysql/mysql.sock' >>>>>>>>>>>> (2)") >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Tried to 'setenforce 0' but I get the same error. >>>>>>>>>>>> >>>>>>>>>>>> Tried to add the non-standard socket in the /etc/my.cnf main >>>>>>>>>>>> config file still no luck. >>>>>>>>>>>> >>>>>>>>>>>> The only way to make it work is to change the weewx.conf to >>>>>>>>>>>> force it use the TCP/IP connector instead of the default socket >>>>>>>>>>>> connector. >>>>>>>>>>>> >>>>>>>>>>>> [[MySQL]] >>>>>>>>>>>> >>>>>>>>>>>> # The host where the database is located >>>>>>>>>>>> >>>>>>>>>>>> host = *127.0.0.1* >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Is the socket location hard coded in the weedb.mysql? >>>>>>>>>>>> >>>>>>>>>>>> Is there something else I am doing wrong or missed in the >>>>>>>>>>>> documentation? >>>>>>>>>>>> >>>>>>>>>>>> Is there something that should be changed in the code or added >>>>>>>>>>>> in the documentation? >>>>>>>>>>>> >>>>>>>>>>>> Thank you for your time. >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> -- >>>>>>>>>>>> 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 [email protected]. >>>>>>>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>> 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 [email protected]. >>>>>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>> 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 [email protected]. >>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>> >>>>>> >>>>>> -- >>>>> 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 [email protected]. >>>>> For more options, visit https://groups.google.com/d/optout. >>>>> >>>> >>>> -- >>> 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 [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
