InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
0answers
19 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 ...
0
votes
2answers
13 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 ...
1
vote
1answer
27 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 ...
0
votes
1answer
73 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in mysql although I feel the schema should be optimized for it since it's based on the wordpress database model. I've got 4 tables... ...
1
vote
1answer
16 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
22 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
15 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
1
vote
1answer
43 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 ...
0
votes
1answer
35 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 ...
0
votes
0answers
26 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 ...
0
votes
1answer
68 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
24 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
38 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 ...
0
votes
1answer
31 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 ...
3
votes
2answers
99 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 ...
0
votes
0answers
28 views

Cannot open table slave_master_info

I have a dabatase working on InnoDB engine Now it is damaged. I can´t open any table, it shows error 1146 Table 'database.tablefromdatabase' doesn't exist I tried innodb_force_recovery with all ...
1
vote
1answer
31 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. ...
0
votes
0answers
29 views

CPU jumps massively and stays high, under very light load

We had two separate RDS instances exhibit the same pattern today. This was both during low load times, one during the lowest load time for that server. They both started at almost the same time. No ...
0
votes
1answer
55 views

MYSQL any way to improve this query's performance?

MYSQL version 5.6.23 CENTOS 7 Hi, I have the following query that is coming from an application that I am hoping to make more efficient from the mysql side of things. I have an index built on ...
0
votes
1answer
32 views

InnoDB Table has backed up INSERT and UPDATE queries, high CPU

I am running an MySQL DB with all InnoDB tables. I often have many queries backed up for the same table when I run SHOW PROCESSLIST; Basically, I keep a table of every logged in customer and update ...
0
votes
1answer
36 views

What is the performance benefit when using a temporary table to update a regular table?

I'm trying to understand what the performance benefit between two different methods of updating multiple table rows. For the first method we have an explicit update statement for each line to be ...
0
votes
0answers
39 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 ...
0
votes
0answers
47 views

Why is MySQL not saving inserted data?

So I have the following insert statement that I run through MySQL Workbench: INSERT INTO PeakOnlineCount (time_stamp, LocationId, Count) values ('2015-03-26', 0, 1) If I run the following query: ...
0
votes
0answers
73 views

How to restore mysql database in XAMPP using backup of mysql folder?

I have a backup of the entire xampp/mysql folder. How can I use it to recover my old db on a fresh installation of XAMPP? Simply copying the old xampp/mysql folder to new xampp leads to mysql errors ...
0
votes
2answers
36 views

Slow database drop in mysql

Just installed a new 24 core server and it is noticeably slower when doing truncates and drops. Example: $ mysql -u root lmcdental_test2 Reading table information for completion of table and column ...
0
votes
1answer
18 views

I have a DB that is locking up from numerous updates to a table: Should I convert it from MyISAM to InnoDB?

FYI: I don't believe this is a duplicate question, I've looked and most are general questions with vague answers. See more specifics below: Currently a database I am in charge of locks up every once ...
0
votes
0answers
41 views

Percona Master-Master lag issues

I have 2 servers with master-master replication using Percona MySQL 5.6 and I am having replication lag in the second server: db-02, however db-01 never had a lag situation. What could be causing ...
0
votes
1answer
15 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 ...
4
votes
1answer
60 views

Does MySQL exectution plan depend on available indexes?

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available? ...
0
votes
1answer
58 views

Complex query performance in MySQL InnoDB versus MyISAM

I am running on MariaDB 10.0.15 on a 4GB RAM cloud server with Local SSD disk for database. My model: users <n---n> right_holders <n---n> tracks ---n> raw_detections I have this ...
1
vote
2answers
81 views

Large ibdata file in MySQL

I am having MySQL database with all my tables as InnoDB with file_per_table config on. But Still I m seeing huge ibdata file ( ~50GB). All tables are having .ibd file as well. This is a machine I have ...
1
vote
2answers
70 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 ...
2
votes
2answers
99 views

mysql write lock

I dont add any manual locking or any transaction anything, just manual running queries from php script, and execute below two update queries at the same time update table_x set status = 'PROCESSING' ...
0
votes
1answer
28 views

Yet another “ERROR 1005 can't create table” question

Well, I've been looking at the ERROR 1005 questions, but I haven't been able to see my error among the issues addressed by those questions. This code is generated by MySQL Workbench under Linux. Can ...
0
votes
1answer
51 views

Why does InnoDB allow a 772 byte unique constraint?

I've defined a unique constraint for two columns: A bigint and a VARCHAR(256) COLLATE utf8mb4_unicode_ci Then this error occured (which I'm already familiar with): Specified key was too long; max ...
1
vote
1answer
33 views

“Unknown table engine 'innodb'” error after changing my.cnf

I am getting "Unknown table engine 'innodb'" for some query after changing my my.cnf. I have reverted the old my.cnf and restarted mysql and now I can see innodb. Could you please help me figure out ...
0
votes
0answers
19 views

Big cpu caused by mysql

I have big cpu caused by mysql, 3 vcores, centos 6 64bits http://s3.postimg.org/z8l73e98z/mysql.png [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 ...
0
votes
1answer
68 views

Can't Delete Orphaned MySQL Temp Table

One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes). ls -l sql -rw-rw---- 1 mysql mysql 8570 Mar 13 12:05 #sql-ib32694.frm ...
0
votes
1answer
15 views

What if we skip the slave by counter 1 when it failed at event 1 of a transaction which has events 2 and 3

We have a master-slave replication which has all Innodb data. I ran 3 update statements under one transaction on master. ============= start transaction update T1 update T2 update T3 end ...
0
votes
2answers
39 views

troubleshoot high value for mysql innodb pages written per second

Our database is writting about 600 pages a second to its innodb buffer pools. Usually this value is about 10 pages/sec, so the server is experiencing a very high IO utilization, around 50%. Is there ...
0
votes
1answer
53 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 ...
0
votes
2answers
37 views

How can delete an orphaned temporary table on Mysql Innodb?

I was issuing an Alter table statement and suddenly the server crashed, Now i can not run any alter table to that table because it shows me the error 1050 and it says that a table named #sql1504d ...
1
vote
1answer
48 views

Concurrency when simulating autoincrement with triggers on MySQL

I am updating an old application to implement virtual tenancy, which involves adding a tenant_id column to the primary key of every single table. The application uses auto-incrementing columns ...
1
vote
1answer
20 views

Acceptable to use Antelope and Barracuda simultaneously?

I've been doing some reading about the Antelope and Barracuda file formats for InnoDB. I recall at least one source mentioning that the two should not be used simultaneously. I can't think of a good ...
0
votes
1answer
21 views

Will a static MySQL database become bigger in file size over time, even without anyone writing to it?

I backed up a database a few weeks ago, then for comparison downloaded it again. It was about 10,000 kB larger. There should not be anyone writing to it, but there are lots of reads. Will a static ...
1
vote
1answer
85 views

What is InnoDB Buffer Usage and how to clear it?

In Mysql Workbench when I go in Server > Server Status there is a field called "InnoDB Buffer Usage" It is "95%" even if there is no query running. Why? It affects my performance in my new queries? ...
3
votes
0answers
55 views

Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 ...
1
vote
2answers
36 views

InnoDB row metadata size (minimum row size)

I'm trying to decide whether to normalize a thing further, and need to know what the smallest a row in InnoDB can be (I've got MySQL 5.6). This is not exactly my case, but for simplicity, let's ...
0
votes
2answers
49 views

MySQL - Changing from system tablespace to file per table

The MySQL database I am working on has the InnoDB tables and indexes stored in the system tablespace which is now > 20Gb. Some years ago I managed a MySQL database that made use of a file per table ...
3
votes
2answers
144 views

How to speed up mysql export and import

I need to perform a DB migration from Server A to Server B. Server A: MySQL version installed 5.0.6 Server B: MySQL version installed 5.6.20 OS: Both are running RedHat Linux 64bit. 16 ...