June 20, 2013

Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL

May 1, 2013 By 10 Comments

… Cluster (PXC) for MySQL is a hot thing right now and some…cannot replace in short term. Control Your Auto-Incrementing Columns PXC/Galera controls auto-incrementing values internally within the cluster, this…especially if you are planning to write on multiple nodes. Have PRIMARY KEYS If you still …

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

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

November 29, 2011 By 8 Comments

… InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT…); Our InnoDB table with auto increment column will be like this: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT…, 1 row affected (0.00 sec) Insert the same value multiple times. As you will see, the INSERT is ignored and…

Filed Under: MySQL

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

October 27, 2012 By 6 Comments

…being defined first. So, MySQL checks our INSERT, sees that the next auto-inc value is available, …that our INSERT … ODKU on a table with multiple unique indexes such as this one would be… signed types for AUTO_INCREMENT columns is almost never a good idea; it wastes half of the column‘s available …

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

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… Auto Increment handling Yet another known Innodb problem is table locks which are taken for statement duration for Inserts with auto-increment column…. others due to statement level MySQL Replication which among other requirements, needs all auto-increment values in multiple value insert to be…

Filed Under: Events and Announcements Tagged With:

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

February 26, 2013 By 1 Comment

… error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000): Incorrect column name… and dropping FT indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER TABLE statement…

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

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…last refeshed. The incremental method is somewhat conceptually similar to using mysql binary logs for … every MV gets a special column `mview$pk` which is an auto_increment BIGINT surrogate key for…. You can use this to refresh multiple incremental refresh views to the same transactional point…

Filed Under: Insight for DBAs, MySQL

Post: Statement based replication with Stored Functions, Triggers and Events

December 16, 2011 By 4 Comments

… same auto incremental values on master and slaves the actual used auto incremental value is logged as an extra event just before the statement. MySQL… if we insert multiple values and we have gaps? Example: Imagine the table has this values on the auto incremental column: 1,2,3…

Filed Under: MySQL

Post: Stored Function to generate Sequences

April 2, 2008 By 17 Comments

… values(‘two’,1000); This implementation uses single table to maintain multiple sequences which application can use in desired way, though you… wonder why you would like to use sequences instead of MySQL auto_increment columns ? Leaving aside more exotic ways of sequences even pure sequential…

Filed Under: Insight for Developers Tagged With: ,

Post: Announcing Percona Server 5.1.66-14.2

January 15, 2013 By Leave a Comment

… the Percona Software Repositories). Based on MySQL 5.1.66, including all the … binary log and that could cause multiple SQL injection like vulnerabilities. This bug … to AUTO_INCREMENT columns. Bug fixed #1039536 (Alexey Kopytov). In cases where indexes with AUTO_INCREMENT columns where correctly…

Filed Under: Events and Announcements, MySQL, Percona Software Tagged With:

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… contain WHERE clauses with columns that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT… results. CREATE TABLE users ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INT NOT NULL DEFAULT 0, status TINYINT UNSIGNED…

Filed Under: Insight for DBAs, Insight for Developers, MySQL
« Older Posts