You searched for mysql innodb primary key stored where

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

Full Text Search Webinar Questions Followup

August 24, 2012 By Bill Karwin 5 Comments

I presented a webinar this week to give an overview of several Full Text Search solutions and compare their performance. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording. During my webinar, a number of attendees asked some good questions. Here are their questions and my [...]

Filed Under: Events and Announcements, Insight for Developers, MySQL

How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By Miguel Angel Nieto 4 Comments

In my previous post I explained how it could be possible to recover, on some specific cases, a single table from a full backup in order to save time and make the recovery process more straightforward. Now the scenario is worse because we don’t have a backup or the backup restore process doesn’t work. How [...]

Filed Under: Insight for DBAs, MySQL

A recovery trivia or how to recover from a lost ibdata1 file

June 3, 2011 By Yves Trudeau 8 Comments

A few day ago, a customer came to Percona needing to recover data. Basically, while doing a transfer from one SAN to another, something went wrong and they lost the ibdata1 file, where all the table meta-data is stored. Fortunately, they were running with innodb_file_per_table so the data itself was available. What they could provide [...]

Filed Under: MySQL

Connecting orphaned .ibd files

May 13, 2011 By Aleksandr Kuzminsky 19 Comments

There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in [...]

Filed Under: MySQL

Innodb row size limitation

April 7, 2011 By Fernando Ipar 12 Comments

I recently worked on a customer case where at seemingly random times, inserts would fail with Innodb error 139. This is a rather simple problem, but due to it’s nature, it may only affect you after you already have a system running in production for a while.

Filed Under: Insight for Developers, MySQL Tagged With: InnoDB, Storage Engine, Tips

Flexviews – part 3 – improving query performance using materialized views

April 4, 2011 By Justin Swanhart 20 Comments

Combating “data drift” In my first post in this series, I described materialized views (MVs). An MV is essentially a cached result set at one point in time. The contents of the MV will become incorrect (out of sync) when the underlying data changes. This loss of synchronization is sometimes called drift. This is conceptually [...]

Filed Under: Insight for DBAs, MySQL

Moving from MyISAM to Innodb or XtraDB. Basics

November 22, 2010 By Peter Zaitsev 2 Comments

I do not know if it is because we’re hosting a free webinar on migrating MyISAM to Innodb or some other reason but recently I see a lot of questions about migration from MyISAM to Innodb. Webinar will cover the process in a lot more details though I would like to go over basics in [...]

Filed Under: Events and Announcements, MySQL Tagged With: InnoDB, MyISAM, XtraDB

Recover BLOB fields

July 1, 2010 By Aleksandr Kuzminsky 4 Comments

For a long time long types like BLOB, TEXT were not supported by Percona InnoDB Recovery Tool. The reason consists in a special way InnoDB stores BLOBs. An InnoDB table is stored in a clustered index called PRIMARY. It must exist even if a user hasn’t defined the primary index. The PRIMARY index pages are [...]

Filed Under: Insight for DBAs, MySQL Tagged With: Backups, InnoDB, Recovery

Debugging problems with row based replication

May 6, 2010 By Justin Swanhart 12 Comments

MySQL 5.1 introduces row based binary logging. In fact, the default binary logging format in GA versions of MySQL 5.1 is ‘MIXED’ STATEMENT*; The binlog_format variable can still be changed per sessions which means it is possible that some of your binary log entries will be written in a row-based fashion instead of the [...]

Filed Under: Insight for DBAs, MySQL Tagged With: High Availability, Recovery, Replication, row based logging, Tips

Recovering from a bad UPDATE statement

Full Text Search Webinar Questions Followup

How to recover deleted rows from an InnoDB Tablespace

A recovery trivia or how to recover from a lost ibdata1 file

Connecting orphaned .ibd files

Innodb row size limitation

Flexviews – part 3 – improving query performance using materialized views

Moving from MyISAM to Innodb or XtraDB. Basics

Recover BLOB fields

Debugging problems with row based replication

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA