MariaDB (MySQL) Login Failure New Install Fix

Sometimes…

So I decided, just for grins, to install MariaDB (MySQL) on the Raspberry Pi M3 under Devuan 2.0 just as a test case for how small a system I could use for the GHCN data. I’d done the install on the XU4 and “I know all the steps” so what could go wrong?

True, before: I’d done a MySQL install and then did MariaDB over it as an upgrade. Turns out that now installing “mysql-server” actually just does an install of “mariadb-server” (I originally installed MariaDB, then ripped it out and tried the “2 step” that informed me it was just going to install MariaDB).

As MariaDB is a “drop in replacement” for MySQL, it has the unfortunate side effect of installing a lot of stuff AS “mysql” commands.

In /usr/bin you find the usual set of commands to “do stuff” including the one that lets you set the password on the database system. In the past an ncurses style panel would pop up during installation and set the password during install. This doesn’t happen with the “new” install. So I figured I’d just use the utility that sets the password to set the password…

ls /usr/bin/mar*
/usr/bin/mariabackup
/usr/bin/mariadb
/usr/bin/mariadbcheck
/usr/bin/mariadb-service-convert

root@PiM3Devuan2:/# ls /usr/bin/my*
/usr/bin/myisamchk /usr/bin/mysql_find_rows
/usr/bin/myisam_ftdump /usr/bin/mysql_fix_extensions
/usr/bin/myisamlog /usr/bin/mysqlhotcopy
/usr/bin/myisampack /usr/bin/mysqlimport
/usr/bin/my_print_defaults /usr/bin/mysql_install_db
/usr/bin/mysql /usr/bin/mysqloptimize
/usr/bin/mysqlaccess /usr/bin/mysql_plugin
/usr/bin/mysqladmin /usr/bin/mysqlrepair
/usr/bin/mysqlanalyze /usr/bin/mysqlreport
/usr/bin/mysqlbinlog /usr/bin/mysql_secure_installation
/usr/bin/mysqlcheck /usr/bin/mysql_setpermission
/usr/bin/mysql_convert_table_format /usr/bin/mysqlshow
/usr/bin/mysqld_multi /usr/bin/mysqlslap
/usr/bin/mysqld_safe /usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysqld_safe_helper /usr/bin/mysql_upgrade
/usr/bin/mysqldump /usr/bin/mysql_waitpid
/usr/bin/mysqldumpslow /usr/bin/mysql_zap
/usr/bin/mysql_embedded /usr/bin/mytop

The key one being that “mysql_secure_installation”. I ran it. It asked for the current password and accepted a blank. It claimed to change the password. Then attempting to log in with the same credential / command as before would fail:

chiefio@PiM3Devuan2:~$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I’ve spent a few days, on and off, trying to make this go / fix the permissions issue. Today I resorted to some web searches on the “problem” eventually finding this:

https://superuser.com/questions/949496/cant-reset-mysql-mariadb-root-password

down in the text a ways:

EDIT, some months later: I’m now used to have this problem come back frequently, I think at each update of MariaDB (or something like that). So I’ve got a better comprehension of the probem ; there’s an UNIX_SOCKET plugin that can let you log in a MariaDB account without having to create a password, because it uses the shell’s credentials to trust you, without having to enter any password. In fact, this plugin is an authentication plugin and not a method of communication with the SQL server. So you can safely disable it if you don’t use unix socket as a logging-in method. The only thing I can’t explain is why the UNIX_SOCKET plugin is regularly set on each account of the database, without any action on my side.

This has the nice side effect that, when it happens, you can login to the SQL server without having to restart MariaDB with –skip-grant-tables: just log-in to the system’s root account, then just connect with mysql -u root without password, then reset the plugin field in the way it is explained above.

OK, that got me logged in to MariaDB:

root@PiM3Devuan2:/# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Now I can proceed with the basic business of setting up login / permissions / DB Tables / etc. and try to get back to more normal operations.

The article linked states to remove the socket based connection, but a further comment says that breaks some other bits, so I’m not sure exactly what the final “fix” will be. Hopefully I’ll figure out how to just let me do the regular old connection by “mysql -u {whoever} -p” and not have to take this “be root” path or fiddle with the socket connector; but at this point the end game is unknown.

This breaks the maintenance cron job on Ubuntu 16.04 (at least) because that script is expecting to log in without a password using the socket plug-in. See superuser.com/questions/957708/… for details. – colan Jul 21 ’16 at 20:24
Okay, interesting, but this does not happen anymore (my legacy passwords setup does not break randomly now). Maybe the Unix socket plugin is set on the root user only, now? Couldnt’ check that right now. But I don’t like the idea that the OS decides for me what authentication setup to use. – Morgan Touverey Quilling Jul 21 ’16 at 20:45
This problem sucks, it happened on Debian 9, no root password was asked when I installed MariaDB and I could not even reset it. I hope it will not happen again after an upgrade. Should we replace MariaDB with MySQL to avoid such problems? – baptx Nov 30 ’17 at 16:03
Not sure it still happens, it may have been fixed now (I can edit the post if anyone knows). Maybe it happens only at the installation, which would be a good default setup. – Morgan Touverey Quilling Nov 30 ’17 at 22:20
1
I just noticed that if we don’t use the unix socket plugin for the root user, it will fail to install phpmyadmin package on latest Debian: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO). So the best solution is to keep using the default unix socket authentication with command sudo mysql -u root, which I think is also more secure and performant. – baptx Dec 1 ’17 at 12:31

So “somebody” decided to “fix what isn’t broken” and broke all the prior documentation of how to set up MySQL / MariaDB while causing the usual password reseting process to be broken too. Sigh.

In Conclusion

So, OK, I’ve got a way in, even if it is ass-backwards. I can move forward with recreating the temperature database and adding users and all.

With some luck, I’ll figure out how to turn off this “improvement” to my “security” that mostly just means I must have a root shell running all the time (NOT a security improvement…) and figure out why in hell I must use a “Socket” to get from my own terminal to my own database on my own system.

In my opinion, many of the present “Oh Bother” changes in Linux have been to cater to the major industrial users (“cloud” providers, Red Hat Enterprise on VMs) at the expense of the “plain old desktop guy at home”. I don’t need a full on industrial installation to fiddle with a database in my own office on a Raspberry Pi. Setting things up to expect a staff including a Network Engineer, Security Officer, Kerberos Administrator, Systems Programmer, etc. etc. is just not helping. In the past, those shops, who have that staff, did the set up for their needs and left the rest of us alone. That seems to be inverting.

Sigh.

I really hope some Distribution rises that just dumps all the Red Hat Corporate pandering changes in Linux and reverts to a “Home Gamer Friendly” process.

We’ve reached the point where constantly layering on ever more complexity in the name of simplification is killing the product. Networking has at least 3 layers over administration that I know of all to “hide” the old way of “edit this file and add this text”. Just stupid complication creep.

If that simpler way doesn’t show up in a year or so, I’ll be slowly moving solutions back onto a BSD base. Most likely FreeBSD, but maybe NetBSD for some. Why? The only reason NOT to use them is the “user hostile” old way of doing things. BUT, as Linux is becoming even MORE “user hostile” with layers on layers of kludge and SystemD crapping on every system possible: We are nearly at parity of “Oh Bother” between the two. At that point, I’ll take “Simple, proven, and familiar” and just suck it up and deal with getting X-windows going. (I’ve configured X before, I just don’t like it…)

There are already some releases on the PC that come with windows already installed. That will show up on the Arm Arch eventually. At that time, I’m gone. If I want to deal with the complexity of an “industrial strength” installation, I’ll do it in BSD not in SystemD crapped up complexified Linux.

With that, I’m going to dive back into figuring out the end game of getting MariaDB to once again be “user friendly”… and getting the NCDC / NOAA / GHCN stuff reloaded and some performance testing done on the R.PiM3.

Subscribe to feed

Advertisements

About E.M.Smith

A technical managerial sort interested in things from Stonehenge to computer science. My present "hot buttons' are the mythology of Climate Change and ancient metrology; but things change...
This entry was posted in NCDC - GHCN Issues, Tech Bits and tagged , , , , . Bookmark the permalink.

11 Responses to MariaDB (MySQL) Login Failure New Install Fix

  1. E.M.Smith says:

    From further down that same thread there are two “solutions” (once you have database access…).

    The basic situation seems to be that the default authenticator was switched from “mysql_native_password” to “unix_socket”; so you can either change it back OR adapt to the change. Adapting to the change requires you to already have access to search a log for a magic string, so you still need that initial login to happen. This also explains why the “set password” process failed, as it is setting the “native password” that isn’t being used…

    Once again, too many “layers” being stacked. In this case a few different and competing authenticators.

    How to just shut off the “unix_socket” authenticator, short form:

    From this answer, http://ubuntuforums.org/showthread.php?t=2275033&p=13272227#post13272227.

    Mysql tries to authenticate root using plugin, not password. You need to disable plugin usage for root.

    shell$ sudo mysql -u root
    
    [mysql] use mysql;
    [mysql] update user set plugin='' where User='root';
    [mysql] flush privileges;
    [mysql] \q
    

    Then, to get the particular “magic string” for unix_socket (this has the skip-grant-tables set to get access, that didn’t work for me at first boot, so had to “become root” and then do a connect… maybe it works in some other context…)

    Connect as previously described:

    mysqld_safe –skip-grant-tables

    Log file will be showed:

    160518 23:21:01 mysqld_safe Logging to ‘/usr/local/mysql/data/ab123456.domain.com.err’.
    160518 23:21:01 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

    Search log listed (in this case: /usr/local/mysql/data/ab123456.domain.com.err) for right socket:

    cat /usr/local/mysql/data/ab123456.domain.com.err | grep "socket: "
    Version: '5.5.49-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  MariaDB Server
    

    and use it in mysql connection:

    mysql --socket /tmp/mysql.sock -u root
    root@ab123456:~# /usr/local/mysql/bin/mysql --socket /tmp/mysql.sock -u root
    
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 1
    Server version: 5.5.49-MariaDB MariaDB Server
    

    I note in passing he does a “cat | grep” when simply doing “grep /usr/…” would be fine.

    Then, the other option, just reset the login authenticator to use “mysql_native_password”:

    By default marriadb uses “unix_socket” authentication plugin to set passwords this should be “mysql_native_password” so

    switch database..

    use mydatabase;
    

    first see witch plugin is set..

    SELECT user, plugin FROM user;
    

    set it to “mysql_native_password”

    UPDATE user SET plugin="mysql_native_password"; 
    

    set new password…

    update user set authentication_string=password('My@Password'), plugin='mysql_native_password' where user='root';
    

    I’m most likely to do the last of those so behaviour just reverts to what it was before (and my login command stays the same)

    Grumble…

  2. E.M.Smith says:

    OK, I’ve just added the user “chiefio” in that root shell terminal, then created the DB, added tables, and did a first load of the inventory data:

    In the Root terminal MariaDB login:

    MariaDB [(none)]> CREATE DATABASE temps;
    Query OK, 1 row affected (0.28 sec)
    
    MariaDB [(none)]> GRANT ALL ON temps.* to chiefio@localhost
        -> IDENTIFIED BY 'LetMeIn!';
    Query OK, 0 rows affected (0.21 sec)
    

    Then was able to login “as me” using a start command with my login / password in it:

    chiefio@PiM3Devuan2:~/SQL/bin$ bcat mystart
    mysql -u chiefio -p --local-infile temps
    

    So for now at least, I’m just going to leave it that I must “sudo” to do maintenance and I’ll use my MariaDB login / pw for the actual loading data and reporting.

    I then created the various tables (by doing a “source FOO” on the table building scripts I’d saved like:

    MariaDB [temps]> source SQL/Schemas/continent.sql
    Query OK, 0 rows affected (0.26 sec)
    
    MariaDB [temps]> source SQL/Schemas/country.sql
    Query OK, 0 rows affected (0.18 sec)
    

    As you can see, at fractional seconds to build the table schema, it’s a non-problem even on the R.Pi M3. More than enough CPU.

    I then loaded some of the data (and had to change the path in my script as my home directory location is different) without issue:

    MariaDB [temps]> source SQL/Schemas/load.continent.sql
    Query OK, 1 row affected (0.10 sec)
    Query OK, 1 row affected (0.05 sec)
    Query OK, 1 row affected (0.07 sec)
    Query OK, 1 row affected (0.02 sec)
    Query OK, 1 row affected (0.02 sec)
    Query OK, 1 row affected (0.02 sec)
    Query OK, 1 row affected (0.02 sec)
    Query OK, 1 row affected (0.03 sec)
    

    For the 8 records in “continent” (one being At Sea) the load speed is 1/10 second to 2/100 second, so again no problems.

    I made a Pi version of the “inventory load” SQL command reflecting the Seagate 500 disk I’m using and the xfs file system for loading the GHCN v3.3 “inventory” data. A bit over 7000 lines one for each thermometer over the whole record:

    chiefio@PiM3Devuan2:~/SQL/bin$ cat loadPi3.sql 
    LOAD DATA LOCAL INFILE '/SG500/xfs/chiefio/SQL/v3/3invent.out' INTO TABLE invent3;
    

    Ran it:

    MariaDB [temps]> source SQL/bin/loadPi3.sql          
    Query OK, 7280 rows affected (2.74 sec)              
    Records: 7280  Deleted: 0  Skipped: 0  Warnings: 0
    

    So 7280 records loaded n 2.74 seconds. Not bad at all…

    I started to do the load of the GHCN v3.3 temperature data. It ran for 4 or 5 minutes… “htop” showing a fair amount of “D” disk wait states. Simples, really. I was taking a lot of “Long Seeks” as MariaDB by default stuffs your database into /var and I’d put that by itself on a nice isolated partition on the same slow disk as my home directory. The file being loaded is about 411 MB:

    chiefio@PiM3Devuan2:~/SQL/v3$ ls -l 3temps.out 
    -rw-r--r-- 1 chiefio chiefio 411010800 Feb  8 19:25 3temps.out
    

    At the start, /var had about 377 MB free. My bad..

    chiefio@PiM3Devuan2:~/SQL/bin$ df
    Filesystem     1K-blocks      Used Available Use% Mounted on
    [...]
    /dev/sda3      393024000  68257384 324766616  18% /SG500/xfs
    [...]
    /dev/sda10       2086912   1709624    377288  82% /var
    [...]
    chiefio@PiM3Devuan2:~/SQL/bin$ df /var
    Filesystem     1K-blocks    Used Available Use% Mounted on
    /dev/sda10       2086912 2086584       328 100% /var
    

    So, OK, I need to either jump up the file system size or find out how to change where the database lives.

    At the same time, taking “long seeks” from slice 3 to slice 10 in moving 400 MB is not ideal (especially as swap and /tmp are on the same not-so-fast older USB2.0 disk…

    But I’m “over the hump” to making MariaDB “go again” but on the Raspberry Pi Model 3.

    So I’m off for a little while to do disk maintenance / planning and I’ll be back later with some more performance data. Also, I’ll need to re-do all that installing “idle” and Python libraries and “stuff”… so it could be a while.

    But, it’s working again, and I get to proof that all this works on the PiM3 just dandy too ;-)

  3. kneel63 says:

    “Grumble…”
    Yeah – like networking…
    OK, the ip command is pretty good, but the old ifconfig (no longer available in new distros) gave you in one command what you now need two to do! I used both, depending on what I wanted, but now…
    Grumble, indeed!

  4. E.M.Smith says:

    SystemD Strikes Again!

    I found several pages that all said how to move the data base default location. Tried it. Didn’t work. Mostly they point at the old place where the config files live, and in the new way they are in another directory and a bit further down, but that wasn’t THE big problem…

    For example this:
    https://www.tecmint.com/change-default-mysql-mariadb-data-directory-in-linux/

    says to edit:

    # vi /etc/my.conf
    OR
    # vi /etc/mysql/my.conf
    

    and change datadir and socket.

    In the newer version those are in fact in
    /etc/mysql/mariadb.conf/50-server.cnf
    for the datadir and in
    /etc/mysql/debian.cnf
    for socket.

    But even doing that didn’t work. A bit more searching showed that SystemD has now stuck it’s snout firmly up the skirt of MariaDB and controls where it can put its data bits.

    https://dba.stackexchange.com/questions/185302/mysql-mariadb-cannot-change-default-datadir-on-debian-9-1-server

    Everywhere systemD goes, it breaks things that “just worked” before…

    MySql/MariaDB cannot change default datadir on Debian 9.1 server

    I have a problem to move the default datadir of Maria DB to another partition, it appears to be very common but i tried everything I can without luck.
    […]
    You must set ProtectHome=false in the systemd config file in order to move your datadir to the /home directory.

    According to Debian policy rules, you should set this option value in a custom file like /etc/systemd/system/mariadb.service.d/YOUR_CUSTOM_FILE.conf containing:

    [Service]

    # Prevent accessing /home, /root and /run/user
    ProtectHome=false

    Then reload systemctl daemon:

    systemctl daemon-reload

    After that you should be able to change MariaDB datadir from /var/lib/mysql to /home/mysql.

    Which would be all fine and good… except I’m not running SystemD…

    So I’m locked into /var/lib/mysql …

    Except…

    As Root, I can put in place a symbolic link instead of that directory and just “move on” so I did.

    I stopped the server ( “service mysql stop”) then:

    I did a “mkdir /WDTB/xfs/MariaDB” then “chown mysql:mysql /WDTB/xfs/MariaDB”. That made the destination. Then I did a “mv /var/lib/mysql/* /WDTB/xfs/MariaDB”, then “rmdir /var/lib/mysql”

    After that it was just:

    ln -s /WDTB/xfs/MariaDB /var/lib/mysql
    service mysql start
    

    and Bob’s Yer Uncle…

    “Someday” when that actually gets fixed / removed from MariaDB I’ll go do it “right” without the symbolic link, but for now it’s all I needed.

    I then proceeded to do the load again.

    MariaDB [temps]> source SQL/Schemas/temps3.sql
    Query OK, 0 rows affected (0.40 sec)
    
    MariaDB [temps]> source SQL/bin/tempsPi3l.sql
    Query OK, 5554200 rows affected (6 min 28.75 sec)    
    Records: 5554200  Deleted: 0  Skipped: 0  Warnings: 0
    

    So 6 1/2 minutes to load 411 MB of data into 5.5 Million “rows”. I can live with that.

    In this case the data is read from one disk and loaded into another, so no head seeks…

    I can now proceed to the Python installation bits, re-run some of the maps and reports as QA and then be “good to go” on a relatively clean new install of MariaDB on the Raspberry Pi under Devuan.

    I really really hope that without too much delay more folks can start abandoning SystemD and realize just how evil it is and how much it breaks “everything you know” and how it has its “fingers” in every opening possible… It’s just wrong…

  5. E.M.Smith says:

    @kneel63:

    Devuan still has ifconfig ;-)

    root@PiM3Devuan2:/etc/mysql/mariadb.conf.d# which ifconfig
    /sbin/ifconfig
    root@PiM3Devuan2:/etc/mysql/mariadb.conf.d# ifconfig
    eth0: flags=4163  mtu 1500
            inet 10.1.1.10  netmask 255.255.255.0  broadcast 10.1.1.255
            inet6 fe80::ba27:ebff:fecf:83ed  prefixlen 64  scopeid 0x20
            ether b8:27:eb:cf:83:ed  txqueuelen 1000  (Ethernet)
            RX packets 30300  bytes 16902759 (16.1 MiB)
            RX errors 0  dropped 0  overruns 0  frame 0
            TX packets 31763  bytes 5282418 (5.0 MiB)
            TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
    [...]
    

    I use it often… ;-)

  6. E.M.Smith says:

    Oh, and here you can see the data is going where it belongs:

    root@PiM3Devuan2:/WDTB/xfs# du -ms /WDTB/xfs/MariaDB/
    983	/WDTB/xfs/MariaDB/
    root@PiM3Devuan2:/WDTB/xfs# df /var /WDTB/xfs
    Filesystem     1K-blocks      Used Available Use% Mounted on
    /dev/sda10       2086912   1341720    745192  65% /var
    /dev/sdb3      974155020 106542224 867612796  11% /WDTB/xfs
    

    /var not filling up and “stuff” showing up in /WDTB/xfs …

  7. E.M.Smith says:

    A fairly complicated report with joins and all (and not a lot of index fields) takes about one minute:

    root@PiM3Devuan2:/SG500/xfs/chiefio/SQL/bin# cat compbasenow.sql 
    SELECT I.latitude, I.coslong FROM invent3 AS I 
    INNER JOIN temps3 as T on I.stationID=T.stationID 
      WHERE year<1949 AND year<1981 AND I.stationID NOT IN 
       (SELECT I.stationID FROM invent3 AS I 
        INNER JOIN temps3 as T ON I.stationID=T.stationID 
        WHERE  year=2015) 
    GROUP BY I.stationID;
    

    I think it’s not a “right” report in that I think I wanted GT 1949, but “whatever” it did all the same processing anyway. The result?

    |  30.0000 | -121.24 |
    |  50.0000 |  -93.20 |
    +----------+---------+
    2354 rows in set (1 min 1.09 sec)
    
    MariaDB [temps]> 
    

    Almost exactly one minute. I can live with one minute to do a complete database read, joining a large temperature data table to the inventory table, twice, and then selecting on it.

    I think that’s pretty much an existence proof that all you really need to run the GHNC data through a MySQL or MariaDB database and make reports is a Raspberry Pi Model 3 and a couple of minutes.

    To quote a bunch of bad cop movies: “We’re done here.”

    So now I’m going off to do that Python stuff… back later…

  8. E.M.Smith says:

    OK, got it to work. I’ve once again made the “Global Thermometers GHCN v3.3 BASELINE over NOW GIStep” global sin (really cos) projection global map. At a guess, it was about 90 seconds to run.

    The “Gotchas”:

    It seems that the Pi version of Python does NOT by default include the database connector. That must be installed. Also, to do it, you must install “pip” the Python Package Manager. As Python comes in two flavors, there are two flavors of it to install… (I DO wish the current crop of programmers understood exponentials and why even a ^2 was a Very Bad Thing…)

    So the links and stuff:

    https://www.raspberrypi.org/forums/viewtopic.php?p=1401961

    gave clue as to why Python code that ran FINE before on the Devuan XU4 was barfing.

    Re: Connect to MariaDB from Python
    Quote
    Fri Aug 24, 2018 3:23 pm
    For anyone else that comes through here – I couldn’t get it working until I lucked into the right download:
    Code: Select all

    sudo apt-get libmariadbclient-dev
    

    This enabled:

    Code: Select all

    pip install mysql-connector 
    

    As per https://github.com/sanpingz/mysql-connector provided example, I was able to connect.

    This was for python3.7 running in virtualenv.

    The first command proceeded Just Fine:

    root@PiM3Devuan2:/etc/mysql/mariadb.conf.d# apt-get install libmariadbclient-dev
    Reading package lists... Done
    Building dependency tree       
    Reading state information... Done
    The following additional packages will be installed:
      zlib1g-dev
    The following NEW packages will be installed:
      libmariadbclient-dev zlib1g-dev
    0 upgraded, 2 newly installed, 0 to remove and 3 not upgraded.
    Need to get 1,785 kB of archives.
    After this operation, 10.1 MB of additional disk space will be used.
    Do you want to continue? [Y/n] y
    Get:1 http://pkgmaster.devuan.org/merged ascii/main arm64 zlib1g-dev arm64 1:1.2.8.dfsg-5 [200 kB]
    Get:2 http://pkgmaster.devuan.org/merged ascii-security/main arm64 libmariadbclient-dev arm64 10.1.37-0+deb9u1 [1,585 kB]
    Fetched 1,785 kB in 2s (740 kB/s)               
    Selecting previously unselected package zlib1g-dev:arm64.
    (Reading database ... 102669 files and directories currently installed.)
    Preparing to unpack .../zlib1g-dev_1%3a1.2.8.dfsg-5_arm64.deb ...
    Unpacking zlib1g-dev:arm64 (1:1.2.8.dfsg-5) ...
    Selecting previously unselected package libmariadbclient-dev.
    Preparing to unpack .../libmariadbclient-dev_10.1.37-0+deb9u1_arm64.deb ...
    Unpacking libmariadbclient-dev (10.1.37-0+deb9u1) ...
    Processing triggers for man-db (2.7.6.1-2) ...
    Setting up zlib1g-dev:arm64 (1:1.2.8.dfsg-5) ...
    Setting up libmariadbclient-dev (10.1.37-0+deb9u1) ...
    

    The second one, though, depends on having the Python Package Manager installed. As there are two Pythons, there are two Pips…

    (Notice a pattern here? EVERY time you fork a divergent THING you get a ^2 problem space… just saying… I have a Clue Stick and I’m not afraid to use it….)

    So OK:

    root@PiM3Devuan2:/etc/mysql/mariadb.conf.d# apt-get install python-pip
    Reading package lists... Done
    Building dependency tree       
    Reading state information... Done
    The following additional packages will be installed:
      libpython-all-dev python-all python-all-dev python-crypto python-keyring
      python-keyrings.alt python-pip-whl python-secretstorage python-wheel
    Suggested packages:
      python-crypto-dbg python-crypto-doc libkf5wallet-bin gir1.2-gnomekeyring-1.0
      python-fs python-gdata python-kde4 python-keyczar python-secretstorage-doc
    [..]
    

    and of course…

    root@PiM3Devuan2:/etc/mysql/mariadb.conf.d# apt-get install python3-pip
    Reading package lists... Done
    Building dependency tree       
    Reading state information... Done
    The following additional packages will be installed:
      libpython3-dev libpython3.5 libpython3.5-dev python3-cffi-backend
      python3-crypto python3-cryptography python3-dbus python3-dev python3-gi
      python3-idna python3-keyring python3-keyrings.alt python3-pyasn1
      python3-secretstorage python3-setuptools python3-wheel python3-xdg
      python3.5-dev
    Suggested packages:
    [...]
    

    YEAH Yeah yean I get it… another ^2 thing…

    then

    root@PiM3Devuan2:/etc/mysql/mariadb.conf.d# pip install mysql-connector
    Collecting mysql-connector
      Downloading https://files.pythonhosted.org/packages/59/e0/775bf5fb3dd4c7f9aa6877907d4a96eecca6886c603dedfea6e843e94560/mysql-connector-2.1.6.tar.gz (11.8MB)
        100% |████████████████████████████████| 11.8MB 21kB/s 
    Building wheels for collected packages: mysql-connector
      Running setup.py bdist_wheel for mysql-connector ... done
    [...}
    

    AND….

    root@PiM3Devuan2:/etc/mysql/mariadb.conf.d# pip3 install mysql-connector
    Collecting mysql-connector
      Using cached https://files.pythonhosted.org/packages/59/e0/775bf5fb3dd4c7f9aa6877907d4a96eecca6886c603dedfea6e843e94560/mysql-connector-2.1.6.tar.gz
    Building wheels for collected packages: mysql-connector
      Running setup.py bdist_wheel for mysql-connector ... done
      Stored in directory: /root/.cache/pip/wheels/8a/35/dd/9324d413c350bd1a9f92ee2894d78a4b4e19eb32477a69cd1c
    Successfully built mysql-connector
    Installing collected packages: mysql-connector
    Successfully installed mysql-connector-2.1.6
    [...]
    

    and finally I can test my programs again… and I need to change the login string due to that crap above where I changed to “chiefio” to bypass they root restrictions from systemD legacy and…

    # -*- coding: utf-8 -*-
    import datetime
    import pandas as pd
    import numpy as np
    import matplotlib.pylab as plt
    import math
    import mysql.connector as mariadb
    
    plt.title("Global Thermometer GHCN v3.3 BASELINE over NOW GIStemp")
    plt.xlabel("Longitude")
    plt.ylabel("Latitude")
    plt.xlim(-180,180)
    plt.ylim(-90,90)
    print ("Just before the Try")
    
    try: 
        print ("In the Try")
        mariadb_connection=mariadb.connect(user='chiefio',password='LetMeIn!',database='temps')
        print ("did the db assignment")
        cursor=mariadb_connection.cursor()
        print ("did the cursor thing")
        print ("stuff the SQL string")
        sql="SELECT I.latitude, I.coslong FROM invent3 AS I INNER JOIN temps3 as T on I.stationID=T.stationID WHERE year=2015 GROUP BY I.stationID;"
        print ("execute it")
        cursor.execute(sql)
        print ("back from the execute")
        stn=cursor.fetchall()
        data = np.array(list(stn))
        xs = data.transpose()[0]   # or xs = data.T[0] or  xs = data[:,0]
        ys = data.transpose()[1]
        print ("do the plot")
        plt.scatter(ys,xs,s=1,color='red',alpha=1)
        
        
        sql="SELECT I.latitude, I.coslong FROM invent3 AS I INNER JOIN temps3 as T on I.stationID=T.stationID WHERE year>1949 AND year<1981 AND I.stationID NOT IN (SELECT I.stationID FROM invent3 AS I INNER JOIN temps3 as T ON I.stationID=T.stationID WHERE  year=2015)GROUP BY I.stationID;"
     
        cursor.execute(sql)
        stn=cursor.fetchall()
        data = np.array(list(stn))
        xs = data.transpose()[0]   # or xs = data.T[0] or  xs = data[:,0]
        ys = data.transpose()[1]
    
        plt.scatter(ys,xs,s=1,color='blue',alpha=0.5)
    
    
    
        plt.show()
    
    except:
        print ("This is the exception branch")
    
    finally:
        print ("All Done")
    #    if db_connection:
    #    db_connection.close()
    

    Gives me my map / graph again… Oh Joy…

    OK, I got it working on the Pi despite the need to install things that come by default on the Odroid and despite SystemD crap trying to control the world and despite… well, all the crap…

    So, Moving On…

    I’ll be using this system (generic Devuan 2.0 on R. Pi M3) instead of the XU4 (Armbian with Devuan “uplift”) as the “going forward” platform for the GHCN on MariaDB stuff.

    Why?

    Despite it being somewhat slower and a more “picky” install with a few more hidden “gotcha!”s it is a more generic system and cheaper to make. (Pi at about $40 with PSU and straight Devuan 2.0 download / install OS).

    Then there’s the fact that it is “fast enough” at about 1 to 2 minutes per report / graph. That’s FINE.

    It also gets all the GHCN stuff out of a dependency on the Ordoid XU4 and an Armbian / Devuan conversion process – so a “cleaner” path to product. Less things folks can point at and ask questions about.

    With that, I’m going back to the process of fiddling with GHCN (various versions) and looking for interesting insights.

    “Someday”: I’ll do a “soup to nuts” straight install build script and post it. That will make this more “approachable” for folks wanting to DIY. What is “up” now is enough of a “Here There Be Dragons!!” for others with clue to replicate, and that’s enough for now.

    With that, I’ve got a bottle of sake behind me (along with a very nice Friday Sushi Dinner) and a bottle of Pinot Grigio in front of me calling my name… So while I’m “one bottle of ‘sheets to the wind'” I can still type OK… but I hope that 2 bottles will end this infirmity… At least for a while…

    I’d post the graph the Pi made, but it is exactly the same as the one the XU4 made so not a lot of point.

    With that, I’m off to refill my cup…

  9. p.g.sharrow says:

    @EMSmith; sounds like you have earned you libations this week. The Raspi-3+ running Devuan still seems to be the way to go. A bit slower but more solid and a much larger user base. Maybe the kids at the foundation will fix the IO during their next up grade of their cute little toy.
    We are working towards a 3D printer facility and I think the Raspi for the control system is the best way to go even if MS is a bit more friendly at the start,,,pg

  10. E.M.Smith says:

    @P.G.:

    While I really like the added speed of the Odroid XU4 and the RockPro64 – both have a bit of software roughness.

    The XU4 works great on Armbian most of the time but occasionally the “upgrade” version isn’t QA checked enough and isn’t quite right. (So now that I’m functional again I’ll likely just do a ‘roll back’ to an older release on it for just reading web pages and such).

    The ProckPro64 is still a very young port and several bits just don’t work. In particular, it didn’t have the ability to see / mount a USB Disk. Kind of a core facility… The Pi was like that for the year after it first came out, so I figure about 6 months more and that software ought to be mature enough for most stuff to “just work”. PROVIDED enough folk embrace the hardware…

    But what continues to happen is that when something goes BUMP in the night, the Pi M3 just works. It has a user base of many millions and that’s a lot of folks finding and reporting “bugs” and a lot of folks handing in proposed patches… so things rapidly get fixed in the devo releases and it’s pretty solid by the time it is a production release.

    I’m OK with it being a bit slower for everything but editing postings. The Worldpress editor is a fairly high page weight then FireFox spell checking is a constant loop expecting a multi-core multi-thread Intel chip… so once something gets a couple of pages long it starts to bog down the typing… So it’s a tiny bother, but not enough to be a pain.

    I’ve bought several “variety boards” and like them for various things, but what is clear is that they each come with some “issues” as well. For the Orange Pi there’s a magic phrase you must put in the boot config for it to work with a DVI adapter… OK, took about 6 months to a year after I had one and was forced to use it with the TV HDMI to find that bit out. It is also running straight SystemD Armbian.

    For the others it is that there is no direct Devuan port – I get put into the “Run a SystemD OS” or the “convert one via the Devuan ‘uplift’ process” – that tends to leave “artifacts” (like a dialog box at login saying dbus was unhappy with what it didn’t get). Then some just “up and die”. (In fairness, I had one R.Pi B+ board die [though it was in Florida and might have been a static issue during a storm] and one R.Pi M3 arrived with dead WiFi chip built in [which I decided was OK as I have uses that don’t need a WiFi chip] so there is some hardware failure rate. That’s out of 6 total R.Pi boards over several years.)

    In the end I just find myself wanting the R.Pi Foundation to come out with a 2 GHz 2 GB Pi M4 ;-)

    FWIW, the reason for the bold line in the example posted above was to highlight that I changed the user to be my user name not root (to dodge that setting of the unix_socket).

    OH:

    As an aside per Python install: When I installed Pandas, the dependencies included bringing in matplotlib and that .tk stuff so I didn’t need to do them as distinct installs. Somebody figured out to add them to the dependencies… Nice. So that was also a bit easier on that front.

    As a machine controller, the Pi would be just dandy. There are folks already doing it:

    https://www.raspberrypi.org/blog/octoprint/

    OctoPrint is a free open-source software, created and maintained by Gina Häußge, that performs a multitude of useful 3D printing–related tasks, including remote control of your printer, live video, and data collection.

    Control and monitoring

    To control the print process, use OctoPrint on a Raspberry Pi connected to your 3D printer. First, ensure a safe uninterrupted run by using the software to restrict who can access the printer. Then, before starting your print, use the web app to work on your STL file. The app also allows you to reposition the print head at any time, as well as pause or stop printing if needed.

    Live video streaming

    Since OctoPrint can stream video of your print as it happens, you can watch out for any faults that may require you to abort and restart. Proud of your print? Record the entire process from start to finish and upload the time-lapse video to your favourite social media platform.

    https://octoprint.org/

  11. Pingback: Hadley Baseline Thermometers Vs Now – GHCN v3.3 | Musings from the Chiefio

Anything to say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.