Even experienced programmers can sometimes be thrown for an infinite loop–or other code failure–with these common MySQL mistakes.
When a database application goes belly up, the cause often can be traced to sloppy coding. Every now and then, the reason for a misbehaving app is an idiosyncrasy in the platform itself. Here’s how to prevent your database from tripping over one of these common MySQL mistakes.
Handling Numeric Value Overflows
In an Aug. 11, 2014, article in the Database Journal, Rob Gravelle describes a “gotcha” (not a bug) in the way MySQL handles numeric value overflows. When a value supplied by an automated script or application is outside the range of a column data type, MySQL truncates the value to an entry within the acceptable range.
Generally, a database system will respond to an invalid value by generating an error instructing the script to proceed or abort, or it will substitute the invalid error with its best guess as to which valid entry was intended. Of course, truncating or substituting the entered value is almost certainly going to introduce an error into the table. Gravelle explains how to override MySQL’s default handling of overflow conditions to ensure it generates an error, which is the standard response to invalid entries in most other databases.
Misuse of Primary Keys
According to Justin James on the Tech Republic site, the most common database programming no-no is the misuse of primary keys.
James insists that primary keys should have nothing at all to do with the application data in a row. If they’re not system values managed by the system, you’re likely to encounter problems when you change the underlying data or migrate the data to another system.
Using Stored Procedures as a Crutch
Another frequent cause of program problems is overuse of stored procedures, which James describes as a “maintenance disaster.” There’s no easy way to determine which applications are using a particular stored procedure, so you end up writing a new one when you make a significant change to an app rather than adapting an existing stored procedure. Instead, James recommends that you use advanced object-relational mappers (ORMs).
Not every developer is sold on ORMs, however. On his Experimental Thoughts blog, Jeff Davis explains why he shies away from ORMs. Because ORMs add more lines of code between the application and the data, they invite more semantic errors. Davis points out that debugging in SQL is simpler when you can query the database as if you were an application. The more lines of code between the application error and the database, the more difficult it is to find the glitch.
Overuse of BIGINT
One of the common database errors identified by Thomas Larock on the SQL Rockstar site is playing it safe by overusing the BIGINT data type. If you’re certain no value in a column will exceed 100,000, there’s no need to use the 8-byte BIGINT data type when the 4-byte INT data type will suffice. You may not think a mere 4 bytes is significant, but what if the table ends up with 2 million rows? Then your app is wasting 7.8MB of storage. Similarly, if you know you won’t need calendar dates before the year 1900 or after 2079, using SMALLDATETIME will make our app much more efficient.
Misuse of GUIDs
On the SQL Skills site, Kimberly Tripp highlights another common database design error: use of non-sequential globally unique identifiers, or GUIDs. In addition to creating fragmentation in the base table, non-sequential GUIDs are four times wider than an INT-based identity.
Is your app’s failure to launch due to poor typing skills?
Maybe the best way to start your hunt for a coding error is via a printout rather than with a debugger. That’s the advice of fpweb.net’s tutorial on troubleshooting SQL errors. The problem could be due to a missing single quote mark, or misuse of double quotes inside a string. If you get the “No value given for one or more required parameters” error message, make sure your column and table names are spelled correctly
Likewise, if the error states “Data type mismatch in criteria expression,” you may have inserted letters or symbols in a column set for numeric values only (or vice versa). The FromDual site provides a complete list of MySQL error codes and messages, including explanations for many of the codes, possible sources for the errors, and instructions for correcting many of the errors.
The FromDual site explains how to find sources of information about MySQL error messages.
For many of the MySQL error messages, the FromDual index provides an explanation, reasons for the error message’s appearance, and potential fixes.
With a little work and a lot of sticky notes, these common MySQL mistakes are fairly easy fixes. There’s nothing worse than looking like a nOObie programmer and damaging the integrity of your application. So stay sharp, and take a second look at your MySQL queries.