We’ve all got SQL that needs tidying up.
It wont fix your syntax but it will make it look nice.
A little while ago I wrote a php routine to import CSV files that contain a lot of data into a MySQL table. It works, but it takes it’s time doing so.
I was originally reading each line using a CSV parser and then writing each line into the table. I have 28+ files each containing 200MB of data in a million rows in each file.
Now for a change.Continue reading
Updates on my Stretch servers were failing due to an expired key. I tried the usual retrievals for keys using:
$ sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 8C718D3B5072E1F5
Even searching the keys.gnupg.net key server didn’t return the key.
As this failed I resorted to getting the key manually from the MySQL site and importing it into my apt keyring.Continue reading
I’ve used a few SQL GUI’s over the years, SQuirreL, DBVisualizer, HeidiSQL, MySQL Workbench, but the one that stands out recently is DBeaver.
It’s got a community and enterprise edition. The community does everything I need and connects to all the SQL servers we use, Microsoft SQL, MySQL, Postgres/PostGIS.
Being Java based it’s cross platform, so you can use it in Windows too.
As I’ve been messing around with Sphinx I discovered that MariaDB actually includes the SphinxSE plugin out of the box. So I figured I’d ditch my MySQL installation on Debian and go back to the default Debian repository version MariaDB.
$ sudo apt-get remove mysql-server mysql-client $ sudo apt-get install mariadb-server mariadb-client
This didn’t go to plan. Firstly it failed to run the apt configure scripts for
mariadb-server-10.1 so it would not complete the install.
Checking the logs
Feb 21 15:52:29 mypc kernel: [ 323.603758] audit: type=1400 audit(1519228349.780:26): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/etc/mysql/mariadb.conf.d/" pid=5175 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0
It goes back to a previous post I made about apparmor and MySQL. This time the problem was because the config file paths/names are different.
My local install of mysql-community-server decided to fail today after applying some updates. I’m running Debian buster/sid so these kind of things are to be expected. But this was a totally new one to me.
Mysql failed to start so the update wouldn’t install.
Unpacking mysql-community-server (5.7.21-1debian9) ... Setting up mysql-community-client (5.7.21-1debian9) ... Setting up mysql-client (5.7.21-1debian9) ... Processing triggers for systemd (236-3) ... Processing triggers for man-db (22.214.171.124-4) ... Setting up mysql-community-server (5.7.21-1debian9) ... Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details. invoke-rc.d: initscript mysql, action "start" failed. ● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: activating (auto-restart) (Result: exit-code) since Thu 2018-01-18 08:54:36 GMT; 9ms ago Process: 11123 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid ^[[0;1;31m(code=exited, status=1/FAILURE)^[[0m Process: 11088 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS) dpkg: error processing package mysql-community-server (--configure): installed mysql-community-server package post-installation script subprocess returned error exit status 1 Errors were encountered while processing: mysql-community-server Log ended: 2018-01-18 08:54:36
Then in my syslog file I saw lots of activity constantly trying to start and failing.
For quite a few years we’d been running Percona on our Nagios server with no issues. So no reason to change, until sometime over the past few days the repository’s public key expired and automated updates were failing.
I tried to update the key, searching key servers and eventually gave up. I resorted to removing the repo’s from my apt
sources.list. Then I just installed the MySQL apt repo and installed MySQL. As it installs it warns you about having a backup as data already exists and may be lost after the install. However, for me it simply removed Percona for me, installed MySQL and was up and running without any issue.
So it maintained all my users, schemas and tables and performed as expected.
Not that there’s anything wrong with Percona, just that I took the easy option of going with what the majority of our install base uses.
Today’s a MySQL day. After the shocker that is Microsoft Licensing I decided to seriously look at what MySQL has to offer the enterprise. I read their white paper and thought something sounds too good to be true. So I spent half an hour on the phone with someone in their enterprise team.
Apt-get update fails because the PGP keys for the repository have expired.
$ sudo apt-get update W: An error occurred during the signature verification. The repository is not updated and the previous index files will be used. GPG error: http://repo.mysql.com jessie InRelease: The following signatures were invalid: KEYEXPIRED 1487236823 KEYEXPIRED 1487236823 KEYEXPIRED 1487236823 W: Failed to fetch http://repo.mysql.com/apt/debian/dists/jessie/InRelease W: Some index files failed to download. They have been ignored, or old ones used instead.
Update the necessary key by adding it to your keystore
$ sudo apt-key adv --keyserver pgp.mit.edu --recv-keys A4A9406876FCBD3C456770C88C718D3B5072E1F5
With a Debian Jessie install right now you’ll be getting MySQL 5.5. If you want something newer you’ll need to use MySQL’s own repositories by adding them to your apt sources.
The install works pretty seamlessly. Just make sure you follow the guide. Step 1 a) download the repository setup: http://dev.mysql.com/downloads/repo/apt/
Once done it’s a simple case of dpkg install it and then update the repos and install/upgrade your MySQL server.