Mysql Numeric Overflow Gotcha —

Todays Gotcha Exposed A lot of database tables are populated by means of an automated script or application. Inevitably, one of these will attempt to insert an invalid value into a column. This event can be handled by the database in one of several ways: 1. It can throw an error so that your script or application may deal with it by either proceeding or aborting. 2. It can truncate the value. 3. It can guess at what might be a better and/or more acceptable value and insert it instead. In case you havent guessed it, MySQL takes the second course of action (more or less). While both the second and third options make for more hassle-free batch processing, both can come back to bite you when you revisit the stored data at a later time. Consider the following table that stores information about menu items, including its ID, name, price, description, and caloric value. delimiter $$ CREATE TABLE `menu_items` ( `id` int(11) NOT NULL, `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL, `price` decimal(5,2) DEFAULT NULL, `description` varchar(255) CHARACTER SET latin1 DEFAULT NULL, `calories` smallint(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci $$ The following sample insert statement contains data that is typical for a breakfast menu, except for the price, which is off the charts at over $50,000 and seven (?!) decimal points. In most other databases, that would cause the record to be rejected. INSERT INTO menu_items VALUES ( ‘6’, ‘Belgian Waffles’, ‘500000.9599999’, ‘Two of our famous Belgian Waffles with plenty of real maple syrup’, ‘650’ ); But not MySQL. It accepts the new row, but does issue a warning: 1 row(s) affected, 1 warning(s): 1264 Out of range value for column ‘price’ at row 1 A quick SELECT ALL on the menu_items table shows that the price was set to 999.99, which is presumably the largest possible value for the field: SELECT * FROM `test`.`menu_items`; 1 row(s) returned 6, Belgian Waffles, 999.99, Two of our famous Belgian Waffles with plenty of real maple syrup, 650 And this is exactly what the makers of MySQL intended. As described in the docs : If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. Can We Force MySQL to Throw an Error Instead? There are in fact a couple of ways to force MySQL to throw an error when the data does not conform to the field specs. MySQL can operate in different SQL modes so that its behavior matches a sites global operating requirements, or to that of specific applications. Setting the sql_mode variable affects MySQLs flexibility on SQL syntax as well as the data validation checks it performs. Now, there is quite a long list of individual and combined sql_mode values, but the following are arguably the most important: ANSI: Changes SQL syntax and storage engine behavior to conform more closely to standard SQL. STRICT_TRANS_TABLES: If a value cannot be inserted as given into the table, then abort the statement. TRADITIONAL: Makes MySQL behave more like a traditional SQL database system. Setting the sql_mode to this value will raise an error instead of a warning when inserting an invalid value into a column. The sql_mode can either be set at server startup or at runtime. To set the SQL mode at server startup, use the –sql-mode=”[modes]” option on the command line, or sql-mode=”[modes]” in a config file such as myconfig.cnf on Unix or myconfig.ini on Windows, where [modes] is a comma-delimited list of the available modes. The SQL mode can be cleared at any time by setting it to an empty string, as in –sql-mode=”” on the command line, or sql-mode=”” in a config file. It may also be changed at either the global or session level at runtime via the SET statement: SET GLOBAL sql_mode = ‘[modes]’; SET SESSION sql_mode = ‘[modes]’; Setting the GLOBAL variable affects the database behavior for all clients that connect from then on whereas setting the SESSION variable affects only the current client.
For the original version including any supplementary images or video, visit

LG G3 vs. Samsung Galaxy S5: How The Two Best Android Phones You Can Buy Compare – Yahoo Finance

Design and Hardware Design ultimately comes down to personal preference, but there are a few things to consider when comparing the Galaxy S5 and LG G3. First off, the G3 is slightly bigger than the S5. If you need a larger screen, LGs flagship may be better suited to your taste. Samsungs phone comes with a 5.1-inch display, while the G3s is full-on phablet status at 5.5 inches. View gallery . Lisa Eadicicco In terms of hardware, the LG G3 and Samsung Galaxy S5 are both equipped with top-of-the-line components. Their processors are equally fast and capable of handling your everyday needs, but there are some discrepancies when it comes to display quality. The G3 is one of the first smartphones to come with a display with a super-high resolution of 2560 x 1440, while the S5s display features a 1080p resolution. On paper, this means the G3s display should be sharper than the S5s. In everyday use, however, I didnt see much of a difference. In fact, I preferred the S5s display in some circumstances. When looking at high-res photos on Flickr, the S5 displayed color more vibrantly than the G3. The Galaxy s5’s display is also much brighter than that of the G3. When watching a YouTube video on the G3, it became difficult to discern details because the screen was so dim. This was especially noticeable in darker scenes. What Its Like To Use Them A smartphones software is just as important as its hardware. If its user interface is complicated and difficult to navigate, youre not going to enjoy using the phone. Both Samsung and LG have heavily skinned Android to include their own software features and additions. In the past, Ive criticized both for being too cluttered and busy, but Samsung and LG have both made some improvements on this front. Ultimately, I prefer the LGs version of Android compared to Samsungs TouchWiz overlay. The G3s UI is a bit softer than Samsungs slightly rigid interface. The company has slightly tweaked the layout which LG calls its Simplified UI to feel much cleaner and smoother.
For the original version including any supplementary images or video, visit


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s