Tag Archives: mysql

Simple Diagrams of MySQL Schema

Posted on by

Jess Robinson’s SQL-Translator CPAN module translates and parses SQL statements. The SQLfairy project has some nice binaries that, among other things, use GraphViz or GD to draw pseudo-ER diagrams from SQL CREATE statements. Drawing a diagram of an SQL schema is as easy as sqlt-diagram --db=MySQL -o schema.png -i png -t "title" --color --gutter 100 -c 2 schema-erd.sql. There are a few minor issues – the program seems to choke on the LOCK TABLES statements in mysqldump output. But overall, the results are quite nice. The script can take (as easy as putting it in a Makefile) mysqldump output and generate a diagram like the one below, including foreign key constraints. I also found a simple intro and example in a post on Neil Saunders‘ blog.

sqlfairy output

MySQL General Query Log and Awk Trimming Columns

Posted on by

So today I started to implement a number of complex regex-based rules and templates to get rsyslog to parse ISC DHCPd logs in realtime. Unfortunately one of my templates must have been wrong, because I started seeing some errors about a field that cannot be blank in /var/log/messages. Unfortunately, rsyslog doesn’t log the query that raised the error, or the name of the template, or anything else useful – just that there was a database error. With over a dozen new templates, this didn’t help much. But the following technique did:

MySQLd has a General Query Log that can be activated by addling a line like:
log=/tmp/query.log
to /etc/my.cnf under the [mysqld] section. This will log *all* queries to the specified log file, even if they resulted in an error or did not manipulate data.

I couldn’t find documentation on the log file format, but I observed that each line appeared to begin with some whitespace, then a number (perhaps a connection or section number, or maybe some sort of query ordering number), then the word “Query”, then the query. The following awk expression prints everything from the third column on, dropping the first two columns (the number and “Query”):
awk '{ for (i=2; i<=NF; i++) printf "%s ", $i; printf "\n"; }'
For easy analysis, the output of that can be piped into sort and then uniq.

The Jargon File

Posted on by

I know it’s been quite a while since I’ve been around. Hopefully I’ll post more, as the semester is pretty much over and it’s time for my winter projects.

I was pretty bored working on assignments for my Database Technologies class the other day. I had also recently purchased a copy of The New Hacker’s Dictionary (the printed edition of The Jargon File) from Amazon and was reading through it. For those of you who aren’t familiar with The Jargon File, it’s the standard glossary of the wonderful terms thrown around by us geeks and hackers, as well as a source for definitions of the many words that have fallen out of favor… well… when people stopped logging in to mainframes to write their thesi. It’s currently at version 4.4.7 and is painstakingly maintained by ESR.

Anyway, I happened to be randomly flipping around the book, and landed on the entry for zeroth on page 501, which made reference to fencepost errors on page 187. What a pain to find! So, I stopped by the listing of alternate views of the Jargon File… but found all of the ones marked as searchable to be gone. So…

Over the course of a few days (I guess it’s an example of how time can be made – I did this during the final week of the semester, exams and all, and finished all of my classes as well as this project) I downloaded the DocBook XML, wrote a few scripts to parse it out and put it in a MySQL database (complete with cross-references, indexes, and (hopefully soon) full-text searching).Then, I added a simple web interface allowing various types of searches and listings.

Though the project was done more to occupy myself and get a little more experience with PHP parsing XML and doing full-text searches, hopefully I’ll have the time to finish it up – there are still a few minor bugs (the parsing lost some of the formatting of ASCII art… I think there’s a trim() that got stuck in there somewhere) and I’d like to implement full-text searching of definitions, overall it was a fun project, given that I did it in about 4 days while working and finishing up school.

If you’re looking for a searchable, cross-referenced version of the Jargon File online (complete with revision history and comments), take a look at The Jargon File on JasonAntman.com. There’s a search function, listing by first letter, one-page listing of all entries, and hopefully a few other goodies soon. Most importantly, though the documentation is sparse right now, the scripts used to parse the XML, cleanup the database and display/search everything are available for anyone who wants them.

Update, Eventum/MySQLTicketing Integration

Posted on by

Well I know I haven’t updated in a while. I have a whole bunch of links that I’d like to comment on, but things have been horribly busy. You can find the links in my “1-toblog” folder on del.icio.us (prefixed with “1-” so it shows up at the beginning of my bookmark menu).

In monitoring land, I’ve paused my Hyperic HQ VM as I wasn’t too pleased with how the features panned out. I was invited to beta test Groundwork Open Source 5.2b, but I’m not crazy about the open-ness of a non-public Beta, and am honestly not that intrigued by the small feature set (though, admittedly, they do need more documentation on the F/OSS version). I’d still like to try them all, especially Zenoss Core, but I’m pretty busy with class, and things are heating up at work and with a few consulting projects.

In my “spare time” (read: staying up until 5 AM and somehow still getting up for work at 9) I’ve been working on something that’s been bugging me for a while – getting Nagios to automatically open and update tickets in Eventum, the ticketing system that I (and MySQL) use. The general idea is to use a “glue” script, written in PHP (Eventum’s native language). It will (hopefully) keep track of which hosts/services it has opened tickets for (and what the ticket ID is), and decide from that whether to open a new ticket or, if one already exists for that host/service, update it. It should also handle changes to assigned user/group, update categories, priorities, etc. This will all be based on a DB table that maps problem severities and hosts/services to the users, groups, categories, and priorities that they should be assigned.

The biggest problem is that I’m not a whiz at object-oriented PHP, and like any good OO program, Eventum is broken down into dozens of objects, classes, and files. With the help of the Xdebug debugging extension for PHP, which prints full debugging output including stack and function call traces, I’ve been able to *finally* – after about four hours of work – write a simple little 15-line script that uses ONLY existing Eventum classes, unmodified (except for a separate init.php with some stuff commented out), which gets a list of users assigned to an issue. From here, it shouldn’t be difficult to get full issue information and then, hopefully, add and update issues.

I have a basic description of the project on my wiki, and the current (development, so could be broken) source code in CVS, which can be seen through ViewVC on my site.

Stay Tuned!

Sun and MySQL

Posted on by

I’ve got a lot to update about in the next few days. I’m back in my apartment for the new semester of school, I’ve got a lot of infrastructure work to do here (most importantly getting net-to-net VPN with DynDNS at both ends working from here to home, even if it means bypassing IPcop totally), and I also did some freelance administration over winter break, including some work with SCO OpenServer and some work with DF generation – I also set up my own PDF form filling system, which I’ll be posting in CVS once it’s done. Most importantly, the new mailserver has been up and running for 22 days without any problems. And, by far the most exciting, I’m typing this up on my beautiful new Asus eeePC 4G (which I upgraded to 1 GB RAM) running OpenSuSE 10.3 from a 4 GB SDHC card – A HOWTO will be coming soon. I’m also going to be doing some work with sitemaps on this site – specifically Google sitemaps – and building one map for the blog, Mediawiki, and static content. I’m also testing Google Analytics on my wiki to augment Webalizer. I’ll be updating the blog in the next few days with a lot of content.

I’m also catching up on all of my RSS feeds in Google Reader (yes, I use Google everything – but not mail or Docs) which I mostly ignored over break. I started yesterday with 780 items, and I’m down to 500. I’m also going to attempt to start quick little “links of the day” entries, as it seems that it’s taking me too much time to fully blog on the interesting news of the day, and my list of things to blog on is getting way too long. (Just as a side note, the only reason I really use del.icio.us is so I can have the firefox plugin sync my bookmarks between the many machines I use.)

Anyway, on to the hottest bit of news today – Sun Microsystems has acqured MySQL AB. Now, I must say that I’m an intern at Sun – the Campus Ambassador to Rutgers University. Essentially, my job is talking to students, professors, and staff and telling them that “Yes, Sun does open source, and they have a few things that should interest you” – in addition to giving tech demos, handing out CDs, and raffling off t-shirts – and even a SunSPOT. I must emphasize that what I say here is my own opinion and nothing more. Heck, I’m an intern, it’s not like I know any more about what’s going on at Sun than anyone else who reads Digg.

Anyway, lots of people are talking about the MySQL acquisition. I’ve heard every possible opinion, but it seems like lots of them are worrying about something along the lines of Sun pushing a Solaris/Apache/MySQL/Java stack on SPARC hardware. Now, I’ve been watching Sun *very* closely for the past year or so. And I’ve been following Jonathan Schwartz pretty closely too, especially his blog post about the MySQL acquisition.

I can’t say anything as a Sun employee (I don’t have anything more to say than anyone else who follows the news) but, personally, I think a lot of people who are worried are just missing the point. Sun used to be a closed, proprietary company pushing Solaris on SPARC. But it seems to me, lately, Sun is all about options. Java’s Free software now. Solaris has been opened as well. I don’t get the feeling that acquiring MySQL is about pushing Solaris instead of Linux, or that it will cause Sun to ignore PostgreSQL or Oracle/Solaris products. It’s about options. Just look at Sun in the news lately – more and more Intel-based commodity hardware, Solaris, Linux, or even Windows on Sun Hardware, Java, OpenSolaris…

I very much get the feeling that Sun/MySQL is not about Sun trying to force LAMP out of the marketplace or, as some have conjectured, to turn MySQL into a mostly proprietary product with minimal free version. It seems to be about options. About diversity. Whatever you want, Sun can help. Everything from million-dollar Enterprise servers running Trusted Solaris and giant Oracle databases to Intel-based 1U or 2U LAMP servers – or even, now, LAMP servers on non-Sun hardware. (Though why anyone with the money to do so wouldn’t buy Sun hardware, I have no idea).

Stay tuned for more.