MySQL basics for testers

Have you ever been part of this conversation?

Development Group: The application is ready to be released!

Management: Did you test it?

Development Group: Um…yes…we ran our code in our development environment and everything worked great.

Management: Test it again!!

We hope that you haven’t been on either end of this conversation but many, many people have.

The key to the successful release of any application is to test, test, then test again. So, how do we test things like the database design and the base data (if any) being delivered to the end user?

There are lots of available resources that talk about software testing. Most of them focus on the application code, or GUI interface. In this article we hope to provide some useful strategies for testing the database layer of an application development project.

One of our most recent projects was the design and testing of a MySQL database backend for a major American university, so we are going to offer a specific approach for MySQL basics for software testers in this article.

There is a saying in software testing: “There are as many ways to test the database backend of a development project as there are designers and testers.” That’s why standards like software development life cycle (SDLC) and even ITIL processes were developed and imposed on many development shops – to the chagrin of many free-wheeling developers and the delight of many more disciplined developers.

Some of the more casual terms used by testing groups include Sanity and Smoke Testing – both of interesting origin. The term Smoke Testing comes originally from hardware design where the engineers would connect all of the new hardware, turn on the power and see if there was any smoke, or worse, a fire as a result. Sanity Testing is a little more recent in its origin but equally colorful in its imagery.

Origins aside, Smoke Testing is a valid and valuable part of testing, particularly when we consider the supporting MySQL database and MySQL database structures. It involves the scripted or automated testing of the critical functions of the database design and objects.

Sanity testing is used to determine if a much more specific part of the database design continues to be valid after minor or major changes in adjoining areas of the database design – the addition of new tables, implementation of a new trigger or change of a pre-existing, referencing index.

MySQL has matured into an enterprise level database management system (DBMS) over the last several years. After its apparent weathering of being purchased by Oracle (if it hasn’t been sctuttled yet, it probably won’t be), MySQL has rapidly added many features that are generally expected to be available in an enterprise DBMS. However, MySQL is still comparatively young and there are still a number of available, but possibly not advisable, work-around functions in the core DBMS code.

When we take on a project that is implemented using MySQL as a backend DBMS, we pay particular attention to testing the main areas of database issues, including:

  • Data corruption due to poor data normalization or unscalable original design
  • Inconsistent data often due to lack of attention to the stated application business rules or poor validation of the user-entered data by the business rules implemented at the database level
  • Redundant data due to duplication of data or allowing invalid data to be entered by the user via the GUI

So, the main tests that should be implemented in any database development project, but especially so with younger database platforms like MySQL are the following:

1. Functional testing

  • Check for proper performance of the stored procedures, functions and triggers that facilitate the population of the database tables and indexes
  • Check for proper syntax, parameter types, sizes and order

 2. Data quality testing

  • Integrity checks for Foreign and Primary key values
  • Check for correct values in table columns with the DEFAULT constraints

 3. Performance and data load testing

  • Check proper execution of stored procedures, queries and sub-queries under heavy load conditions and large returned datasets
  • Check for proper matching between input and output parameter values to remove unnecessary parameter translation
  • Check for large volumes of returned NULL values under heavy load conditions and large returned datasets

 4. Entity and relationship testing

  • Check for the existence of all required tables for the base design
  • Check for the existence of all required table columns for application queries and foreign key constraints
  • Check for column ordering with respect to the calling procedures
  • Check for proper execution of any cascading delete triggers

With these basics for software testing, a software project supported by a MySQL database platform should stand up to management and user scrutiny.

About the Author: As the Online Marketing Strategist for The DBA Shoppe, Brian Hines promotes the benefits and rewards of remote database administration.  A former personal trainer, Brian promotes healthy living, a love of the outdoors, and another Toronto Blue Jays World Series. For more information on The DBA Shoppe please click here.