{"id":2005,"date":"2010-03-12T06:53:28","date_gmt":"2010-03-12T04:53:28","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2005"},"modified":"2010-03-12T15:27:07","modified_gmt":"2010-03-12T13:27:07","slug":"but-i-do-want-mysql-to-say-error","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/but-i-do-want-mysql-to-say-error","title":{"rendered":"But I DO want MySQL to say &#8220;ERROR&#8221;!"},"content":{"rendered":"<p>MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.<\/p>\n<ul>\n<li>Using <strong>GROUP_CONCAT<\/strong> with a small <strong>group_concat_max_len<\/strong> setting? Your result will be silently truncated (make sure to check the warnings though).<\/li>\n<li>Calling <strong>CREATE <span style=\"text-decoration: line-through;\">TEMPORARY<\/span> TABLE<\/strong>? You get <a href=\"http:\/\/www.joinfu.com\/2010\/03\/a-follow-up-on-the-sql-puzzle\/\">silent commit<\/a>.<\/li>\n<li>Issuing a <strong>ROLLBACK<\/strong> on non-transactional involved engines? Have a warning; no error.<\/li>\n<li>Using <strong>LOCK IN SHARE MODE<\/strong> on non transactional tables? Not a problem. Nothing reported.<\/li>\n<li>Adding a <strong>FOREIGN KEY<\/strong> on a MyISAM table? Good for you; no action actually taken.<\/li>\n<li>Inserting <strong>300<\/strong> to a <strong>TINYINT<\/strong> column in a relaxed <strong>sql_mode<\/strong>? Give me <strong>255<\/strong>, I&#8217;ll silently drop the remaining <strong>45<\/strong>. I owe you.<\/li>\n<\/ul>\n<h4>Warnings and errors<\/h4>\n<p>It would be nice to:<!--more--><\/p>\n<ul>\n<li>Have an <strong>auto_propagate_warning_to_error<\/strong> server variable (global\/session\/both) which, well, does what it says.<\/li>\n<li>Have an <strong>i_am_really_not_a_dummy<\/strong> server variable which implies stricter checks for all the above and prevents you from doing with <em>anything<\/em> that may be problematic (or rolls back your transactions on your invalid actions).<\/li>\n<\/ul>\n<p>Connectors may be nice enough to propagate warnings to errors &#8211; that&#8217;s good. But not enough: since data is already committed in MySQL.<\/p>\n<p>If I understand correctly, and maybe it&#8217;s just a myth, it all relates to the times where MySQL had interest in a widespread adoption across the internet, in such way that it does not interfere too much with the users (hence leading to the common myth that &#8220;MySQL just works out of the box and does not require me to configure or understand anything&#8221;).<\/p>\n<p>MySQL is a database system, and is now widespread, and is used by serious companies and products. It is time to stop play nice to everyone and provide with strict integrity &#8212; or, be nice to everyone, just allow me to specify what &#8220;nice&#8221; means for me.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data. Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though). Calling CREATE TEMPORARY TABLE? You get silent commit. Issuing a ROLLBACK on non-transactional involved [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,18,35,20],"class_list":["post-2005","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-data-types","tag-sql_mode","tag-syntax"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-wl","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2005","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2005"}],"version-history":[{"count":14,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2005\/revisions"}],"predecessor-version":[{"id":2180,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2005\/revisions\/2180"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2005"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2005"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2005"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}