June 19, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

… on crontab so that schema changes are automatically picked up.  Nice, …mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY… columns flagged ‘nullable’ and ‘must be unique‘ (woops!), single column tables, incrementing column…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: ,

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

… of posts on the small but handy improvements – changes and bug corrections – in MySQL 5.6 that can make our lives easier… it mean that tools like pt-duplicate-key-checker will not be necessary for MySQL 5.6? Let’s have a look… example, if one of the two indexes was unique or a foreign key. This is the output of our tool when run…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: Galera Flow Control in Percona XtraDB Cluster for MySQL

May 2, 2013 By 5 Comments

…mechanism, unlike anything you find in standard async MySQL replication. It is my belief that the … this setting really does no more than to change if the fc_limit is dynamically resized or…of the transactions, which translates into number of unique key lookups into the certification index, which is a…

Filed Under: Insight for DBAs, Percona Live, XtraDB Cluster Tagged With: , , ,

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…key as UNIQUE this has benefits and drawbacks. Benefit being you can get extra optimizations by optimizer knowing index is UNIQUE… Regarding changing primary key to …key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key…

Filed Under: Insight for DBAs, MySQL

Post: Using Flexviews - part two, change data capture

March 25, 2011 By 26 Comments

…available for MySQL. Change Data Capture can be used to: Monitor a database table, or tables for changes. …has some basic requirements: MySQL 5.1+ row based logging (binlog_format=1) unique server_id in … primary key, > c2 int > ) > engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> insert …

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: How to create/restore a slave using GTID replication in MySQL 5.6

February 8, 2013 By 7 Comments

… there are many documents about that: http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-…_EXECUTED should be empty before changing GTID_PURGED manually but we can’t change it with SET because is …Now, if you don’t get any error like primary/unique key duplication then you can run the pt-table-…

Filed Under: MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… code changes. Unfortunately, I think that hope may be premature. While it is true that InnoDB full-text search in MySQL 5… define a column and a unique key as they suggest, your data won’t load due to unique key constraint violations unless you also… did make one configuration change to the default InnoDB settings for MySQL 5.6, and that was to change innodb_ft_min_token…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Knowing what pt-online-schema-change will do

November 1, 2012 By 1 Comment

…with –dry-run and –print, pt-online-schema-change changes nothing but prints a lot of information…Notice that the table has a PRIMARY key and another non-unique key named “guest_language”–this will be … bugs in the tool, problems with the MySQL server (privileges, etc.), or other oddities, there…

Filed Under: Insight for DBAs, Percona Software Tagged With:

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

… UNSIGNED NOT NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY(username) ) ENGINE=InnoDB; Now consider the following sequence of…, MySQL checks our INSERT, sees that the next auto-inc value is available, and claims it, but then it checks the UNIQUE KEY…. There is, of course, a trivial solution to this: just change the AUTO-INCREMENT column to use a BIGINT, and problem…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE

November 29, 2011 By 8 Comments

…the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to…is possible to return to the traditional method changing the innodb_autoinc_lock_mode to 0. But… AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2…

Filed Under: MySQL
« Older Posts