… takes advantage of is TIMESTAMP field. By default first TIMESTAMP column will have its value automatically updated to current timestamp on insert and update. We actually could have omitted now() in insert clause…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 4 Comments
…. Example: mysql> create event year ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO t VALUES(YEAR(CURDATE())); Binary Logs: First… SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO t VALUES(YEAR(CURDATE())) After one hour the event gets executed. The insert is…
Post: Testing InnoDB "Barracuda" format with compression
April 23, 2008 By 3 Comments
… SET latin1 NOT NULL, `published` timestamp NOT NULL DEFAULT ’0000-00-00 00:00:00′, `crawled` timestamp NOT NULL DEFAULT ’0000-00… NULL, `author_id` int(10) unsigned NOT NULL, `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `fromfile` varchar(255) NOT NULL, `language_id…
Post: Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
… of CURRENT_TIMESTAMP. From the manual, here is how MySQL handles timezone locality with timestamp fields: Values for TIMESTAMP columns are converted from the current… in EDT time. Now, lets insert some rows into a table with timestamp field configured to use CURRENT_TIMESTAMP as the default and verify…
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
…, -> last_name VARCHAR(45) NOT NULL, -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (actor_id), -> KEY idx_actor…_PTR. Unfortunately this pointer is lost when new record is inserted. But the actual old values remain in the undo segment…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…they’re detrimental. Not only do these indexes slow down insert operations, the MySQL optimizer sometimes appears to become rather …null, order_date datetime not null, last_updated timestamp not null default current_timestamp on update current_timestamp, foreign key(item_type_id) references item…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
…(3) unsigned NOT NULL default ’0′, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`url`,`thumb_width`,`thumb_height`) ) ENGINE… – it is often many images from one domain/album are inserted at about same time which makes access locality much better…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
…_message_id` bigint(20) unsigned NOT NULL, `message_published` timestamp NOT NULL default CURRENT_TIMESTAMP, `kind` enum(‘link’,'img’) NOT NULL, `url_title… key in order which makes things very predictable, but the inserts into the secondary key index are random – which leads to… to see how it will grow with further inserts, and I also suspect random INSERTS into so dense space going to be…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… this table were almost exclusively INSERT … ON DUPLICATE KEY UPDATE (INSERT ODKU), with the columns from the INSERT part of the statement corresponding…) NOT NULL, host_id TINYINT UNSIGNED NOT NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE… is currently set to 2. If we do an INSERT … ODKU on this table, we see the following: (root@localhost) [test]> insert into…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
July 1, 2010 By 48 Comments
… `timestamp` does not, because you’ve defined the primary key to be `id` and `timestamp`, which means BOTH values you’re inserting/updating must equal the values currently in the database to make… primary key match and therefore update the row instead of insert a new row with the same `id`, but different `timestamp`.
