The main reason for that VFP to MYSQL conversion project was that the customer is in the process of growth and is establishing multiple locations which all will use the same system to some extend. In addition to that we are going from a system where everyone on the system did pretty much everything to one where there are only going to be a few people who have access to everything and most will just work in their “special corner” of the system. Some will just do order entry, some receivables, some sales personel will have only access to their clients etc etc.

At present the VFP system resides on a shared Network drive and if a modification is needed well then one just replaces or adds a Screen or report on that particular shared network drive. Now it would be necessary to push this to multiple locations, in different configurations and that shared network drive wont do anymore – especially over the internet.

Luckely all …

Hello folks, it’s great to be back from hiatus. This is the 183rd edition of Log Buffer (arguably the best edition of Log Buffer yet!), the weekly review of database blogs.

The last time I wrote this was just under 2 years ago!!! WoW. Things have changed. Sun bought MySQL, Oracle bought Sun. Those were bombshell deals. At least you can rest assured that some things can be constant. I still eat my daily serving of broccoli (among other healthful “things”). I urge you all to go the fridge and grab some veggies prior to sitting down for this week’s… ahem… digest.

Starting with Oracle, Pythian’s own Alex Fatkulin illustrates a bug (?…likely) that could lead to logically corrupted data. …

I started with my VFP to MYSQL conversion in earnest at the end of December. To keep the cost down until I know what I am doing I am using the GA version 5.1.43 and now WORKBENCH to do my development. Actually initially I used the 5.0 Administrator and Query which worked fine but did not have any supports for triggers. Workbench 5.1.18 frustrated me for many reasons so I started to use the Beta of 5.2 which in my opinion is better but still a Beta. Trying to help to improve I filed about 20 or so bug reports and got into email conversations with the developers.

In course of that I mentioned the process of converting from VFP (Visual Foxpro) to MYSQL and I was asked to Blog about my experiences with that and feed it to Planet MySQL. Starting to read Planet MySQL I must say that I am probably an oddity here as there does not seem to be any VFP people here. For that reason I also fed and anounced my Blog to some VFP boards and feeds. I even got …

PBXT continues to amaze me. I want to know that maximum rate at which an engine can perform IO. I ran sysbench on MySQL 5.1.45 on a server with fast IO for PBXT, InnoDB (1.0.6 plugin) and MyISAM.

The test used sysbench readonly configured to fetch rows by primary key using the HANDLER statement. This option is available in sysbench on Launchpad. The test used a uniform distribution and the engines were configured to cache about 10% of the table. Most row fetches should require 1 disk read. In this case, the disk is very fast as tmpfs was used to store database files. We can get 10,000 to 100,000 IOPs at a commodity price. Can MySQL benefit from this? Yes it can, but this varies greatly between storage engines.

The peak QPS for PBXT is 2X better than InnoDB (1.0.6 plugin). The peak QPS for MyISAM is 2X better than PBXT. A graph of the results is …

From a security standpoint VFP is a nightmare. You can do pretty much anything on the program end with passwords etc but I never found a way around the fact that you pretty much have to give the client access to the Data directory. VFP being a flat file system (even with the introduction of DBC’s – databases) you had to give the client at the very least read access to the tables and if you wanted him/her to change something then write access too. Only way around that might have been to use VFPOLEDB and a web server but then why use VFP.

Now moving to MYSQL I want to point out some instant benefits you get migrating to MYSQL and that is security. One thing probably not immediatly visible to a newbie is the power of the “DEFINER” and INVOKER security options on stored procedures. This allows clients to do things under certain circumstances that they do have no right to. I want to use the following example to demonstrate the very …

I would like to use this installment of my blog to show some differences between VFP and MYSQL on how problems need to be solved. On the top it seems to me that the MYSQL optimizer needs a quite different strategy and some more help then the “rushmore” one in VFP. There is probably some room for improvements left in both approaches but I usually stop optimizing when I get under 2 seconds on getting results.

The task was to roll back the AR aging to a prior date. Basically a standard business problem. Accounting comes, looks at last months aging, makes some adjustments and then wants to rerun the aging, or the bank wants it as of a particular date or things just went nuts and everyone was busy with … whatevever …. and did not get to run the aging at the proper time. So to handle this a query was devised to back out all invoices and applications as to a certain date.

In the late 80’s the customer got and SBT accounting …

If you want to try out the beta features in MySQL Cluster 7.1 then you can either use the appropriate binaries or you can build it for yourself from source. Here I explain how to do this on LINUX.

Note that if you want to make use of OpenJPA then you first need to install OpenJPA and Connector/J.

The example here was on Fedora12 with the MySQL Cluster 7.1.2 source:

CFLAGS=”-O3″ CXX=gcc CXXFLAGS=”-O3 -felide-constructors -fno-exceptions -fno-rtti” ./configure -prefix=/usr/local/mysql –enable-assembler –with-mysqld-ldflags=-all-static –with-plugins=max –with-openjpa …

1. Tsung -- GPLed multi-protocol (HTTP, PostgreSQL, MySQL, WebDAV, SOAP, XMPP) load tester written in Erlang.
2. Myth of China's Manufacturing Prowess -- The latest data shows [...] that the United States is still the largest manufacturer in the world. In 2008, U.S. manufacturing output was $1.8 trillion, compared to $1.4 trillion in China (UN data. China’s data do not separate manufacturing from mining and utilities. So the actual Chinese manufacturing number should be much smaller). Also contains pointers to an interesting discussion of lack of opportunities for college grads in China.
3. OpenSSO and the Value of Open Source -- …

This is a small revision and will only be available through SVN. However, it is an important one to speak about as it solves a former issue when running the application on a screen smaller than 1024px wide. While most users may not have noticed this since they have larger monitors it has been noticed [...]

In my opinion, one of the best things that happened to Planet MySQL lately, is Explain Extended, a blog by Alex Bolenok (also known as Quassnoi on Stackoverflow).

I never had the pleasure of meeting Alex in person, but his articles are always interesting and of high quality, and the SQL wizardry he pulls off is downright inspiring. I really feel humbled by the creativity of some of his solutions and his apparent experience with multiple RDBMS products.

Alex' most recent post is about aggregation, and finding a top 3 based on the aggregate:

In …