{"id":1576,"date":"2009-02-25T16:25:32","date_gmt":"2009-02-25T06:25:32","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=1576"},"modified":"2009-02-25T16:25:32","modified_gmt":"2009-02-25T06:25:32","slug":"no-implicit-defaults","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/25\/no-implicit-defaults\/","title":{"rendered":"No implicit defaults"},"content":{"rendered":"

See also: MySQL Bug 43151<\/a><\/p>\n

The MySQL Manual<\/a> proudly states that you don’t get implicit default values if strict mode.<\/p>\n

mysql> set sql_mode='STRICT_ALL_TABLES';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int,\r\nprimary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;\r\nQuery OK, 0 rows affected (0.03 sec)\r\n\r\nmysql> show create table t1\\G\r\n*************************** 1. row ***************************\r\n       Table: t1\r\nCreate Table: CREATE TABLE `t1` (\r\n  `a` int(11) NOT NULL default '0',\r\n  `b` int(11) NOT NULL default '0',\r\n  `c` int(11) NOT NULL default '0',\r\n  `d` int(11) NOT NULL default '0',\r\n  `e` int(11) NOT NULL default '0',\r\n  `f` int(11) NOT NULL default '0',\r\n  `g` int(11) NOT NULL default '0',\r\n  `h` int(11) NOT NULL default '0',\r\n  `i` int(11) NOT NULL default '0',\r\n  PRIMARY KEY  (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`i`,`h`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=latin1\r\n1 row in set (0.00 sec)\r\n\r\nmysql> insert into t1 (a) values (1);\r\nQuery OK, 1 row affected (0.00 sec)<\/pre>\n
Which means your getting a default value you never specified.<\/p>\n
In my latest Drizzle patch, we no longer give you what you didn’t ask for (an implicit default).<\/p>\n
You can still (of course) specify an explicit default (and I’ve done this in some of our test cases).<\/p>\n","protected":false},"excerpt":{"rendered":"
See also: MySQL Bug 43151 The MySQL Manual proudly states that you don’t get implicit default values if strict mode. mysql> set sql_mode=’STRICT_ALL_TABLES’; Query OK, 0 rows affected (0.00 sec) mysql> create table t1 (a int, b int, c int, … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_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,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[75],"tags":[70,87],"class_list":["post-1576","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","tag-drizzle","tag-frm"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-pq","jetpack-related-posts":[{"id":2362,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/04\/21\/http-json-alsosql-interface-to-drizzle\/","url_meta":{"origin":1576,"position":0},"title":"HTTP JSON AlsoSQL interface to Drizzle","author":"Stewart Smith","date":"2011-04-21","format":false,"excerpt":"So... I had another one of those \"hrrm... this shouldn't be hard to hack a proof-of-concept\" moments. Web apps are increasingly speaking JSON all around the place. Why can't we speak JSON to\/from the database? Why? Seriously, why not? One reason why MongoDB has found users is that JSON is\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2011\/04\/Screenshot-11-300x230.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":2310,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/03\/sql-oddity-alter-table-and-default-values\/","url_meta":{"origin":1576,"position":1},"title":"SQL Oddity: ALTER TABLE and default values","author":"Stewart Smith","date":"2011-03-03","format":false,"excerpt":"\u00ef\u00bb\u00bfSo, the MySQL (and Drizzle) ALTER TABLE syntax allows you to easily change the default value of a column. For example: CREATE TABLE t1 (answer int); ALTER TABLE t1 ALTER answer SET DEFAULT 42; So, you create a TIMESTAMP column and forgot to set the default value to CURRENT_TIMESTAMP. Easy,\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":478,"url":"https:\/\/www.flamingspork.com\/blog\/2005\/10\/03\/a-funky-thing-done-last-week\/","url_meta":{"origin":1576,"position":2},"title":"a funky thing done last week…","author":"Stewart Smith","date":"2005-10-03","format":false,"excerpt":"still have to talk to people about standards for this sort of thing and all that. But as a first checkin - funkyness++! mysql> select * from INFORMATION_SCHEMA.DATAFILES; select * from INFORMATION_SCHEMA.TABLESPACES; Empty set (0.03 sec) Empty set (0.00 sec) mysql> CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1292,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/12\/10\/stewart-learns-sql-oddities\/","url_meta":{"origin":1576,"position":3},"title":"Stewart learns SQL oddities…","author":"Stewart Smith","date":"2008-12-10","format":false,"excerpt":"What would you expect the following to fail with? CREATE TABLE t1 (a int, b int); insert into t1 values (100,100); CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); BEGIN; INSERT INTO t2 values(100,100); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1383,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/02\/row-id-in-mysql-and-drizzle-and-the-engines\/","url_meta":{"origin":1576,"position":4},"title":"row id in MySQL and Drizzle (and the engines)","author":"Stewart Smith","date":"2009-02-02","format":false,"excerpt":"Some database engines have a fundamental concept of a row id. The row id is everything you need to know to locate a row. Common uses include secondary indexes (key is what's indexed, value is rowid which you then use to lookup the row). One design is the InnoDB method\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":578,"url":"https:\/\/www.flamingspork.com\/blog\/2006\/02\/15\/information_schemafiles-querying-disk-usage-from-sql\/","url_meta":{"origin":1576,"position":5},"title":"INFORMATION_SCHEMA.FILES (querying disk usage from SQL)","author":"Stewart Smith","date":"2006-02-15","format":false,"excerpt":"In MySQL 5.1.6 there's a new INFORMATION_SCHEMA table. Currently, it only has information on files for NDB but we're hoping to change that in a future release (read: I think it would be neat). This table is a table generated by the MySQL server listing all the different files that\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1576","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=1576"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1576\/revisions"}],"predecessor-version":[{"id":1578,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1576\/revisions\/1578"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=1576"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=1576"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=1576"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}