Monday, January 18, 2010

MySQL - GUI's - Workbench

MySQL Gui's   Common MySQL commands   MySQL Workbench issues   MySQL field types

Website maintenance can be considered in two main halves, the administration of files portion and administration of databases (if any) portion. For file administration, adding and deleting files, I use a secure ftp program and an ssh tunnel to my provider. But what about database administration? If I am given MySQL databases, and if that provider has phpMyAdmin installed, then I use a mix of command line and phpMyAdmin database commands. As a side note, I like PostgreSQL more than MySQL, but most webspace providers only install MySQL.

command line
Making a command-line connection to the database is a good place to start, even if we will use a GUI later. Through the command line, we can easily verify connection is possible, and we can also run MySQL installation scripts, etc.

Let's imagine we had a database at Google that was web-facing w/remote access permissions. This would never happen, but this is the way ISP's often provide database access to you and me. We could connect to our imaginary Google database in the following way:
$ mysql -h -u pietro -D mystuff -pwiggetystop

(note: no space between the "-p" command and the password itself "wiggetystop") So, if I've done this correctly, I'll be logged into my Google database and have a cheery "mysql >" prompt waiting for mysql commands.

By default, many distros that have package management programs install phpMyAdmin into the Apache area. It's understood that Apache is used to parse the php files in phpMyAdmin, but Apache is another annoying level of overhead which also brings on security concerns if used outside of localhost. I only use phpMyAdmin when it's provided on an ISP's server. What is the solution for accessing a database remotely from one's local machine using a GUI? We don't want to pay for Navicat, now do we?

mysql workbench
It turns-out MySQL makes a GUI developer suite called "Workbench", which includes administration, navigation, and design tools. After checking dependencies, I downloaded, compiled, and installed the program, actions which appeared to have proceeded successfully. The start-up command is $ mysql-workbench, and it might have a few options for that command.

The reason this screenshot has no administration is because Workbench couldn't load the administration and nav modules, which left only the design module (pictured). There is a documented bug when compiling in Linux, even with all the Python libraries I could think of installed. The Python program pexpect, was considered helpful in the bug report, so I installed that as well. No improvement. Currently, I only have the design interface, as shown in the image above.

After digging around, filing a bug report (incidentally, they don't even want to answer within the bug forum where they will be useful to all, they direct one instead to their IRC channel where only the current people in the room will see the solution - ridiculous), all of the usual stuff, I dug in deeper with strace. It appears that it's the usual problem: Java, the most ridiculous run-time library since Visual Basic. This garbage occurs from each object MySQL Workbench attempts to retrieve:

**Message: WARNING: MetaClass db.maxdb.Catalog is registered but was not loaded from a XML

This seems to be the Java getDocument call. And there is no way to fix this I know of except to once again spend hours checking every exported path and poorly designed JRE directory search requirement. I've seen this kind of thing in other Java based programs. It's not Java itself that sucks so badly, it's their (apparently) lazily designed afterthought of a Run-Time Enivironment. And Java's inability to tolerate errors slewing off its own runtime environment, certainly doesn't help. This particular thwart experience is probably related to their "MetaClass" requirments.

I don't want an application with Wine built-in. It conflicts with the Wine I already have installed and is an incredibly boggy duplication of effort.

No comments: