The Author – Bart Busschots
Creative Commons
Unless otherwise stated, the content on this site is licensed under Creative Commons Attribution, Noncommercial, No Derivative Works 3.0 License
Nov
25
Perl DBD::mysql on OS X Lion
Filed Under System Administration, Computers & Tech on November 25, 2011 | 12 Comments
I ran into a problem this week when the perl libraries for accessing MySQL databases refused to work on Mac OS X Lion. I did a ‘regular’ install:
- Install the 64bit version of MySQL Community Server (being sure to use the .dmg version so as to get the .pkg installer)
- use CPAN to install the needed database libraries:
$ perl -MCPAN -e shellcpan[1]> install Bundle::DBIcpan[1]> install DBD::mysql
There were no errors during the install, so I assumed all we well, until I tried to actually use the libraries to access a database that is! Using DBI to try connect to a MySQL database gave the following error:
install_driver(mysql) failed: Can't load '/Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle, 1): Library not loaded: libmysqlclient.18.dylib Referenced from: /Library/Perl/5.12/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle Reason: image not found at /System/Library/Perl/5.12/darwin-thread-multi-2level/DynaLoader.pm line 204.
After a lot of googling I found plenty of people with the same problems, including people on Snow Leopard, so I figured this was not a new problem. I tried a number of the suggested solutions, and most did not work, but after two days of trying, I found one that did, and it was wonderfully simple!
The problem is that the MySQL libraries are not in the OS’s library path, so they are not being found, most of the proposed solutions tried to tackle the problem at compile time, or to use simlinks to hack the libraries into the path, but like I say, these solutions didn’t work for me. What did work is simply updating the library path in my environment!
If you run the command below before executing your Perl script the library is found and all is well!
export DYLD_LIBRARY_PATH="/usr/local/mysql/lib:$DYLD_LIBRARY_PATH"
It’s a little awkward to do this all the time, so I added the line to my ~/.bash_profile file, and now it just works for me!
I spent two frustrating days trying to fix this, so hopefully I’ll save others some time by sharing my solution.
May
16
Foreign Key Problems with MySQL
Filed Under 42 (Life the Universe & Everything) on May 16, 2006 | 2 Comments
Having first read all the documentation on Foreign Keys and hence side-stepping the most common pit-falls by making sure all my tables were of type InnoDB, all the columns involved had indices and had exactly matching types I was most disappointed to get a bizarre and senseless error from MySQL:
Can't create table './epath/#sql-1a1_f9.frm' (errno: 150)
I’m using the latest stable release of MySQL (5.0.22) on OS X 10.4 Tiger.
The first thing I tried to solve this was a bit of googling which led me to loads of threads reminding people about the obvious stuff I was sure to avoid (table type, indices & matching data types). However, deep in the bowls of one of these discussions I did discover that, despite the documentation’s utter failure to mention this fact, foreign keys apparently only work when the columns involved are un-signed numbers.
The columns I was trying to link first were both of type varchar so I tried with a different pair of columns of type int (which I first set as being un-signed). This worked so I took the identical syntax and tried again with my varchar‘s, no joy, just the same uninformative error. It then occurred to me that the items in these columns will always be exactly two characters long so I changed their type to char(2) but again I got the same nonsensical error.
Some more googling seems to confirm my theory that MySQL will only be happy with foreign keys that are numeric un-signed types. This is a serious problem for the project I’m now working on. My data has a load of primary keys that are non-numeric that need to referenced as foreign keys all over the place. The two most obvious examples being 2 letter department codes and 3 letter alpha-numeric machine IDs. I could hack this by creating additional integer auto-increment fields in my departments and hosts tables and using these as primary keys instead of the logical fields but that is wasteful and pointlessly complex.
This is my first foray back in to serious MySQL development for a year having used PostgreSQL in the interim and I think I’ll be switching back to PostgreSQL before you can say ‘MySQL sucks’.
May
15
MySQL on the Mac
Filed Under System Administration, Computers & Tech on May 15, 2006 | 2 Comments
MySQL on the Mac has come a long way in the last few years. MySQL now release binaries for the Mac and those binaries are contained in a nice OS X package and even contain a preferences pane for the System Preferences App to allow you to easily control your MySQL server. I installed the latest version on my machine in work today and it is fair to call the installation process painless. You just click next a few times and enter your password once or twice. Because of this I have no problem saying that the server end of things with MySQL is now sorted on the Mac, but what about client GUIs? In this article I’ll be describing three free MysQL GUIs that I found and tested. All my tests were carried out with the latest stable binary release of the MySQL server for OS X (5.0.21) on a G5 PowerMac.
CocoaMySQL
The first GUI I came across was CocoaMySQL and I have to say I was far from impressed. Although the interface is nice in it’s simplicity the app simply does not play nice with the latest versions of MySQL server and hangs and does funny things so until the app starts to work correctly with modern servers the simplicity of it’s interface is irrelevant. After failing to create a table I un-installed this program.
MySQL Administrator
MySQL Administrator is released by the MySQL people so I considered this app to have great promise. The first thing I noticed is that it gives you an awful lot of
information about your MySQL server and also lets you control the server
and edit it’s config very well. However, when it came to creating tables I
was horribly disappointed.
I tried a simple test to create a table with two columns, an auto
incrementing integer called ‘id’ that is the primary key and then a
column called ‘name’ that is a varchar. The interface let me
prepare it all beautifully but the moment I clicked ‘Apply’ I got an
SQL error. I tried repeatedly but no matter what I did with the GUI I
could not get this simple table created. The GUI shows you the SQL it’s
about to execute and lets you edit it so I did eventually get the table created by manually editing the SQL being sent. I did experiment on a bit
and found that if you don’t use auto increments the interface does work
but since I use them a lot I just got fed up with this application. My
disappointment was magnified even more when I realised that there is no
way to enter data with this app either.
I had very high hopes
that this app would turn out to be my on-stop-shop for everything I
want to do with MySQL but it simply isn’t. The fact that it’s obviously
still buggy as hell and that you can’t use it to insert data means that
I really have no use for this app.
YourSQL
YourSQL is another nice simple front end for MySQL. I tested the latest stable version (1.7.2). This is very much a program for people who are familiar with MySQL and know the various datatypes etc.. This program will only make sense to people who know what they are doing but for those people it will save a lot of time. It uses a Finder style column-based interface for navigating servers, databases and tables which works really well if you have a lot of databases to manage. This is a good app for speeding up simple DBA tasks if you know what you are doing. The app is small and fast and hence I actually really like it.
I spent a few hours using this app today to build a fairly complex database and I have to say I found it a joy to use. It was easy to add a database, to add and edit tables and to view, search and insert data. To me that is what a MySQL GUI should do so this app gets 9 out of 10. Why not 10 out of 10? Well it didn’t do one thing, it didn’t give me an easy way to set up foreign keys. However, the interface makes it very easy to run SQL queries directly so if you want to add a foreign key to a table you can do so yourself quite easily in this way.
Below is a screen shot I took while I was experimenting today.
Conclusions
Although the actual MySQL server is now very easy to set up and control on the Mac there is still a shortage of good GUIs out there. ATM there is only one that I would recomend over the classic phpMyAdmin route and that is YourSQL.
Aug
12
Switching from MySQL to PostgreSQL
Filed Under 42 (Life the Universe & Everything) on August 12, 2005 | 4 Comments
I was recently forced to switch from MySQL to PostgreSQL for a project I am working on and at first I was very reluctant to change because I had been using MySQL for years, was very familiar with it, and knew my way round the developers section of the MySQL website. However, having worked with PSQL I would now never go back to MySQL, it is simply in a lower league than PSQL!
When using a DB the differences are not all that big but when creating and admining DBs there are some differences that will throw you at first so I’ll list the ones I came across and how to get by them.
User Management
MySQL has a bizarre model of user management, the user bart from say localhost is considered a completely separate user to the user bart from any host or the user bart from a particular host. This makes managing users interesting and TBH I have always hated this aspect of MySQL. PSQL takes a simpler view, it uses the system accounts as its accounts. The user bart is the user bart no matter where he is logging in from and his password is his system password. Also, there is no root account as such on PSQL, if you access PSQL as the user who owns PSQL then you have ‘root’ powers, if you don’t you only have the powers that have been given to you by the DBA with GRANT statements.
Control of access to different databases by different users from different places is all controlled from a text file called pg_hba.conf in which you specify how PSQL should authenticate different users trying to access different DBs from different places. You have a number of choices ranging from ‘trust’ which lets any system user access the DB as any user without a password to ‘password’ which requires the user to supply a password and ‘ident’ which only allows the user to log into the DB as the user they are logged into the system as. pg_hba.conf is well commented with lots of examples so it is really easy to manage client authentication on PSQL and makes MySQL look over complicated and under-powered.
AUTO_INCREMENT
There is no AUTO_INCREMENT keyword in PSQL. This scared the crap out of me at first because I thought I had a real problem, but of course I didn’t, PSQL has a special data type for fields that need to auto increment called SERIAL. This creates an implicit sequence which adds a slight complication in that you need to grant users access to BOTH the table AND the sequence if you want them to be able to add rows to the table. The sequence always has a name with the format: <table>_<field>_seq.
ENUM
Again PSQL does not have an ENUM type which, at first, seems like a problem but you can create fields that are effectively enumerations using the CHECK keyword (and you can do MUCH MUCH more with it too!). To set up an enumeration called user_level with the possible values ‘STUDENT’, ‘MENTOR’ and ‘ADMIN’ you would use the following syntax:
user_level VARCHAR(10) DEFAULT ‘STUDENT’ NOT NULL CHECK (user_level IN (‘STUDENT’, ‘MENTOR’, ‘ADMIN’))
Transactions
PSQL has excellent transaction support and it is stable and works with things like JDBC which is more than can be said for MySQL ATM. And, to make it even better, the syntax is immensely simple!
BEGIN;
-- do work
COMMIT;
Shell
The psql shell to access PSQL is very very different to the mysql shell you access MySQL through. Basically you are just gonna have to RTFM it! Also, when you get stuck (and you will) just remember that \h gives you help and \q exits! The thing I missed most were MySQLs simple keywords SHOW and DESCRIBE, PSQL has the same functionality but just with much less memorable commands, e.g. \dt lists all the tables in the current DB.
Overall I find that PostgreSQL is a more powerful and more mature and robust piece of software than MySQL and since it is also open source I can see no reason to choose MySQL over PostgreSQL. Yes, if you have been using MySQL for years you will have a bit of a learning curve but trust me, it is well worth the bit of time and effort to do that learning!