Tag Archives: mysql

OSCON Sessions, Day 1

July 24, 2008

I went to 5 sessions today, and I was pleasantly surprised by most of them. CouchDB CouchDB is a distributed non-relational database written in Erlang. It is unique in that its main query interface is simply HTTP REST, and for every UPDATE, it simply creates a new version of the row. Additionally, you can request the entire history of a row very simply. Hypertable An open-source implementation of Google’s bigtable. Hypertable uses novel methods such as Bloom filters to significantly [...]

MySQL issues in Hardy Heron

April 12, 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 [...]

Sweet SQL queries for Amarok

May 11, 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;

Moving MySQL Databases

March 12, 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;” | [...]

MySQL Capitalization Issue

February 24, 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’ [...]

SELECT and ORDER rows from multiple tables

September 26, 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 [...]