Displaying articles with tag mysql
Posted by hank,
Thu Jul 24 04:08:00 UTC 2008
I went to 5 sessions today, and I was pleasantly surprised by most of them.
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.
An open-source implementation of Google’s bigtable. Hypertable uses novel methods such as Bloom filters to significantly decrease query times, as well as smart messaging to distribute a database across many nodes. It is also non-relational.
A group of CS professors hailing from Africa have gotten together to create a community that fosters creativity and innovation from people in Africa. People in first-world countries can participate by acting as mentors, or directly contribute to the projects involved. Chisimba is an open-source MVC framework for rapid application development. I am very interested in contributing to this project.
I thought going in that this would be somehow in the same ballpark as Hypertable and CouchDB, but I was disappointed. Basically, they are using compression and some fairly neat indexing to speed up traditional database queries. The main problem is that they only have a Java API, which completely turned me off after 30 minutes. Before that, it seemed like they were getting some pretty promising results. If they add some more APIs in the future, this may be another one to take a look at.
An awesome talk by Paul Fenwick from Australia, generally detailing failures in computer science and engineering going back into the 20th century and even back to Roman times. This was a wonderful presentation - he’s a really good speaker - and it poked a lot of fun at New Zealand.
All in all, I must say that this OSCON is much better than last year’s at least according to what I was looking for in the sessions. The exhibit hall is also very good this year - I’m pretty loaded down with swag at the moment.
I know someone who would have gotten a kick out of Temporally Quaquaversal Virtual Nanomachine Programming In Multiple Topologically Connected Quantum-Relativistic Parallel Timespaces…Made Easy! had they been here. He needs to come next year (you know who you are..)
Tonight, I also attended FOSCON 4: Cooking with Ruby. This was a spectacular event hosted by Cubespace. I have to say that the live coding competition was a great spectacle, and held everyone’s attention for hours. It was an epic battle between Symfony, Rails, Smalltalk/Seaside, and Drupal. The rankings ended up being the following:
- Rails
- Drupal
- Symfony
- Smalltalk/Seaside
The presentations were good as well for the most part (notes here). AND THEY HAD BEER! I had some of the best keg beer imaginable - I thought it would be crap like you usually get out of a keg, but this was real quality Northwestern hopped pale ale. My cup says Bridgeport Ales, so I’ll have to investigate. If anyone knows the exact beer that was available in the left-side keg tonight, I’d appreciate a comment. I also met some cool people, some of which are all into XMPP and ejabberd. I may have to check all of that out now…
Tags: 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
