Displaying articles with tag mysql
Posted by hank,
Sat Apr 12 00:44:00 UTC 2008
I couldn’t get MySQL to start in Ubuntu 8.04 Hardy Heron after I changed my datadir to /nexus/tardis/mysql - it turns out I needed to change the AppArmor configuration like so:
/etc/apparmor.d/usr.sbin.mysqld
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
/nexus/tardis/mysql/** rwk,
/nexus/tardis/mysql/ r,
This was in response to the following syslog messages:
/var/log/syslog
Apr 12 00:30:59 tardis mysqld[17818]: 080412 0:30:59 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
Apr 12 00:30:59 tardis mysqld[17818]: 080412 0:30:59 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
Apr 12 00:30:59 tardis kernel: [25185.601980] audit(1207974659.063:36): operation="inode_permission" request_mask="r::" denied_mask="r::" name="/nexus/tardis/mysql/mysql/host.frm" pid=17817 profile="/usr/sbin/mysqld" namespace="default"
Apr 12 00:30:59 tardis kernel: [25185.602018] audit(1207974659.063:37): operation="inode_permission" request_mask="r::" denied_mask="r::" name="/nexus/tardis/mysql/mysql/host.frm" pid=17817 profile="/usr/sbin/mysqld" namespace="default"
Apr 12 00:30:59 tardis mysqld_safe[17823]: ended
Apparently I didn’t see this important message in my.cnf when I was modifying it:
# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
Then, I went and checked /etc/mysql/debian.cnf for the debian-sys-maint password and set it like so:
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<that password>' WITH GRANT OPTION;
Tags: mysql
Posted by hank,
Fri May 11 04:22:00 UTC 2007
I was messing around writing some sweet SQL statements for amarok tonight. You can either run them using the MySQL console or using dcop (google ‘amarok dcop’). Here’s some examples:
# List artists and their average rating and number of ratings ordered by favorite artists first
SELECT a.name, avg(s.rating) avg, COUNT(s.rating) count FROM tags t, artist a, statistics s WHERE a.id=t.artist AND t.url=s.url GROUP BY a.name HAVING count > 10 ORDER BY avg DESC;
Tags: mysql
Posted by hank,
Mon Mar 12 05:56:00 UTC 2007
When you move MySQL databases as described here, make sure you don’t move the ib_ files, but also make sure to move the ib files, like ibdata1. Also, move the mysql subdirectory into the new location. Just did this - worked great. Now I have RAID5’d databases.
I was doing this to convert an SQLite version of my Amarok database to a MySQL implementation.
cd ~/.kde/share/apps/amarok && sqlite3 collection.db .dump | grep -v "COMMIT;" | grep -v "BEGIN TRANSACTION;" | perl -pe 's/INSERT INTO \"(.*)\" VALUES/INSERT INTO \1 VALUES/' > amarok
I also had to do this because of a maximum key length problem, which I found the solution to here:
vim amarok
:%s/VARCHAR(1024)/VARBINARY(255)/g
And I finished it off with this:
mysql -p -u root amarok < amarok
Now, I’ll never lose the database even if I reinstall Linux.
Tags: mysql
Posted by hank,
Sat Feb 24 08:07:00 UTC 2007
So, a problem with MySQL (in my opinion) is that it is not case sensitive by default for VARCHAR fields. That makes getting rid of crappy entries like ’ITALY’ a bother. I mean, sure, I could just post-process it with ruby (see titleize), but what’s the fun in that.
select distinct BINARY(lead_country) from countries;
Ah, finally recognizes that ITALY is not Italy. One is definitely uglier than the other one. Now for the change.
update countries set lead_country = 'Italy' where lead_country = "ITALY";
Much better.
Tags: mysql
Posted by hardwarehank,
Tue Sep 26 13:09:45 UTC 2006
Interesting problem brought up by Itosu. Basically, he had two tables,
called rfs and dfs, and both of them had a column called uname.
All he wanted to do was select all of rows from both tables and order them by
uname. After some research, this ended up being trivial:
SELECT * FROM rfs UNION ALL SELECT * FROM dfs ORDER BY uname ASC;
The ORDER BY takes effect on the entire union if it comes at the end. This is
very handy, and I have a feeling I will be referring back to it someday.
Tags: mysql
Blog Posts