InnoDB is the main ACID-compliant Storage Engine used in MySQL.

learn more… | top users | synonyms

6
votes
2answers
2k views

Good/Better config for MySQL on an EC2 Large Instance

I have an EC2 Large instance dedicated to MySQL. It will be serving a Joomla/Magento combo so it has a blend of InnoDB and MyISAM tables. I have only worked with MyISAM in the past and am therefore ...
1
vote
2answers
213 views

mysql queries not working suddenly !

When I do simple select or insert it says- Unknown table engine InnoDB It was working properly just few days back.what it's suddenly gone wrong ?
1
vote
1answer
276 views

Mysql InnoDB recovery, transferred from a dead Mac to Windows

After a Mac server crashed, I'm scrambling to recover records from an InnoDB table. All tables shared a single data file. I've copied ibdata1 and the two log files over to a new mysql server hosted ...
4
votes
1answer
1k views

Changing MySQL tmpdir kills InnoDB functionality

I've got a query that keeps failing because I don't have enough temporary space available. So, I created a directory on a different partition at /data/tmp, chmoded this directory to 777, and updated ...
5
votes
2answers
920 views

MySQL 5.5.9 Query Cache not working when schemas have hyphens (“-”) in their name

I am running MySQL 5.5.9 x86_64 RPM as downloaded from mysql.com. Running on CentOS 5.5 Xen DomU. I have enabled the Query_cache however MySQL NEVER uses it. All of my tables are InnoDB. Why is the ...
6
votes
3answers
9k views

Simpler way to convert all tables from InnoDB to MyISAM

Previously, I use this: USE dbname; ALTER TABLE tablename ENGINE=MYISAM; I'm looking for simpler way to convert all tables in a database, rather than writing every table name one by one
3
votes
2answers
3k views

How to disable all MariaDB storage engine except MyISAM?

(in my.cnf) I've tried default-storage-engine = myisam, skip-innodb, and commented out every innodb features, but none of them works
3
votes
3answers
6k views

How to know storage engine used of a database?

Previously, on every database created, I use: mysql -u root -p CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin; GRANT ALL ON dbname.* TO 'dbuser'@'localhost'; and then use the database ...
1
vote
1answer
2k views

How do you load a database with foreign keys on the master without out breaking replication?

I have a MySQL master-slave setup. One master; One slave; Lots of databases. I'm moving an app into this setup from third party. They provided a mysqldump file of the data and database structure. It ...
0
votes
1answer
947 views

Unable to start mysqld due to inability to allocate memory

I'm unable to start mysqld due to it being unable to allocate enough memory. I've installed the DB using the defaults: sudo mysql_install_db --user=mysql Then when I try to start mysqld, I get ...
3
votes
4answers
19k views

Setting a time limit for a transaction in MySQL/InnoDB

This sprang from this related question, where I wanted to know how to force two transactions to occur sequentially in a trivial case (where both are operating on only a single row). I got an ...
0
votes
2answers
369 views

Can transactions be forced to occur sequentially in MySQL?

Let's say that I have two servers that simultaneously initiate a transaction on a MySQL 5.5 database using InnoDB. Both transactions do the same thing: Read a single row, then update the row based on ...
3
votes
4answers
3k views

MySQL: how to convert many MyISAM tables to InnoDB in a production database?

We have a production database that is made up entirely of MyISAM tables. We are considering converting them to InnoDB to gain better concurrency & reliability. Can I just alter the myISAM ...
3
votes
1answer
2k views

what is gen_clust_index used for in mysql?

It seems I have a deadlock in mysql (running innodb) that's due in part to a lock on gen_clust_index, however, this isn't a table that I've created. So... what's the short and sweet description of ...
0
votes
1answer
247 views

A Most Puzzling MySQL Problem: Queries Sporadically Slow

This is the most puzzling MySQL problem that I've encountered in my career as an administrator. Can anyone with MySQL mastery help me a bit on this?: Right now, I run an application that queries my ...
7
votes
1answer
3k views

How to disable keys in MySQL InnoDB tables?

Is it possible to disable keys in InnoDB tables? If yes, how? If not, why?
2
votes
2answers
2k views

Restoring a slave MySQL database from raw backups of master gives InnoDB tablespace errors

I have a master/slave replication setup where I use InnoDB and MyISAM tables in over 7000 databases that I want to copy from my master to the slave to restore replication. Both servers were running ...
17
votes
6answers
11k views

MySQL InnoDB - innodb_file_per_table cons?

By default MySQL InnoDB stores all tables of all DBs in one global file. You can change this by setting innodb_file_per_table in the config, which then creates one data file for each table. I am ...
2
votes
1answer
1k views

MySQL write intensive setup, kjournald performs a lot of I/O

I have a MySQL (actually, MariaDB 5.2) setup deployed on a Rackspacecloud 4Gb node. All tables are InnoDB, InnoDB buffer pool size is 2G and it is 95% full most of the time. I use ext3 as a filesystem ...
5
votes
1answer
482 views

Mysql disaster recovery

We just got a major disaster: somebody made an uncontrolled update on the production database, and obviously, the backup process is not working since a long time, so we got a major data loss. A 40 ...
0
votes
2answers
176 views

MySQL - Innodb, search

I recently switched from myisam to innodb in one of my mysql tables. Before i was using MATCH(). Since innodb doesn't support match() i decided to use LIKE. Now I have a problem. My table is 190,6 ...
1
vote
3answers
285 views

MySQL Backup and Recover

I have been running mysqldump on my set of MySQL databases (MyISAM and INNDOB). Recently I wanted to restore the databases to another server, and so I created the databases and imported the dump file. ...
0
votes
1answer
697 views

Import from *.sql into mysql… IBD files missing

Running ubuntu 10.10, MySQL, I imported a set of databases from a backup. The import looks OK - I can query the databases and see data in them. The strange thing is that in the /var/lib/mysql/ ...
1
vote
1answer
276 views

Can I change a MySQL table back and forth between InnoDB and MyISAM without any problems?

I have a site with a decently big database, 3Gb in size, a couple of tables with a dozen million records. It's currently 100% on MyISAM, and I have the feeling that the server is going slower than it ...
2
votes
1answer
1k views

Join performance on MyISAM and InnoDB tables

I am thinking about converting some tables from MyISAM to InnoDB in my mysql server. The tables will certainly benefit from the change because a lot of write requests come to these tables, while there ...
0
votes
2answers
733 views

changing innodb_buffer_pool_size makes error

i want to increase my innodb_buffer_pool_size value to get some more performance and when i try, my select queries arises error such as "Incorrect information file in .." i'm just adding ...
3
votes
1answer
1k views

mysql INNODB inserts very slow

The database's schema is as follows. CREATE TABLE `items` ( `id` mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT , `name` varchar( 45 ) NOT NULL , `main_type` tinyint( 4 ) NOT NULL ...
0
votes
2answers
1k views

Is it ever safe to set innodb_flush_log_at_trx_commit=0?

I have a server (Dell PE2950) with a battery-backed write cache on the raid controller. Is it safe for me to set innodb_flush_log_at_trx_commit=0 on a mysql server with data files stored on a raid0 ...
1
vote
1answer
79 views

What are some general tips to make InnoDB for MySQL perform at its highest?

I've been using MyISAM exclusively for several years now and know the ins-and-outs pretty well of how to optimize it, but I've just recently started using InnoDB for some of my tables and don't know ...
2
votes
2answers
1k views

MySQL InnoDB table shows a negative number of rows in phpMyAdmin

I just converted a MyISAM table to InnoDB with around 1.4 million rows. When I converted it to InnoDB, it now shows -1.4 million rows. The table still works as expected, but why does it shows ...
1
vote
2answers
972 views

IO issues with InnoDB, review of assessment required

TL;DR - Having some IO based issues with InnoDB. Before I sign the form to request more hardware can you have a look and see if you agree with my assessment Working for a client, I have inherited a ...
2
votes
2answers
2k views

mysqld crashes on any statement

I restarted my slave to change configuration settings to skip reverse hostname lookup on connecting and to enable the slow query log. I edited /etc/my.cnf making only these changes, then restarted ...
6
votes
2answers
10k views

Why do MySQL queries pile up in “Sending data” state?

We're using InnoDB tables as the back end of a web application, and everything was fine for about two years until we had to restart MySQL a few weeks ago. (We hadn't disabled reverse DNS lookups, ...
0
votes
2answers
994 views

SHOW ENGINES; does not list InnoDB

Since yesterday, my InnoDB tables in my Mysql installation on Ubuntu don't work anymore, I tried to open them using Navicat, but I get the "Unknown Table engine 'InnoDB'" error. When I try listing ...
1
vote
1answer
1k views

How to use most of memory available on MySQL

I've got a MySQL server which has both InnoDB and MyISAM tables. InnoDB tablespace is quite small under 4 GB. MyISAM is big ~250 GB in total of which 50 GB is for indexes. Our server has 32 GB of RAM ...
0
votes
1answer
193 views

mySQL process overloading server - Need Help

I ran into an issue the last few days, I have a database server 2 Quad Core Processors and 24 gb of ram, recently we have come across a big issue the server is running normally at about 130% cpu then ...
2
votes
1answer
866 views

Facebook's Flashcache writeback block cache

Im quite interested in Facebook's code which was thrown over the wall again.. FlashCache ... Sounds fast, using SSD's to help scale InnoDB/MySQL, but it was designed as a generic caching module that ...
0
votes
1answer
841 views

MySQL innodb has lots of processes hanging in “update” state once every minute

Description of problem I have a fairly large MySQL installation. There are at least 3 separate servers which run the innoDB storage engine. Once every minute, at the same time each minute, for about ...
4
votes
2answers
2k views

What is the difference between MySQL Community Edition and Classic Edition?

With the news of Oracle pulling the InnoDB engine from MySQL Classic Edition, you now need to use MySQL Community Edition to get InnoDB for free. Oracle doesn't really make it clear what is different ...
0
votes
1answer
580 views

MySQL ibdata1 permissions error on cloned EC2 instance

I am trying to get a Rails app running on a cloned EC2 instance and am running into some permissions issues with my ibdata1 file. On the original instance, everything behaves as expected. However, ...
2
votes
2answers
204 views

How can I know if there are innodb tables in a mysql server?

I have a mysql server and I want to disable innodb support for it. It's said that I can cut-off my memory usage up to 100MB. I'm pretty sure but not 100% sure, that there is no innodb tables in the ...
0
votes
1answer
3k views

Fail to set innodb as default engine on Ubuntu 10.04

I've got a problem setting innodb as the default database engine in MySQL 5.1 on Ubuntu 10.04. As soon as I switch to innodb as default engine the server refuses to start. This is what I get in the ...
1
vote
2answers
320 views

Will deleting old MySQL data improve performance?

Would deleting old data from my tables improve MySQL query performance? When my queries use indexes? When my queries don't use indexes? When I use InnoDB? When I use MyISAM?
1
vote
3answers
4k views

How-to manage a MySQL table with over 2 million records

I have an InnoDB table running on MySQL 5.0.45 in CentOS. To make matters worse it is all running in a virtual machine. There are multiple tables that have the probability of exceeding 2 million ...
4
votes
1answer
851 views

MySQL and large pages/hugetlb performance gain in numbers?

MySQL has large page/hugetlb support for InnoDB. And there's plenty of posts (example) on the topic. But does anyone have examples of what performance changes they saw ? I have seen other database ...
7
votes
4answers
5k views

Modifying columns of very large mysql tables with little or no downtime

I periodically need to make changes to tables in mysql 5.1, mostly adding columns. Very simple with the alter table command. But my tables have up to 40 million rows now and they are growing fast... ...
0
votes
2answers
1k views

Mysql start fails with Operating System error 13

I have XAMPP on my Ubuntu Lucid system and everything worked fine. But there seems to be some problem now and mysql wouldn't start. I had tried to recover a few Drupal databases and hence copied ...
4
votes
1answer
2k views

MySQL data recovery (innoDB) - missing .frm files but intact ibdata

A recent server fiasco left me with a by-all-appearances intact ibdata file but only half of my .frm-containing directories. I've copied all of this to a fresh mysql install, and have successfully ...
1
vote
1answer
1k views

Unknown table engine 'InnoDB problem

I installed a MySQL Server 5.1 on my machine (Windows Server 2008) and I chose MyISAM as the default DB storage engine. I created one empty DB on it just for testing. Later I reconfigured it using the ...
1
vote
1answer
980 views

MySQL's innodb_file_per_table and “too many tables”

I'm running MySQL (5.0 but I don't think it matters for anything after 4.1) and I've seen advice about using the innodb_file_per_table configuration option for InnoDB tables. This is usually done so ...