Home Switchboard Unix Administration Red Hat TCP/IP Networks Neoliberalism Toxic Managers
May the source be with you, but remember the KISS principle ;-)
Skepticism and critical thinking is not panacea, but can help to understand the world better

Perl Scripting for Open Source Databases


See also

Recommended books

Recommended Links

Free Databases

Free for individual
and non profit use



PostgreSQL MySQL Oracle Tivoli  DB Security Ingres Humor Etc

The best collection of links to start is not this page but  Pal's Linux RDBMS Library.

My page is pretty rudimentary and currently not maintained well as my current work and research interests are more in OS security area.  But still it was useful for my students before and might be useful for somebody now. So I decided to keep it on the WEB.  There are two major open source relational databases:  PostgreSQL and MySQL.

PostgreSQL History

PostgreSQL, originally called Postgres, was created at UCB by a computer science professor named Michael Stonebraker, who went on to become the CTO of Informix Corporation. Stonebraker started Postgres in 1986 as a followup project to its predecessor, Ingres, now owned by Computer Associates. The name Postgres thus plays off of its predecessor (as in "after Ingres"). Ingres, developed from 1977 to 1985, had been an exercise in creating a database system according to classic RDBMS theory. Postgres, developed between 1986-1994, was a project meant to break new ground in database concepts such as exploration of "object relational" technologies.

Stonebraker and his graduate students actively developed Postgres for eight years. During that time, Postgres introduced rules, procedures, time travel, extensible types with indices and object-relational concepts. Postgres was later commercialized to become Illustra which was later bought by Informix and integrated into its Universal Server. Informix was purchased by IBM in 2001 for one billion dollars.

In 1995, two Ph.D. students from Stonebraker's lab, Andrew Yu and Jolly Chen, replaced Postgres' POSTQUEL query language with an extended subset of SQL. They renamed the system to Postgres95.

In 1996, Postgres95 departed from academia and started a new life in the open source world when a group of dedicated developers outside of Berkeley saw the promise of the system, and devoted themselves to its continued development. Contributing enormous amounts of time, skill, labor, and technical expertise, this global development group radically transformed Postgres. Over the next eight years, they brought consistency and uniformity to the code base, created detailed regression tests for quality assurance, set up mailing lists for bug reports, fixed innumerable bugs, added incredible new features, and rounded out the system by filling various gaps such as documentation for developers and users.

The fruition of their labor was a new database that garnered a reputation for rock solid stability. With the start of its new life in the open source world, with many new features and enhancements, the database system took its current name: PostgreSQL. ("Postgres" is still used as an easy-to-pronounce nick-name.)

PostgreSQL began at version 6.0, giving credit to its many years of prior development. With the help of hundreds of developers from around the world, the system was changed and improved in almost every area. Over the next four years (versions 6.0 - 7.0), major improvements and new features were made such as:

The four years following (versions 7.0 to 7.4) brought the Write-Ahead Log (WAL), SQL schemas, prepared queries, outer joins, complex queries, SQL92 join syntax, TOAST, IPv6 support, SQL-standard information schema, full-text indexing, auto-vacuum, Perl/Python/TCL procedural languages, improved SSL support, an optimizer overhaul, database statistics information, added security, table functions, and logging enhancements and significant speed improvements, among other things. A small measure of PostgreSQL's intensive development is reflected in its release notes.

Today, PostgreSQL's user base is larger than ever and includes a sizeable group of large corporations who use it in demanding environments. Some of these companies such as Afilias and Fujitsu have made significant contributions to PostgreSQL's development. And, true to its roots, it continues to improve in both sophistication and performance, now more than ever. Version 8.0 is PostgreSQL's long awaited debut into the enterprise database market, bringing features such as tablespaces, Java stored procedures, point in time recovery, and nested transactions (savepoints). With it came a long awaited feature --- a native Windows port.

Many organizations, government agencies and companies use PostgreSQL. You will find installations in ADP, CISCO, NTT Data, NOAA, Research In Motion, The US Forestry Service and The American Chemical Society. Today, it's rare to find a large corporation or government agency which isn't using PostgreSQL in at least one department.

If there were ever a time for you to seriously consider using PostgreSQL to power your application or business, it would be now.

The history of MySQL AB Dries Buytaert
MySQL, the open source database product that puts the "M" in LAMP, was created by MySQL AB, a company founded in 1998 in Sweden. Sun Microsystems acquired MySQL AB on 26 February 2008 for approximately $1 billion. It became standard database in LAMP stack and as such aqured tremendous popularity.

Taking into account a poor shape of most imbed database languages,  Linux+Apache+MySQL+ PHP combination (or Linux+Apache+PostgreSQL+PHP)  at least for WEB applications looks comparative to proprietary offerings. Using Python is the other possibility. I feel that adding procedural components to SQL is a questionable idea and the only reasonable way to extend SQL go is to use a decent scripting language.  Java is another possibility but as it strong typing, it's  wrong possibility ;-). Although Java acquired industry standard of new Cobol (Cobol 2000 ;-), I would like to warn you that it neither provide high productivity typical for scripting language, no high performance typical for compiled languages (it's getting better). A lot of organizations spend millions of dollars on Java interfaces with databases with marginal or oven negative results as for development or support costs. They would get the same or better result much quicker and for a fraction of money with any decent scripting language.

If you need really large database I would see first if the database design is implementable under PostgreSQL. for historical reason I prefer Perl, but this is for only historical reasons. For smaller databases MySQL is OK or in case you work with WEB might even be a better bet (more literature, more open applications to study, more people to consult about problems, etc). In case those two are for some reason unsuitable Oracle on Solaris is a safe (but extremely expensive) solution. Oracle on Linux is a questionable bet -- it you are paying those huge money for the database you need a decent hardware and the best OS the money can buy -- that means UltraSparc III and Solaris 9. As an embeddable database Berkeley DB is an excellent solution.

I generally prefer PostgreSQL which is free in true sense of the word (it is licensed under BSD license), but of course you will be better off  buying support if you are doing something serious unless you want to became a PostreSQL developer.  But MySQL is a slightly stronger bet if we are talking about  <Web-server>>-<scriptinglanguage>-<database>  troika. Actually apache-PHP-MySQL is a very strong, and now pretty polished, development platform.

Note:  Please remember that for large projects changing databases is a very painful experience that should be avoided as much as possible.

Again, this page should be viewed only after Pal's Linux RDBMS Library:

This site is a compilation of the best free online readings about relational databases on Linux. If you're a Linux RDBMS/database administrator, a database designer/developer, or simply a Linux user with database ambitions, you'll find links to valuable resources here: articles, papers, and books on various aspects of relational database management. Needless to say, much of this material is more or less applicable to other (UNIX) environments, too.

Good luck !

Dr. Nikolai Bezroukov

Top Visited
Past week
Past month


Old News ;-)

[Feb 20, 2017] How to delete or remove a MySQL-MariaDB user account on Linux or Unix

Feb 20, 2017 |
How to delete or remove a MySQL/MariaDB user account on Linux or Unix by Vivek Gite on February 15, 2017 last updated February 16, 2017 in Linux , MySQL , UNIX I created a MySQL / MariaDB user account using this page . Now, I have deleted my wordpress blog and I want to delete that user account including database too. How do I delete or remove a MySQL or MariaDB user account on Linux or Unix-like system using mysql command line option?

Both MySQL and MariaDB is an open source database management system. In this quick tutorial, you will learn how to delete ore remove user account in MySQL or MariaDB database on Linux or Unix-like system.

Warning : Backup your database before you type any one of the following command.

Step 1 – Steps for removing a MySQL/MariaDB user

If you decided to remove open source application such as WordPress or Drupal you need to remove that user account. You need to remove all permissions/grants, and delete the user from the MySQL table. First, login as mysql root user to the MySQL/MariaDB server using the shell, run:
$ mysql -u root -p mysql
$ mysql -u root -h server-name-here -p mysql
Sample outputs:

Fig.01: The MySQL/MariaDB shell

Fig.01: The MySQL/MariaDB shell

Step 2 – List all mysql users

Once you have a MySQL or MariaDB prompt that looks very similar to fig.01, type the following command at mysql> or mariadb> prompt to see a list of MySQL/MariaDB users:
mariadb> SELECT User,Host FROM mysql.user;
Sample outputs:

Fig.02: How to see/get a list of MySQL/MariaDB users accounts

Fig.02: How to see/get a list of MySQL/MariaDB users accounts
In this above example, I need to delete a mysql user named 'bloguser'@'localhost'.

Step 3 – List grants for a mysql user

To see what grants bloguser have, enter:
mariadb> SHOW GRANTS FOR 'bloguser'@'localhost';
Sample outputs:

Fig.03: Display user grants

Fig.03: Display user grants

  1. bloguser – Mysql/Maridb user name
  2. localhost – Mysql/Mariadb host name
  3. mywpblog – Database name
Step 4 – Revoke all grants for a mysql user

Type the following sql command:
mariadb> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bloguser'@'localhost';
Sample outputs:

Query OK, 0 rows affected (0.00 sec)
Step 5 – Remove/Delete the user from the user table

Type the following sql command:
mariadb> DROP USER 'bloguser'@'localhost';
Sample outputs:

Query OK, 0 rows affected (0.00 sec)
Step 6 – Delete the database

Type the following command:
mariadb> DROP DATABASE mywpblog;
Sample outputs:

Query OK, 0 rows affected (0.00 sec)

And there you have it. A MySQL/MariaDB user deleted or removed from the server on Unix or Linux via command line option.

[Dec 08, 2014] Adminer - A lightweight and full-featured database management tool

Adminer is a full-featured database management tool written in PHP. Unlike phpMyAdmin, it consists of a single file. Adminer is available for MySQL, PostgreSQL, SQLite, MS SQL and Oracle.

The software acts as a drop-in-replacement for phpMyAdmin with a better user interface, better support for MySQL features, higher performance and more security.

[Oct 21, 2012] The Split Between Larry Ellison And Marc Benioff Is Getting Bigger, Uglier, And Geekier by Julie Bort

Oct. 17, 2012 | Business Insider

On Friday, announced it wants to hire 40 to 50 people next year to work on a "big project" using the open-source database PostgreSQL-a big threat to Oracle's core database offering.

Salesforce offers software over the Internet to businesses. That software, in turn, runs on Oracle today. The rumor is that Salesforce may be looking at ways to change it.

This ad came just days after Ellison publicly dissed for offering PostgreSQL to its Heroku cloud-computing customers while using Oracle for its bread-and-butter app.

During a talk at Oracle's OpenWorld conference earlier this month in San Francisco, Ellison bragged about Oracle's eat-your-own-dogfood policy: Oracle's cloud software runs on Oracle technology.

" bases its entire cloud on Oracle database," Ellison said, as reported by Neil McAllister of the Register, "but its database platform offering is PostgreSQL. I find that interesting."

Naturally, is a big Oracle customer. Larry Ellison helped found and the whole idea of cloud computing.'s CEO, Marc Benioff, studied at Ellison's knee as an Oracle executive.

But as the two companies increasingly compete with each other, the CEOs are now on the outs. Benioff was removed as a keynote speaker from last year's OpenWorld-so he set up a rival keynote across the street. He wasn't even invited to this year's conference.

Salesforce's job ad appeared a few days after Ellison's remarks. It says that project involves the "design and implement major pieces of the core database infrastructure" using PostgreSQL. wouldn't comment on the project, or the rumors that this was an attempt to switch away from Oracle.

"We're always evaluating/exploring different technologies," a Salesforce spokesperson told Business Insider. "We have a broad strategy when it comes to data persistence which includes not only Oracle, but also Postgres, HBase, homegrown file storage, etc."

But some important backers of PostgreSQL sure are excited.

A company called EnterpriseDB makes money by offering commercial support for a version of PostgreSQL.

In the past four years, EnterpriseDB has grown its customer base from about 200 in 2008, to about 2,000 today, including Sony Online Entertainment and TD Ameritrade Ed Boyajian, the company's CEO, Ed Boyajian, told Business Insider. (Skype and Instagram famously use the free, open source version, too.)

"Many of those customers have migrated off Oracle," he said. "That's our primary business."

He added that when a company "of the scale" of is seen making "making a big commitment" to PostgreSQL, more enterprises will view it as a viable alternative to the Oracle database, too.

LevelDB A Fast Persistent Key-Value Store

Google Open Source Blog

Wednesday, July 27, 2011 | 10:00 AM

Labels: c++, leveldb, library, storage

LevelDB is a fast key-value storage engine written at Google that provides an ordered mapping from string keys to string values. We are pleased to announce that we are open sourcing LevelDB under a BSD-style license.

LevelDB is a C++ library that can be used in many contexts. For example, LevelDB may be used by a web browser to store a cache of recently accessed web pages, or by an operating system to store the list of installed packages and package dependencies, or by an application to store user preference settings. We designed LevelDB to also be useful as a building block for higher-level storage systems. Upcoming versions of the Chrome browser include an implementation of the IndexedDB HTML5 API that is built on top of LevelDB. Google's Bigtable manages millions of tablets where the contents of a particular tablet are represented by a precursor to LevelDB. The Riak distributed database has added support for using LevelDB for its per-node storage.

We structured LevelDB to have very few dependencies and it can be easily ported to new systems; it has already been ported to a variety of Unix based systems, Mac OS X, Windows, and Android.

LevelDB has good performance across a wide variety of workloads; we have put together a benchmark comparing its performance to SQLite and Kyoto Cabinet. The Riak team has compared LevelDB's performance to InnoDB. A significant difference from similar systems like SQLite and Kyoto Cabinet is that LevelDB is optimized for batch updates that modify many keys scattered across a large key space. This is an important requirement for efficiently updating an inverted index that does not fit in memory.

LevelDB is available on Google Code, we hope you'll find it useful for your projects.

By Jeff Dean and Sanjay Ghemawat; Google Fellows

[Oct 27, 2008] ora2pg 4.9 by Gilles DAROLD

About: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database. It dumps the database schema (tables, views, sequences, indexes, grants) with primary, unique, and foreign keys into PostgreSQL syntax without editing the SQL code generated. It also dump Oracle data into PostgreSQL DB as online process or into a file. You can choose what columns can be exported for each table.

Changes: This release add extraction of a function-based index that appears as SYS_NC, and a new configuration option to not export alter sequence after data export

[Aug 05, 2008] Automation for the people Hands-free database migration

Most of the applications I've worked with over the years have been enterprise applications requiring the management of lots of data. Development teams working on such projects often treat the database as a completely separate entity from the application. This sometimes stems from an organizational structure that separates the database team from the application-development teams. Other times, it's simply what teams are used to doing. Either way, I've found that this separation leads to some of the following practices (or lack thereof):

These are inefficient practices that leave developers out of sync with data changes. Moreover, they can cause the application's users to experience problems with inconsistent or corrupt data.

Figure 1 illustrates the manual approach that's often used on software development projects. Manual changes are often inconsistently applied and error prone, and they can make it difficult to undo what's already been done or analyze the history of database changes over time. For example, a DBA might remember to apply changes to the lookup data on one occasion, but a developer might forget to insert this data later into the same table.

You can avoid the manual approach's pitfalls by implementing a database change strategy that minimizes human intervention. Through a combination of practices and tools, you can use a consistent and repeatable process for applying changes to your database and data. In this article, I'll cover:

Dr. Dobb's Database Report / A tool for prototyping data structures by Sergei Savchenko

If you are familiar with SQL, then the small relational database language (SRDL) Sergei presents here will seem like an old friend. SRDL is designed to provide your applications with flexibility in dealing with database problems

Sergei is a developer for Counselware in Montreal, Canada. He can be contacted at savs@CS.McGill.CA

When developing software, we are often confronted with what appears to be a typical database problem, yet ends up requiring operations some database engines can't deliver. Obviously, what we need in such situations is a tool that lets us quickly create custom, specialized features. The tool I present in this article -- a small relational database language called SRDL -- is designed to provide applications with a considerable amount of flexibility in dealing with database problems. If you are familiar with SQL, you will find a lot of similarities in SRDL. There are differences between the two languages, of course, primarily in the definition of relations, structure of operators, and typing scheme.

SRDL is implemented in C++ (using template classes). The current implementation is about 2500 lines of code. It should compile under GCC and most other modern C++ compilers, and run on practically any platform. The implementation can either be used interactively or embedded into C++ applications that call member functions implementing the operators explicitly. The complete SRDL source code (distributed as freeware) is available electronically; see "Programmer's Services," page 3.

There are two distinguishable levels in the SRDL implementation -- the lower-storage level and the upper-manipulation level. There is a minimal interface between the two, and all algorithmic complexity of operators is in the upper level, with the lower level providing a way to read or write into some particular format on some storage. Multiple implementations of the lower level are possible, so that relations can be stored in different formats (or even in different storage areas; for example, placed into memory to speed up execution of consecutive operators).

Linux Today - PostgreSQL or MySQL Which is Faster

Victor R. Rivarola S. - Subject: MySQL? Not even in the race. ( Apr 24, 2007, 23:09:27 )

Don't beleive me? Make a file named mysql.sql and feed it into mysql:


-- These statements should fail because of
-- referential integrity.


-- If we got this far, we can already laugh at this "database"'s referential integrity.

-- Now lets check data type safety.


-- MySQL and SQLite both finish these statements succesfully, showing that any referal to them as database should be enough to make anybody knowleadgable die of laugher.

-- Now, let us test the contents of both A and B.


Run this script if you dare on a MySQL server. It will spit on your face:

5 5
0 7

Feed it like this:

cat mysql.sql | mysql database_name

Note that if you copy the script to the clipboard and then paste it back into the MySQL console, it will at least give you a warning when you try to insert 'A' into ID, but it will go on happily and do it. Better, but not enough.

Any database that will let you execute those commands in that order is not fit for a children's toy.

MySQL does, Postgres doesn't. Microsoft Access doesn't either, so it is a better database than MySQL.

Yes, I know about InnoDB. However, it is not the default table type. Instead, MyISAM piece of junk is. Which means that of all the people who use MySQL, very few actually use InnoDB. So, argument is moot.

Besides, to use InnoDB you would need to add a "TYPE=InnoDB" after the closing parenthesis and before the final semicolon. This totally non-standard "feature" (MS style).

Besides how does this helps the type check misfeature? It doesn't. Once again you must resort to a nonstandard, nondefault, totally unknown, mode change command to fix it.

I would end up calling MySQL a toy, but even that would be WAY too generous.

As for the fact of its popularity, if you believe that fallacious argument you would have to conclude the obviously untrue statement that Microsoft Windows XP is much, much, much better than Linux.

[Jun 07, 2007] Tuning LAMP systems, Part 3: Tuning your MySQL server by Sean A. Walberg

Make your MySQL server fly with these server tuning tips

07 Jun 2007 (IBM DeveloperWorks)

Applications using the LAMP (Linux®, Apache, MySQL, PHP/Perl) architecture are constantly being developed and deployed. But often the server administrator has little control over the application itself because it's written by someone else. This series of three articles discusses many of the server configuration items that can make or break an application's performance. This third article, the last in the series, focuses on tuning the database layer for maximum efficiency.

A Message to the Community

Farewell Foxbase: "We are announcing today that there will be no VFP 10"

We have been asked about our plans for a new version of VFP. We are announcing today that there will be no VFP 10. VFP9 will continue to be supported according to our existing policy with support through 2015 ( We will be releasing SP2 for Visual FoxPro 9 this summer as planned, providing fixes and additional support for Windows Vista.

Additionally, as you know, we've been working on a project codenamed Sedna for the past year or so. Sedna is built using the extensibility model of VFP9 and provides a number of new features including enhanced connectivity to SQL Server, integration with parts of the .NET framework, support for search using Windows Desktop Search and Windows Vista as well as enhanced access to VFP data from Visual Studio.

Concurrently, the community has been using CodePlex ( to enhance VFP using these same capabilities in the VFPx and VFPy projects. Some of these community driven enhancements include:

To reiterate, today we are announcing that we are not planning on releasing a VFP 10 and will be releasing the completed Sedna work on CodePlex at no charge. The components written as part of Sedna will be placed in the community for further enhancement as part of our shared source initiative. You can expect to see the Sedna code on CodePlex sometime before the end of summer 2007.

The VFP team

Microsoft Visual FoxPro Roadmap

Microsoft Visual FoxPro Roadmap

The VFP team made a special announcement to the community on March 13, 2007. The team has announced that there will be no VFP 10. VFP 9 will continue to be supported as per the support policy ( through 2015.

After the release of Visual FoxPro 9.0 and Visual FoxPro 9.0 Service Pack1, the Visual FoxPro team at Microsoft has been working on a new project code-named Sedna. Sedna takes advantage of enhancements in Visual FoxPro 9.0. The primary goal of Sedna is to expand on the ability of Visual FoxPro-based solutions to better integrate with other Microsoft products and technologies.

Features in Sedna will target Visual FoxPro interoperability with application components created using Visual Studio 2005, the .NET Framework 2.0 and SQL Server 2005. Sedna will also help improve the ability for Visual FoxPro 9.0 solutions to be successfully deployed on Windows Vista. Sedna is planned to be released before the end of Summer of 2007.

Additional information can be found in the transcript of theVisual FoxPro DevCon 2005 Interview with Alan Griver and Ken Levy from June 2005, as well as the September 30th 2005 audio podcast FoxShow #24: Interview with Ken Levy - both interviews discuss details of the Visual FoxPro Roadmap. The complete SouthwestFox 2005 keynote slide deck on the Visual FoxPro Roadmap and community news now online, 1.6MB PPT download SouthwestFox2005_Keynote.ppt.

There are two pillars for the main themes of Sedna,Interoperability and Extensibility. Some of the extensibility enhancements will target report system features like additional report output file types. For interoperability, the areas (in priority) including the following upcoming Microsoft products and technologies:

Sedna Feature Overview documents describe the various aspects of Sedna.

Visual FoxPro will also release a service pack that includes several critical fixes, including those required to better support Windows Vista.

As indicated in prior public statements, Microsoft does not plan to merge Visual FoxPro into Visual Studio .NET, nor are there plans to create a new Visual FoxPro .NET programming language. Visual FoxPro will remain stand-alone Win32 based, and will run on 64-bit Windows in 32-bit compatibility mode.

For more information about Visual FoxPro, including answers toVisual FoxPro FAQ, go to the Visual FoxPro Developer Center Web site. at

(Updated: March 2007)

Microsoft Does Open-Source and FoxPro a Real Favor

Not true, but interesting :-)

"Microsoft has announced that it will open-source the core portions of the Visual FoxPro DBMS software to its CodePlex community development site. At the same time, Microsoft has announced that it will no longer be making new versions of the FoxPro DBMS."


Microsoft will be releasing the completed Sedna work on CodePlex at no charge. The components written as part of Sedna will be placed in the community for further enhancement as part of our shared source initiative.

You can expect to see the Sedna code on CodePlex sometime before the end of summer 2007.

NOTE that the released part is Sedna and NOT VFP nor VFP core elements!

Sedna is a project Microsoft has been working on for the past year or so. Sedna is built using the extensibility model of VFP9 and provides features like better connectivity to SQL Server, integration with parts of the .NET framework, wrappers for Vista APIs to make it easier to write applications that run on Vista machines, as well as better support for VFP data in Visual Studio.

The VFP Community Message is at:


[Oct 30, 2006] Oracle's Red Hat support spells trouble for Sun InfoWorld Column 2006-10-30 By Neil McAllister

Sun used to provide slightly cheaper support then Red Hat. Oracle undercut this price so this advantage is lost. Still Solaris stands on its own as an enterprise OS (and it serves as Oracle standard 64-bit platform) and in this role it competes more with AIX and HP-UX then Linux (although X86-64 changed that). So this development might hurt Sun a little bit on low end but it is not applicable to midrange and high-end server were Oracle mainly used. Actually IBM AIX is the major Sun's competitor in his space due to Power 5 CPUs scalability. But all three major Unix vendors have sizable Oracle deployment (surprisingly HP-UX is a strong contender in this space and many large corporations use HP-UX to run Oracle).

According to WimCoekaerts, Oracle's director of Linux engineering, Oracle's own production servers are rolled out with Linux -- not Solaris -- and Linux is now the de facto standard platform for 9,000 Oracle developers.

Sun is quick to point out the technical advantages of Solaris over Linux, and to be fair, they are numerous; score a point for Sun. What's more, Solaris is open source, just like Linux.

... ... ...

Sun says that, when you run the numbers, Red Hat's subscription pricing is expensive compared with what you get with a Solaris license. Oracle apparently agrees, because its baseline Linux support contract will be priced at half what Red Hat charges.

In short, whatever the effect Oracle's Unbreakable Linux has on Red Hat, it will also have a heavy impact on Sun.

[Oct 30, 2006] FAQ - Oracle Validated Configurations

For the list of systems (currently approximately a dozen) see Oracle Validated Configurations

August 14, 2006 Oracle Validated Configurations are pre-tested, validated architectures with software, hardware, storage and networking components together with documented best practices for deployment. Oracle and its strategic partners offer and recommend these configurations to enable end-users to deploy fully tested solutions to achieve standardization with high performance, scalability and reliability while lowering infrastructure costs.

... ... ....

Oracle is seeing significant end-user demand for Linux x86-64 architectures and is fully committed to developing, advancing and promoting the 64-bit commodity Linux. All new chipsets and servers are now being shipped with x86-64 architecture, thereby offering a much wider hardware selection to end-users than some of the other architectures. Therefore, Oracle has chosen to initially make Oracle Validated Configurations available on Linux x86-64.

[Oct 30, 2006] Oracle The biggest Linux vendor you've never heard of InfoWorld Column 2006-06-12 By Neil McAllister

Oracle is involved with Linux, Coekaerts says, foremost because Oracle uses Linux. A lot of Linux. Right now almost 10,000 Linux servers are in use internally at Oracle. Essentially, every production server at Oracle is a Linux server. In addition, about 9,000 developers at Oracle are using Linux to develop products.

A lot of that can be attributed to one simple factor: cost savings. "We use Linux for the same reason all the other companies are using Linux," Coekaerts says.

But there's more to it than that. The Oracle database is a large, complex application that places a lot of demands on the underlying OS. When Oracle wants to experiment, changing how the OS works to optimize database performance, it's easier to do with an open source, community-driven OS than a proprietary one. Hence the number of Linux kernel contributions from Oracle engineers; as a fast research and prototyping tool, Linux can't be beat.

The end result of all this in-house Linux experience is a whole lot of in-house expertise. In a way, then, it was only natural for Oracle to enter into the Linux support business. It's not widely recognized, but Oracle has provided enterprise Linux support through its Unbreakable Linux program for about four years. Now, with its new Oracle Validated Configurations initiative, it is poised to take that a step further.

An Oracle Validated Configuration is essentially what it sounds like. Oracle and its partners have selected specific combinations of hardware and software -- including server hardware, chip sets, Linux OSes, drivers, and storage -- and subjected them to approximately 60 to 70 tests designed to tax each system to the limits of its performance. The Validated label means you're getting a complete system that has been fully configured, certified, and optimized to run Oracle, down to specific kernel module parameters.

The Oracle stamp of approval doesn't just benefit Oracle users. Because Oracle is such a heavyweight application, it tends to highlight problems more quickly than other kinds of software. A system that runs Oracle well is almost guaranteed to run other applications well.

By comparison, Coekaerts says it typically takes customers nine to 12 months to get full server stacks properly configured when they do it themselves. "We're saving lots of people's time, including our own," he says.

But the bigger picture is one of perception. Over the long term, Coekaerts would like to see Oracle recognized for the contributions it has made to Linux throughout the years. As the world's second-largest software company, Oracle's influence over the industry isn't going away, but its reputation as an outsider in the world of open source just might.

"We're doing Linux the way we should be doing it," Coekaerts says. "We're trying to use our influence to do something good."

[Oct 28, 2006] Oracle Announces The Same Enterprise Class Support For Linux

On October 25, 2006 Oracle announced its own support for a clone of Red Hat. It is evident that Oracle will eat Red Hat lunch, but this is also a severe blow to Suse (making Novell almost irrelevant in enterprise Linux space). Some people think that this is a revenge for Jboss, but big business is not about retribution. Still details are pretty interesting and somewhat damaging for Solaris at mid-range: Sun should be very careful with licensing and cost of support to avoid the falloff...

Currently, Red Hat only provides bug fixes for the latest version of its software. This often requires customers to upgrade to a new version of Linux software to get a bug fixed. Oracle's new Unbreakable Linux program will provide bug fixes to future, current, and back releases of Linux. In other words, Oracle will provide the same level of enterprise support for Linux as is available for other operating systems.

Oracle is offering its Unbreakable Linux program for substantially less than Red Hat currently charges for its best support. "We believe that better support and lower support prices will speed the adoption of Linux, and we are working closely with our partners to make that happen," said Oracle CEO Larry Ellison. "Intel is a development partner. Dell and HP are resellers and support partners. Many others are signed up to help us move Linux up to mission critical status in the data center."

"Oracle's Unbreakable Linux program is available to all Linux users for as low as $99 per system per year," said Oracle President Charles Phillips. "You do not have to be a user of Oracle software to qualify. This is all about broadening the success of Linux. To get Oracle support for Red Hat Linux all you have to do is point your Red Hat server to the Oracle network. The switch takes less than a minute."

"We think it's important not to fragment the market," said Oracle's Chief Corporate Architect Edward Screven. "We will maintain compatibility with Red Hat Linux. Every time Red Hat distributes a new version we will resynchronize with their code. All we add are bug fixes, which are immediately available to Red Hat and the rest of the community. We have years of Linux engineering experience. Several Oracle employees are Linux mainline maintainers."

[Sep 15, 2006] PostgreSQL Slammed by PHP Creator

BitUbique - Pumping Bits Everywhere - Starting with SQLite

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine. The primary benefits of using SQLite is that you can create a self-contained database in your application.

What is so great about this? Well, for starters you can:

With SQLite, it is possible for you to distribute single application binary, with almost complete database and SQL querying capabilities. Compared to another embedded database such Berkeley DB, which offers just key and value assignment, SQLite is much more versatile and easy to use. You can just use all the SQL syntax that you have become familiar with to manipulate your data. The data itself is stored inside a single disk file on your local file-system, which can grow to a maximum of 2 terabytes (241 bytes) in size. For more information, visit SQLite site.

Under Ubuntu, to install SQLite, issue the following command:

sudo apt-get install sqlite3 libsqlite3-dev libsqlite3-0 sqlite3-doc

Finnish Revenge as mySQL Gets Solid Support Open Source

Oracle played a big card when it bought InnoDB, the most popular way to inject data into the open source mySQL database.

Monday mySQL responds by getting Solid™ Information Technology, a proprietary database vendor, to take its solidDB Storage Engine for MySQL open source, under the GPL, starting in June.

Solid has its base in telecommunications and transaction processing, which had been considered a completely different market from the small fry mySQL supplies. It has 3 million copies out at places like Alcatel, Cisco, EMC, HP, NEC, Nokia, and Nortel.

The addition of Solid technology to mySQL, the company said, puts mySQL into the enterprise league and makes it a direct threat to Oracle.

But does it? After all, Solid is in that enterprise market, albeit a niche within it. Solid is not going away, and this is supposed to be a complementary deal.

So I talked to Paola Lubet, vice president of marketing for Solid, She told me her 14-year old company had been looking for a way into the broader enterprise market for some time, and sees open source as a "go to market" opportunity.

"Our decision at the moment is to go into the open source track and use mySQL as a channel. So we're going to make available code that works only with mySQL. On the side we have a proprietary line of products."

Going to open source with mySQL was also a comfortable decision for Solid. Both companies were founded in Finland, and still do most development there. The U.S. arms of both companies are in the same building in Cupertino.

Stacey Quandt, research director for the Aberdeen Group in Boston, told me the deal also opens new markets to mySQL. "For years mySQL has defined itself as not being a competitor (in the enterprise space), but with a transactional engine that gap can be narrowed," she said.

Web 2.0 projects built on mySQL can also move ahead with confidence they're ready as they move into transactions and as users "rush to the rail" to support them.

"There's more work to be done, but the gap is narrowing," Quandt said of mySQL. "What I would look for in terms of feature initiatives and measuring success is for mySQL to now get into telco and manufacturing and other verticals." And for Solid? "The network effects of open source may help them grow their installed base."

You might say that Oracle's open source problems are far from…Finnished.

[Apr 06, 2006] Oracle vs. PostgreSQL Users speak outBy Mark Brunelli, News Editor

A recent interview has DBAs talking about the merits of the open source PostgreSQL database management system (DBMS) as compared to Oracle – and their opinions truly run the gamut.

In that interview, Robert Treat and Jason Gilmore, co-authors of "Beginning PHP and PostgreSQL 8: From Novice to Professional," said that PostgreSQL 8.0 is much more than just a back end for Web sites. In many situations, the authors say, PostgreSQL can be used instead of or as a complement to Oracle and other DBMSs.

DBAs responding to the interview said they liked the low cost of the open source database, while others said that Oracle's rich feature set is second to none.

Jim Allen, a longtime Oracle professional and an independent technology consultant, says he has had considerable experience with PostgreSQL 7.4, but not the newest version, 8.1.

Allen believes that PostgreSQL is much more suitable for the casual database developer, such as Java developers who need a back end for [Java Database Connectivity] access.

"PostgreSQL has a solid set of features now that includes most if not all of what these developers would ever use," Allen said. "Oracle has a feature set several orders of magnitude more rich, but few if any of these features would ever be used by this group."

Another thing Allen likes about PostgreSQL is the fact that the stored procedure compilation is transactional.

"You can recompile a stored procedure on a live system, and only transactions starting after that compilation will see the changes," he said. "Transactions in process can complete with the old version. Oracle just blocks on the busy procedure."

Matt S., a DBA, said that he has successfully used PostgreSQL 8 in conjunction with Microsoft SQL Server 2000 and 2005, as well as with Oracle 10g.

"I was impressed thoroughly with the ease of implementation, as well as compatibility and installation of PostgreSQL, considering its open source nature," he said. "Using it in enterprise applications [and] Web site situations was relatively painless [and] a simplified security structure made it very appealing."

Another user, who did not want to be identified, said that it makes more sense to compare PostgreSQL to OracleXE, a slimmed down and free version of the Oracle DBMS. The user said that like PostgreSQL, OracleXE is easy to install and use. He added that OracleXE includes Apex/HTMLDB to help developers quickly build and deploy Web applications.

"I've used PostgreSQL in the past [and] it is fine," the user wrote. "However, Oracle is stepping up and making a pretty good product and making it easier for organizations to upgrade their database systems down the road from the free version to their other versions of their product."

More on this topic:

Why PostgreSQL can best SQLServer, Oracle

PostgreSQL, MySQL, Oracle or other?

Richard Goulet, a senior Oracle DBA with a New England-area power components manufacturer, said that he uses Oracle and PostgreSQL side-by-side for numerous tasks.

Goulet agrees that PostgreSQL is easy to use, and he says it complies with the SQL standard nicely. He adds that there is plenty of support readily available for PostgreSQL through numerous mailing lists. But that's where Goulet's fondness for the open source software ends.

PostgreSQL doesn't behave as nicely as Oracle when the system fills up, Goulet said. In those instances, the system tends to crash quickly.

Goulet said that setting up a TCP/IP connection capability with PostgreSQL is hardly an intuitive process. To do it, he says, one needs to modify the postgres.conf and pg_hba.conf files manually.

"The last big thing between PostgreSQL and Oracle that's really missing is a gateway product from Oracle. These two don't talk to each other except by externally built and most times [highly customized] connectors," Goulet said. "An Oracle gateway to PostgreSQL would mean a lot to those who use both products happily."

Josh Berkus, who works with the PostgreSQL Project Core Team, said there are in fact third party tools available on the Web which help integrate Oracle and PostgreSQL data. They include Ora2pg and DBI-Link, he said.

[Mar 14, 2006] Five reasons why you should never use PostgreSQL -- ever By W. Jason Gilmore |

Within the past two years, Oracle, IBM and Microsoft have all released freely available versions of their flagship database servers, a move that would have been unheard of just a few years ago. While their respective representatives would argue the move was made in order to better accommodate the needs of all users, it's fairly clear that continued pressure from open source alternatives such as MySQL and PostgreSQL have caused these database juggernauts to rethink their strategies within this increasingly competitive market.

While PostgreSQL's adoption rate continues to accelerate, some folks wonder why that rate isn't even steeper given its impressive array of features. One can speculate that many of the reasons for not considering its adoption tend to be based on either outdated or misinformed sources.

In an effort to dispel some of the FUD (fear, uncertainty, and doubt) surrounding this impressive product, instead, I'll put forth several of the most commonplace reasons companies have for not investigating PostgreSQL further.

Reason #1: It doesn't run on Windows

PostgreSQL has long supported every modern Unix-compatible operating system, and ports are also available for Novell NetWare and OS/2. With the 8.0 release, PostgreSQL's support for all mainstream operating systems was complete, as it included a native Windows port.

Now, you can install the PostgreSQL database on a workstation or laptop with relative ease, thank to an installation wizard similar to that used for installing Microsoft Word or Quicken.

Reason #2: No professional development and administration tools

Most users who are unfamiliar with open source projects tend to think DB administrators manage them entirely through a series of cryptic shell commands. Indeed, while PostgreSQL takes advantage of the powerful command-line environment, there are a number of graphical-based tools available for carrying out tasks such as administration and database design.

The following list summarizes just a few of the tools available to PostgreSQL developers:

Reason #3: PostgreSQL doesn't support my language

Proprietary vendors' free databases:
Database heavyweights IBM, Microsoft and Oracle have all recently released free versions of their products. More information about the respective products can be found by navigating to the following links:

Today's enterprise often relies on an assortment of programming languages, and if the sheer number of PostgreSQL API contributions available are any indication, the database is being used in all manner of environments.

The following links point to PostgreSQL interfaces for today's most commonly used languages: C++, C#, JDBC, Perl, PHP, Python, Ruby and Tcl.

Interfaces even exist for some rather unexpected languages, with Ada, Common Lisp and Pascal all coming to mind.

Reason #4: There's nobody to blame when something goes wrong

The misconception that open source projects lack technical support options is curious, particularly if one's definition of support does not involve simply having somebody to blame when something goes wrong.

You can find the answers to a vast number of support questions in the official PostgreSQL manual, which consists of almost 1,450 pages of detailed documentation regarding every aspect of the database, ranging from a synopsis of supported data types to system internals.

The documentation is available for online perusal and downloading in PDF format. For more help, there are a number of newsgroups accessible through Google groups, with topics ranging across performance, administration, SQL construction, development and general matters.

If you're looking for a somewhat more immediate response, hundreds of PostgreSQL devotees can be found logged into IRC ( #postgresql?).

You can plug in to IRC chat clients for all common operating systems (Windows included) at any given moment. For instance, on a recent Wednesday evening, there were more than 240 individuals logged into the channel. Waking up the next morning, I found more than 252 logged in, including a few well-known experts in the community. The conversation topics ranged from helping newcomers get logged into their PostgreSQL installation for the first time to advanced decision tree generation algorithms. Everyone is invited to participate and ask questions no matter how simplistic or advanced.

For those users more comfortable with a more formalized support environment, other options exist. CommandPrompt Inc.'s PostgreSQL packages range from one-time incident support to 24x7 Web, e-mail and phone coverage. Recently, Pervasive Software Inc. jumped into the fray, offering various support packages in addition to consulting services. Open source services support company SpikeSource Inc. announced PostgreSQL support last summer, along with integration of the database into its SpikeSource Core Stack.

Reason #5: You (don't) get what you (don't) pay for

To put it simply, if you require a SQL standards-compliant database with all of the features found in any enterprise-class product and capable of storing terabytes of data while efficiently operating under heavy duress, chances are PostgreSQL will quite satisfactorily meet your needs. However, it doesn't come packaged in a nice box, nor will a sales representative stand outside your bedroom window after you download it.

For applications that require Oracle to even function properly, consider EnterpriseDB, a version of PostgreSQL, which has reimplemented features such as data types, triggers, views and cursors that copy Oracle's behavior. Just think of all the extra company coffee mugs you could purchase with the savings.

Computerworld Users tempted by free commercial databases

... Using the open-standard JDBC interface, Savvica ported its data to DB2 Express-C from MySQL in less than a day, said Green.

Defections such as Savvica's hearten big commercial database vendors, including Microsoft, Oracle, IBM and even Sybase, which have all released free "express" databases in the past six months.

Despite more robust features, these hugely profitable databases have in recent years lost mind share -- and, increasingly, customers -- to their open-source counterparts. MySQL AB's success has epitomized the corporate revolt against the license and support fees charged for commercial databases.

But the free express databases are "significantly challenging the conventional wisdom about commercial vs. open-source databases," said Peter O'Kelly, an analyst at Burton Group.

The commercial database vendors are opening a second front by adding support for application frameworks popular with open-source users. On Tuesday, Zend Technologies released software that enables developers to write applications interacting with the Oracle database in the PHP scripting language.

"IBM and Oracle are doing something similar to what MySQL has done: win the hearts and minds of developers by giving them easier access to technologies," said Mike Pinette, Zend's vice president of business development.

It's early, and the success of the big commercial database vendors at wooing back software developers -- who wield increasing influence over corporate buying decisions -- is not yet clear.

In the area of database instructional book sales, considered a good indicator of developer interest, sales of SQL Server how-to books have surpassed MySQL books this year, according to Roger Magoulis, director of research at leading publisher, O'Reilly Media. He believes that interest is due more to the general release of SQL Server 2005 last fall, rather than just its free edition -- especially as sales of Oracle or DB2 how-to books have not increased significantly since the release of their free versions.

Sybase says its Adaptive Server Enterprise 15 express edition has been downloaded 45,000 times since its September release, with "a lot of that converting into business," according to Marty Beard, Sybase's senior vice president of corporate development and marketing.

Microsoft, which released its first free database, MSDE, back in 1999, did not immediately provide the number of downloads of SQL Server 2005 Express, which was released last October. But Oracle said hundreds of thousands of developers and students have downloaded Oracle XE since its beta release that same month. IBM's DB2 Express-C was made generally available only in late January.

In contrast, the latest 5.0 version of MySQL has been downloaded more than 6 million times since October, said Zack Urlocker, vice president of marketing at the Cupertino, Calif.-based firm. "Sure, the express versions are free, but they come with very significant limitations, especially the lack of support," Urlocker said. "No enterprise customer will go into production with a database that cannot be supported."

MySQL user Andy Meadows said he hasn't been tempted to switch.

"Unless it's a large CRM or identity management system, I've found MySQL to be robust and scalable enough," said Meadows, president of Live Oak Interactive Inc., an Austin-based Web development and hosting firm. And while he acknowledges that "you can do quite a bit within the parameters" of the express databases, he fears that vendors will pressure him to upgrade to an expensive supported version of their database.

Rajeev Kaula, a professor in Missouri State University's information systems department, said Oracle XE is easier to install than earlier "lite" Oracle databases and helps teach students to program more efficiently.

"Students who honed their skills on MySQL and PHP tend to treat databases only as a way of storing tables," Kaula said. Learning on Oracle XE, "they are realizing the power of transferring the business logic to the database itself."

[Feb 28, 2006] This Oracle Database Wants to Be Free

The company has taken its Database 10g Express Edition (XE), a stripped-down, free version of its flagship database software, to general availability.

XE is exiting beta mode, where it received strong global support from several hundred thousand Java, .NET, PHP and Web developers and students.

Programmers use it to write database applications on Windows and Linux platforms, Oracle said in a statement.

For example, Oracle said Missouri State University computer science students use XE in their classes to get a better feel for the Oracle database through procedures, functions and triggers.

The software is built on the same code base as Oracle Database 10g Release 2 and is compatible with all of Oracle's database family of products. Oracle intended XE to have a simple upgrade path for users who wish to upgrade and get the bells and whistles associated with Database 10g standard and enterprise editions.

Database XE is available now on 32-bit Windows and a slew of Linux operating systems: Debian, Mandriva Linux 2006 Power Pack+, Novell's SUSE Linux Enterprise Server 9 and SUSE Linux 10, Red Hat Enterprise Linux 4, Red Hat Fedora and Ubuntu.

The software can be downloaded for free here.

[Nov 10, 2005] O'Reilly Network Using Perl in PostgreSQL by Andrew Dunstan

Most Perl users are familiar with using Perl to talk to databases. Perl's DBI is, along with ODBC and JDBC, one of the most common and widely ported database client interfaces. The DBI driver for PostgreSQL, DBD::Pg, is very well-maintained, and quite featureful. For example, it recently acquired proper support for prepared statements. Previously, the client library had emulated these, but with the latest DBD::Pg and PostgreSQL distributions, you can get real prepared queries, which can lead to big performance gains in some cases.

However, there is another way of using Perl with PostgreSQL--writing little Perl programs that actually execute inside of the server. This way of using Perl is less well known than using the DBI driver, and is, as far as I know, unique to PostgreSQL. It lets you do some very cool things that you just can't do in the client.

[Nov 7, 2005] Open Resource InfoWorld CA spins off Ingres. Does anyone care By Dave Rosenberg. See also the Wikipedia article about Ingress Ingres - Wikipedia

CA is selling its Ingres database technology to private equity firm Garnett & Helfrich Capital, which is forming a new company to develop and market the open-source software.

Does anyone care? Where will Ingres get it's market share from? Enterprise DB is sort of a different animal and MySQL has such a huge user base it doesn't seem like a great business move to launch yet another open source RDBMS database...especially when Ingres isn't widely adopted in the enterprise and doesn't have a niche that will get it in the door. Maybe I am missing something?

At last years LinuxWorld I moderated panel of open source database company executives, including a guy from CA who unfortunately became the target for everyone, especially me, to attack. That was largely based on the fact that CA was treating it as some noble offering to the community. But I still think Ingres is an also-ran product without a big market.

Posted by Dave Rosenberg on November 7, 2005 11:02 AM | TrackBack (0)


I disagree with your comments. Being the original database and enhanced to the level it is Ingres is the most advance database in the market. It has a large customer base who are loyal to Ingres. Running a small website is fine one can use MySQL or anything else for that, but if you are doing something serious, uou need a database which is as serious as Ingres.

Ingres scales really well, has great backup and recovery, can work on multiple platforms, installs in a flash and the best part is that with Ingres being such a mature product you do not have to worry about stability of the system.

MySQL 5 is just coming out with some very important database features which Ingres has had for years.

Ingres is the "real" database at fraction of the cost of Oracle, but languished due to lack of promotion. With G&H pumping in so much resources on it, I believe it can be the best out there leaving MySQL far behind. Project details for Cascade

Cascade is a Web-based content management system. It's based around the idea of organizing resources into a hierachy of categories, much like Yahoo. Some features include generating static or dynamic HTML, allowing user comments on ratings on resources, design abstraction through templates, suggested addition and update management, an auto-generated "What's New" page, support for related categories and virtual subcategories, and more. It can use Postgres or MySQL as its SQL database. Project details for Postgresql AutoDoc

Postgresql AutoDoc has the ability to output XML, which can be loaded into Dia to create a UML diagram of the database (complete with table relations and descriptive information), an HTML form for further detailed information, GraphViz .dot output, and Docbook 4.1 style SGML for inclusion with project documentation as an appendix. It works on any 7.x PostgreSQL-based database. Project details for libsite-db-perl

The libsite-db-perl module provides basic and easy database-connectivity through both URLs (like 'postgres://user:password@myhost/mydatabase') and standard DBD-parameters. This method should be useful for both beginners and experts since it decreases the duplication of code and its nice URL-based access. Project details for AllCommerce

AllCommerce is an e-commerce/content application based on Perl and SQL92 databases which runs under Unix/Linux/Win2000 using SQL database engines (MySQL/Postgres/etc). In addition to a shopping cart, it provides tools for content, merchandise, statistics, vendor, order, and inventory. Its modular design allows it to be used as a complete or partial solution. Project details for dSQL

dSQL is an SQL query tool for MySQL, Oracle, Postgres, MS- SQL, ODBC drivers, and all supported Perl DBI drivers. It uses Glade and GTK-Perl.

Enterprise Systems India Shines in CA's $1 Million Programming Challenge

At last summer's Linux World expo, Computer Associates International Inc. (CA) unveiled plans to open up the source code to its Ingres R/3 database.

Ingres is used by thousands of customers, but (these days) it's far from a relational-database powerhouse. With this in mind, CA sought to sweeten the pot, announcing a total of $1 million in prizes to encourage programmers to develop open-source tools to migrate applications and data from DB2, Oracle, and other databases to Ingres R/3.

Last week, CA announced the results of its Ingres challenge. The winners were determined by a panel that included Robin Bloor (of consultancy Hurwitz and Associates), JBoss architect Gavin King, and Ingres founder Dr. Michael Stonebraker. Qualifying entries had to ensure that applications running on Oracle, IBM, and other databases could interoperate unmodified with Ingres.

By all accounts, the winning entries-called Shift2Ingres, EzyMigrate, and DbConverter-do so splendidly. What's more, they also amount to a victory of sorts for the Indian subcontinent. Two of the three winning entries were submitted by programming teams based in India (New Delhi and Kerala, respectively). The third, for the record, was written by Bipin Prasad, a programmer from New York.

Shift2Ingres, which took home the biggest prize ($400,000) is a schema-, data-, and application-migration toolset for Oracle. It's based on a Java GUI that lets DBAs configure and perform the migration of tables and underlying data, views, grants, sequences, PL/SQL procedures and functions, triggers and other schema objects from Oracle to Ingres r3 databases.

EzyMigrate, one of the two $300,000 winners, is a database-migration tool for SQL Server. It uses ODBC to connect to discover database tables resident in SQL Server and displays table definitions to end users by means of a Web-based front end. DBAs can use the interface to make modifications to the table definitions, and the tool itself creates and populates the tables in the target database. DBAs can use EzyMigrate to selectively perform individual table migrations, but the tool also provides models for several different data migration scenarios, including drop-and-replace, delete-and-replace, and append.

The final winner, DbConverter, also took home a $300,000 purse. The database-migration program can convert tables, views, synonyms, indexes, triggers, constraints, groups, roles, users, permissions, sequences and other schema components from MySQL. DbConverter uses a Java-based UI that lets DBAs select not just which components they want to use for a migration, but also what kind of migration they want to perform-e.g., directly into the target database or by means of generating a SQL script and relevant output files so they can perform the migration externally. The tool also supports plug-ins that can be written to parse and convert associated applications. - Oracle jumps on PHP bandwagon

"Oracle is the latest database vendor to put its weight behind the PHP scripting language for business, with a new tool that integrates PHP applications with its databases.

"Oracle and PHP tool developer Zend Technologies have developed a PHP engine called Zend Core for Oracle. The tool, to be released for free in the summer, will integrate Oracle's databases and Zend's PHP environment..."

NewsForge Database vendors are joining the open source party

Oracle: Open source hucksters and challenge

Oracle Vice President of Technology Marketing Robert Shimp, whose company is among the only database providers not trending toward open source in some way, was critical of some open source moves by database makers in an interview with NewsForge. Shimp did not name names, but he noted that while Oracle welcomes the market growth and competition from open source databases, much of the open source database noise is centered on "orphanware."

Shimp cites companies "using open source because they see it as a marketing mechanism -- a tool for creating hype or awareness of older products. This is 'orphanware' -- software they want to abandon that has no real commercial value, so they put it out and see what happens."

Shimp elaborated by dividing open source database strategies into two categories: "serious" open source databases that provide transparency, allowing developers and users to learn and share; and the "hucksters" putting out abandonware.

In terms of competition from open source, Shimp said Oracle views the other databases as an asset in bringing new database users to the market, calling Oracle's biggest competitor the filing cabinet. Quite often, users are introduced to databases through a free or open source database, then move to Oracle as their needs become greater, according to Shimp, who called innovation Oracle's challenge and advantage.

"I'm confident we'll be able to create cool things that will get people to use Oracle," he said. "But I love the challenge the open source guys are providing."

IBM: Seeding the market

IBM program manager Les King, who touted Big Blue's move to open its Cloudscape database through the Apache incubator project Derby, said he took exception to the idea that Cloudscape was a case of abandonware. "It had a very thriving life on its own before we decided to open source it," King said.

He also indicated that like Oracle, IBM sees open source as a way to gain more market share by catering to developers with open source databases.

"There is an opportunity for vendors offering base code to hopefully seed their own market," King said. "Certainly, if we consider the seeding play, you need something to seed and Derby is perfectly set up to start seeding DB2 Express."

While there is talk of IBM open sourcing parts of its full-featured, enterprise-class DB2, King discounted the idea, referring to the complexity and value of the code.

"If you take the multiple millions of lines of code [in DB2], it naturally doesn't lend itself to dumping all of that code out there," King said. "In addition, today, there is a lot of intellectual property in the software we sell, and we wouldn't want to make it all open source."

King did anticipate more open source moves from more database players, however.

"I think you'll start to see more choice as companies do take pieces of software and make it open source because that's what they're targeting," King said, referring to developers. "It doesn't seem to be slowing."

IBM and Oracle say they are happy to see open source bring new users to the overall database market, but OS database players say that their users prefer to stick with open source, which is now rivaling DB2 and Oracle, even at the higher level.

CA: Commodity competition

Computer Associates Senior Vice President of Development Tony Gaughan referred to a sort of seeding, indicating his company -- which this year released its Ingres database under its own Trusted Open Source License, sees open source as a chance to increase mindshare and foster innovation by collaborating with its community.

Gaughan, who said the Ingres open sourcing is a return to the database's "roots" as an open source project at UC Berkeley, also pointed to the database as another instance of commoditization.

"Customers do not set out to buy a database, they purchase an application that requires a database," Gaughan said.

"We have seen a need and demand for an enterprise-class, open source database solution," Gaughan said. "MySQL is suited to read-only operations and serving up HTML content; PostgreSQL has a much richer feature set but has scalability problems and doesn't have a company behind it providing enterprise-level support; Ingres has a mature, proven, scalable transactional database, and includes clustering, peer-to-peer replication, and distributed query support."

In response to calls that the CA Trusted Open Source License is not OSI-approved and the Ingres moves are half-hearted, Gaughan said the elements not included in the available source were B1 security and the spatial object library for the database, which CA does not own.

"The security component that was removed was B1 security, which is a level of security used exclusively in situations of national security and is only available on B1 secure operating systems such as Sun CMW," Gaughan said. He added CA is working on a new 3-D, OpenGIS-compliant spatial object solution the company plans to develop with its community.

PostgreSQL: Others are too little too late

PostgreSQL core team member Josh Berkus said the open source moves by other companies are both a marketing play and done for technical reasons.

"I think that recent events in databases have vindicated the idea that open source will continue to spread through the software world, annexing one sector at a time," Berkus said in an email. "It's not a question of if software companies will need an open source strategy, but when."

While it might have been a competitive concern if Sybase and CA were making Linux and open source moves three years ago, Berkus indicated it is now the larger, older companies that are at risk from open source progress.

"My feeling is that both of these are good examples of proprietary software companies worried about being left behind by open source," Berkus said.

Berkus said databases are a likely part of the software stack to go open source because they are infrastructure software, making it easier to attract developers. However, Berkus questioned whether Sybase or CA could reap the same benefits as would a database that was born open source or free.

"In the case of CA, I'm going to reserve judgment until its license is approved by the Open Source Institute," Berkus said. "Right now, it looks like more PR than substance; unlike IBM, CA has not made Ingres separate from CA product management, which means that they're rather unlikely to attract developers. Compare the failure of both Borland's Interbase and SAP's SAP-DB as open source projects -- no offense to Firebird DB, which became a dynamic project after they forked it away from Borland. CA seems to be repeating the same mistakes."

As for Sybase, Berkus said the likely reason the competitor released the free version for Linux was a Software Development Magazine survey, which suggested PostgreSQL was pushing Sybase out of the market. Sybase did not respond to that contention.

Berkus said regardless of what other vendors are doing, the open source databases are catching up and in some instances surpassing the proprietary competition.

"PostgreSQL and even MySQL have surpassed Sybase in several areas, even if we lag behind in others," Berkus said. "PostgreSQL is particularly a threat to Sybase because our very robust, fully ACID transaction support, high availability, and support for custom statistical functions and complex queries make PostgreSQL perfectly suitable for a variety of financial applications."

MySQL: Everyone wants to be a toy

For MySQL CEO Marten Mickos, the free and open source database cavalcade from the old veterans is welcome news, and a validation that open source databases are now competing at the highest levels.

"We think it is good news for users, and we welcome these products to the open source world, Ingres, and the Linux world, Sybase," Mickos said. "We have predicted for some time that this would happen. It validates the MySQL business model. Two years ago, people said MySQL was a toy. Now, apparently everyone wants to be a toy!"

Mickos said the open source trend among databases is because of a combination of things, and is also, "a typical reaction from a large company that would like an older product to become more popular."

"Some years ago, Borland did the same thing with Interbase, and later they withdrew from the open source world," Mickos said.

However, the MySQL chief did refer to the MaxDB database, formerly known as SAP DB, as an example of older, closed code that was successfully nurtured with open source.

"MaxDB may be the only DBMS that started as closed source and was later successfully open sourced," Mickos said. "SAP AG open sourced it some years ago, and today, our company is the open source and commercial channel for MaxDB. It is a very robust, enterprise-level database and it powers an increasing number of SAP R/3 applications all over the world. It is also being used more and more by cost-conscious enterprises, by government agencies, and in developing economies. So here you have a great example that a DBMS with a long history can indeed enjoy new growth."

Sybase: Sidestepping corporate approval

While it has not released its Adaptive Server Enterprise (ASE) database as open source, Sybase has scored success with the free Linux version of its database. Citing the same database stepping stone theory as others, Sybase Senior Group Marketing Manager Amit Satoor said companies are struggling with the switch from free and open source databases to enterprise-class databases.

"Most of the customers just want low-cost access to software so they can start projects that they can deploy on a scalable platform," Satoor said.

Sybase sought to strengthen its Linux database recently with the announcement that its ASE database would run on IBM's eServer OpenPower server, a prominent Linux deployment based on the Power 5 processor.

Sleepycat: Feeling pressure from open source

Berkeley DB open source database maker Sleepycat Software's Vice President of Marketing Rex Wang said moves by Sybase, CA, and IBM were the older players' reactions to inroads from the open source newcomers.

"There's no doubt that these proprietary database vendors are being pressured to do this by the success of open source vendors," Wang said, referring to a Sybase statement that the free Linux ASE was intended to compete directly with the open source databases, as well as DB2 and Oracle. "The fact that large, incumbent, proprietary players have been motivated to make these moves indicates that the momentum is real."

Wang said Sybase, for example, felt tremendous price pressure and therefore made a restricted version of its product free to the most price-sensitive segment of its market. "Their hope is to get people to try it for free, then sell them the unrestricted version as they scale their use," he said.

But not so fast, Wang indicated, as Berkeley DB's developer focus and relative maturity -- in the market for eight years -- mean it is already appropriate for mission-critical use.

Open source getting good enough

Yankee Group senior analyst Dana Gardner said the use of a free Linux or open source database to introduce customers to a wider range of products that scale up to enterprise is a legitimate strategy. However, Gardner also said the databases that have been open source since the start may benefit from an evolving, total open source solution.

"What will be interesting is if the full stack of open source components becomes some kind of de facto standard," Gardner said. "In a best-of-breed open source approach, what are the databases that are part of that de facto standard?"

Gardner added that while he does not see open source databases such as MySQL and PostgreSQL dethroning the dominant Oracle and DB2 databases, the capabilities of the open source databases are quickly catching up and are also sufficient for many higher-level users.

"MySQL and PostreSQL -- those are quite full-featured," Gardner said. "If they continue that trajectory, good enough is good enough for many people."

FutureSQL Web Database Administration Tool

FutureSQL is a Rapid Application Development web database administration tool written in Perl. FutureSQL allows one to easily setup config files to view, edit, delete and otherwise process records from a MySQL database. It uses a data dictionary, configuration files and html templates, and allows "pre-processing" and "post-processing" on both fields, records and operations. It allows multiple views and operations on a data set, including the use of joined tables for queries and reports.

A demo application with most of the features is included.

[Aug 12, 2004] Open source making its mark

By Robert Westervelt, News Writer
12 Aug 2004 |

Oracle, Microsoft and IBM will respond to a flood of interest in open source databases by slashing prices and ramping up automation features, according to "DBMS: Foundation of application Infrastructure," a market report issued by Cambridge, Mass.-based Forrester Research Inc.

Proprietary database management system (DBMS) vendors are beginning to feel the pinch from MySQL, Computer Associates International Inc.'s Ingres and other open source DBMS vendors that are attracting new customers with a low cost, no-frills systems.

"Right now we're seeing the impact of open source databases in the entry-level database arena," said Noel Yuhanna, a senior analyst at Forrester. "As open source vendors add new features and functionality, the adoption rate [of open source databases] will increase."

While more than 80% of enterprises continue to focus on the top-tier DBMS products -- such as Microsoft SQL Server, IBM DB2 and Oracle -- for mission-critical database applications, open source products are accounting for more low-end, small scale deployments, according to the Forrester report.

Most of the open source DBMS deployments are for non-mission-critical applications, but Forrester predicts that more than 20% of overall deployments will be mission-critical by 2006.

Forrester surveyed DBAs, chief information officers and other IT personnel at 85 North American firms that use or plan to use open source software. Of those, 52% said that they use or plan to use MySQL DBMS. Those surveyed said they were lured by low cost of support and maintenance, low acquisition costs and easier integration with customized software.

"Open source is clearly making a dent in database low-end deployments today," Yuhanna said. "I expect larger scale deployments in the near future."

For now companies are downloading the free open source versions, initially to test out the functionality in their specific environment, Yuhanna said. Once enterprises get through the initial stages of their testing models, most will make the switch to the fully supported version.

"Enterprises that are serious about open source will purchase the supported version," Yuhanna said.

MySQL is the leader in open source systems, but CA announced plans in May to open up the source code to its Ingres DBMS. In addition, CA said last week it would offer $1 million to encourage development of an open source database migration toolkit.

"We're seeing very good comments about Ingres in terms of performance, scalability and feature sets," Yuhanna said. "It's definitely going to be an important open source database and will compete on the deployment adoption rate with MySQL."

Meanwhile, IBM is also jumping on board, announcing recently that it would contribute its Cloudscape Java database, which it acquired from Informix, to the Apache Software Foundation. The project is called "Derby," and amounts to more than 500,000 lines of Java code.

While open source databases begin to take more market share, Forrester said it is seeing increased interest in mobile and XML databases. A Sybase subsidiary, iAnywhere Solutions Inc. dominates the mobile space with more than 65% of the market.

XML-enabled databases supported by Oracle, IBM, and Microsoft also continue to grow, according to Forrester. The current market size is about $250 million and is likely to grow to $400 million by 2007, Forrester said.

[Aug 12, 2004] NewsForge What's to come in the (Linux) software market

By: Michael Dortch, Robert Frances Group

The LinuxWorld Conference and Expo in San Francisco last week featured many interesting announcements. However, the show was at least as noteworthy for its implications as for its actual news announcements. IBM Corp.'s announced transformation of its Cloudscape database solution into an Open Source offering follows closely the announcement of a similar transformation for Computer Associates International, Inc. (CA)'s Ingres database solution. Between Cloudscape and Ingres, Open Source developers now have multiple new and powerful options for building more "enterprise-class" solutions.

CA also announced actual availability of Ingres r3 as an Open Source offering. The company also announced a million-dollar challenge/inducement to developers who build tools that help users migrate to Ingres from other proprietary and Open Source database solutions.

Other software vendors announced, discussed, and/or failed to deny or dispute rumored plans to transform other proprietary solutions into Open Source offerings. Clearly, the typing is on the display for many of these vendors. They understand that they need to deliver Open Source complements and alternatives to their proprietary solutions, or complements and alternatives priced like Open Source offerings, to remain competitive. No IT executive desirous of continued employment is going to be able to justify paying proprietary-level prices for applications without strongly compelling value propositions, when supporting operating environments become increasingly cheap or free.

But no IT executive similarly incented is going to argue to "rip and replace" all proprietary solutions with Open Source alternatives. The enterprise software market, therefore, looks more and more like a striated, multi-tiered arena, analogous in some ways to the current market for broadcast content.

Today, there is "free" broadcast content subsidized rather speculatively by advertising, the effectiveness of which is impossible to track perfectly. There is partly subsidized content, paid for by combinations of grants and subscriptions paid for by only a portion of all consumers. Finally, there is completely subsidized content, for which consumers pay via subscriptions or per use.

Soon, there are likely to be three similar tiers of the enterprise software market. There will be free software, offered in the hopes that it will generate follow-on demand for fee-based enhancements, services, and/or support. There will be proprietary software, priced, sold, and supported much as it is today. In addition, there will likely be a rapidly growing middle tier of enterprise software solutions that are priced aggressively and built atop free and inexpensive Open Source foundations, including but not limited to Linux.

This likely means expanded choices for developers and enterprises alike. However, environments made up of various combinations of solutions from all three tiers will definitely require comprehensive, integrated management. A key question is, therefore, from what vendors will such management solutions come?

The ability of the leading traditional IT management vendors to answer this question apparently varies widely. BMC Software, Inc., which won a Best of Show award at LinuxWorld in 2003, announced no new solutions or reaffirmations of its commitment to Linux support in San Francisco last week. This is in marked contrast to CA, Hewlett-Packard Co. (HP), and IBM. CA, in addition to the announcements discussed above, has repeatedly stated publicly that it is encouraging eventual development of an entire Open Source IT management suite. Regarding HP and IBM, there were no specific HP OpenView or IBM Tivoli announcements at LinuxWorld. However, both HP and IBM were prominent at the show, and very willing to discuss how their respective management arms are committed to making Linux and Open Source solutions safe bets for the enterprise.

Veritas Software Corp. , a vendor known primarily for storage management, reminded LinuxWorld Expo attendees that it had been shipping Linux solutions since 1999. The company also reinforced its new position as a provider of solutions intended to enable utility computing. Veritas also announced offerings intended to enable rapid migration between Linux and other environments, and that it had joined the Open Software Development Labs, Inc.

Veritas is transforming and expanding its core mission by focusing specifically on making enterprise IT architectures ready for Linux and Open Source solutions. IT executives should expect to hear lots of other vendors tout similar strategies - and be prepared to carefully separate promise from reality.

RFG believes IT management solution vendors must quickly and firmly declare and demonstrate willingness and ability to help IT executives build and operate infrastructures that embrace Linux and Open Source. Furthermore, IT executives should work with their most trusted vendors to ensure that architectures at those executives' enterprises become and remain sufficiently flexible and elastic to support promising new Open Source solutions as they appear. Such architectures will enable IT executives to incorporate such solutions as they demonstrate the ability to lower costs and deliver other business benefits, without disrupting operations or enterprise elasticity.

Meanwhile, IT executives should ensure that their leading incumbent management vendors have strategies and offerings adequate to address growing enterprise Linux and Open Source support requirements - or begin considering alternative solutions and vendors.


Michael Dortch is a principal business analyst and the IT infrastructure management practice leader at Robert Frances Group (RFG). RFG provides business-centric, timely advice, consulting, and research about the IT marketplace to Global 2000 IT executives, their teams, and their senior executive colleagues. More information is available online at

Database Pipeline Do Open Source Databases Pack The Gear By Rick Whiting Courtesy

Information Week

Open-source databases took a giant step toward the mainstream last month when Hewlett-Packard began supporting MySQL and certifying it to run on HP servers--the first major system vendor to do so.

HP's move adds to the growing evidence that open-source databases--primarily MySQL--are becoming a viable alternative to commercial databases from IBM, Microsoft, and Oracle. "It's no longer the lunatic fringe," says Gartner analyst Kevin Strange, who has seen a surge of interest in MySQL in the last six to eight months.

Like the open-source Linux operating system and Apache Web server, open-source databases are freely available on the Web, and developers can download the source code and modify it any way they like. But companies also can pay vendors such as MySQL AB and PostgreSQL Inc. for support and other services in the same way they can purchase Linux software and support from Red Hat Inc.

Why the sudden interest in open-source databases? Cost is the main reason. Companies are undertaking new IT projects as the economy improves, but budgets remain tight and IT managers are increasingly open to low-cost alternatives to high-priced database software. MySQL implementation costs can be as little as 10% or 20% the cost of a commercial database, says Mike Gaydos, the lead architect of MySQL solutions at IT services firm EDS.

Business-technology executives now have a higher comfort level with open-source software overall. As Linux, Apache, and the JBoss open-source application server gain acceptance, IT execs who just a year ago might have balked at the idea of using an open-source database are taking a second look.

Open-source databases lack some of the sophisticated capabilities offered by commercial databases, but they're widely perceived as capable of handling routine and even critical computing tasks. AMR Research, in a report predicting open-source databases will be widely adopted by 2006, found that 43% of companies using open-source databases say they can handle mission-critical jobs today, while 37% expect them to be ready for such tasks within 24 months.

Increased use of MySQL and other open-source databases, in fact, represents something of a revolt among IT buyers against IBM, Microsoft, and Oracle, which continue to fill their database products with new features and technology that many business users aren't ready to use. Oracle, for example, built grid-computing capabilities into its 10g database, even though many customers are a long way from building grid systems.

The government of Pottawattamie County, Iowa, is swapping out its Windows server software for Linux. It uses MySQL for several database applications and plans to convert others to MySQL from Microsoft's SQL Server database. Aside from the cost advantage MySQL has over SQL Server, county IT director Thomas Broniecki claims MySQL is more secure than Microsoft's database.

Few companies, however, are ripping out their Oracle, Microsoft, or IBM DB2 databases in favor of open-source databases. Commercial databases are too entrenched within IT networks, and businesses have too much invested in applications--either developed in-house or packaged apps from PeopleSoft, SAP, and others--that run on those databases.

Open-source databases move in most often for new, custom-built applications, particularly within small and midsize companies, educational institutions, and government organizations.

"Pretty much every part of the business runs on MySQL," says Corey Ostman, technology VP at LLC, an online comparison-shopping Web site. The database serves up content to the Web site, which gets thousands of hits every second during peak times, and tallies up the clickstream data used to calculate fees paid by retailers to PriceGrabber.

PriceGrabber chose MySQL when the company was started in 1999 because the database was easy to manage, Ostman says. He had worked with Oracle's database and says it needed constant attention and tuning. And while cost wasn't a major factor in the initial decision to use MySQL, Ostman says it doesn't hurt that its maintenance and support from MySQL AB costs "thousands of dollars per year rather than [the] hundreds of thousands of dollars per year" a commercial database would cost.

MySQL finds its way into some big companies. Sabre Holding Corp. has 45 Linux-based servers running MySQL databases to give travel agencies fare and seat-availability information. But the heavy-duty job of calculating prices and processing reservations remains on an HP NonStop transactional database.

While the roster of open-source databases includes PostgreSQL and Berkeley DB, the momentum is rolling in favor of MySQL, which has more than 4 million installations.

Doug's Impromptu Survey

A customizable online survey using Perl, Apache, and MySQL.

Some new scripts:

O'Reilly Open Source Software Convention 2002

Brian Aker, OSDN

Track: Databases
Date: Friday, July 26
Time: 1:45pm - 2:30pm
Location: Grande Ballroom B

Do you find yourself in the position of having a database that has a lot of data and a need to extract quantitative information from it? Or do you find that in some cases selecting large amounts of data is not practical. Large data sets can be costly too remove from a large database. When you think of these types of needs you think of data warehousing. From there you start thinking about big machines with large and costly licensed commercial databases. MySQL offers "user defined functions" which gives you the ability to extend MySQL for data warehousing needs. Through these you can extend MySQL to do complex analysis inside of the database. This is not limited to traditional character and number data, exotic binary data like images and music can be analyzed as well.

Examples will show how you can take well known Open Source libraries like Image-Magick and use these inside of MySQL.

Additional examples will be shown using myPerl. With myPerl you can even bypass the need to learn the internal structure of MySQL UDF structure and instead use a popular and easy to use scripting language like perl.

Download presentation file

MySQL Software - MyPerl

Basically this creates a poor man's stored procedure for MySQL using perl. You can store perl in a column (or just pass it directly to the myperl function). A sample call would look like: mysql> select myperl("$MYSQL_MYPERL_VALUE = 2*3");

New for this version:
You can now specify an integer for the first parameter if you need something longer then 255 characters.
Perl Interpreter is not longer created for each connection!
Other random cleanup

Index of -docs-pgsql-src-pl-plperl

PL/Perl allows you to write PostgreSQL functions and procedures in
Perl.  To include PL/Perl in the build use './configure --with-perl'.
To build from this directory use 'gmake all; gmake install'.  libperl
must have been built as a shared library, which is usually not the
case in standard installations.

Consult the PostgreSQL User's Guide and the INSTALL file in the
top-level directory of the source distribution for more information.

PostgreSQL Building and Installing PLPerl - PostgreSQL Manual - PHP Freaks

25.2. Building and Installing PL/Perl

If the --with-perl option was supplied to the configure script, the PostgreSQL build process will attempt to build the PL/Perl shared library and install it in the PostgreSQL library directory.

On most platforms, since PL/Perl is a shared library, the libperl library must be a shared library also. At the time of this writing, this is almost never the case in prebuilt Perl packages. If this difficulty arises in your situation, a message like this will appear during the build to point out this fact:

*** Cannot build PL/Perl because libperl is not a shared library.
*** You might have to rebuild your Perl installation.  Refer to
*** the documentation for details.

If you see this, you will have to re-build and install Perl manually to be able to build PL/Perl. During the configuration process for Perl, request a shared library.

After having reinstalled Perl, change to the directory src/pl/plperl in the PostgreSQL source tree and issue the commands

gmake clean
gmake all
gmake install

to complete the build and installation of the PL/Perl shared library.

To install PL/Perl and/or PL/PerlU in a particular database, use the createlang script, for example createlang plperl dbname or createlang plperlu dbname.

Tip: If a language is installed into template1, all subsequently created databases will have the language installed automatically.

SANS InfoSec Reading Room - Securing MySQL Server on FreeBSD 4.5

[Jun. 26, 2003] MySQL FULLTEXT Searching

Have you ever wanted to search text stored in your database, but couldn't figure out how to do it efficiently? Are you lazy like me and don't enjoy maintaining reverse indexes, dictionaries, and word scores? You're in luck. The release of MySQL 4.0 has made searching text stored in databases available to the masses.

MySQL has had FULLTEXT searching in one form or another since version 3.23.23. FULLTEXT indices in MySQL allow database administrators and programmers to designate any character-based field (CHAR, VARCHAR, or TEXT) as a FULLTEXT index, which allows for complex text searching against data stored in those fields.

This feature is not to be confused with the LIKE function in MySQL. LIKE works more along the lines of a regular expression. On the other hand, FULLTEXT indices are fully indexed fields which support stopwords, boolean searches, and relevancy ratings.

MySQL Sessions

Check out the exciting MySQL sessions at this year's Open Source Convention including A Guided Tour of the MySQL Source Code and Benchmarking MySQL Queries.

This article assumes you have a working installation of MySQL, a good understanding of how MySQL works, and a basic understanding of web programming (with PHP, Perl, or something similar). Further, this article may not be of any interest to those who are already using FULLTEXT indices in a production environment.

How it Works

The MySQL team has made it extremely easy to add FULLTEXT searching to your tables. They are created much like regular KEY and PRIMARY KEY indices. For the purpose of this article we are going to make a basic blog table, put some data into it, and start searching. Before we get ahead of ourselves we need to create some tables.

Creating the tables

-- The main blog table with our FULLTEXT index
-- Nothing extreme here, but you get the idea
CREATE TABLE blog_entries
  title CHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (entryID),
  KEY (posted),
  KEY (categoryID),

-- A category table so you can organize your posts and 
-- later do some fun searching based on such data.
CREATE TABLE blog_categories
  name CHAR(75) NOT NULL DEFAULT '',
  PRIMARY KEY (categoryID)

-- Add some data into your blog
INSERT INTO blog_categories VALUES (1,'Personal');
INSERT INTO blog_categories VALUES (2,'Work');
INSERT INTO blog_categories VALUES (3,'Editorials');

INSERT INTO blog_entries 
        'About miester',
        'I was born in michigan in 1980 in a small town called Adrian. 
         My mother is named Sue, while my father is named Mike. 
         They currently live in a small town called East Jordan. On April 
         27th, 2003 I will graduate from Eastern Michigan University with a 
         degree in Computer Information Systems.');

INSERT INTO blog_entries 
        'Today at work',
        'While I was at work today I was having some problems
        with the RAID array. It seems that we have a rogue cron script that 
        is causing problems. When I find out more info I will post it here.');

INSERT INTO blog_entries 
        'After I graduate I am taking a 2 week vacation. On my 
         agenda is a trip to Washington DC to see my girlfriend\'s sister 
         as well as throwing a few discs at the local disc golf course.');

INSERT INTO blog_entries 
        'I have had a horrible cold for the last few days. Today I drank a
         revive vitamin water with 150% of my daily dose of vitamin C. That
         should help things out.');

Querying the Data

Now that we have data in our tables we can begin to query it. There are some restrictions to FULLTEXT searching, which are covered below. You will want to read over the restrictions before you use FULLTEXT indices in a production environment. For now we are going to do a simple query for the word mother.

mysql> SELECT entryID,title
    -> FROM blog_entries
    -> WHERE MATCH (title,entry) AGAINST('mother');
| entryID | title         |
|       1 | About miester |
1 row in set (0.00 sec)

There are a few things to note when querying FULLTEXT indices. First, MySQL automatically orders the results by their relevancy rating. Second, queries that are longer than 20 characters will not be sorted. Third, and most importantly, the fields in the MATCH() should be identical to the fields listed in the table's FULLTEXT definition.

All other MySQL syntax works as you'd expect with a FULLTEXT search, meaning you can further limit your search terms. We could search blog entries based on posting date or category. If you let your imagination wander you can think of all sorts of ways to filter your data. Let's look for blog entries that only appear in the Personal category and match the term michigan.

mysql> SELECT E.entryID, E.title,
    -> FROM blog_entries AS E, blog_categories AS C
    -> WHERE E.categoryID=C.categoryID AND
    ->       MATCH (E.title, E.entry) AGAINST ('michigan') AND
    ->       E.categoryID=1;
| entryID | title         | name     |
|       1 | About miester | Personal |
1 row in set (0.00 sec)

Note that we not only did a join but also filtered the results based on the category. Another thing to note is that FULLLTEXT indices are not case sensitive. If you would like to use MySQL's relevancy rating in your code you can add the MATCH() ... AGAINST() clause to your SELECT statement as well.

mysql> SELECT E.entryID, E.title,,
    ->        MATCH (E.title, E.entry) AGAINST ('michigan') AS score
    -> FROM blog_entries AS E, blog_categories AS C
    -> WHERE E.categoryID=C.categoryID AND
    ->       MATCH (E.title, E.entry) AGAINST ('michigan') AND
    ->       E.categoryID=1;
| entryID | title         | name     | score           |
|       1 | About miester | Personal | 1.2635315656662 |
1 row in set (0.00 sec)

Boolean Searches

Probably the most anticipated feature in MySQL 4.0's FULLTEXT is the ability to do boolean searches without having to process the query strings. This means you can add +s and -s to your queries, along with a host of other commands, and MySQL will interpret them for you.

mysql> SELECT E.entryID,E.title,
    -> FROM blog_entries AS E, blog_categories AS C
    -> WHERE E.categoryID=C.categoryID AND
    ->       MATCH (E.title,E.entry) AGAINST ('+vacation -washington' IN BOOLEAN MODE) AND
    ->       E.categoryID=1;
| entryID | title     | name     |
|       4 | Vacation! | Personal |
1 row in set (0.00 sec)

We have two entries with the word vacation in the title, but since we removed washington, entryID 4 does not show up in the result. You can read all about IN BOOLEAN MODE on MySQL's FULLTEXT manual page.


A few restrictions affect MySQL FULLTEXT indices. Some of the default behaviors of these restrictions can be changed in your my.cnf or using the SET command.

Altering FULLTEXT's Default Behavior

There are several ways to alter the default behavior of FULLTEXT. MySQL has some tips for fine tuning the FULLTEXT search, but the details are a little sparse. The most common problem is the four character minimum word length on queries. Before we go over that, let's review the variables associated with the FULLTEXT searching.

| Variable_name            | Value          |
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_min_word_len          | 2              |
| ft_max_word_len          | 254            |
| ft_max_word_len_for_sort | 20             |
| ft_stopword_file         | (built-in)     |
5 rows in set (0.00 sec)

The variable we wish to change is ft_min_word_len. According to the manual we should be able to change this via the SET VARIABLE command, but, in reality, this does not work. After asking the mailing list about this problem I was told this had to be specified as a startup option. To change the minimum query string to three characters, start the MySQL server as follows.

$ cd /path/to/mysql
$ ./bin/safe_mysqld --user=mysql -O ft_min_word_len=3 &

After you have restarted your MySQL server (don't forget to change your startup scripts) you have to rebuild the FULLTEXT indices. The manual suggests the following command:

-- Replace tbl_name with the name of your table
mysql> REPAIR TABLE tbl_name QUICK;

After you have rebuilt your indices, you should be able to search with query strings of three or more characters instead of the default four character limit.

Having fun with MySQL FULLTEXT Searching

I'll save the implementation details for a later article, but here are some interesting ways in which you could use MySQL FULLTEXT searching to finding data on your website more interesting.


A great way to add cross referencing to articles would be to store a query (i.e., linux for a post about Debian GNU/Linux) with each article. If an article had a query, PHP could then perform a FULLTEXT search, returning those results as "Related Articles". Furthermore, you could use PHP to create an advanced search script that allowed users to search the database based on category, criteria, pricing, etc.


ispell is a great tool for open source developers to use to make their applications more user friendly. By adding ispell to your search engine, you can check the spelling of each query, offering alternate queries if the query was spelled wrong. Everyone's favorite search engine does something similar.

Looking towards the Future

According to the manual, the MySQL team has a lot they still want to implement into FULLTEXT searching. Here is a brief overview of those enhancements:

The proximity operators will really make FULLTEXT searching impressive. This will allow you to do searches on words based on how close together they are. For example, if you currently searched for 'mysql search' you would get results even where mysql and search appear at opposite ends of the document. With proximity operators, the scoring algorithm gauges how close together the words are. Documents where mysql and search appeared directly next to one another would score higher than documents where they were not close together.

Stemming is a great way to make search engines smarter. This would allow MySQL to search for words that share the same lexical root. For example, queries for running would return documents with ran and run as well as running.

[Jun 13, 2003] Are Open Source Databases Following in Linux' Footsteps By Clint Boulton

At a partnership announcement with Dell Computer in New York City back in April, Oracle (Quote, Company Info) CEO Larry Ellison stood alongside his fellow CEO Michael Dell and discussed how the Linux open source operating system was stealing market share from Microsoft's Windows products.

In the future, he asserted, Microsoft would be all but irrelevant by the Linux trend. The crowd nodded and smiled at his screed. This, you could almost hear them thinking, was Larry at his best.

But then someone in the audience took the flipside to his thesis, asking Ellison: If Linux, in all of its open-source glory, would irreparably harm Microsoft's proprietary approach, could he not see the threat of open-source database vendors to his company? Ellison said no, and then enumerated Oracle's market strength, brand, track record and high level of security as reasons for Oracle's staying power.

Few can dispute those points. Oracle, which specializes in selling to high-end customers, is still a $10 billion company at the top of the database market. In the most recent calculations from research firm Gartner, Oracle retained a whopping 43 percent of the overall relational database market. IBM and Microsoft, which also cater to small and mid-sized businesses with their database products, are the other two most successful vendors in the vaunted space, with shares of 24 and 17 percent, respectively. It should be noted that IBM, which bumps heads with Oracle often in the quest for large customer contracts, has posted impressive gains in the database sector, and led during the last quarter in terms of new, licensed users.

But while commercial database vendors remain locked in a lunge and parry fight for customers, in the background is a small movement in which providers are open-sourcing their database as a less expensive alternative to the ingrained vendors. This open-source movement, which some experts predict will follow a similar arc to Linux, is led by a Swedish firm that has been quietly building momentum as an alternative in the market for small business, departmental or commodity use: MySQL.

Industry opinion is divided as to whether MySQL or another of its ilk might challenge Oracle, Microsoft or IBM at the low end of the database market, where fewer specialty features are needed.

IDC analyst Carl Olofson summed up open-source databases technology: "Open source relational database management system (RDBMS) software is a disruptive technology. As such, it is used, not to address the high end requirements of those who demand the latest and greatest, but to address the needs of application and tools developers and vendors who are looking for just enough database technology to provide an affordable solution."

This is certainly comparable to Linux, which has enjoyed growth propelled by many major vendors, such as IBM and Oracle, as well as myriad Linux providers like Red Hat and SCO. These firms have pumped many dollars and resources into the movement to provide an alternative to proprietary systems such as Unix and Microsoft's Windows operating system. Isn't it imaginable, therefore, that open-source databases might follow the same path? After all, Linux started as a small movement, too.

MySQL's value proposition (contrary to Oracle, Microsoft or IBM) is to provide the code for its MySQL database for free, under the free software/open source GNU General Public License (GPL) or a non-GPL commercial license, which they make money from through service fees. And it's working.

In the spirit of "If you build it, they will come," MySQL has been building and users have been coming: MySQL has seen an estimated 4 million installations and over 30,000 downloads per day for its flagship product. The company counts Yahoo!, Lucent Technologies, Sony Pictures Digital Entertainment among its customers. At a time when investor dollars are scant, the firm this month landed $19.5 million in funding, led by Benchmark Capital, and appointed one Benchmark general partner Kevin Harvey to its board.

MySQL versus the big three

Impressive credentials and support? Perhaps. But why go to an open-source database when Oracle 9i, Microsoft SQL Server and IBM DB2 are held in such high regard? In short, for the same reason why businesses are moving to Linux platforms: to save on total-cost-of-ownership, among other areas. The importance of this cannot be understated in this weak economy. Gone are the days when CEOs were looking to pad the top line; here are the days where the CEOS scrutinize how much cash they can shave off the bottom line.

So, consider a few options from Oracle, Microsoft and IBM. Oracle offers two main packages, an enterprise edition geared for high-end use at $40,000 per processor and a standard edition for $15,000 per processor, which is targeted for small- and medium-sized businesses (SMBs). Similarly, Microsoft has a standard edition of SQL server for $4,999 per processor, and an enterprise-class offering for $19,999 per processor. IBM's DB2 portfolio features an enterprise edition for $25,000 per processors, and a workgroup edition for $7,500 per processor (comparable to Oracle and Microsoft's standard editions).

By contrast, MySQL offers its database on a much lower scale and cost to the casual user. MySQL CEO Marten Mickos discussed his company's value proposition and licensing model in a recent interview.

"We are what we call a 'second wave open source' company, meaning we have a functioning business model that is in harmony with our free software principles," Mickos said. "Our dual licensing allows us to offer our software free of charge under the GNU General Public License (GPL) while at the same time selling the same product under a regular commercial license. We can do this because we own our software and have the freedom to license it any way we wish."

Olofson explained where open-source database technologies, such as those provided by MySQL, and two of its brethren, PostgreSQL and Firebird, fit into the market.

"Although the huge majority of MySQL usage today derives from free open source downloads, MySQL hopes to move from the geek community to the business community through the embedded database model, and they are counting on this channel as a key to future revenue growth," Olofson said.

Mickos, who said the company's goal is to "make superior database technology available and affordable to all," said commercial licenses account for nearly two-thirds of its revenues. The remainder of the company's earnings come from support and services. MySQL charges a flat fee of $440 per server and customers can have as many users and as many CPUs as they want.

"Put this in context of the pricing of other DBMSs [database management systems] and you can understand why we sell so much," Mickos said.

Now, given the price comparisons between MySQL and the other, strictly commercial vendors, why isn't MySQL grabbing up market share by the fistful? Does MySQL not threaten the three giants? No, and Mickos and his firm don't claim to. There are many, many reasons for this, some of which Ellison alluded to last April. MySQL is new, much smaller than the database behemoths, and does not offer much of the special functionality of large database vendors, something that they say the high-end, more specialized customers require.

"We focus on the commoditized part of the market - the one in which performance, reliability, convenience and price are the determining factors," Mickos said. As such, Mickos said he and his outfit feel they are complementary to say, Oracle, or IBM, who offer some highly specialized features, as opposed to competitive. In practical terms, while Oracle may power the high-performance needs of a search firm, certain departments might use MySQL as a simple alternative.

What the commercial vendors say

It's tough to find members of the commercial side of things that would argue with Mickos. Jeff Jones, director of strategy data management at IBM, praised MySQL and its brethren, noted that they are commanded by smart people with some great ideas. But he still sees the pros of the commercial side.

"I think you can argue there is a focus and a clarity of requirement to the commercial side," Jones said. "IBM has been working with databases since the '70s and things are done differently. There is a different degree of ability, of relational theory, for what we do. We have to bring scalability optimization, we have to understand how to extend relational theory to handle things like XML, to extend search technology, integrate more analytic capability, federate integrated business intelligence."

Interestingly, IBM has a small database offering that can slide into the low-end slot alongside MySQL, in the recently released DB2 Express. Geared for the low-end market, or a handful of seats, DB2 Express is priced at $499 per server, or $99 per user.

Ken Jacobs, vice president of product strategy at Oracle, shares Jones' feelings about the main differences between the open-source database players and commercial vendors of today. But, like Ellison, he suggested that it is Microsoft that has the most to lose from open-source, arguing that the evolution of open-source databases like MySQL could chomp away at Microsoft's low-end market share.

"Microsoft may be the real target or victim here," he said. "When people make decisions to purchase a database one of the deciding factors to go with open-source is that it is cheap." Admitting that Oracle and IBM's products are not as cheap because they target customers with more specialized needs, Jacobs said any cannibalization from low-end products is likely to be in Microsoft's camp. Jacobs said Oracle does not feel threatened by MySQL, and, like Mickos, said he sees it as a complementary product. "These DBs are interesting and can serve a niche. In fact, I'd argue that MySQL customers are also Oracle customers who have outgrown SQL Server."

Sheryl Tullis, product manager for SQL server at Microsoft, begs to differ. First, she disputed the notion that that SQL server's success is tied to SMBs, as many people suggest. "In the recent Gartner figures, SQL server grew 16.8 percent, Oracle declined 20.5. That shows customers are seeing value in SQL Server."

"We think [open source databases] are a different segment than who we sell to," Tullis said. We see MySQL as being primarily for hobbyist technologies, or departmental use. We see it as something a developer might use if they do not want to go through IT procurement."

Tullis said if Microsoft's customers do need a lightweight database, Microsoft offers MSDE, or Microsoft SQL Desktop Edition, and embeddable engine developers or ISVs can use as a datastore. And it's free. Moreover, she argued that Microsoft has actually learned, through MySQL, how to create a "database community that is interesting and passionate."

One of the knocks on open-source databases is they don't have some of the features large-scale servers do, and are therefore not attractive to customers. Meta Group analyst Charlie Garry has heard the arguments about MySQL and its brethren not having the special functionality or scalability of a DB2 or a 9i. He has a counter argument.

"There is a line of thinking that Oracle overshot the market by having too much feature functionality," Garry said. "One is XML databases. There is not a whole lot of people who are storing data in XML format."

"Now, there's an interesting feature I would guarantee, most people have never even heard of it or knew it was in 9i," Garry said. Then there are things like a partitioning option. More than 60 percent of enterprises are running on 4 processors or less and 50 percent of the resources are unused. A lot of people don't need partitioning, or 64-bit indexing that are offered by commercial vendors, so these are not necessarily things that people want to pay for."

"The beauty of open-source databases is that that we often see a cycle among commercial vendors, where they to continue to add feature functions to trump that other guy," Garry said. "Open-source databases are forcing users to think what is important to me, and think about right-sizing. When you're a tech leader like Oracle, you run into this rut where you have to drive revenues, listen to investors who tell you need these esoteric features."

Slashdot Revisiting FreeBSD vs. Linux for MySQL

"Jeremy Zawodny, who looks after all of Yahoo!'s MySQL servers says MySQL now runs very well on FreeBSD. He is no longer steering people toward Linux. There are two important things you should do to make the FreeBSD/MySQL combo work well: (1) build MySQL with LinuxThreads rather than FreeBSD's native threads, and (2) use MySQL 4.x or newer."
Pathwalker (103) * <> on Saturday May 24, @03:33PM (#6031858)
( | Last Journal: Friday May 23, @05:20AM)
Personally, I like using "cd /usr/ports/databases/postgresql7 && make install" rather than using a binary package, as that way you have the source code on your system, and you can use all of the wonderful things in the contrib directory.

tsearch is a very nice GIST indexed full text search that does word stemming.
reindexdb is a handy way to regenerate all of the indices in a database without interrupting anything.
earthdistance is far faster than writing the same thing as a SQL function.
ltree is wonderful when you have to deal with a hierarchy.

And those are just the ones I remember offhand...

Re:or (3) pkg-add -r postgresql7 (Score:5, Informative)
by TheRaven64 (641858) on Saturday May 24, @09:39PM (#6033224)
( | Last Journal: Sunday May 25, @08:26PM)
Up to a point this is true. However MySQL does win on one point, namely that if you do not need most of the features of a real RDBMS then it is faster than Postgres. If you are just using it as an information store for a web site, for example, then it may be a better choice. As always, right tool for the right job.
[ Reply to This | Parent ]

Chapter_8 Data Mining and Statistical Analysis Using SQL A Practical Guide for DBAs

PostgreSQL The most advanced Open Source database system in the world

PostgreSQL101 -- quick start for Linux/FreeBSD users

Building a Database-Driven Web Site Using PHP and MySQL - interviews - PHP Help

An interesting interview with Michael "Monty" Widenius is the designer and lead programmer for the MySQL database. Some little known facts...

I know that MySQL really isn't made to work specifically with PHP alone, but since they're so popular together do you think there might be some type of collaboration in the future? And if so, can you give us any details? If that question doesn't make any sense, do you like beans?

Yes, we have are very interested in a close collaboration with the PHP people. In fact, we already work closely with them to ensure that the MySQL interface that is included with the latest PHP versions are kept up to date. We are also adding new functionality to MySQL that will help PHP users:

We are adding stored procedures (with the ANSI SQL 99 syntax) to MySQL. When this is done we will add support for PHP inside MySQL (as an alternative stored procedure language).

I can eat beans, but prefer to not eat them every day...

isolti's question:
Hi Monty, I like your product. It works great. My question is what feature(s), if any, is missing from MySQL that is in the proprietary Oracle? What missing feature (if any) could stop somebody from migrating to MySQL from Oracle?

This depends on lot of how your application is written. My belief is that MySQL's current functionally is good enough to make it possible to move a substantial portion of all new database applications to MySQL without any major difficulties.

The major difficulties come with old legacy applications that use either a lot of Oracle specific features or some of the ANSI SQL features that MySQL is still missing.

The biggest stumbling blocks are sub selects and stored procedures. Some applications (but much fewer) are also very dependent on views and foreign keys.

In the MySQL 4.1 source tree we already have working sub selects and we have three guys working on adding stored procedures to MySQL (for MySQL 5.0). Full foreign keys support is also planned for 5.0. In MySQL 3.23 and 4.0 we have only support for foreign keys for InnoDB tables.

When the above accomplishments are met, it will be even easier to migrate from traditional databases, like Oracle, to MySQL.

If you are depending on clustering (where replication is not good enough) or some of the more exotic capabilities of a database like Oracle, then it will still take some time before you can easily migrate to MySQL. Fortunately this is not true for most applications.

In practice we hear every day of people who are migrating from Oracle and other SQL databases to MySQL with good results and we are working hard on our side to ensure that this trend will continue.

webhappy's question:
Considering your long experience in designing databases, what do you consider the most challenging aspect? What parts were the most fun? In addition, because database software usually involves squeezing the best performance, what aspects of computer science do you feel are the most important in developing a next-generation database suite? And finally, do you think designing a database (of course, not something as full-featured as mySQL) could be a fruitful computer science project? :)

The most challenging aspect is to get the code bug-free for a lot of users that use the database in ways that you could not have even imagine when you wrote the code. Apart from that, to get the SQL optimizer to do right in 'most' cases is a challenging task that one could work on for several lifetimes:)

Most fun is the feedback you get from enthusiastic MySQL users who are successfully using your product in different ways and are impressed with how well it works.

The "science" that I find most important in developing databases is to understand how to get things to work in really small systems and design algorithms that are scalable from these to large systems. Unfortunately this is not something that is well known and only people who have had first hand experience with programming on 16K machines (or less) know this by heart.

Yes, designing a database can be a very fruitful computer science project; What can be equally fruitful is to take one of the open source databases (like MySQL :) and extend it to do things that it could not do before. This way you have not only done something that is good for yourself but also something that other people can enjoy and benefit from.

Jesse's question:
You have an interesting business model. Most companies that give away a product haven't been doing so well. I am interested to know where more of your revenue stream comes from, pay for support or paid (non-GPL) licenses? On the same note, what tips would you have for someone pursuing a similar business model?

For the moment the biggest part of our income comes from license sales of the non-GPL licenses, but support and training also provides good income to MySQL AB.

We have found that the GPL license works extremely well if you have a library that you can get very widely used and that is extremely useful to embed in a lot of commercial applications. A database server (MySQL), a language engine (Zend) or a GUI widget kit (QT) are good examples of this. It's not that good for standalone applications that people don't have a reason to embed in their products.

The tip I have is if you create an application and release it as GPL with the intention of selling commercial licenses for a non-GPL version, you should start giving a lot of free support to everyone that uses it and actively help people use and spread it.

I believe that you should NOT later change your license to a non-open source/free software license or have two different versions of the product --one (with less functions) for GPL users and a more featured version for paying customers. It's always better to be fully open source/free software compatible than try to create a hybrid.

imerilai's question:
Do you have plans for seriously developing a GUI for MySQL administration and SQL development? Something like MyCC, but much more functional? Or is your strategy focused on developing the core server and leaving GUI development for third parties?

Our current strategy is to work full time on MyCC and have this as a framework for all future GUI applications.

The idea with MyCC is that anyone should be able to create dynamically loadable 'plugins' to enhance it the way you want.

For example, if you don't like the way the current ALTER TABLE works in MyCC you should be able to code an alternative (better?) interface for ALTER TABLE and easily replace the default functionality with this.

By having a lot of plugins -- some created by us and others created by third parties -- we hope to have all the functionality in the GUI that you could ever ask for.

dimonemon's question:
My question is development related. Are there any, or will there be some books, tutorials, etc. that explain internal structure of MySQL so that curious people like myself can analyze and study the code and understand how subsystems interrelate and work together. Thanks, Dmitriy

This is something that we would really like to see happening and we have been working on this ourselves (albeit slowly because of time constraints).

In the MySQL source distribution the file Docs/MySQL-internals.texi includes a lot of comments of the MySQL source. Whenever we feel that something within MySQL needs to be documented so that we can understand how it works, we add it to this document.

Over time we hope that the above document will grow to cover all aspects of MySQL, but for the moment the source code is the best way to learn the internals of MySQL.

Grok's question:
As any Slashdot poster would be happy to inform you, the four big features missing from MySQL are Stored Procedures, Views, Triggers and Foreign Keys. I happen to disagree with them and think the most needed new feature is better database documentation. PHPMyAdmin has special tables that allow you to do this, but it needs to be more standardized which means the tables need to ship by default. Specifically, Foreign Key documentation and descriptions of each field in a table. This would allow pretty charts of the DB structure, but more importantly would allow those who work with the table to understand more easily how it works. Those above five features aside, what do you think will/would be the most important change to MySQL?

MySQL 4.1 will allow you to store foreign keys definitions and retrieve them for all table types. (Currently we only support this for InnoDB tables). MySQL 4.1 already supports comments for each field, in addition to table comments that 3.23 supports.

Apart from the above features the most important aspect is to add all the other missing ANSI SQL 99 features without making MySQL slower.

We are working on OLAP functionality in 4.1 (CUBE and ROLLUP) and are also constantly researching new ways to make MySQL even faster for data warehousing uses.

Another important feature is the ability to emulate other database syntax to make it easy to emigrate to/from them to MySQL.

lindset's question:
What do you think MySQL's biggest advantage(s) over other Database servers are? And what do you think is the reason for the success of MySQL on the web (MySQL on the backends of sites)?

Speed, reliability and light footprint are the biggest advantages of MySQL.

From the start MySQL was optimized for web applications and when the web took off around 1998 and people started to look around for a database, MySQL was there ready to be used.

In other words, the main reason was being in the right place at the right time with a reliable product that everyone suddenly needed.

Matt's question:
What was your inspiration behind MySQL? Why did you begin development in the first place?

First, MySQL was developed as a SQL interface on an old database motor that I had been working with (and written) over a period of 15 years.

We needed something that would enable web users to get access to the data and David Axmark (a co-founder of MySQL AB) and I thought that SQL would be the best language for this.

In other words, we developed MySQL because we needed it ourselves. We released it as open source because we believed that we had created something good and thought that someone else could probably have some use for it. We became inspired and continued to work on this because of the very good feedback we got from people that tried MySQL and loved it.

andyl's question:
We are currently moving from Access to MySQL, but the one thing that is sorely missed is the reporting features of Access. I know that I can use ODBC to connect and still use Access, but I would really like to get rid of it completely and find a lower cost (and better!) solution. Are there any plans for MySQL to offer something along these lines, or do you know of anything that can do the job?

Over time, the MySQL control center (MyCC) will include most of the functionality in Access that most users need. We have a long way to go for this but as more and more developers use MyCC and add new plugins to this we hope that we we'll have something for Access users in the not too distant future.

You can find the MySQL control center on the download pages at

is a simple Perl script to sync a MySQL database from a dumpfile. For each row in the database that is different from that in the dumpfile, the differences are output and the user is asked what action should be performed. If there is a modification timestamp in the table, a suggestion is made to the user.

db2ssd - a tool for reverse engineereing database structures to uml diagrams

db2ssd ist a (no longer simple) perl-program that connects to a database, extracts it's structure and produces a diagram readable by the following programs

It is somewhat inspired by PostgreSQL AutoDoc but aims at beeing database independent (currently MySQL, Postgres and ODBC are supported at different levels of completeness)

Since db2ssd now supports DIA as well, it will be renamed to REVENGE soon (REVerse ENGineering -> REVENGE, see?). I still don't know what the last "e" might stand for :)) The internal modules are already contained in a package named Revenge.

Introduction to Xindice

Build XML-based applications with open source Xindice, a native XML database system, and see how this technology differs from relational database management systems. (Web architecture zone)

[May 22, 2002] Web sites insecure as ever

Comment: Web sites insecure as ever. By David Rae [22-05-2002]

Most, if not all, of corporate web sites are fundamentally insecure. And this insecurity can allow attackers to access databases, delete or change information, and cause absolute chaos with very little effort or technical know how.

The problem is with web applications. Back in the good old days when companies used the internet for nothing more than hosting an elaborate electronic brochure, there was no threat. The IT guys would have little to do with the process, with the marketing department taking responsibility for outsourcing most of the work to third-party web developers.

Well things have changed. The level of interaction through corporate sites is overwhelming, and web applications allow this interaction to take place - whether it be shopping carts, authentication services or money transfers.

Whatever interaction takes place, information on the database has to be accessed. The commands coming from the web site are seen by firewalls and intrusion detection systems as bona fide - SQL commands are seen as legitimate.

Unfortunately, attackers can use this level of trust to their advantage by sending dubious requests to a database. The firewall and IDS will see the request as SQL, and let it through, but it is extremely unlikely the database will be instructed to look for 'expected' requests.

A direct SQL injection is the process of making a direct call to the database. It is frighteningly simple to carry out, and can have horrendous results for your business.

On many websites a simple SQL command will enable attackers to change such delicate information as Admin passwords and the price of goods listed in an online store. The scope for damage is limited only by the imagination of the attacker, and the strength and security of your web applications.

So what can you do about it? Short of ripping down your entire web site, and starting from scratch, securing every single web application is nigh-on impossible.

But an excellent place to start is the Open Web Application Security Project (OWASP). This was started in September 2001 by developers and security professionals to address this very problem.

Download the 'Guide to Building Secure Web Applications and Web Services' (the latest draft was released 10 days ago) and try very hard not to cry into your beer. It makes fascinating, if terrifying reading.

Being aware of the problem is the first step. It allows you to go to the board, set out your stall and say "This is what the problem is, this is why the problem occurred, this is what could happen and this is what needs to be done".

It also allows you to question web developers and any third-party security consultants you choose to hire, with confidence and knowledge of the problem.

OWASP is also working on an Open Source tool called Web Scarab that will test the security of your web applications. The group hopes to have the software completed within six months and it will be able to test a complete web site, or individual applications for security vulnerabilities.

Comment on this story

WODA Administrator's Manual

WODA is a standalone database management system for the World Wide Web. It consists of a Perl program e.g., which contains WODA engine. Each individual database application is defined in the database definition file (definition). This file defines the data dictionary, customizes the user interface and then calls the engine for all the processing. It uses two hashes to do so:

One engine therefore supports several databases and is entirely database independent. All operating system dependent parameters are defined within the engine. There are a few language specific engines such as or for the English (uk) and the Slovenian language (si). Friends on the Internet also created translations into other languages. So can you.


Perl module for transferring between XML documents and relational databases.

Development Status :: 5 - Production/Stable [filter]
Environment :: Other Environment
Intended Audience :: Developers
License :: Public Domain
Operating System :: OS Independent
Programming Language :: Perl
Topic :: Database :: Database Engines/Servers
[filter], Database :: Front-Ends [filter] Category Reviews - Non-SQL Databases for Linux

The best-known databases these days are based on SQL, but are often overkill for what you need to do. This review discusses lighterweight alternatives, including xBase, DBM, and ISAM systems. Editorials - You Say You Want a Revolution (or Dude, Where's My Database)

Remember those heady days of the mid-to-late 1990s? When Webmonkey was required daily reading, Hotdog and HoTMetaL Pro were the "cool" HTML editors (though vi and Notepad predominated), and the Browser Wars were relevant? When virtually all Web sites were collections of static pages, even if generated via some dynamic process(es) such as VB widgets or Perl scripts? When Perl CGIs and .shtml pages were the only true approximation of dynamic Web development available? When Java still meant coffee, ASP was a snake, and Linus was a character in the Peanuts cartoon? Remember? Ah, the bad old days.

Today, things on the Web are unbelievably different. Reflecting the entire culture of the Internet, the Web is today a collection of mostly dynamic Web pages. From the high-traffic sites running on custom or packaged software (such as Vignette Story Server or Future Tense), through the smaller business sites running off Cold Fusion/Access, to individual sites running off Blogger or similar packages/services, dynamic pages are overtaking static pages at an increasingly rapid rate.

[Feb 23, 2002] MySQL and Perl, the marriage of convenience

MySQL and Perl have been around for quite a while. They are still widely used even if the "fashion" is changing. This article talks about these two products working together as a whole, either on the Internet or on your local network. The provided example is written for Unix systems, free or not, even if it can be adapted to other widespread "systems". What this article is : a small review of what can be done with this pair, showing ease of use, speed, reliability, security...

What this article is not : neither a MySQL tutorial nor a Perl tutorial; neither a MySQL review nor a Perl review. Accordingly, we'll see MySQL at work in combination with Perl, without forgetting that "there is more than one way to do it".

[Aug 23, 2001] LinuxPR: Great Bridge releases integrated open source platform

Great Bridge did not last long...

"Great Bridge LLC, the leading provider of solutions based on the PostgreSQL open source database, today announced the release of Great Bridge WebSuite, an integrated open source platform for building high-performance Web-based applications. Great Bridge WebSuite integrates the enterprise-class Great Bridge PostgreSQL database with the popular PHP scripting language and market-leading Apache Web server to shorten application development cycles and accelerate time to market.

Great Bridge has packaged the WebSuite release with a graphical installer, leading administration tools, professional documentation and an installation and configuration support package to help application developers quickly and easily deploy the power of PostgreSQL for demanding business processes.

"We've seen tremendous interest in the open source environment from businesses. We want to fuel that interest by providing a ready platform for rapid development and deployment cycles of large-scale, database-driven Web applications," said Robert Gilbert, Great Bridge's president and chief executive officer. "WebSuite integrates the best open source technologies for the Web into a single release to shorten development cycles and speed time to revenue."

[Jul 1, 2001] Linux Gazette: PostgreSQL's Multi-Version Concurrency Control

Joseph Mitchell outlines a way that PostgreSQL users can work around the lack of a pure "no-locking" capability.
Quite a few people these days use Oracle in some capacity to store data
for applications. Anyone who is not an Oracle DBA (database
administrator), but needs to occasionally look into the database to see
whether data is being stored properly, is likely using sqlplus.

To set up environment variables to make access to the database easier,
many people will "su" to the Oracle user account (usually just
called "oracle"). Another way to do this is by putting the environment
commands into a file and source it.


    stty erase \^H
    export ORACLE_BASE

    export ORACLE_HOME

    export ORACLE_SID

    export PATH

    export LD_LIBRARY_PATH

    alias db='sqlplus user@mydb'

If you were to store a set of commands like this in a file and call
it "ora", then you could source it with ". ora" or "source ora" when
you need to check the database. In fact, if you're running more than a
single database instance, then you could set up a file for each of them.

Let's look at our environment after sourcing the file:

    boson% env



Now that our environment is set up, we can start up sqlplus with the db
alias established at the bottom of our ora file. The db alias contains
the name of the user we want to log in as and the instance name that we
want to work with.

Either of the two approaches to gaining access to Oracle (using
the "oracle" user account or logging in after sourcing your own file
will require you to enter a password. One of the benefits of setting up
your own file is the tidy way in which we can set up aliases for what
may be numerous database instances.

The "stty erase ^H" command depends on the particular terminal type you
are using. When in doubt (and when the current setting doesn't
backspace properly for me), I enter "stty erase" and then hit my
backspace key. I can then put whatever shows up my screen (e.g., ^?)
into my file.

I find setups like this are particularly useful when I find myself
working on numerous systems and need to keep track of a minimal amount
of information for each of them.
Checking Oracle with the tnsping Command
By Sandra Henry-Stocker

Two weeks ago, this column described the commands for setting up an
Oracle user environment. By capturing such things as the Oracle home
directory and Oracle instance into a file, you can easily adopt an
environment that makes using sqlplus and issuing SQL commands easy.
Just knowing a small number of SQL commands allows you to troubleshoot
a database connection and determine whether a database server is
running and managing data correctly.

An important file that you might also want to get to know is the
tnsnames.ora file. On a client, this file describes the various
databases with which the client may interact; on a database server, it
the databases or "instances" that the server supports.

The tnsnames.ora file, which you should find in the directory defined
by $ORACLE_HOME/network/admin, will have look something like this:

            (PROTOCOL = TCP)
            (Host =
            (Port = 1521)
        (CONNECT_DATA = (SID = DB01)

The parentheses should balance (i.e., an equal number of open and close
parentheses) but the way the parameters are broken across lines is a
matter of style. The parameters that you will be most interested in are:

    * Host ? The IP address or fully qualified domain name of the
      Database server;
    * Port ? The port where the Oracle server should be listening for
      database requests;
    * SID ? The name of the Oracle instance.

The tnsping command can then be used to indicate whether a listener is
active on behalf of a database. Once you've set up your Oracle
environment (as described in the earlier column), you can issue a "ping
" command. If there are no problems, then you should receive
a response that says "OK" followed by the time required for the

    % ping DB01
    Attempting to contact(ADDRESS=(COMMUNITY=DB01) (PROTOCOL=TCP)
     (Host= (Port=1521))
    OK (30 msec)

tnsping is especially helpful when you are unsure whether you are
connecting properly to a database. The netstat command may only show
you an ESTABLISHED connection:

    boson% netstat -anP tcp | grep 1521   8760  0  8760  0  ESTABLISHED

The tnsping command, however, might indicate that the database is not
listening or that it cannot resolve the tns service, indicating that
it's time to check your tnsnames.ora file to ensure you are specifying
the correct information or talk to your Oracle database administrator
(DBA) to determine if there are any problems on the server.

To learn more about Oracle administration, you might want to check out
the slideshow available at Though this
slideshow addresses Oracle Database administration on Linux, it
applies, more or less, to Oracle on any Unix system.

If you work with systems that support or make use of Oracle, then
you'll likely benefit from knowing enough about database administration
to determine whether your databases are functioning properly.

[Jun 1, 2001] I/O Considerations for Database Performance by Jim McKinstry

McKinstry discusses various aspects of I/O that should be considered when designing a system to run a database.

[Apr 04, 2001] Open Source Databases Won't Fly by Nandit Soparkar (a faculty member at the University of Michigan).
Read the article carefully. It provides no specific examples, no example of production environment scenarios, no feature comparisons. Only sweeping generalizations. Postgres and MySQL, they can be awfully powerful in the skilled hands. Open source database may not be able to compete against Oracle and Informix on high end mission critical applications (like a database for an airport or bank accounts) but for a regular website MySQL and PostgreSQL can be quite competitive. Also there is some overlap between file systems and databases. Reiserfs looks like it is being extended to be a database as well as a file system...

... ... ...

Now certainly, you will not disagree that commercial databases have been wildly successful and time-tested. And in doing so, various problems have been recognized and solved or resolved (eg consider data independence). While open source systems are far newer and lithe, one still encounters problems in using them; eventually, they too will come to be similar in spirit to the commercial efforts: old wine in new bottles, or vice versa? And then there is the issue of installed base of legacy systems.

Significant is my lament as regards the lack of rigor in coding and programming. While there are relatively few well-accepted coding standards, at least the importance of issues such as careful documentation, checking for the logic in the programs written, maintenance of the versions, all contribute to errors.

... ... ...

Having said all of the above, let me also mention that I believe open source database systems will serve to provide the means to run pilot projects, teach people, as well as for relatively small projects. They will be wonderful in these cases, where one would not want to use a heavyweight commercial system: where a flyswatter would work well, a cannon would be disastrous Besides, it would be vastly cheaper-it always boils down to the money, I am afraid. And now, I must find myself a bomb shelter…

Ganesh Prasad - Subject: Wrong, and for the following reasons ( Apr 17, 2001, 01:02:42 )

A superficial glance at the relative market share and technical features of Open Source vs. commercial databases today may lead one to the hasty conclusion that commercial databases will continue to rule. However, there are many reasons why the tables could be turned in as little as 3 years.

These reasons (and they are mutually reinforcing) are progress, adequacy, economy and network efficiencies. Let's analyse them separately first.

Progress: Remember the arguments against Linux two years ago, before kernel 2.2? Linux wasn't scalable beyond two CPUs, it didn't support large files or large amounts of RAM, it didn't have a journalling filesystem, it didn't support USB, etc., etc. Most analysts then wrote off Linux for those reasons. What do people think now?

Similarly, PostgreSQL doesn't support replication, its query support isn't as extensive as Oracle's, PL/pgSQL isn't as rich as Oracle's PL/SQL, it doesn't have very good graphical administration tools, etc., etc. Do you think things will remain the same in 3 years? PostgreSQL 7.1, released just last week, fixed several deficiencies of 7.0 (2k row limit removed, outer joins supported, better support for complex queries, more performance improvements and better admin tools). Things will only get better.

Adequacy: It's often remarked that 90% of the market is at the low end. That's why more Toyotas are sold than BMWs, though BMWs are arguably "better". More people use products that are good enough and affordable than use the very best products. With the release of PostgreSQL 7.1 last week, PostgreSQL's claim to being "good enough" just got a lot stronger. More people will find PostgreSQL to be adequate for their requirements. Yes, Oracle or DB2 may still be "better", but how will they fare against a "good enough" product that is free?

Economy: PostgreSQL will always remain free of charge. That's not likely to be true of Oracle, DB2 or SQLServer. Those products may be given away in some special deals (sales promotion), but they're unlikely to remain free. When features converge, price becomes an important issue, and PostgreSQL has a permanent advantage over its commercial rivals.

Network efficiencies: Internet Explorer has had an enviable distribution channel, -- the Windows CD --, which has led to its ubiquity. PostgreSQL has an equally potent distribution channel -- the Linux CD. PostgreSQL will penetrate every organisation penetrated by Linux. When a good enough database comes free with your (free) OS, why hunt around for another one with gratuitous extra features that you need to pay for?

Now put these four factors together. A free database that is good enough for most users and keeps getting better, comes to you free with your OS. How long can you resist?

Prof. Soparkar's argument seems to imply that customers are fools. I respectfully disagree.

Ganesh Prasad

Nick Mailer - Subject: And in the real world.. ( Apr 17, 2001, 01:36:08 )

Well, I run a popular hosting/solutions company, and I helped hand-write our whole support/customer service/billing/invoice/cashflow database system in Perl and MySQL, and I can tell you that the system beats the hell out of the big, bloated, expensive Oracle-type solutions. It's been running brilliantly for about three years now, and we've never had one single problem with the technology in that time. Yes, maybe Postgre would be fun for having the RDBMS handle some transactioning (but, to be honest, I am quite happy to handle that at the Perl side) - but then Postgre is Free Software too :-)

So here you are, Mr Dataquest Professor Expert. I am a CEO in the Real World running a Real World Company, and I can tell you _out right_ that if Oracle paid _me_ money to run their system, I would not accept it. Huge, bloated, hidden, unnecessary - that's exactly the sort of nonsense I dispensed with when I ditched the Microsoft world (well, the X Window System excepted, but life can't be perfect ;-)

Paul - Subject: Re: Strange Attitude ( Apr 17, 2001, 02:30:09 )

> A lot of people writing this stuff are already being paid to write code. Are they not going to use the same techniques and expertise in their open source code? In fact, when you're writing because you want to and have more freedom in how you do it (instead of being management led) I would expect it to be better.

Bingo. I think you nailed it exactly, people always excel when doing something they enjoy. I think that those who write open source typically take more pride in what they do, as their names are attached.

Rupert Pigott - Subject: Re: Roach Motel... ( Apr 17, 2001, 13:10:28 )
> The same dynamic applies to MySQL and PostgreSQL. Plus, customers have the source code, so they don't have to wait for the developer to fix the bugs. The point is, don't underestimate the ultimate success of open source products.

This is why I hated "Profs" at Uni. While I was required to substantiate my arguments they'd come up with some ill-researched, unsubstantiated drivel and it would be law. Given that his vocation and job *should be* founded on substantiated agrument we have to question how he acquired his title.

For example, he FAILED to mention (I can only assume that this is due to a lack of research) that INFORMIX was originally based on Postgres (which is of course the forebear of PostgreSQL).

Obviously INFORMIX has is further down the line on this dynamic than PostgreSQL, but I don't see any reason why it can't continue to improve and acquire features just like it's commercial counterparts. PostgreSQL has already got some unique features such as the versioning system it uses for transactions, err, that's a *GOOD* innovation, as opposed to a check box on marketing literature.

Prof. Pigott (Master of Unsubstantiated Drivel)

a former closed source commecial "enterprise" level database now is open sourced. SAP DB is the database engine behind one of the world's best known enterprise applications systems is now under GNU licenses (GPL and LGPL). You don't often get more enterprise mission critical that SAP R/3.

[Jan 20, 2001] IBM developerWorks: Using Perl to Access DB2 for Linux

IBM developerWorks offers this tutorial on using Perl's DB2 components.

[Nov 11, 2000] Installing and Configuring: MySQL, Apache with SSL, PHP, and mod_perl

"...we'll assume you have a x86 computer running Debian GNU/Linux. We'll also assume you have a basic understanding of how to use a UNIX shell, and that you have superuser access to the machine. Everything in this example can be done remotely via ssh or telnet as well as locally from the machine console."

[Oct 11, 2000] Enterprise Linux Today: Leading PostgreSQL developers join Great Bridge

"Bruce Momjian, Tom Lane and Jan Wieck, part of the six-member international steering committee leading the development of PostgreSQL, will assume senior-level positions at Great Bridge."

MYSQL. - AbriaSoft - Software for An Open World

great LAMP distribution ( LAMP: Linux (operating system), Apache (Web server), MySQL (database) and PHP (scripting language).)

Abria SQL Lite provides all the tools needed to develop web-database applications in a quick and easy installation. Abria SQL Lite includes MySQL, Apache Web Server, Perl, PHP, and phpMyAdmin.

Why and How I Installed PHP4, MySQL, and Apache on Windows 98 By Edward Tanguay Originally appearing in The Web Developer's Journal.

Four months ago I was selected as Web developer for a large Web site project for six hospitals here in Berlin. It was going to be an exciting project in which I would work together with a marketing company to realize a network of Web sites. However, when we had the first meeting, the head of the computer department said, "Wait a minute. You program in ASP? We run Linux boxes here and can't justify the cost or security risk of an NT-Server." They were unwilling to budge and I didn't want to force Microsoft on them and then have to work with disgruntled system administrators, so I backed out gently and declined the contract.

Beyond ASP: JSP, ColdFusion or PHP

Since that day I've been looking for ways to extend my server-side programming skills beyond the powerful but environment-limited Active Server Pages. I concluded that I basically had three choices outside ASP for server-side scripting: JSP, ColdFusion and PHP. The first, JSP, is promising but still has a relatively small community and a lack of ISPs which support it. In addition, if you use the JSP server from Allaire it is quite expensive. The second choice, ColdFusion, also from Allaire, provides you with a powerful and robust server-side scripting environment centered around databases and e-commerce, but again, the problem is it simply costs too much. If you don't have access to the multi-thousand dollar Enterprise server, you're out of luck (the free ColdFusion Express server doesn't even support session variables - not very useful).

LAMP is red hot and free

PHP, on the other hand, is free. It is combined with three other technologies to form a powerful Web development solution abbreviated LAMP: Linux (operating system), Apache (Web server), MySQL (database) and PHP (scripting language). Even with MySQL's insistance that businesses pay a nominal fee for its use, Linux, Apache and PHP are absolutely free which make the LAMP solution the most cost-effecive solution for individuals, universities and businesses. Web devlopers wishing to acquire valuable scripting skills can do so for free. On top of that, LAMP is generally known to be more secure and robust than other solutions (less crashing, less rebooting, inherent UNIX security). On top of that, LAMP has one of the largest, most active, most dedicated communities on the Web. PHP and the LAMP solution was clearly the best choice for me and I was excited.

The Linux hurdle

However, if you are a Windows user, learning Linux is like being parachuted into Italy without knowing Italian. You will be hopelessly and thoroughly lost at the UNIX prompt. Although my repeated forays into the Linux world have enabled me to install Suse Linux 6.4, set up the X Window System and even use the vi editor, I still lack so much basic knowledge of Linux that even the simplest things such as unpacking files or installing software bring me to a paralyzing stop. So even if you are the type of Windows user who can buy a $50 computer book on almost any application and become an expert in it by the end of the week, Linux is not something you are going to learn in a week, or a month or even a year without an inordinate investment of time, effort, paradigm shifting and lots of long, frustrating nights. Hence LAMP is not an option for the casual Windows user wanting to learn PHP scripting.

Settling for WAMP

Luckily, PHP, MySQL and Apache can also run on Windows (hence WAMP). Note that this is not the most stabile platform to serve your pages to the world, but it is an adequate solution if you want to learn and begin developing with PHP/MySQL while remaining in your familiar Windows environment. You can install Apache, PHP and MySQL on your Windows 98 machine, then upload your applications via FTP to a more stabile LINUX or UNIX server. WAMP is a good compromise for Windows-based Web developers who want to expand their server-side scripting skills into PHP and MySQL.

WAMP installation cryptic but not impossible

It took me a full seven days until I had Apache, PHP and MySQL on my Windows 98 installed, configured and working. It was not easy. The Web is full of well-meaning but conflicting manuals which give you instructions such as to enter "/php4win/" when it should be "c:/php4win/" - that one took me two days to figure out. Another two days was spent downloading incorrect MySQL packets (the manuals tell you to "download MySQL" but when you get to the page you are faced with a choice of about 30 different download possibilities). Luckily there were some very helpful people on the Web Developer's List who kept helping me until I got it. Thanks again to all of you!

Free workshop on installing PHP4, MySQL, Apache on Windows 95/98

If you want to install PHP4, MySQL and Apache on Windows 95 or 98, take my free online workshop on how to do it. This 14-step workshop has explicit instructions and screen shots which will lead you past all the pitfalls. With this Workshop, you can have PHP4, MySQL and Apache installed, configured and working on your Windows 98 within the hour.

Edward Tanguay is a Web developer and language trainer based in Berlin. For more diaries and tips on development visit Edward's Web Developer Site.

Ed Barlows Sybase Shareware

Slashdot 30+ GB Databases On Unix

Someone please tell me that I will be able to use large files painlessly on Linux sometime. Until then, run large databases on name brand UNIX servers with name brand UNIX. Linux on x86 is good at a lot of things, but a large database isn't one of them YET.

CaptainZapp asks: "A customer of mine runs a ~30 GB data warehouse on a Sybase SQL Server Database. Now their business requires a mirror of this database in a different location. An offer by a reputed U/X vendor for the hardware turns out to be about five times as expensive as when you get a reasonable x86 box, with the necessary amount of disk space and, say, 1 gig of memory. What does the esteemed Slashdot community think: Is Unix capable of handling a database of this size and what other terrible pitfalls do you foresee?" He's not worried about "mission-critical" here, he's just wondering if it's possible.

Two words (Score:4, Interesting)
by JonK ( on Wednesday July 26, @08:23AM EDT (#44)
(User #82641 Info)
Bad Idea.

Changing RDBMSs is a Really Painful Experience and one to be avoided at all costs if possible: it makes changing OSes look trivial (hell, even upgrading from one point release to the next can be a world of pain). If the data's already on Sybase then for god's sake keep it on Sybase. Go for Sybase on Linux, Sybase on SCO, Sybase on NT or whatever but remember: it's a RDBMS and the underlying platform is effectively irrelevant (pauses for flames as thousands of enraged Slashdotters start to spout off and steam at the ears)

Re:Three words:with three words (Score:4, Informative)
by arivanov on Wednesday July 26, @09:30AM EDT (#117)
(User #12034 Info)
Very bad idea. Or maybe even "Stupidity is limitless"

1. If you have not noted Oracle legal has walked around every single site that had Oracle vs X benchmarks (X=mysql, sybase, informix) and made them drop them. This is actually possible under the 8.0x EULA. Actually just read the EULA. It is a masterpiece in itself. You are not allowed to benchmark the product and not allowed to question the fact that it is fscking slow and not ANSI compliant. That is besides the fact that if I was you I would not buy something where the manufacturer intentionally disallows fair comparison with other products. It is enough to say fsck this at least for me...

2. The original database is on Sybase. Sybase is at least more or less syntactically ANSI SQL compliant. Oracle is as far from ANSI as it gets. It will be a good guess that it will take you ages to port the bloody thing. And porting it will be more expensive than the "expensive" hardware.

3. I would see if the database design is implementable under postgreSQL or MySQL on an Alpha. Alpha is cheap. A reasonably good alpha is under 5000$. Storage will be a 1000$ more. This is as much as an appropriate x86 box. Postgres does not have a 2GB database limit anyway as it splits database files. MySQL does not have this limit on alpha because the platform is 64 bit. Your problems are in the key limitation/lob interface for postgress and transactions for MySQL.

4. If Neither of the solutions in 3 is implementable you have to open wide you wallet and buy informix for Intel or DB2 for intel. Both of them work and are ANSI compliant. In btw DB2 for Intel linux developer edition is free. Free period. No expiration. So you can actually see if the database will work. And they match Oracle on some benchmarks and DB2 beats the crap out of it when it comes to real scalability and clustering.
@*** Baker's Law *** Misery no longer loves company. Nowadays it insists on it.

30Gb databases (Score:1)
by LinuxGrrl (Rachel at LinuxGrrls dot Org) on Wednesday July 26, @07:56AM EDT (#7)
(User #123916 Info)
Linux/IA32 probably not, at least under e2fs as you'll likely hit the 2Gb filesize limit, depending on how the database engine involved implements storage (Oracle using its own data partition in "raw iron" style?). Linux on other architectures, specifically the 64bit ones (Alpha, Sparc, Sledgehammer and IA64 before long) would probably be fine.
Re:raw partitions (Score:4, Interesting)
by rnturn on Wednesday July 26, @10:46AM EDT (#183)
(User #11092 Info)

``oracle uses its own raw partitions/filesystem to store its data. this speeds up oracle''

It doesn't have to manage it's own disk space. And it may, under certain conditions, provide better performance. We have been moving away from raw data partitions. This after running some benchmarks of a large table residing on raw partitions vs. the same data residing in tables in a filesystem. The performance was actually better while accessing the data in the filesystem. We're talking 10+% better performance not just a few percent. Our experience, based on our benchmarks, and discussions with Oracle technical people, is that the preference for using raw data partitions was based on performance tests using older versions of UNIX and less capable filesystems. Of course, your mileage may vary.

Aside from performance, if your database changes frequently, adding and deleting tablespaces is a major pain (with long downtime) when you're using raw data partitions but is a snap when you're using filesystems for data. If your database is fairly static raw partitions might buy some little bit of performance but, again, at the expense of managability. IMHO, raw data partitions just aren't worth it. Even if comparitive performance were a wash, the easier means of managing the database weighs in favor of filesystems.

Oracle officially recommends against RAID ... (Score:4, Informative)
by Nicolas MONNET ( on Wednesday July 26, @11:52AM EDT (#225)
(User #4727 Info)
seek times are dramatically improved in most (if not all) RAID levels

Seek time is not going to be any better in mirrorring, for one. The two heads reading the same data won't go faster than one head, will they?

Then for striping, this usually won't make any kind of difference since data access will be randomly spread over the disk. So there you go.

NOW smartly organizing the database WITHOUT striping amongst several disks *will* make seek times faster, actually, it will require less seeking. A typical Oracle installation (as recommended by Oracle) will have for example the software on one disk, the indexes on another, and the actual data on a third.

Now since one DB transaction requires typically at least one index lookup and one data retrieval, which are unlikely to reside close to each other on one disk. Now when they're separated on two disks, subsequent queries will have less seek time .

Now, since I was right, will you give me my karma back? ;) Adaptive Server Enterprise for Linux Download Page [Sybase ASE]

Linux Today

"The current version (ASE ESD#6) package is different from previous distribution of ASE The new package separates ASE and OC/OS SDK. Also OC/OS is now available for Alpha platform. You can also find FreeBSD version of the package. Intel, Alpha and FreeBSD releases differ only in packaging and they are all built from the same source."

"Software is FREE for development as well as Deployment. The License Agreement has been updated to include FreeBSD platform."

"Adaptive Server Enterprise ESD#6 for Linux runs on Linux systems with Kernel 2.2.12 and glibc-2.1.2-11 installed. Verify that your system meets the hardware/software requirements before installing the software."

Database browsing, analysis and manipulation - easier than ever! -

a tool for working with SQL Server and probably Sybase databases. Suggested by Boris Denin

Inprise - Borland Introduces Interbase 6.0 Now Free and Open Source on Linux...

Linux Today

"Inprise/Borland (Nasdaq: INPR) today announced the availability of the source code for InterBase 6.0, its cross-platform, standard query language (SQL) relational database management system (RDMS). Binary formats for the Linux, Windows and Solaris operating systems are also available for download, free of charge. With Version 6.0, Inprise/Borland introduces performance improvements, a higher level of SQL92 compliance, and a replication engine."

[Jun 29, 2000] NewsAlert -AbriaSoft Announces Its First Distribution Of MySQL Database Software For The Open Source Market

FREMONT, CA -- (INTERNET WIRE) -- 29-06-2000 -- AbriaSoft(TM), a provider of database solutions, announced today the launch of its new distribution, Abria(TM) MySQL Lite. AbriaSoft is an Open Source company founded to provide packaged MySQL(TM) support and software with development and administrative tools to the public. MySQL is an Open Source database, originally developed by T.c.X Consulting in Sweden.

Abria MySQL Lite significantly reduces the cumbersome process of installing and configuring MySQL and Apache into a quick installation of less than ten minutes. Abria MySQL Lite offers an integrated, turnkey install of RedHat Package Modules (RPMs) for MySQL 3.22, Apache Web Server, PHP3 and Perl. PhpMyAdmin, a tool for web-based MySQL administration, is also included. AbriaSoft's distribution runs on the RedHat Linux 6.x platform. Versions for other Linux distributions will be released later in the year. Abria MySQL eases the installation of all these components on a RedHat Linux server, and makes sure that all dependencies and files install correctly. Older versions of MySQL are automatically upgraded to newer versions.

"This distribution of MySQL is the first of its kind," says Mark Gerow, Project Manager for AbriaSoft. "We believe this will further establish MySQL as the premier choice for a back-end database for Web servers."

Abria MySQL Lite is available for unrestricted free download from AbriaSoft's Web site ( News, information, and FAQs on Abria MySQL, and on other Open Source products are also available at the site. As an added service to MySQL fans, AbriaSoft will be offering computer-based training (CBT) courses on CD-ROM for MySQL. These interactive training CD-ROMs will be available for demo and purchase on the AbriaSoft Web site in July 2000. AbriaSoft will be shipping its "Standard" and "Professional" editions within the next two months, offering SSL, WebMin, AbriaSoft MySQL Admin Tool, GUI Admin Tools, and all the components necessary in quickly install and run a high performance MySQL web site.

DBGUI - Sybase database interface

The current (DBI/DBD) version is 2.1.8. (Released Apr 01 2000)
The current (SybPerl) version 1.6.8. (Released December 14 1999). From program description:

DBGUI is a complete X graphical database interface that can -

Linux Today - Wired Red Hat Wants the Whole Fedora

"The financial wizards who successfully launched Red Hat, the celebrated Linux vendor, are now targeting the lucrative database market as the next fertile ground for capitalizing on open-source development."

"Frank Batten Jr.'s Landmark Communications, which took Red Hat public last year, launched subsidiary Great Bridge LLC to market commercial products and services supporting open-source database PostgreSQL."

"PostgreSQL has more than 1,000 developers worldwide and competes with leading proprietary databases such as Oracle and Microsoft SQL Server. Great Bridge's launch coincides with the release of PostgreSQL version 7.0, which adds performance improvements, new querying capabilities, among other enhancements."

[Apr 19, 2000] LinuxWorld: A practical guide to Oracle8i for Linux

"If you've been having trouble installing Oracle8i, be bewildered no longer. Uche Ogbuji explains why Oracle8i is one of the wildest, wooliest installs ever, and how to work around its quirks."

[Apr 2, 2000] PostgreSQL book completed through chapter 10

New chapters on numbering rows, combining selects, data types, transactions and locks.

Linux Today Linux Journal IBM's Universal Database

Have you been trying to install DB2 Universal Database on a Linux-based workstation? Did you run into some troubles? As I scan around the DB2 and Linux newsgroups, I hear from many of users who are getting frustrated when trying to get DB2 running on Linux. How did this problem get so big? Well, the Linux phenomenon is relatively new and ever-changing. Recently, all sorts of vendors are flocking to market their distributions, with slight differences between them all. Combine that with what seems to be quarterly releases and you can see how communication channels between the Linux vendors and the people who build applications to run on them get clogged. While efforts are being made between application developers and Linux vendors to define this communication pipe, you can use the information in this article to get yourself up and running in no time at all.

DB2 for Linux is officially supported on the following Linux distributions: Caldera OpenLinux, Red Hat Linux, TurboLinux and SuSE Linux. This article will take you through the steps involved in installing DB2 on each of the supported Linux distributions. In the article, I assume you have not previously installed a version of DB2 and you are not maintaining any of the default users created by a default DB2 Installation. The three user IDs that will be created during a DB2 installation are: db2inst1, db2fenc1 and db2as. If you have any of these users on your system, be sure to remove them and their associated directories before installing DB2. This article also assumes you are familiar with the rpm command, used to install packages. If you are not familiar with this command, refer to your Linux documentation.

Complete Story Inprise Offers InterBase 6.0 - Linux for Public Field Test

InterBase 6.0 is a powerful, high-performance cross-platform relational database designed for business-critical, mobile computing and Internet-based applications on Linux, Windows NT, Solaris, and other UNIX operating systems."

Linux Planet offers two tutorials on setting up a MySQL based website part 1 and Part 2.

Linux PR Orasoft Team Releases 3.0 of Oracle Apps for Linux

Press release:
Dec 10th, 17:50 UTC

The graphical applications use the GTK widget set and are released under the GPL.

GODFREY, Illinois, Dec 10-- The Orasoft team is happy to announce the official stable release of the Oracle Applications for Linux suite. We believe that it is the most advanced database software available on the Linux platform for using and managing Oracle databases. The graphical applications use the GTK widget set and are released under the GPL. Source code and RPM executable are located at the Orasoft website (

Included in the suite are the following applications:

Procedit: - A visual procedure/function/package editor.
SqlWork: - A database querying tool similar to SQL Worksheet.
Session Monitor: - A DBA snooping tool to view user activities.
TableBrowser: - A table viewing/manipulating application.

The release follows a lengthy beta period in which over 1000 people participated.

Version 3.0 of the suite was released Dec. 9 and is now available for free (RMS) download.

CONTACT: Matthew Chappee
Orasoft Team Leader

Linux Today PostgreSQL Book In Progress by Bruce Momjian

I have completed through chapter 6 of my book, "PostgreSQL: Introduction and Concepts." Of course, this is only the first draft. It can be viewed at:

There are major improvements in this version. I have added sections to chapter 4 dealing with DEFAULT and functions/operators. The figure placement and font usage is improved.

Chapter 6 covers using multiple tables and joins. This was a very difficult chapter for me to write.

-- Bruce Momjian

Linux Today The Register Red Hat preps e-commerce oriented Linux upgrade

"The Oracle connection comes from the two companies' plan to promote Oracle 8i on Linux. The notoriously anti-Microsoft database giant is already pushing its product hard as a key e-commerce tool on Windows, and by working with Red Hat it can provide a solution that's entirely Windows-free."

"To that end, Red Hat will build stronger links to Oracle 8i into its Linux distribution. The journaling file system, which records file operations, making recovery from crashes a less haphazard -- and therefore faster -- operation, a new volume manager and support for greater amounts of RAM all help here too."

LinuxPlanet- Apples and Oranges- A Linux DBMS Comparison, Part I

SlashdotNetscape gives Good Press for MySQL and Linux -- interesting discussion

OS2 e-Zine! - MiniSQL versus MySQL

TDAN Archive -- The data base administration newsletter archive.

[Feb 10, 1999] DBMEdit

A user-friendly Web-based DBM database editor (in Perl 5)

Recommended Links

Google matched content

Softpanorama Recommended

Top articles


**** Open Directory - Computers Programming Databases

**** Pal's Linux RDBMS Library

This site is a compilation of the best free online readings about relational databases on Linux. If you're a Linux RDBMS/database administrator, a database designer/developer, or simply a Linux user with database ambitions, you'll find links to valuable resources here: articles, papers, and books on various aspects of relational database management. Needless to say, much of this material is more or less applicable to other (UNIX) environments, too.

Ingres - Frequently Askes Questions

DB Resources

Bruce Momjian's Writings

The Open Web Application Security Project

MYSQL. - AbriaSoft - Software for An Open World -- great LAMP distribution ( LAMP: Linux (operating system), Apache (Web server), MySQL (database) and PHP (scripting language).)

Abria SQL Lite provides all the tools needed to develop web-database applications in a quick and easy installation. Abria SQL Lite includes MySQL, Apache Web Server, Perl, PHP, and phpMyAdmin.


Free Databases


Free for individual and non profit use




SS-File Database Manager -- flat file database


The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D

Copyright © 1996-2018 by Dr. Nikolai Bezroukov. was initially created as a service to the (now defunct) UN Sustainable Development Networking Programme (SDNP) in the author free time and without any remuneration. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.

FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available to advance understanding of computer science, IT technology, economic, scientific, and social issues. We believe this constitutes a 'fair use' of any such copyrighted material as provided by section 107 of the US Copyright Law according to which such material can be distributed without profit exclusively for research and educational purposes.

This is a Spartan WHYFF (We Help You For Free) site written by people for whom English is not a native language. Grammar and spelling errors should be expected. The site contain some broken links as it develops like a living tree...

You can use PayPal to make a contribution, supporting development of this site and speed up access. In case is down you can use the at


The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the author present and former employers, SDNP or any other organization the author may be associated with. We do not warrant the correctness of the information provided or its fitness for any purpose.

The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.

Last modified: July 28, 2019