In Oracle and MySql, to get the column name and data type of a table, you can use:

desc MyTable

or

describe MyTable

The equivalent of desc in Sql Server is sp_columns. Therefore, run the command below will get similar results:

sp_columns MyTable

This is a reminder to anyone who works in technology, particularly open source, that the job market is healthy. If you've been waiting for the right time to start looking, that time is here.

I'm just saying this because I get a continuous stream of requests from friends, former co-workers, open source community associates, and random people who read here. Things like "Mike, we desperately need a MySQL DBA. Do you know anyone?" or "Do you know anyone who is available to do some PHP/Perl work." My unscientific sense is that companies are paying more because good open source folks are in demand.

Rather than posting every individual job that comes along I'm just going to say it once in general...look around. You'll find them. These companies are out there actively looking, they aren't the very special, hidden, must-be-part-of-a-secret-society jobs. You should be able to find them with a little poking around.

Just a …

I've been needing this for some time; the ability to search for apt-get packages on Ubuntu when I don't know the name of the package. Turns out there's a corresponding utility for doing this:

shell> apt-cache search <search string>


You'll want to be sure your repository is up to date with apt-get update. Today I need DBI to get Perl connected to MySQL, but can't figure out what the apt-get package name is (I tried "DBI", "perl-DBI", "DBI-perl"). A search immediately gives me what I need:

shell> apt-cache search dbi
libdbd-mysql-perl - A Perl5 database interface to the MySQL database
libdbi-perl - Perl5 database interface by Tim Bunce
libxml-sax-perl - Perl module for using and building Perl SAX2 XML processors


And find that apt-get install libdbi-perl gets me right back on the road.

Ubuntu (in …

Reading about Peter Zaitsev's feature idea about Finding columns which a query needs to access - which I would really like to see implemented - reminded me of a bug report I filed in 2004 and which bit me again only a few days ago. You can find it under Bug #7074 in the MySQL bug tracking tool. Although it is filed as a feature request, I think one should be aware of this, as it may cause problems in your applications (it did in ours).

Basically it is about explicitly specifying which columns you need in a result set, instead of just using SELECT *. This is generally a good idea, however if the table contains BLOB columns, it becomes even more important, as …

Reading about Peter Zaitsev's feature idea about Finding columns which a query needs to access - which I would really like to see implemented - reminded me of a bug report I filed in 2004 and which bit me again only a few days ago. You can find it under Bug #7074 in the MySQL bug tracking tool. Although it is filed as a feature request, I think one should be aware of this, as it may cause problems in your applications (it did in ours).

Basically it is about explicitly specifying which columns you need in a result set, instead of just using SELECT *. This is generally a good idea, however if the table contains BLOB columns, it becomes even more important, as …

We are currently preparing a MySQL 4.1 to MySQL 5.0 migration. First tests showed a very nasty problem, however.

One of our test cases incorporates queries against DECIMAL columns that use strings as the queried values. In MySQL 4.1 this works flawlessly. The reason behind this is that in contrast to 4.1 the newer server version does a (in my opinion very stupid) conversion from String to double, which in many cases cannot correctly store the precise value.

This may lead to very subtle bugs, especially when using an optimistic locking approach as we do. We only noticed the problem, because we got a ConcurrentModificationException, as an update query that contained a string-ized BigDecimal did not match any rows.

See MySQL bug reports 23260 and 22290 for more details.

Right …

Today I spent several hours trying to find a problem in our application until I found out there was a problem on the MySQL side. In our setup we have several slave machines that replicate data from a master server. The slaves are configured with a series of replicate-do-table directives in the my.cnf file so that only parts of the schema get replicated. The remaining tables are modified locally, so to avoid conflicts they are not updated with data coming from the master.

We do however have the need to replicate data from the master for some special-case tables. To solve this we usually have a column that indicates whether a record was created on a master or a slave machine and use an appropriate WHERE clause in all updates. This avoids collisions in concurrent INSERTs on the master and the slave. The application knows which of the two records to use.

Due to historical reasons I …

Over on the MySQL Index Analyzer site I have just released version 0.04 as a downloadable package.

This is the first GUI version that allows real analysis and has more features than the command line version.

A quick overview of what is new:

• Swing GUI
• Analysis features as on the command line
• Copying of generated ALTER TABLE statements to the clipboard
• Information on data and index size distribution
• Rudimentary analysis of possible disk space savings

So go have a look :)

Over on my MySQL Index Analyzer blog I just posted two screenshots of the first working GUI drafts. If you are interested, you will have to check this version out from the SVN repository, I will prepare a new distribution package soon.

As did last week's Craig Mullins of dbazine.com I wrote a short post about the LogBuffer recently when I found it in my Blog's referrer list and was promptly asked if I would like to compile one myself.

So here am I am, welcoming all of you to the the 9th issue of the Log Buffer, a Carnival of the Vanities for the DBA community. Once again you will find a plethora of links to all sorts of information on the one thing that keeps many of us both fed and sometimes close to blank despair.

Myself being some sort of a mixture between a software developer and database admin I have had a fair amount of time over the last years to get experience …