InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
0answers
38 views

Does MySQL InnoDB locks multiple rows with READ_COMMITTED isolation level?

I am in trouble while resolving LOCK WAIT TIMEOUT EXCEED error with MySQL InnoDB. I have gone through this article and it says if we use isolation level READ_COMMITTED then my update query should ...
0
votes
0answers
35 views

insert/update query running slow to specifi tables

I am monitoring MySQL client connections and queries run time. Found some insert/update queries to specific tables are slow than expected. I know the tables which are slow are big in size > 600K, i ...
1
vote
3answers
65 views

SQL self join with condition

I have table like: bu_id bu_name parent_id 1 DefaultBU NULL 601 bu1 1 602 bu2 601 603 bu3 NULL 604 bu4 603 I want to retrieve the ...
0
votes
1answer
213 views

Migrate Jira to MySql - Unknown system variable 'storage_engine'

I'm migrating JIRA in Windows from HSQL to MySQL and I got stuck with the message Unknown system variable 'storage_engine'. Though I created a backup, installed MySQL and copied J connector to libs ...
0
votes
0answers
903 views

Blob datatype issue with log file

I am getting continuously below error message in mysqld.log [ERROR] InnoDB: The total blob data length (10152274) is greater than 10% of the redo log file size (3072). Please increase ...
0
votes
0answers
54 views

How to break down a COMMIT transaction?

I suspect the following transaction is blocking a bunch of others in my mysql-5.6.19b.R1 database: ---TRANSACTION 10769842051, ACTIVE (PREPARED) 51 sec flushing log 3 lock struct(s), heap size 360, 1 ...
2
votes
1answer
214 views

How mysql (innodb) handles auto increment column

I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can ...
0
votes
3answers
27 views

Clustering MySQL with InnoDB engine

I have a MySQL single server setup with all tables using the InnoDB engine (because I need support for foreign keys). As response times are getting longer, I now believe I need to create a cluster of ...
-1
votes
1answer
55 views

Can we restore mysql db without .ibd & .frm files?

I have a data folder and the database folder with .frm & .ibd files is missing, any way to restore the database? Currently the data folder has: ibdata1 ib_logfile0 ib_logfile1
0
votes
1answer
204 views

Error 1067: The process terminated unexpectedly [InnoDB]

I am facing the following error when i try to restart the MySQL service after an unexpected shutdown of the database server last night. Could not start the MySQL service on Local Computer. Error ...
2
votes
1answer
71 views

MySQL auto increment column increases after insertion error occurs

I was doing some insertion tests in my database and I realized that when the INSERT statement fails, due error 1452, causes the auto_increment value increase by 1. Even though, there wasn't any row ...
0
votes
3answers
3k views

Duplicating database on the same server without data loss

I want to duplicate an innodb database on my mysql server for testing another version of the software. Using tools for example phpmyadmin reduce the database around 20 MB and a "lot" of rows are ...
0
votes
0answers
147 views

after a crash and disk i/o error, mysql won't start

We had an i/o error (don't know yet why) but we restarted the server, run an fsck and everything seemed to be fixed, the server booted ok, just that mysql doesn't want to start. Nothing works, we have ...
1
vote
1answer
56 views

MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels?

In MySQL documentation it's written: If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. Why ...
1
vote
0answers
143 views

MariaDB is ignoring /etc/my.cnf

The variable I am trying to change is innodb_buffer_pool_size. As you can see below the settings is 512M. But SELECT @@innodb_buffer_pool_size; returns 134217728. When I execute: /usr/libexec/mysqld ...
0
votes
0answers
33 views

what is causing Errors running from Localhost

wp_users: InnoDB: Tablespace is missing for table 'ftbw/wp_users' wp_usermeta: InnoDB: Tablespace is missing for table 'ftbw/wp_usermeta' wp_posts: InnoDB: Tablespace is missing for table ...
0
votes
2answers
69 views

Best configuration for MySQL? InnoDB and MyISAM

I read that InnoDB is supposed to be better at writing into MySQL than MyISAM, however, I'm trying it out, by running 3000 MySQL queries(Inserting rows, 8 Columns + id)... but results haven't turned ...
1
vote
2answers
48 views

Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?

I have a large (3M rows) InnoDB table in MySQL 5.6.21 in production and I want to add some columns. From the MySQL Documentation on Overview of Online DDL I understand that, even if this is an ...
46
votes
4answers
75k 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 ...
0
votes
0answers
30 views

where does MySQL store data which is inserted into tables?

I have a small table named animals in database named test: mysql> select * from animals; +----+------------+-----------+ | ID | species | location | +----+------------+-----------+ | 1 | ...
1
vote
3answers
8k 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
0answers
63 views

Are these slow query and locks make database server get high load sys CPU two times in a week?

As title I mention, my database server sometime get high sys CPU. I do some troubleshooting I am possible. We built database using mariadb galera. We use two nodes(db1, db2) + one garbd. When db2 ...
1
vote
2answers
158 views

Mysql causing high load average with large amount of server resources left

I've have 2 servers running a application and the server that runs the mysql sgbd is with the load average around 3 times more than the webserver. I think that is something wrong because i have a lot ...
1
vote
1answer
375 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
2
votes
1answer
367 views

Mysql HY000 the table table_name is full when adding new column

I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full. I changed my tmp_table_size and heap_size but mysql still says same error. ...
22
votes
1answer
37k views

What is the best way to reduce the size of ibdata in mysql?

I have some Production servers whose ibdata files increases in size day by day. It has already consumed 290GB of space. The tables in the servers are mostly InnoDB and there are high read and write ...
1
vote
2answers
283 views

How to recover previous values in Mysql Table?

I have MySQL Innodb table of 14M rows. There was a column called is_disabled which had different values. By mistake I ran the query without the where clause: update url_queue set is_disabled=1 ...
3
votes
1answer
2k views

Getting duplicate primary key for auto increment column

I have a MySQL Innodb table with 'id' as auto increment primary key column. My Scripts are doing following operations: insert ignore into tablename set x='a', y='b' update ignore tablename set ...
10
votes
6answers
19k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
1
vote
1answer
57 views

Write-lock a whole table during transaction

I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
6
votes
1answer
9k views

Error when dropping InnoDB database

I am trying to drop a database in mysql, but every time I get this error ERROR 2013 (HY000): Lost connection to MySQL server during query even logged in as root. Originally, I had a corrupted ...
0
votes
0answers
2k views

MariaDB MySQL server does not start

My MariaDB mysql server doesn't start with error 1067 This is the error log: 140222 0:43:44 InnoDB: The InnoDB memory heap is disabled 140222 0:43:44 InnoDB: Mutexes and rw_locks use ...
1
vote
1answer
62 views

Very weird Innodb Corruption Issue

I'll try to keep this to the point. System: OPenVZ VPS, debian 6, 512 MB RAM, 50 GB HDD (25GB free) I have a mysqldump dump file (3GB), that was created when the innodb database was working fine. ...
1
vote
2answers
94 views

MariaDB/InnoDB: What to do on “Locks in Status-Log but no locked table found”

We migrated lately from MySQL to MariaDB. SELECT VERSION() 10.0.13-MariaDB-log When executing this: SHOW ENGINE INNODB STATUS I get (shortened): ... ---TRANSACTION 279257007, ACTIVE 23981 ...
2
votes
1answer
107 views

Is it possible to restore a MySql database only using files from Data directory?

I have a mysql installed inside a windows virtual machine. The hard drive was crashed and the system is not able to restore all again. But I can have access to entire c: ROOT. So I went to ...
1
vote
1answer
57 views

Is it safe to disable InnoDB doublewrite buffering when setting Innodb_page_size = filesystem page size?

As I understand, doublewrite buffering is needed to avoid partial write, because Innodb_page_size default is 16K, while filesystem page size normallly is 4K; I wonder if I set "Innodb_page_size = ...
1
vote
2answers
140 views

Need to make MySQL's LOAD DATA LOCAL INFILE load large data faster

I need to load 4 million rows of data into a MySQL InnoDB table using LOAD DATA INFILE and would like to know if there are server configuration options I can tweak to get faster load. It took me 15 ...
1
vote
1answer
168 views

MySQL InnoDB 5.6.23 cannot start segmentation fault 11

I have a Linux Gentoo server which has OwnCloud application installed. It works for about 2 months with out any problems, and sudden this morning MySQL died. I have tried to restart mysql server and ...
1
vote
1answer
25 views

if set global transaction, how can I commit?

I found that MySQL can set a global transaction, but can I commit in another session? refer: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html
0
votes
1answer
110 views

MySQL: check max chracter length (ERROR 1118 (42000): Row size too large)

I'm trying to convert/copy MyISAM to InnoDB table and I'm getting the following error message: mysql> INSERT INTO test.innodb_table SELECT * FROM test.myisam_table ERROR 1118 (42000): Row size ...
83
votes
9answers
75k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
2
votes
2answers
401 views

Can anyone offer an opinion on handlersocket vs memcached performance in MySQL 5.6

I must say I was a little mis(un)-informed about the MySQL Memcached plugin which offers the same NoSQL like access to InnoDB tables as HandlerSocket, I had only had experience with entirely ...
0
votes
1answer
50 views

Query perfomance with multiple JOINs on MyISAM-based DB

I'm working with Enron Email Dataset in MySQL database. After I executed data import from the dump file into MyISAM-based DB, I got the set of tables without any connections between them via foreign ...
0
votes
1answer
76 views

Rolling update to all rows in innodb table (no rollback if aborted)

I have the following table, which stores the Social Security Death Master File database (~86M rows): CREATE TABLE `death_master_files` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ssn_an` smallint(5) ...
0
votes
1answer
39 views

InnoDB index concurrent inserts

I have an InnoDB table with an index on it. I have a few million rows to insert, so I thought I might take advantage of the row-level locking and do it in parallel. I'm inserting in batches of 10000, ...
1
vote
2answers
153 views

Speeding Inserts of longtext or longblob columns

i'm currently migrating a DB2 database to MySQL, using SQL WORKBENCH Tool It basically does a select from DB2 and Insert to DB2. It works fine, but on the tables with longblob or longtext columns ...
1
vote
1answer
470 views

Server stuck in INTERNAL DDL LOG RECOVERY IN PROGRESS

Recently, one of my servers had a crash. I had alot of trouble but managed to restore everything. But, Now, the first background thread upon startup is locked on "INTERNAL DDL LOG RECOVERY IN ...
7
votes
2answers
3k views

COUNT(*) on InnoDB whenever phpMyAdmin loads

It's a known issue that innodb is slow in SELECT count(*) FROM Table Hence I avoided that. But I notice that whenever I login to phpmyadmin, it will automatically execute a count(*) for my innodb ...
3
votes
2answers
147 views

Inner Join and Right Join same query

I'm using MySQL and InnoDB, I have a table reserver with 2 columns : idReservation and idChambre I have a table reservation and a table chambre Now I want my query to return every "chambre.id" who is ...
3
votes
4answers
6k views

Mysql innoDB write operations are extremely slow

I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...