Since I’ve run into a wall where Python 2.x can connect to MySQL, while the Python 3.x that I need (to use the statistics packages) can not connect to the MySQL database with the MySQL Connector (as it isn’t there yet on Arm/Debian). Then found that MariaDB (essentially a not-corporate-connected version of MySQL by the same guy who wrote both) has become the default on the newer Debians. So I’m going to give it a go. This means re-installing all that stuff under different names…
This panel pops up in the process of the install, so it looks like there is a “migration” and after that my MySQL stuff may be dead:
─────────────────┤ Configuring mariadb-server-10.0 ├────────────────────┐ │ │ │ MariaDB is a drop-in replacement for MySQL. It will use your current │ │ configuration file (my.cnf) and current databases. │ │ │ │ Note that MariaDB has some enhanced features, which do not exist in │ │ MySQL and thus migration back to MySQL might not always work, at least │ │ not as automatically as migrating from MySQL to MariaDB. │ │ │ │ Really migrate to MariaDB? │ │
So here’s the installation of MariaDB:
root@odroidxu4:/# apt-get install mariadb-server Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: libjsoncpp0 libuuid-perl Use 'apt-get autoremove' to remove them. The following extra packages will be installed: mariadb-client-10.0 mariadb-client-core-10.0 mariadb-common mariadb-server-10.0 mariadb-server-core-10.0 Suggested packages: mailx mariadb-test tinyca Recommended packages: libhtml-template-perl The following packages will be REMOVED: mysql-client-5.5 mysql-server mysql-server-5.5 mysql-server-core-5.5 The following NEW packages will be installed: mariadb-client-10.0 mariadb-client-core-10.0 mariadb-common mariadb-server mariadb-server-10.0 mariadb-server-core-10.0 0 upgraded, 6 newly installed, 4 to remove and 5 not upgraded. Need to get 9,213 kB of archives. After this operation, 30.4 MB of additional disk space will be used. Do you want to continue? [Y/n] y Get:1 http://auto.mirror.devuan.org/merged/ jessie-security/main mariadb-common all 10.0.38-0+deb8u1 [17.8 kB] Get:2 http://auto.mirror.devuan.org/merged/ jessie-security/main mariadb-client-core-10.0 armhf 10.0.38-0+deb8u1 [729 kB] Get:3 http://auto.mirror.devuan.org/merged/ jessie-security/main mariadb-client-10.0 armhf 10.0.38-0+deb8u1 [1,034 kB] Get:4 http://auto.mirror.devuan.org/merged/ jessie-security/main mariadb-server-core-10.0 armhf 10.0.38-0+deb8u1 [3,880 kB] Get:5 http://auto.mirror.devuan.org/merged/ jessie-security/main mariadb-server-10.0 armhf 10.0.38-0+deb8u1 [3,534 kB] Get:6 http://auto.mirror.devuan.org/merged/ jessie-security/main mariadb-server all 10.0.38-0+deb8u1 [17.5 kB] Fetched 9,213 kB in 8s (1,024 kB/s) Preconfiguring packages ... (Reading database ... 101223 files and directories currently installed.) Removing mysql-server (5.5.62-0+deb8u1) ... Removing mysql-server-5.5 (5.5.62-0+deb8u1) ... [ ok ] Stopping MySQL database server: mysqld. Removing mysql-client-5.5 (5.5.62-0+deb8u1) ... Removing mysql-server-core-5.5 (5.5.62-0+deb8u1) ... Processing triggers for man-db (2.7.0.2-5) ... Selecting previously unselected package mariadb-common. (Reading database ... 101002 files and directories currently installed.) Preparing to unpack .../mariadb-common_10.0.38-0+deb8u1_all.deb ... Unpacking mariadb-common (10.0.38-0+deb8u1) ... Selecting previously unselected package mariadb-client-core-10.0. Preparing to unpack .../mariadb-client-core-10.0_10.0.38-0+deb8u1_armhf.deb ... Unpacking mariadb-client-core-10.0 (10.0.38-0+deb8u1) ... Selecting previously unselected package mariadb-client-10.0. Preparing to unpack .../mariadb-client-10.0_10.0.38-0+deb8u1_armhf.deb ... Unpacking mariadb-client-10.0 (10.0.38-0+deb8u1) ... Selecting previously unselected package mariadb-server-core-10.0. Preparing to unpack .../mariadb-server-core-10.0_10.0.38-0+deb8u1_armhf.deb ... Unpacking mariadb-server-core-10.0 (10.0.38-0+deb8u1) ... Processing triggers for man-db (2.7.0.2-5) ... Setting up mariadb-common (10.0.38-0+deb8u1) ... Selecting previously unselected package mariadb-server-10.0. (Reading database ... 101134 files and directories currently installed.) Preparing to unpack .../mariadb-server-10.0_10.0.38-0+deb8u1_armhf.deb ... Unpacking mariadb-server-10.0 (10.0.38-0+deb8u1) ... Selecting previously unselected package mariadb-server. Preparing to unpack .../mariadb-server_10.0.38-0+deb8u1_all.deb ... Unpacking mariadb-server (10.0.38-0+deb8u1) ... Processing triggers for man-db (2.7.0.2-5) ... Processing triggers for systemd (215-17+deb8u7) ... Setting up mariadb-client-core-10.0 (10.0.38-0+deb8u1) ... Setting up mariadb-client-10.0 (10.0.38-0+deb8u1) ... Setting up mariadb-server-core-10.0 (10.0.38-0+deb8u1) ... Setting up mariadb-server-10.0 (10.0.38-0+deb8u1) ... Installing new version of config file /etc/apparmor.d/usr.sbin.mysqld ... Installing new version of config file /etc/init.d/mysql ... Installing new version of config file /etc/logrotate.d/mysql-server ... Installing new version of config file /etc/mysql/conf.d/mysqld_safe_syslog.cnf ... Installing new version of config file /etc/mysql/debian-start ... 190219 12:42:41 [Note] /usr/sbin/mysqld (mysqld 10.0.38-MariaDB-0+deb8u1) starting as process 7509 ... [ ok ] Starting MariaDB database server: mysqld. Setting up mariadb-server (10.0.38-0+deb8u1) ... Processing triggers for systemd (215-17+deb8u7) ...
So I guess I’ll find out if I’ve broken things, or made them better ;-)
A Bit Later
I’ve launched an “idle” session and re-run one of my Python programs and it did make a graph, so at a minimum I’ve not broken things ;-)
Next I’ll get to figure out how to tell if I’m actually running MariaDB or not…
Rerunning the B&W global map (that has a version check in it)
try:
db=MySQLdb.connect("localhost","root","OpenUp!",'temps')
cursor=db.cursor()
cursor.execute('SELECT VERSION()')
data=cursor.fetchone()
print "Version %s" % data
gives:
>>> ================================ RESTART ================================ >>> Version 10.0.38-MariaDB-0+deb8u1
So it looks like that’s all it takes to move to MariaDB. It’s a “drop in replacement”, but with a hard time rolling back.
Next I’m going to try some of the “missing bits” in MySQL and see if they are working in MariaDB. That will take a while, so after lunch some time. That will be done as an update here.
Should be able to do this to get version
/mysql > mysql -V
mysql Ver 15.1 Distrib 10.2.21-MariaDB, for Linux (x86_64) using readline 5.1
Generally MariaDB is a few steps ahead of MySQL (in my experience anyway). The one area of issue I had was not being able to use MySQL Workbench successfully with MariaDB – but I haven’t tried for quite some time.
We made the move to maria db some time ago at work, (keep in mind I am not a dba), my recollection is it was a pretty painless move, we gained some significant speed up in some tasks, and the feature set is better in maria db than mysql.
There are a few minor differences which should be listed on the maria db site. I recall we ran into a couple minor situations where command strings had to be slightly tweaked to get the behavior we expected, but over all it was an improvement and we never looked back.
Last post was from me, my fingers got ahead of my brain while manually filling out the userid info for the post which wordpress is no longer preserving.
[Reply:Fixed it for ya ;-) -E.M.S.]
OK, I shifted over to idle3 and it is running Python 3.4.2 where there were a few differences.
I’m not really sure all of the things I changed are necessary but they were what got things working. (I’ve backed out several copied from a model that, on insertion, didn’t fix it. I think I’ve backed out all the no op bits). I’ve bolded where I changed things and got past some issues.
This code does, in fact, make the two color cos(lat) global map.
I deliberately left some things named as “MySQLdb” and didn’t change to mariadb so that code change would be minimal. For new codes I’ll change that..
In the first DB query you can see I added a bunch of diagnostic prints that pointed me to the fact that the database connection (“login”) call is a bit different and that was biting me. Using named arguments fixed it (instead of the position dependent in Python 2):
# -*- coding: utf-8 -*- import datetime import pandas as pd import numpy as np import matplotlib.pylab as plt import math import mysql.connector as MySQLdb 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: db=MySQLdb.connect(user='root',password='OpenUp!',database='temps') print ("did the db assignment") cursor=db.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: db.close()The other thing is those last two “print” statements. I had to add () around them.
Not too much to change, really.
Now that this is working and I know what to change, I can next see if the stats libraries I want can be imported. That I’m now able to run with a database on Python 3.x is a big problem gone.
One small step for a geek, one giant leap for all Nerd Kind ;-)
Well, this works now:
So guess I can try making a bigger jump into the statistics libraries and such…
Pingback: MariaDB (MySQL) Login Failure New Install Fix | Musings from the Chiefio
Hi Have you had the opportunity to quantify (in a debian env) the overhead of triggers yet? cheers. G man
No, I’ve not used triggers at all. Aside from being a relative Nooby to MariaDB, the idea of loading up a Raspberry Pi with even more processes and processing during any one step seemed counterproductive. Making the machine thrash more with memory competition and more disk seeks when it is already fully loaded ought to be slower not faster… but worth a test someday, maybe.
Besides, this system is simple enough that running it as a set of linked processing steps is reasonable anyway.