Paul has released Version 0.95 of his PBXT MySQL Transactional Storage Engine.

Here is what I did to get it operational under CentOS 4.3.

su -
useradd pbxt
su - pbxt
wget http://www.primebase.com/xt/download/mysql-4.1.16-pbxt-0.9.5.tar.gz
tar xvfz mysql-4.1.16-pbxt-0.9.5.tar.gz
cd mysql-4.1.16-pbxt-0.9.5
./configure --with-debug=full --with-pbxt-storage-engine --without-query-cache --with-mysqld-user=pbxt  --prefix=/home/pbxt/mysql
make
make install
scripts/mysql_install_db
cd /home/pbxt/mysql
./bin/mysqld_safe --user=pbxt --basedir=/home/pbxt/mysql &

Now, lets test and confirm PBXT is operational.

bin/mysql -uroot mysql
Reading table information for completion of table and column names
You can turn off this …

MySQL provides two different tools to test the MySQL Server with SQL statements. One is mysqltest and in 5.1 mysqlslap. Both of these tools have quite different purposes. This is a quick review of the usage of mysqltest.

Current Usage

Under Linux deploys, the README in the mysql-test directory gives you all the information you need to know to run.

To run the full test suite.

cd /opt/mysql/mysql-test
 ./mysql-test-run

Rather easy, it does take some time, and I was surprised to find a number of tests marked as ’skipped’. The general purpose of having tests in a product is to provide coverage of software functionality, and tests should always be forward …

For those that are using a Referential Integrity based Storage Engine in MySQL, the use of Foreign Keys can provide an extra level of data integrity within your application. There are of course caveats to this statement, such as correct design, proper transactional use etc, but that’s a topic for another time. The use of CASCADE has an effect on this.

So, just a quick refresher, using the MySQL Sakila Sample Database (if you don’t have it, get it. This can be a valuable tool in many other areas). Let’s first check out the Schema Diagram. We will …

Today, will go down in my professional history as quite possibly the lowest I would ever think of a software developer. I’ve carefully avoided the term “fellow coder”, speaking of a IT industry sticking by fellow IT people, but not today.

I presently support an existing production system, some 1000+ users that’s been in place around 3 years in which I’ve had no prior involvement until recently. Integration with other external parties within the system have provided an esclation in errors being reported in this external communication, and lack of adequate feedback to the user is another topic. Email is the means of reporting administratively of user errors, again another topic of issue. Within these emails, which are almost impossible to manage due to the limited internal GUI only toolset and lack of access to actual email account files to automate scripting (yet another topic? Do you see a trend here), is some relevent information …

One of my first major tasks at MySQL has just been completed - a major rewrite of the Connector/ODBC (C/ODBC) documentation.

There were three major focuses for the rewrite:

1. Bring the documentation up to date. We had a mix of information on the latest release (currently 3.51, but 5.0 is currently in development), but many of the sections didn’t reflect that new version. There is also new information on how to install the driver on Mac OS X.
2. Restructure the information. This is something I’m doing across the board on the Connectors docs, as I try to re-organize them all into a more coherent, and compatible, structure. For example, I’ve collated all of the tips about using C/ODBC with different applications into their own section, organized by application. I’ve also extended the information; for example we now have a …

Seems I have over time, thought of many ideas, jotted some notes on some, and even done some work, but everybody knows that “home projects” can take a long time.

Here are a few that have resurfaced over the past month, and I doubt I’ll ever get to them, or perhaps some other enterprising person has already done a similar thing. Of course most are for my own personal/professional gratification, but input from others of “great idea, when do we see it” could sway my interests.

INFORMATION_SCHEMA for MySQL Version 4

Why?

Well, quering the INFORMATION_SCHEMA is very cool, and long overdue for information gathering including statistics, schema definitions, schema version comparision tools etc. Of course there are concerns regarding the performance of using the INFORMATION_SCHEMA, and any design should significantly …

At this week I came across great blog of Peter Zaitsev and Vadim Tkachenko - two developers from High Performace Group at MySQL Inc. This blog’s name is MySQL Performance Blog and, as you probably assume, it is about misscelaneous sides of MySQL optimization and related stuff. So, if you are MySQL administrator or, even, developer, that works with MySQL, I would advise you to take a look at this blog - there are lots of useful MySQL optimization info.

Tags: mysql, high, performance, …

A recent article at CNN talks about how MySQL operates.

As one of the MySQL team, I can attest that works, but it requires a significant amount of coordination, and lots of online communication through email, IRC, Skype and other methods to keep everbody talking and all the projects working together.

The flip side to that process is that we all get involved in different areas, and you tend to be much more aware of what is going on company wide. There is also better cooperation - because we can all get involved we can all provide our experience and expertise to a wide range of problems and projects. Also, because we come from such a wide range of backgrounds and environments, we have a much wider perspective.

So not only does remote, and earth-wide staffing work, but it provides us with a level of cooperation …

As part of both my UltimateLAMP side project, and my greater involvement with MySQL Workbench, I’ve been wanting to review and document the database schemas of Open Source products.

Indeed, as part of discussions with Mike Zinner of MySQL AB at the recent MySQL Users Conference, I suggested an idea used in the Java World, for example by Clover and Agitar, where to promote the usefullness of their respective tools (in this case code coverage), they provide results against Open Source Products. For an example check out Clover Examples.

With MySQL Workbench, to get some greater …

Here are some steps involved. Using the current MySQL defacto engine InnoDB. Of course, Falcon, PBXT and others will enable alternative engines to be used.

Convert Table Storage Engine Types

$ mysql -u[user] -p[password] [database] -e "SHOW TABLES" | grep -v "Tables_in" | sed -e "s/^/ALTER TABLE /" | sed -e "s/$/ ENGINE=InnoDB;/" > upgrade.sql
$ mysql -u[user] -p[password] [database] < upgrade.sql

NOTE: This may not work for all tables, for example those with FULLTEXT indexes will fail.

For the introduction of Referential Integrity we need to ensure the following.

• Each Foreign Key column should have an index. Prior to 4.1 I think this was a requirement, however it’s a good general practice regardless for SQL performance.
• The datatype must match between Primary Key and Foreign Keys. The most obvious oversight is normally UNSIGNED, however you also for example have INT …