You searched for mysql auto increment bad value innodb

Recovering from a bad UPDATE statement

October 19, 2012 By Michael Coburn 5 Comments

Did you just run an UPDATE against your 10 million row users table without a WHERE clause? Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement? This is possible if you are running in binlog_format=ROW ! Imagine this scenario:

CREATE TABLE `t1` (
 `c1` int(11) NOT NULL AUTO_INCREMENT,
 `c2` varchar(10) NOT NULL,
 PRIMARY KEY (`c1`)
) ENGINE=InnoDB;
INSERT INTO `t1` (`c2`) VALUES ('michael'), ('peter'), ('aamina');

CREATE TABLE `t1` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` varchar(10) NOT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB;

INSERT INTO `t1` (`c2`) VALUES ('michael'), ('peter'), ('aamina');

We run an accidental UPDATE statement that [...]

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

When is MIN(DATE) != MIN(DATE) ?

September 4, 2012 By Ernie Souhrada 5 Comments

Inspiration for this post is courtesy of a friend and former colleague of mine, Greg Youngblood, who pinged me last week with an interesting MySQL puzzle. He was running Percona Server 5.5.21 with a table structure that looks something like this:

CREATE TABLE foo (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 uid INT UNSIGNED NOT NULL,
 update_time DATETIME NOT NULL,
 ....
 INDEX `uid` (uid, update_time),
 INDEX `bar` (some_other_columns)
 ....
) ENGINE=InnoDB;

CREATE TABLE foo (

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

uid INT UNSIGNED NOT NULL,

update_time DATETIME NOT NULL,

....

INDEX `uid` (uid, update_time),

INDEX `bar` (some_other_columns)

....

) ENGINE=InnoDB;

When he ran this query:

SELECT MIN(update_time) FROM foo WHERE update_time IS NOT NULL AND update_time <> '0000-00-00 00:00:00';

1	SELECT MIN(update_time) FROM foo WHERE update_time IS NOT NULL AND update_time <> '0000-00-00 00:00:00';

The result came back as 2012-06-22 10:28:16. [...]

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

DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

June 22, 2012 By Ovais Tariq 33 Comments

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL

High-Performance Click Analysis with MySQL

December 22, 2008 By Baron Schwartz 13 Comments

We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work.Â The first thing these have in common is that they’re generally some kind of loggable event. The next characteristic of a lot of these systems (real or planned) is [...]

Filed Under: Insight for Developers Tagged With: Ideas, Optimizer, Replication, Tips, XtraDB

How Percona does a MySQL Performance Audit

November 24, 2008 By Baron Schwartz 17 Comments

Our customers or prospective customers often ask us how we do a performance audit (it’s our most popular service). I thought I should write a blog post that will both answer their question, so I can just reply “read all about it at this URL” and share our methodology with readers a little bit. This [...]

Filed Under: Insight for Developers Tagged With: Tuning

AUTO_INCREMENT and MERGE TABLES

September 11, 2008 By Peter Zaitsev Leave a Comment

How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table… which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting [...]

Filed Under: Insight for DBAs Tagged With: Bugs, MyISAM

Recovering Innodb table Corruption

July 4, 2008 By Peter Zaitsev 21 Comments

Assume you’re running MySQL with Innodb tables and you’ve got crappy hardware, driver bug, kernel bug, unlucky power failure or some rare MySQL bug and some pages in Innodb tablespace got corrupted. In such cases Innodb will typically print something like this: InnoDB: Database page corruption on disk or a failed InnoDB: file read of [...]

Filed Under: Insight for DBAs Tagged With: InnoDB

Can having information public hurt consulting business ?

April 30, 2008 By Peter Zaitsev 9 Comments

People frequently ask me if the fact we keep information public can hurt our consulting business ? Lets keep aside for the moment amount of new business publishing this information brings to us but think it also have significant negative effect because people find information on MySQL Performance Blog and use it instead of purchasing [...]

Filed Under: Events and Announcements Tagged With: Percona

Performance gotcha of MySQL memory tables

February 1, 2008 By Peter Zaitsev 5 Comments

One performance gotcha with MEMORY tables you might know about comes from the fact it is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or range lookups. This is however not performance gotcha I’m going to write about. There is [...]

Filed Under: Benchmarks, Insight for Developers

MySQL Users Conference – Innodb

May 9, 2007 By Peter Zaitsev 6 Comments

It might look like it is too late to write about stuff happened at Users Conference but I’m just starting find bits of time from processing accumulated backlog. The Theme of this Users Conference was surely Storage Engines both looking at number of third party storage engine presented, main marketing message – Storage Engine partnership [...]

Filed Under: Events and Announcements Tagged With: InnoDB

Recovering from a bad UPDATE statement

When is MIN(DATE) != MIN(DATE) ?

DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

High-Performance Click Analysis with MySQL

How Percona does a MySQL Performance Audit

AUTO_INCREMENT and MERGE TABLES

Recovering Innodb table Corruption

Can having information public hurt consulting business ?

Performance gotcha of MySQL memory tables

MySQL Users Conference – Innodb

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA