Database Zone

Did you ever wonder how the MySQL fulltext indexing works? I mean the mathematics behind MySQL's fulltext index feature. At the Epsilon-Delta weblog there is an article by Ted Dziuba entitled "Dissecting MySQL Fulltext Indexing" that explains the mathematics behind Fulltext searching and what it can and cannot do.

Fulltext Indexing is a method by which MySQL quantifies the relevance of textual entries to an input query. The concept is similar to the way Google quantifies the importance of pages on the web with PageRank. However, in the context of a database, the requirements of the method are a little different.

» Read More

A pointer from Slashdot: BusinessWeek has another spread on open source this week. Among them is an article about open source vs. the database vendors which focused on how businesses are looking to save money with open source (rather than using the source to innovate). From the article: "The databases work fine, but as data volume grows, so do the checks to Oracle, IBM, or Microsoft. Many users aren't clamoring for more features, and some don't even use the bells and whistles they already paid for. They would happily trade some to get their hands on the source code and a better deal." Disclaimer: that quote came from Sony."

Three ALTER TABLEs to make the AUTO_INCREMENT column complete,
Seven UPDATES for the INTEGER column to prevent disaster,
Nine for TEMPORARY data doomed to DELETE,
One REPLACE for the replication MASTER
In the land of MySQL where the dolphin plays.

» Read More

IBM is offering for free a version of its DB2 database called "IBM DB2 Express-C". The IBM DB2 Express-C only limits the hardware that the database can run on, but is otherwise identical to the full DB2 release.

I think this is not a real limitation compared to Oracle's free version. IBM says it is targeting students, small organizations and software developers looking to integrate a database into their applications.

» Read More

Beginning with MySQL 5.1.5, two functions providing basic XPath (XML Path Language) capabilities are available: ExtractValue and UpdateXML. For detailed informations about these 2 functions and for a basic description of XPath see the MySQL manual here: http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html

ExtractValue is the "MySQL SELECT" equivalent for XML documents and UpdateXML as its name says is the "MySQL UPDATE" equivalent for XML documents.

» Read More

The Access PassView utility reveals the database password of every password-protected mdb file that created with Microsoft Access 95/97/2000/XP or with Jet Database Engine 3.0/4.0 It can be very useful if you forgot your Access Database password and you want to recover it ;-)

» Read More

The Secunia reported that Andy Staudacher has discovered a vulnerability in ADOdb, which potentially can be exploited by malicious people to conduct SQL injection attacks. The vulnerability is related to binary strings and affects PostgreSQL 8 but not MySQL.

Input passed to certain parameters isn't properly sanitised before being used in a SQL query. This can potentially be exploited to manipulate SQL queries by injecting arbitrary SQL code.

» Read More

Here are some of the best free MySQL tools out there. The description is not my review of the product, it is just what the developers say about their tools. If you know some other good ones, you can drop a comment.

» Read More

The begining of this story is a PHPNuke module. The module has a block that picks a random item from a database table and displays it. The content of the table never changes. Here is how the programmer of the block does it:

He knows that there are 6899 rows because the last id in the table has a value of 6899. So with the "rand" PHP function, he picks a random number from 1 to 6899 and then queries the database table with a WHERE clause that checks if the value of the id is equal to the picked number:

» Read More

You can find out what the error code means by either examining the documentation for your system or by using the perror utility.

Some examples:

[root@mylinux ~]#perror 147
MySQL error code 147: Lock table is full; Restart program with a larger locktable

» Read More

MySQL AB, developer of the world's most popular open source database, today announced its second straight quarter of financial profitability and another record year of enterprise sales wins and technical achievement.

With 4 million downloads since it launched in October, MySQL 5.0 is proving its relevance to open source developers and corporate enterprises alike. In 2006, we look forward to another great year of growth for the MySQL ecosystem of community users, industry partners and commercial customers."

» Read More

The "Howto Forge" site has published yesterday a good article entitled "How To Set Up Database Replication In MySQL". It is a step by step tutorial about MySQL replication feature. The article discribes all required steps on systems running "Debian Sarge", however the configuration should apply to almost all distributions with little or no modification.

» Read More

Most solutions, I have seen, for the pagination problem query the DB twice. A first query to get the total number of rows and a second query to get the page with a LIMIT clause.

1- To get the total number of rows:
SELECT COUNT(*) as nb_rows FROM t;

2- To get the page number page_num:
SELECT field1, field2, ...., fieldn
FROM t
LIMIT page_num * itemsPerPage, itemsPerPage;

» Read More

This week, the developers of the open-source PostgreSQL database issued a "critical" update.

One critical fix repairs a denial-of-service vulnerability: on Windows only, the postmaster will exit if too many connection requests arrive simultaneously. This does not affect existing database connections, but will prevent new connections from being established until the postmaster is manually restarted. The Common Vulnerabilities and Exposures (CVE) project has assigned the name CVE-2006-0105 to this issue.

Another critical fix repairs an error in ReadBuffer that can cause data loss due to overwriting recently-added pages. This applies to the 8.1 and 8.0 branches on all platforms.

» Read More

Thomas Kyte the guy of Oracle's Ask Tom page has in his blog a post about creating HTML from cursor. He uses the DBMS_XMLGEN.GETXMLTYPE to create the XML presentation of the cursor and then lXMLData.transform on a generic XSL to convert XML to HTML.

» Read More

This is from Duncan (Merrion) Rantz blog. I donno who wrote this poem and I can't find a trackback url for this entry to send him a ping.


Here is a little script I wrote
You might want to parse the single quotes,
Don't worry be happy
In SQL we have some trouble
When you convert from string to double
Don't worry, be happy......


» Read More

Do you know that the mysql command line tool has --i-am-a-dummy option? It's an option for beginners that has the same effect as --safe-updates. The --i-am-a-dummy option is very helpful because it prevents accidents. If you execute the SQL statement:

DELETE FROM table_name

what happens? You delete all rows from the table. With the --i-am-a-dummy option:

» Read More

Cybernoon reported yesterday that Mumbai to host Oracle Open World.

Oracle India Private Limited (Oracle India) announced today that it will host the premier Oracle OpenWorld conference in Mumbai on January 10-11, 2006. Oracle OpenWorld is Oracle’s global technology and business conference for customers, partners, business and IT decision makers. India is one of the only two countries in the Asia Pacific region to host the conference in Oracle’s current financial year, June 2005 to May 2006.

» Read More

Most INSERT statements are the single-table variety, but Oracle also supports a multiple-table INSERT statement. With a multitable insert, you can make a single pass through the source data and load the data into more than one table.

[ ALL | FIRST ]
WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
[WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]...
[ELSE insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]

» Read More

Purge is useful in 10g when you want to "really truly drop a table". In 10g, tables can be restored after being dropped.

When you drop a table in 10g like this:

drop table table_name;

Oracle renames it (moves it to the recyclebin) and the table is not (truly) dropped.
You can get it back:

» Read More