INSERT … ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. It was introduced in MySQL 4.1 but I still … 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…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 4 Comments
… not the Event itself. Example: mysql> create event year ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO t VALUES(YEAR(CURDATE())); Binary… year ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT INTO t VALUES(YEAR(CURDATE())) After one hour the event gets executed. The insert…
Post: Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
…CURRENT_TIMESTAMP. From the manual, here is how MySQL handles timezone locality with timestamp fields: Values for TIMESTAMP columns are converted from the current…insert some rows into a table with timestamp field configured to use CURRENT_TIMESTAMP… still recalculates the checksums on the fly to get…
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
…(45) NOT NULL, -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (actor_id), -> … sec) # ./page_parser -f /var/lib/mysql/ibdata1 # ./bin/constraints_parser.SYS_TABLES … is lost when new record is inserted. But the actual old values remain…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… inserts, updates and deletes but not so much in 2010. MySQL surprisingly supports declarative integrity constraints. But wait being MySQL… 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
… NOT NULL default ’0′, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`url`,`thumb_width`,`…/repairing large MyISAM tables in case of MySQL/System crash is painful and great … many images from one domain/album are inserted at about same time which makes…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…host_id TINYINT UNSIGNED NOT NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY…currently set to 2. If we do an INSERT … ODKU on this table, we see the following: (root@localhost) [test]> insert… as being defined first. So, MySQL checks our INSERT, sees that the next auto-…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table. But just as frequently, I want to look up blog posts on…delayed_insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert_…innodb_rollback_on_timeoutblogpercona.commanual Innodb_row_lock_current_…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
…reason is that running statistic on a stopped database is more… NOT NULL, `message_published` timestamp NOT NULL default CURRENT_TIMESTAMP, `kind` enum(‘link’,'img…tables=art.link* –datadir=/mnt/data/mysql/ which will show something like … with further inserts, and I also suspect random INSERTS into …
Post: Infinite Replication Loop
October 10, 2011 By 2 Comments
…timestamp included in the last executed sql statement. As some of them are looping, the timestamp… were first inserted. Another way to see it is by monitoring your MySQL server with…on the passive master, but that could also create another loop in case the replication of events with the current…
