InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
0answers
92 views

How to convert .ibd files to .sql [closed]

Could any one please tell how to convert .ibd , .frm, .MYD, .MYI FILES TO .SQL . Or is there any way to open this files in php my admin .. Please tell how to convert this to .sql file
4
votes
2answers
88 views

What's the risk of keeping a table with row size > 8126 in MySQL 5.7?

I just upgraded from MySQL 5.6 to 5.7, and had the bad surprise to discover that a WordPress plugin table contains a ridiculous 438 fields, and exceeds the row size limit of 8126 bytes. Here is the ...
1
vote
1answer
1k views

Mysql Optimization suggessions

I have an InnoDB database table with more than 2 million rows. I am running efficienty indexed queries to reduce server load, but site becomes slower day by day. I ran Mysqltuner.pl to identify any ...
4
votes
1answer
11k views

MySQL: Row size too large (> 8126)

I have a table which has 300 columns in it. Each column is up to 255 bytes (there IS a business justification for this). When I try it create it using VARCHAR(255), I go past the limit for the ...
2
votes
2answers
9k views

Unable to drop the table since its showing waiting for meta data lock

We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on ...
2
votes
1answer
127 views

autoincrement column issue in MYSQL

We are using an autoincrement column in a temporary table, and doing multiple time inserts. The autogenerated ids are not being generated consecutive (I have attached a sample simple query: 16 is ...
1
vote
1answer
33 views

Innodb Isolation levels and locking performance

I am reading a lot about locking and isolation levels in MySQL and InnoDB. I understood the technical differences and basic approach, but I still fail to understand a situation where for example READ ...
1
vote
2answers
64 views

Select query on an index column with gap locking

I am trying to understand in which cases will a select query make a gap locking on an indexed column select. What I read so far: Select queries use the repeatable read mode in InnoDB and will not ...
0
votes
2answers
74 views

Storing multiple types of users and their respective credentials in separate or same table?

So there have been many questions on Stack Exchange DBA and Stack Overflow that go close to answering this, but there's still some dispute within my team on how to properly do a database design. In ...
3
votes
1answer
51 views

Dead Locks between an insert ignore and and an update

Using MySQL InnoDB. As far as I know and read so many time dead locks only happens when one transaction is locking resource A and waiting for B and another transaction does the opposite. So with ...
1
vote
1answer
49 views

My MySQL Server Died

Approximately 1 time per week my MySQL server is shutting down. This is what the log looks like: 2016-03-18 13:46:25 13166 [Warning] Buffered warning: Changed limits: max_open_files: 1024 ...
0
votes
0answers
17 views

innotop “Command Summary”

We provide DBaaS. This is innotop C output: We have only 8% read workload? The rest is write? I don't understand this is very uncommon workload for DBaaS? Statistics since last restart? What is ...
1
vote
0answers
327 views

Negative InnoDB Write Log efficiency in mysqltuner.pl results

I noticed something strange in results from mysqltuner.pl. I see this in mysqltuner output from three of my four servers with MariaDB 10.1.12: [OK] You have the latest version of ...
2
votes
0answers
75 views

InnoDB crashes on trx rollback

I have InnoDB assertion failure during the trx rollback 160316 10:47:31 InnoDB: Rolling back trx with id 5ADBE4173, 1 rows to undo 160316 10:47:31 InnoDB: Assertion failure in thread ...
2
votes
1answer
33 views

Saving data for a colossal maze

I want to build a labyrinth in which players can interact with others through leaving messages for one another, so in its most basic form each "tile" of the map is a forum thread or chat room. ...
0
votes
0answers
4 views

Different innodb_buffer_pool usage in nodes of a cluster

I have a 3-nodes Percona XtraDB Cluster (MySQL 5.6) that was put into operation three weeks ago. I noticed that the innodb_buffer_pool usage is very different in each of the nodes. In node #1, usage ...
0
votes
0answers
27 views

MySQL Storage Engine and Memory

I recently found out that, the databases does not have standard architecture(data model), some of them are, No relationship between tables(entities). No indexes defined. Only integer and varchar ...
1
vote
1answer
31 views

InnoDB slow query (few derived and dependent queries)

I have a select query which always worked pretty fast but data kept increasing which is why this query became very slow (2/3mins). I need to get all 0 values from those tables in the last WHERE ...
9
votes
4answers
18k views

How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
0
votes
2answers
151 views

Multiple WHERE IN on multiple columns

I am not very experienced with sql queries and looking for an efficient way to perform the same WHERE IN condition on multiple columns. More specifically, i'm doing the following: SELECT a, b FROM ...
0
votes
2answers
7k views

Row size too large (> 8126)

I'm facing the following problem. Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, ...
0
votes
1answer
18 views

InnoDB, Writing TRIGGER to INSERT into tables

Table1 "user_address" Field "u_id" Field "street_name" Field "locality_name" Table2 "street_master" Field "s_id" Field "s_name" I am supposed to write a trigger which BEFORE INSERT ON ...
3
votes
1answer
60 views

InnoDB - Intermittent CPU spikes on a large database

I have a machine with a large MySQL 5.6 database (multiple tables with 10-100 million rows). It has a considerable amount of load, especially during the evening and is running on a 16 core machine. ...
0
votes
1answer
60 views

Preventing MySQL index corruption

I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen ...
1
vote
3answers
55 views

character set, not set correctly

I have two database with a table with utf8 table, I wanted to change the encoding of a column, I used two scenarios but a problem happened to me. at first the table was like this : CREATE TABLE ...
1
vote
1answer
103 views

Can I emulate Oracle's autonomous transaction in MySQL?

I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any ...
0
votes
1answer
52 views

Mysql only using 10% of cache [duplicate]

I have a dedicated Mysql server that only uses 1.1 GB out of 16 GB ram (picture link 1). I belive this leads to my Disk I/O utilization beeing at 75% peaks (picture link1). How can i configure sql ...
1
vote
1answer
32 views

Altering a large table to INNODB

I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master. I tried to use the ...
1
vote
1answer
53 views

Are InnoDB files binary compatible between Windows and Linux?

I have seen many posts about recovering InnoDB files. The versions I have are: Windows: 5.6.17 Linux 5.0.96 (I cannot upgrade this at this time) If I create a new InnoDB file on the Windows ...
1
vote
1answer
59 views

Which error codes are safe for `slave_skip_errors` with MySQL Innodb

Does anyone know which error codes are safe to ignore during replication, meaning the slave will remain consistent with the master? For example [mysqld] ...
0
votes
1answer
61 views

If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?

We have an issue with MySQL InnoDB performance. We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts ...
7
votes
3answers
23k 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
82 views

memory leaks in innodb

I have MySQL server 5.6 and I use stress test to evaluate the performance of it. after the test the result of mysqltuner.pl was like below : root@master-1:/home/debian# perl mysqltuner.pl Please ...
0
votes
1answer
81 views

Why Write IOPS,Write throughput,Queue depth decreased on AWS/RDS after changing innodb_flush_log_at_trx_commit and sync_binlog?

I changed values for my Mysql parameters 1. innodb_flush_log_at_trx_commit=2(before it was 1) 2. sync_binlog=0(before it was 1) This has changed some of my parameters on AW RDS drastically ,here ...
1
vote
1answer
58 views

InnoDB AUTO_INCREMENT resetting itself without MySQL restart

We've recently noticed the following scenario happening in many of our databases: A record is inserted, and receives an AUTO_INCREMENT primary key. The record is deleted. Another record is inserted, ...
0
votes
0answers
26 views

How to improve my big MySQL database with low concurrence?

I have a MySQL database who is been used to storage a large amount of data, it doesn't have a lot of concurrence( its minimum), but every day a big amounts of data is loaded and also a profiling ...
1
vote
2answers
297 views

MySQL query INSERT INTO … failed - The Table “x” is full

I apologize if my question is incomplete or noobish, I have extremely limited knowledge of SQL but the boss wants me to figure this one out. Environment: Webmin/Virtualmin | MySQL version 5.1.73 on ...
1
vote
3answers
4k views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
0
votes
1answer
48 views

Is it good idea to use RAID 1 mirroring to back up database content

I'm working on web hosting project. The planned system structure is going to be like this: one main hard-drive will contain the binary of mysql. A second hard-drive will contain the datadir the ...
8
votes
2answers
5k views

Deleting MySQL table with pending transactions

Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)? What happened: I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
2
votes
3answers
6k views

Select query takes more time than it should

I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure: Below is some of ...
0
votes
0answers
26 views

Problem creating database and tables from script [duplicate]

I've done a model diagram with Mysql Workbench, when export the SQL script I run it on my MySql on my develpment ubuntu and works fine but when I run it on the production server the scripts fails. The ...
4
votes
2answers
69 views

Performance for MySQL InnoDB B+Tree index with many duplicate values

I'm trying to diagnose seemingly random performance issues with our database server. Below is a simplified scenario, hopefully generic enough to serve as a useful future reference for anyone looking ...
1
vote
1answer
46 views

How can I 'further' improve my table and queries

This question is an extension of this question. Where Rick James kindly provided a list of things I should change in my table and query. I also changed my application so that unused filters weren't ...
13
votes
2answers
25k views

How to solve “The table … is full” with “innodb_file_per_table”?

I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave ...
0
votes
0answers
44 views

Need help with corrupted MySQL database

I could use some step by step help on using innodb to hopefully recover a corrupted MySQL database. We are running moodle on a ubuntu server which uses the LAMP stack. The database has been corrupted ...
2
votes
0answers
72 views

TokuDB vs InnoDB Database data size

I have two percona servers, one running with TokuDB tables and the other with InnoDB tables. On each server, i have create a single database, with a single table in it containing 10 millions records ...
1
vote
0answers
32 views

Table clustered-index and partitioning in innoDB

I have a table with 45 columns, 3 of which are relevant: table Transactions(tr_id : int primary key, client : int, tr_date : datetime); The inserts to the table are done client-by-client The ...
1
vote
1answer
249 views

MySQL 5.6 Server start fails: InnoDB Operating system error number 23

I use a local MySQL 5.6 Server (InnoDB) on a Windows 7 machine to store several schemas of a research project. Until yesterday the system ran without problems. During a query, MySQL Workbench ...
0
votes
2answers
563 views

Abnormally high MySQL writes and larger than normal Binary Log files. How can I determine what caused this?

We have a MySQL master database which replicates to a MySQL slave. We were experiencing issues where MySQL was showing a high number of writes (but not an increased number of queries being ran) for a ...