laziness, impatience, and hubris | |
PerlMonks |
Checking installed MySQL databases with DBIby fpi (Monk) |
on Mar 22, 2001 at 02:10 UTC ( [id://66159]=perlmeditation: print w/replies, xml ) | Need Help?? |
I just wanted to share what I discovered the hard way when using Perl to do something as simple as checking to see what MySQL databases are installed locally on a Unix machine. This may be easy stuff for the experienced users, but none of this was apparent from reading the man DBI, man DBD::mysql pages and O'Reilly's MySQL book which contains a couple of chapters focusing on Perl. Given: Assume that the code already verifies that mysqld is running. Also assume you have the default mysql:user grant table in MySQL:
Problem: Get a list of existing MySQL databases on the local Unix machine.My initial concern was to keep the code as portable as possible in terms of linking to another database engine (e.g., changing the 'mysql' to 'oracle') in the future. Also, this program is meant to be installed and used on many machines, i.e. not just as a standalone website or one-time solution. Solutions:
option 1: From what I understood from the docs, the most portable way of accessing a list of existing mySQL databases was to use the following:
This method works fine for MySQL and on my machine, until I started installing the script on another machine with the same architecture.Then I began to get privilege errors and I couldn't figure out why. I knew from the docs that this statement only works for the localhost access, but it still should have worked. Finally I discovered the reason: because you can't specify a user and password with this method, it defaults to the user running the perl program. if you specified a MySQL root password in your grant table, then you can't run your perl program as root (or any user with a localhost entry with a password in your grant table for that matter). You need to run the program as any user without a password. option 2: Ok, so the next recommended option was to go with a supported, but less portable way: This install_driver method comes straight out of the DBI docs. I used it because at first I didn't know it was possible to connect to MySQL without specifying a database. It works fine, but it had the same problem as above, in that you couldn't run the program as a user that had a password in the MySQL grant table, eg. as root. option 3: So at this point I found a way to list the databases where I could specify my username and password....by using the command line: which worked fine,and you could even specify a --host tag if you needed. (By the way, those are `back quotes`, not 'single quotes', if you've never used them before). It didn't matter if you ran the program as root or any other user. However,the problem with this was that it would definitely not be portable to other database engines that I would try in the future. option 4: But after a post to Perl Monks, I learned something important from a reply: you do not have to specify a database when using the DBI connect method - something which is never mentioned in the docs or book. So I go back to option 2, but modify it like this: You can leave out the host=$HOST to default to localhost (but leave the trailing colon after mysql, i.e. 'DBI:mysql:'). Works fine. Portable to other databases engines? I'm not sure, but I'm sure it is more portable than option 3. Comments, corrections, and other options are welcome. If none, then I hope this just helps someone in the future. Update: Summarized options with advantages/disadvantages.
Back to
Meditations
|
|