InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

6
votes
2answers
2k views

Why does MyISAM support FULLTEXT Searching and InnoDB does not?

I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
3
votes
1answer
984 views

MySQL 5.5 InnoDB = OFF?

We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine. However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF ...
1
vote
1answer
3k views

ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
3
votes
1answer
3k views

MySQL Bug required remedy : [Warning] Invalid (old?) table or database name '#sql-'

We have a master & multiple slave replication in our production environment. At first, I found this error in the mysql error log on the master and later on the slave. As for all the replication, ...
3
votes
2answers
496 views

Monitoring InnoDB Index Utilization

I would like to monitor how many queries are utilizing the indexes. Is there any program that can show me live query performance and index utilization? Note: I am already aware of slow log file and ...
8
votes
3answers
16k views

Any better way out of MySQL InnoDB log “in the future”?

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
5
votes
4answers
2k views

Incomplete mysqldump

I'm trying to run mysqldump to create a database snapshot, and I'm finding it will randomly stop midway, without reporting any error. My database is relatively small (about 100MB) and is using InnoDB. ...
4
votes
2answers
956 views

High Disk IO, How to mitigate?

I'm a programmer, not a DBA. Be gentle :) Overview InnoDB, MySQL mod_perl script, persistent connections script called every 20 seconds by thousands of users Problem High Disk IO (presumably ...
4
votes
1answer
3k views

How do you read MySQL InnoDB Monitor output?

I am new to DB Administration. I am trying to figure out the cause of deadlock exceptions, but I am unable to make sense of the logs. The log says that "transaction 1 is rolled back". Why is this the ...
2
votes
2answers
632 views

MySQL corruption even after full dump and re-import

We have a production issue where MySQL has started spitting InnoDB corrupt page errors and falling over. The error message seem to relate to indexes so I assume the data itself is ok and when I ...
3
votes
1answer
425 views

MySQL InnoDB Plugin default engine type not accepting config setting

I have a strange MySQL InnoDB issue that I've been struggling with for a few hours now. I'm asking this here, and also on the main StackOverflow site - I will cross-post the answer to the other site ...
2
votes
3answers
500 views

What is the optimal solution for converting a database schema?

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint ...
3
votes
2answers
2k views

Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE

If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction. This is done in order to "lock ...
5
votes
3answers
11k views

Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log). I have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log). The DB on S1 was using one data file ...
2
votes
1answer
666 views

Will a row update check all child tables which have foreign key relationships in InnoDB?

I have a master table and around 60 to 70 child tables, all are InnoDB tables and have foreign key relationships with the master table. Foreign key relations are On-Delete Cascade and On-Update ...
1
vote
1answer
135 views

String Pooling for MySQL

So I'm having this table that logs all errors happened in our production code. It looks like this: id | date | type | message | file | line ...
5
votes
2answers
5k views

Query runs a long time in some newer MySQL versions

I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware ...
2
votes
2answers
311 views

Is there any reason not to use Percona 'innodb_fast_checksum'?

I recently came across a change that Percona Server implemented over normal MySQL InnoDB. The new feature is innodb_fast_checksum XtraDB can use a more CPU-efficient algorithm, based on 4-byte ...
4
votes
1answer
3k views

Consistent logical backup of databases that use MyISAM and InnoDB engines

I have a question regarding the logical backup of MySQL databases that use both MyISAM and InnoDB. The mysqldump utility supports these two options: --single-transaction - Creates a consistent ...
4
votes
1answer
18k views

How to Recover an InnoDB table whose files were moved around

So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for ...
2
votes
1answer
514 views

MySQL proxy and SHOW PROCESSLIST query time

I have MySQL replication currently occurring between two masters bidirectionally, one in a data center locally, another over at Amazon EC2. Everything seems to function normally replication wise, no ...
12
votes
4answers
17k views

How do you identify InnoDB table corruption?

I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
7
votes
3answers
7k views

Best of MyISAM and InnoDB

Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
6
votes
3answers
472 views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
33
votes
3answers
49k views

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help Setup The servers are quite hefty with an ...
4
votes
2answers
5k views

Internal reason for killing process taking up long time in mysql

I copied a big table's structure with (it is an InnoDB table btw) CREATE TABLE tempTbl LIKE realTbl Then I changed an index, and filled it up so I could run some test. Filling it was done using: ...
3
votes
1answer
457 views

InnoDB has a transaction running, but “SHOW PROCESSLIST;” shows nothing

I have a replicated database setup, mostly Innodb databases/tables. The query that I believe caused the issue ran in about 20 minutes on the master database. This is where it gets weird. Show ...
2
votes
2answers
2k views

Corrupt InnoDB tables after every mysql restart. What's wrong?

Recently I changed a few settings in my mysql configuration. I did the following: - stop mysql - save the changed settings - delete all ibdata and ib_logfiles - start mysql Normally this works fine ...
11
votes
5answers
20k views

MySQL server has gone away obstructing import of large dumps

I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a ERROR 2006 (HY000) at line 7758: MySQL server ...
4
votes
2answers
1k views

Preordering the table by a specified column

I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering. Is it possible to order the table ...
12
votes
2answers
3k views

Rows missing after online conversion from MyISAM to InnoDB

We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down. Now that the ...
4
votes
1answer
2k views

MySQL 'LOAD DATA INFILE' : Can't replace entry when foreign key exists

Here's a minimal case for my database scheme: CREATE TABLE `test` ( `id` int(11) NOT NULL, `name` varchar(22) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT ...
1
vote
1answer
257 views

InnoDB Foreign key updating vs. manual in-application updating

I've been working on a "legacy" schema in MySQL Workbench that uses a lot of relations. All of the tables are MyISAM, however, and there are no foreign keys. However, I'm working on a new area of the ...
4
votes
3answers
633 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
5
votes
1answer
3k views

Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

I am Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction". I am going to have to update a program to allow deadlocks. Is it possible that SELECT statement ...
3
votes
1answer
1k views

MySQL InnoDB data file directly on a disk slice (fixed-size)?

It seems that ibdata1 is like a disk slice. In Solaris (UNIX) UFS filesystems (the common one before ZFS), one would separate their disk c0t0d0 into slices, say a 10GB drive would be cut into three ...
5
votes
3answers
10k views

Optimizing InnoDB default settings

I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
4
votes
2answers
6k views

Do INSERTs get auto-committed?

Our application fires an INSERT query to the MySQL Database to add records. I want to know whether or not the records get auto-committed. If I run the ROLLBACK command, when does the database perform ...
1
vote
1answer
340 views

Why did the MySQL Storage Engine Change on Restore in one scenario?

In my production database life, there was one tragic incident when the MySQL engine type was accidentally changed from InnoDB to MyISAM on restoring a recent backup. The result was that all ...
3
votes
2answers
2k views

Different MySQL Datafile Sizes After Restoration

I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86. Some weeks ago, we upgraded the company's ...
6
votes
3answers
6k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
4
votes
1answer
472 views

MySQL Replication: Insert generating VERY huge temporary tables

our replication slave is generating ~300GB temporary files while running relatively simple inserts. Our setup: Master: 5.0.45-0.sles10.x86_64, Slave: 5.0.85-win32, also tried with 5.1.57-win64, ...
2
votes
1answer
138 views

InnoDB foreign key and index duplication?

I'm have a part table like this: part_id INT manufacturer_id INT supplier_id INT part_number VARCHAR(50) part_description VARCHAR(120) with foreign ...
4
votes
2answers
3k views

Using MySQL triggers or transactions?

I want to ask your opinion in using MySQL triggers or transactions in a website. Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
4
votes
1answer
731 views

What are your use-cases for HandlerSocket?

There's a bit of hype around: HandlerSocket It seems like faster, nosql access to MySQL data is becoming more desirable. Percona Server is now shipping with HandlerSocket built in. My question is, ...
6
votes
2answers
3k views

Will these two queries result in a deadlock if executed in sequence?

This is almost certainly the cause of my other question, but I thought it was worth separating the two as I have a hypothesis based on the following log that I would love to have falsified or ...
6
votes
2answers
3k views

Trouble deciphering a deadlock in an innodb status log

We are accessing MySQL from the Microsoft ADO.NET connector. Occasionally we are seeing the following deadlock in our innodb status and haven't been able to identify the cause of the problem. It ...
7
votes
2answers
12k views

MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load

i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
5
votes
1answer
453 views

Dealing with temp tables when I have not control over db variables

I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically. I am wondering if there ...
3
votes
2answers
662 views

Should I move to InnoDB during a planned migration?

I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...