InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
270 views

Corrupted InnoDB table, mysqlcheck and mysqldump crash server

I manage a Percona XtraDB Cluster that uses a network storage with a flapping connection. Periodically we experience a high iowait with crashes and remount of the fs in read-only. Replacing the ...
0
votes
1answer
2k views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
0
votes
1answer
2k views

The total number of locks exceeds the lock table size, even after increasing buffer pool size

I am trying to partition a table with 200 million records in it. This table already exists: CREATE TABLE linkmap_ntoken ( target_nToken varchar(200) NOT NULL, ntoken_hashcode int(15) NOT NULL, ...
0
votes
1answer
42 views

InnoDB: SELECT COUNT(*) using primary key 25 times slower on Windows

MySql 5.6, InnoDB. I have a table with 8 million records. It has an integer primary key, and a unique index u1 CHAR(40). It also has a few small fields and a VARCHAR(1000). SELECT COUNT(*) FROM ...
1
vote
1answer
869 views

MySQL InnoDB Deadlock For 2 simple insert queries

I have a deadlock for this two insert queries: insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, ...
0
votes
1answer
133 views

Row locking or avoiding duplicate insertion on primarykey/otherfield combo

I have a table that stores additional data pertaining to files/images saved in files elsewhere. Each entry can be associated with another type in the system. For users there may be one or more ...
0
votes
2answers
62 views

How can I defragment tables in MySQL?

I have a database named COUNTY. COUNTY has a fragmented table GEO. I executed OPTIMIZE TABLE GEO against the GEO table, but this returned the error "Table does not support optimize, doing ...
1
vote
1answer
103 views

What is the optimum way to store, update, and analyze integer data for 100+ attributes?

I need to store the count of all individual characters (A-Z, a-z, 0-9, spaces, punctuation marks, all symbols...) that occur in different texts which will be frequently changing. I will need to ...
4
votes
1answer
3k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in ...
1
vote
1answer
381 views

MySQL UPDATE query stuck on “Sending Data” State

I am having difficulty getting an UPDATE query to successfully execute, due to the process getting stuck on "Sending Data" state. I feel this differs from similar questions about the "Sending Data" ...
0
votes
1answer
109 views

Restore MySQL Database with only .frm files

One of our servers (running Ubuntu 12.04) recently crashed during a power outage, and MySQL refused to start back up. After a lot of trial and error, I decided to completely reinstall it. Since I ...
1
vote
2answers
58 views

How do you achieve a physically sorted table file organization?

Do I just declare an index on a column in the schema like ? ALTER TABLE `table` ADD INDEX `index_name` (`colDate`) I want the table records to be physically sorted not just when doing an ORDER BY ...
1
vote
1answer
69 views

Optimize slow query

I have this query that takes over half an hour to run on our Linux MySQL db... SELECT ls.emailaddress , lse.subscriberid , lse.eventtype , count(lse.eventtype) as total FROM ...
2
votes
0answers
55 views

Identifying table causing runaway rollback in MySQL 5.5 (InnoDB / Windows)

I have a large database (180 GB), running under Windows, with a runaway rollback that starts running as soon as the database is started up. I have started the database with innodb_force_recovery=3 ...
6
votes
1answer
4k 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 ...
1
vote
1answer
146 views

Sudden periods of performance degradation

For the past couple of days, Ive been observing periods of huge performance degradation. CPU usage on our master MySQL server (Percona Server (GPL), Release rel24.1, Revision 217, InnoDB version ...
1
vote
3answers
77 views

Very slow Insert and update on 20 core 250 Gb RAM dedicated MYSQL 5.5 server on Ubuntu [closed]

Most of the data is in tbl_user_ads. Its a huge table. Many concurrent processes (over 1000) try to select, insert or update this table. The CPU utilization is just around 2%. As I increase the ...
0
votes
1answer
198 views

Doubts about Converting MyISAM to InnoDB (master/slave and more)

I want to convert my MySQL DB (46 Tables, 2.1 GB) from MyISAM to InnoDB. I have a couple of questions about this process. Since I have a Master/Slave structure, do I need to manage this and change ...
1
vote
1answer
45 views

Mysql stop working woocommerce

I have a WooCommerce website hosted on an Amazon EC2 centOS server with 4GB RAM and 4GB swap. My problem is that sometimes MySQL stops working due to failure to allocate memory and also the website ...
2
votes
1answer
274 views

Mysqli prepare error: Unknown storage engine 'InnoDB'

I'm getting this error on my XenForo installation. Server logs show out of memory because of too many php processes opened up which cause mysqld to shutdown. The WordPress installation associated with ...
6
votes
1answer
19k views

Recover MySQL database from data folder without ibdata1 from ibd files

My WAMP directory accidentally get deleted by another user. Only data folder in MySQLis available. And, in that only database-folders (folders in "\bin\mysql\mysql5.6.12\data\" with name of databases) ...
14
votes
2answers
22k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. ...
3
votes
3answers
891 views

Optimal Number of MySQL InnoDB Buffer Pool Instances

Server Characteristics Total system RAM: 8GB (running MySQL + other stuff than MySQL on it i.e. not dedicated to MySQL) Number of CPU Cores: 6 I have data in the db amounting to about 2GB I have ...
0
votes
1answer
54 views

MySQL Query causing high CPU and taking forever to execute

this below query takes a long time to execute in excess of 120 seconds, upon checking the explain plan it seems to be using using where, using temporary; using filesort. Since the application is a ...
0
votes
1answer
53 views

when do i usually optimize an innodb table?

I have a table that's always used 24/7. There is always a stream of update and/or insert of 10-20 rows per 30 to 60 seconds. As stated on the MYSQL documentation: "After doing substantial insert, ...
1
vote
0answers
100 views

How to configure correctly Mysql heavy used server to prevent it from write IO huge spikes?

I have mysql 5.6 server which handles thousands of users online. All tables are Innodb. The problem is, that once or twice a day it writes huge amount of data to disk, or it slows down a lot. Than ...
1
vote
1answer
68 views

Multi-Column PK or Auto-Increment PK?

In another question I mentioned that I have a table where, in order to ensure uniqueness, I need to use multiple columns as the PK. However, lookups are usually only done using the first of those ...
2
votes
4answers
976 views

Calculate row size and max row size for a table

Problem: Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate ...
0
votes
0answers
83 views

Discard tablespace of temporary innodb table in MySQL

I have following problem, i need to discard tablespace of temporary table(file is present, i want to delete this file too) or somehow completely remove this table. This table can't be shown with ...
1
vote
3answers
16k views

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
0
votes
1answer
350 views

MariaDB/InnoDB: Temp file write failure

When attempting to alter a table to enable compression by modifying the KEY_BLOCK_SIZE I received the error: ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB The command ...
0
votes
1answer
3k views

Unable to select the records from specific partition in mysql?

I've created a table using the following query, CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25) NOT NULL, store_id ...
4
votes
2answers
3k views

“freeing items” taking forever on inserts, updates and deletes

Recently I migrated all my MySQL database tables from MyISAM engine to InnoDB. I'm using the DotCloud MySQL service as my server (http://docs.dotcloud.com/services/mysql/). After the migration, ...
2
votes
3answers
2k views

ALTER primary key column from INT to BIGINT in production (MySQL 5.6.19a)

Some INNODB tables in our production database are about to hit the INT AUTO_INCREMENT limit of 2147483647 and we need to alter them to BIGINT otherwise writes will start failing. The tables are in a ...
9
votes
3answers
19k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
3
votes
4answers
8k views

How to add column to big table in MySQL

I'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried: Alter table in screen with table ...
2
votes
3answers
1k views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
2answers
45 views

What storage engine do I need - Mysql?

I have designed a database and came to the point of actually deciding which storage engine is better for my case. I estimate that during the first few years I would have around 100,000 - 500,000 ...
5
votes
3answers
2k views

Insert Speeds for large batches

In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table. Lets take a sample table ...
1
vote
0answers
62 views

Update queries randomly slow on INNODB Mysql

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine. The issue is that, ...
1
vote
1answer
172 views

InnoDB Failure of some kind

I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked. InnoDB is listed under SHOW ENGINES; Any idea ...
2
votes
3answers
739 views

UPDATE waits for lock too long after SELECT in MySQL (Innodb)

I have PHP function Pics::getPicture (yii2 ORM) which selects one row from table and then updates one field from this row. The table structure contains only id (PK), path (VARCHAR(500)) and seen ...
4
votes
3answers
148 views

MySQL Index Creation Internals

Right now there are two ways to build an index of a table in MySQL: Create the table structure first, then import the data and add indexes later. Create the table structure with indexes and then ...
1
vote
1answer
1k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found

I've got the same error, although I'm running galera cluster with 3 nodes. I tried following the instructions from InnoDB: Error: Table "mysql"."innodb_table_stats" not found after ...
10
votes
5answers
13k views

Why using innodb_file_per_table?

There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
0
votes
0answers
48 views

Percona: Innobackupex failing when taking incremental backup

I have been trying to take incremental backup as below and its getting error all the time. Innobackupex 2.1.8 Linux x86_64 Mysql 5.5.45 Step1: innobackupex -uxxx --stream=xbstream datadir > ...
1
vote
1answer
49 views

How can i write a stored procedure which will perform a calculation and store the result in a table?

I have two tables: CREATE TABLE `siteArea` ( `length` decimal(6,2) NOT NULL, `breadth` decimal(6,2) NOT NULL, `Area` decimal(6,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE ...
17
votes
4answers
10k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
2
votes
2answers
2k views

InnoDB tablespace critical

Could the error shown on the screenshot be the reason why my site is very slow? If so, I really need help to fix it since I am far from being an engineer!
1
vote
3answers
179 views

Convert MyISAM to InnoDB

What are the considerations needed when converting myisam to innodb. I have a table around 80 GB with engine myisam. I would like to convert into innodb. What are the considerations needed to ...