TL;DR: Even experienced SQL programmers can sometimes be thrown for an infinite loop — or other code failure — by one of the many pitfalls of the popular development platform. Get the upper hand by monitoring and managing your databases in the cloud via the Morpheus database-as-a-service.
When an database application goes belly up, the cause can often be traced to sloppy coding — but not always. 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 SQL errors.
In an August 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 by most other databases.
Don’t fall victim to one of the most-common developer mistakes
According to Justin James on the Tech Republic site, the most common database programming no-no is misuse of primary keys. James insists that primary keys should have nothing at all to do with the application data in a row. Except in the “most unusual of circumstances,” primary keys should be generated sequentially or randomly by the database upon row insertion and should not be changed. 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.
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.
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.
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.
Cloud database service helps ensure clean, efficient code
One of the benefits of the Morpheus cloud database-as-a-service is the ability to analyze your database in real time to identify and address potential security vulnerabilities and other system errors. TechTarget’s Brien Posey points to another benefit of the database-as-a-service model: By building redundancy into all levels of their infrastructure, cloud database services help organizations protect against data loss and ensure high availability.
In addition to auto backups, replication, and archiving, Morpheus’s service features a solid-state-disk-backed infrastructure that increases I/O operations per second (IOPs) by 100 times. Latency is further reduced via direct connections to EC2. Databases are monitored and managed continuously by Morpheus’s crack team of DBAs and by the service’s sophisticated robots.
Morpheus supports MongoDB, MySQL, Redis, and Elasticsearch. Platform support includes Amazon Web Services, Rackspace, Hiroku, Joyent, Cloud Foundry, and Windows Azure. Visit the Morpheus site for pricing information; free databases are available during the service’s beta.
