I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.

However, if you do:

…

We’re looking to implement CJK Support in Open Source Full Text search engine Sphinx .
Initially we’re thinking to base search ob bi-gram indexing to keep it simple, especially as according to research papers it offers decent quality for most cases. This is not that complex to implement however there is no way we can test it as we have zero knowledge of Chinese or Japanese.

If you know Chinese Japanese or Korean and would like us help us testing Sphinx support for these languages let us know. No special development skills are required. If you’re reading this blog you should be technical enough.

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 …

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 …

It happens sometimes, when I report a bug, that I have an argument with someone at the receiving end of the reporting chain. The raw happens over the definition of a bug. For instance, there is a new implementation of a consolidated tool. The new tool does almost all the old one did, except X. Therefore I file a bug report saying that X is missing. The ensuing argument runs along the lines of:
- This is not a bug, says the Verifier. You can't say it's a bug because it does not do what you want. It should be downgraded to feature request.
- This is the recommended replacement of the old tool, I retort, and as such it should do at least what the old tool did, plus the new stuff. I insist it is a bug.
- The manual does not mention feature X for the new tool, and then it is not a bug. It's a feature request"
The Verifier's reasoning is technically correct, and it is within the boundaries of his allowed action, so I see no point …

This article explains how to create a fixed-size FIFO (first-in, first-out) queue in SQL, where rows added after a threshold will cause the oldest row to be deleted. There are several ways to do this, but MERGE on Oracle and DB2, and MySQL's non-standard extensions to SQL, make an elegant solution easy.

Goal:

Getting rid of filesorts and temporary tables by tuning MySQL queries.

Background:

Filesorts and temp tables are a necessary evil in MySQL, used when MySQL must sort the data before returning the output to the user. They are the most common issue with slow queries in MySQL, the main reason being that if the output is too large, you can kiss goodbye in-memory performance, and say hello to disk access.

Common User and Manager symptoms:

1. Sore throats due to excessive swearing at poor database performance.
2. Sore hip pockets due to lack of scalability requiring continuous hardware purchases.
3. Sore users who are sitting on high speed bandwidth and have to wait more than 1.5 secs for a response from any web page. Google has raised the bar, time to ditch the straddle technique and go for the Fosbury Flop.

Appropriate Medicine:

The quickest way to …

In my last post (see it for details) about stored procedure replication I was wrong.
I'm using 5.0.30 and, in fact, CALL statements are not written to the binlog. My knowledge about SP replication was before 5.0.12. This morning I read carefully the manual :-)

But my problem is real. Using multiple rand() inside a SP cause wrong value replication on the slaves.

Here is the binlog content of my previous post example:

corra@localhost[(none)]> show binlog events in 'veleno-bin.000003' from 18403385 limit 4\G
*************************** 1. row ***************************
   Log_name: veleno-bin.000003
        Pos: 18403385
 Event_type: RAND
  Server_id: 1
End_log_pos: 18403420
       Info: rand_seed1=1044525788,rand_seed2=146374381
*************************** 2. row ***************************
   Log_name: veleno-bin.000003
        Pos: …

In my last post (see it for details) about stored procedure replication I was wrong.
I’m using 5.0.30 and, in fact, CALL statements are not written to the binlog. My knowledge about SP replication was before 5.0.12. This morning I read carefully the manual
But my problem is real. Using multiple rand() inside a SP cause wrong value replication on the slaves.

Here is the binlog content of my previous post example:

corra@localhost[(none)]> show binlog events in 'veleno-bin.000003' from 18403385 limit 4G
*************************** 1. row ***************************
   Log_name: veleno-bin.000003
        Pos: 18403385
 Event_type: RAND
  Server_id: 1
End_log_pos: 18403420
       Info: rand_seed1=1044525788,rand_seed2=146374381
*************************** 2. row ***************************
   Log_name: veleno-bin.000003
        Pos: …

Colin has posted the program for the MySQL Miniconf @ LCA2007, featuring (in no particular order) Laura Thomson, Guy Harrison, Jonathan Oxer, Morgan Tocker, Jonathon Coombes, myself, with Stewart Smith, Colin Charles and many other MySQL users also there.

Plenty of interesting stuff and good fun to be had.
Hope to meet you there!