After some time here is another post concerning MySQL. It is not as if I had not had anything to do with MySQL in the meantime, but most of it was mailing back and forth with their customer support (which is really quite good) to get some issues resolved we stumbled over in our MySQL 4.1 to 5.0 migration.

Before those were fixed we could not use MySQL 5 with our application, because there were some incompatible changes we could not work around.

One of them was the unsatisfactory precision when querying DECIMALs I wrote about earlier. This is fixed in 5.0.32-enterprise and the just released 5.0.33 community edition (see Bug #23260). In fact I was just about to write about the new release policy which I find somewhat strange (enterprise and community editions with the …

In both Sql Server and Oracle, permission changes to a user take into effect right away, even when said user is connected at the time you made the change.

In MySql, it is a little different, depending on how the permissions are given. If you use the GRANT statement, then it takes into effect right away. However, if you create user and give it permissions by manipulating the user table in the mysql system database directly, that is, using Sql statements, then you need to issue:

flush privileges

for those changes to be picked up.

Sql Server 2005 has a command line tool named sqlcmd. MySQL has a command line tool named mysql. Oracle has a command line tool called sqlplus. They can all be used for interactive query processing and batch scripts processing. They do similar things, albeit in different ways. They are functionally equivalent.

For Sql Server 2005, when in interactive mode of sqlcmd, use

:r c:MyFolderMyScript.sql

to read and execute a script file. You may have to type

go

afterwards, if the last line of the script file does not end with the word go.

To use sqlcmd in batch mode, that is, to run a sql script and then get out, use:

sqlcmd -i c:MyFolderMyScript.sql -S MyServerName -E

Replace -E with -U LoginName if you use Sql authentication

For MySQL, while in interactive mode of mysql, use

. c:MyFolderMyScript.sql (on Windows)

Note there should be a backward …

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 …