Category Archives: mysql

Mysql 5.7 on Fedora 25

Had a fair amount of trouble getting mysqld to run properly on Fedora 25.

One issue was that mysql 5.7 introduces expiration of passwords, and ALL my passwords expired, including root. I couldn’t do anything, including change my password. Catch 22.

Also, the tables were out of date, and I needed a mysql_upgrade. But I couldn’t do that, because my password was expired.

Eventually figured out that I could change the password with mysqladmin password “goop”

After this, I was able to get in as root, and eventually get all the passwords marked as legitimate.

I was also able to update all the tables with mysql_upgrade.

In researching and troubleshooting this, I discovered a bunch of errors on cinnamon, with innodb generating errors of the form:

InnoDB: Ignoring tablespace `xbmc_video90/directorlinktvshow` because it could not be opened.

I’m pretty sure what happened here was that I removed the files for old xmbc databases from /var/lib/mysql, but didn’t actually drop the databases first. This is a bad habit left over from pre-Innodb when you could get rid of a mysql database just by removing the files.

I made a lot of attempts to fix this with setting in mysqld.conf:

innodb_force_recovery = 1 (or 2 or 3)

but it never worked. In the end I managed to get rid of these errors by :

mysqladmin create xbmc_videoxx (or xbmc_musicxx)

and then

mysqladmin drop xbmc_videoxx

mysqladmin generates a whole bunch of errors and warnings about not being able to find the files, but it does seem to remove the dreck from the innodb files.

 

 

Making MySQL serve UTF8 correctly

If the MySQL server decides that its default environment is UTF8, and that its client actually wants Latin1, it will translate the return values.

I’ve never before had to be careful of the distinction. Perhaps once in a blue moon I would have a record with a “real” quotation mark or a character with an accent, but if it didn’t work correctly, it was never much of a bother.

Once it became important, I had to understand what was happening. I have a table that has filenames in it, and some of those filenames contain characters (a acute, e grave, o umlaut, etc). The actual files on disk have the names encoded in utf8. The records in the database are also recorded in utf8. But the records were being translated by mysql from utf8 to latin1 as they came in. So “Mamá” was recorded on disk as Mam\xc3\xa1 in the directory, and in the database, but when I got the row into memory, the filename field said Mam\xe1. The difference between latin1 and utf8 for this purpose, is that all these many “western/latin special characters” were actually mapped in latin1 to values within the 256 characters available with 1 byte. So the first 128 in the latin1 codespace were ordinary ascii, and the high order 128 had as many of the western/latin diacriticals as possible crammed in there. And in latin1, e9 is a-acute.

But on the web these days, utf8 is much preferred. Latin1 is ok if all you want is the carefully selected subset of 128 characters that can be shoehorned into the high end of the code-point space. But utf8 is a far more general solution. Using a multibyte sequence to represent over a million characters and special symbols.

Turns out mysql has a bunch of variables to control character set and collating sequence. With phpmyadmin, one can look at database->Variables and see characters_set_database, character_set_filesystem, character_set_result, character_set_server, character_set_system, and a bunch more. Or in mysql client one can show varaiables like ‘%character_set%’;

My problem was that the server had come up believing that some of these were set to utf8 and some were set to latin1. I haven’t tried to figure out the logic of how it figures out its default – I don’t want it to default, I want to tell it what I want. So the solution was to add the directive: “character-set-server=utf8” to the mysql configuration file (on cinnamon it was /etc/mysql/mysql.conf.d/mysqld.conf. After restarting all of the relevant character_set_xxx variables come up as utf8.

Update: 8/9/17

I used these changes also on oregano and tarragon, but it results in a different problem for me on blogforacure data. The blogforacure database, built a long time ago, has lots of tables in latin1. There are not a lot of non-ascii characters, but there are a few. One frequent source is people typing double space after a period, which the ckeditor tries to preserve by creating a non-breaking space, which is hex A0 in latin 1. When the site reads this back, if mysql is told that the database is actually utf8, then it displays this as Â. So if I see a bunch of A circumflex in the output, it means I actually have latin 1 characters in the database, which I am interpreting as if they were utf-8.

Removing the specification of character-set-server=utf8 causes the negotiation to give the right result, and the latin1 non-breaking space appears correctly in the output.

MYSQL on Ubuntu 15.10

I haven’t researched whether this has changed, in 15.10, or whether it has been this way since ubuntu switched to systemd, which is probably the case.

Under systemd, ubuntu no longer uses the /etc/init.d/mysql script, but instead uses a systemd unit in /lib/systemd/system/mysql.service which invokes /usr/bin/mysqld_safe to start and stop mysqld.

I have had a lot of trouble with this, and had to do a lot of debugging to figure out what is going on. Probably I would not have had trouble if I were not trying to port over a running mysql installation manually, i.e. if I just installed mysql-server and proceeded to create new databases, new entries in mysql etc.

One issue is that a mysql install creates a file in /etc/mysql called debian.cnf which contains a user/password for user debian-sys-maint with a generated password, and this is put into the mysql users table, to enable various operations to be performed by mysqladmin using these credentials.

The first problem was that when I copied over the mysql table from the previous installation, I was copying in the old password for debian-sys-main, which didn’t match the debian.cnf file which was installed when I did the apt-get install mysql-server. So I had to read the debian.cnf file, extract the password and change the password in the mysql table.
Continue reading MYSQL on Ubuntu 15.10

Using pam_mysql for authentication

On the occasion of moving the server to amazon, I decided to stop using LDAP. I was making very little use of it, having started to keep my contacts elsewhere. All it was really doing was providing the authentication file for apache logins. And there were so few of those it was quite easy to manage with an htpasswd file.

But recently I’ve begun providing mail to some friends, and while I am happy to provide them with system accounts the problem is that they don’t really WANT system accounts – they just want mail. So the husband asked me, how do I change my password? And the only answer I have is, you have to log on. Worse than that of course, I can’t really even allow him to log on without making him set up for public key authentication.

I would like to enable people to use the server for mail, and to be able to authenticate with apache, and I would like them to manage their own passwords for this, without making them log on via ssh, which would require that they have a key pair registered with me. I looked into being able to change the system password via a webpage, but it looks very messy, and seriously – system passwords are supposed to require a human being – that is intentional.

So I looked into authenticating the mail, and apache, using a database. I found a pam module called pam_mysql which I can use with saslauthd. I set /etc/pam.d/imap and smtp to point to a new pam entry called mail, which uses pam.mysql to autheticate against the database (and still also authenticate against system accounts as well if there is no entry in the database).
Continue reading Using pam_mysql for authentication