InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
2answers
338 views

MySQL: Merge Same Database on Several Location into Single Location

I have registration form, which exactly same for several location. Each of them has REG column, which this field is unique for each row. Basically each data input have different REG number for it, ...
0
votes
2answers
146 views

Does MySQL Workbench lock database during migration process?

I am doing a migration of my database from one server (RDS) to another (localhost) via the MySQL workbench tool. I want to know if during this process my database will be locked for write and read ...
0
votes
0answers
11 views

Unable to rename table due to metadata lock [on hold]

I am trying to rename a table but it always just hangs with a Waiting for table metadata lock. Here is how I am trying to do it: start transaction; RENAME TABLE `main_territorypricing` TO `...
-1
votes
1answer
13 views

How can we migrate Data with MySQL 5.1 - MyISAM to another server with MySQL 5.6 - InnoDB

Currently we have a MySQL 5.1 - MyISAM database on a physical server. We need to move it to another server with MySQL 5.6 - InnoDB at another location. DB size is quite large - 210GB Can someone ...
1
vote
1answer
167 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 ...
4
votes
1answer
2k views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
1
vote
1answer
2k views

MySQL SELECT query hangs - Opening Tables INNODB

On my server I have 6 MySQL databases. They all power Wordpress websites. For some reason, 2 of these databases are not accessible. To my knowledge they use INNODB, but I can't confirm it as even ...
0
votes
2answers
231 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 ...
0
votes
1answer
17 views

Mysql : Aborted connection …/… (Got an error reading communication packets)

This error message had submitted many times in this forum. I have tried the following without success. Context This is a Symfony1/Doctrine1 web application. The error appear on my computer and on ...
1
vote
1answer
300 views

What's the approach to figure out reason for mysql crash?

The database I'm using crashes a lot recently. And I can't find a decent way to dig into the issue. I looked into mysql error log, but don't know how to start. I know there was a "load data infile" ...
0
votes
1answer
215 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
1answer
168 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...
0
votes
2answers
219 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
1answer
27 views

MySQL usage suddenly hitting through the roof

We have a site with MySQL DB running properly for more than 2 years. However, 2 weeks ago we noticed a sudden spike in load. We started the MySQL service and load normalized. We assumed the issue was ...
4
votes
5answers
18k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
0
votes
3answers
83 views

Memory(buffer pool) not populating on mysql start on Percona 5.6.28

We are testing Percona 5.6.28 at the moment, during our testing we found out even though the innodb_buffer_pool_populate is set to ON, it doesn't populate the memory(buffer pool) during mysql start. ...
5
votes
2answers
279 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------...
1
vote
1answer
23 views

MySQL 5.6 InnoDB doesn't work

We have been having issues with one of our Windows environments. It uses MySQL and has been running on MyISAM only. We need to create a few InnoDB tables but the engine won't start. We tried changing ...
2
votes
1answer
272 views

innodb , mysql Indexes difference when adding index

When I am altering one of my MySQL table to add an index I am getting below error in error log. Table a/b contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined ...
0
votes
0answers
21 views

MySQL “stalling” when database grows in size

Having a problem where lots of queries timeout. From innodb engine output it looks like lots of queries are blocked? --Thread 139681112069888 has waited at btr0cur.c line 523 for 0.00 seconds the ...
3
votes
1answer
660 views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
1
vote
1answer
64 views

Optimize mysql database performance

I have a database with some relatively small tables and one big table with about 10 million rows (11 columns) that I would like to optimize its performance. The table looks like this: Id: (int 11, ...
0
votes
0answers
26 views

Getting values from tables to string, combined one-to-many and many-to-many

I am stuck with one task. I have a database with one-to-many and many-to-many relationship. The whole problem is that I need to take from one table all persons in to a separate strings and count how ...
2
votes
1answer
223 views

Why do innodb tables get corrupted so easily?

I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10. We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been ...
0
votes
2answers
20 views

Innodb_buffer_pool explanation

The title might be a bit misleading, as i am fully aware of the concept of innodb buffer pool, yet want to understand something specific about it's calculation process. According to the mysql docs: ...
0
votes
2answers
33 views

MySQL used all drive space running an ALTER and now won't start

I tried to alter a table without paying attention to the size, and MySQL ate up all the free space on the drive. I deleted a million or so old records but the space was still being used. Googling ...
1
vote
3answers
20 views

Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

Consider a table created like this: CREATE TABLE `someTable` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `owner_id` bigint(11) unsigned NOT NULL, `device_id` bigint(11) unsigned NOT ...
5
votes
2answers
2k views

How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB

I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told ...
-1
votes
0answers
51 views

Difference between Mysql table and histats analyzer

Explaining problem immediately. I have partly huge number of transactions (~ 30k per minute) and after putting analyzer system, weird issue comes up. say we have below psudo: Request received ...
1
vote
1answer
1k 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 -...
3
votes
2answers
17k views

How can extract the table schema from just the .frm file?

I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine. I have read a fair number of tutorials today ...
0
votes
0answers
19 views

MySQL - InnoDB deadlock - simple update query

I am facing deadlock issue in one of our production server, details are as follows: Deadlock info: 160711 10:44:45 *** (1) TRANSACTION: TRANSACTION A89CB314, ACTIVE 2 sec fetching rows mysql tables ...
0
votes
1answer
13 views

Maximum or suggested number of bound query-parameters in InnoDB (MySQL)?

We have code that adds thousands of parameters to a query as members of an IN (...) criterion. My aesthetics say to bucket it into multiple queries, but I want an objective reason. Does InnoDB have a ...
4
votes
2answers
397 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 ...
0
votes
2answers
43 views

What is effective way to delete duplicate records based on multiple columns in MySQL?

I have a table named users having approx 70 Million records, out of which I guess 10 Million is duplicate. How to remove those 10 Million records keeping one copy of that. Two records are said ...
0
votes
2answers
1k views

Restore InnoDB to different machine

It was pointed to me from serverfault that the following question would be a better fit here so this is basically my original question from there. I currently have backups that were taken with ...
1
vote
0answers
32 views

How similar is using a secondary index in InnoDB to using a second table joining 'into' the PK?

Since InnoDB secondary indexes are separate B-trees with the key as the sort order and the PK as the 'value' stored, I assumed that reading into the main table by the secondary index was a lot like ...
0
votes
0answers
37 views

MySQL query much slower after conversion from latin1/myisam to utf-8/innodb

I converted one table from latin1 to utf-8 and changed the engine from MyISAM to InnoDB. After that conversion the same select query is 6x times slower than before. I converted the table with the ...
0
votes
0answers
47 views

MySQL server keeps crashing on startup

MySQL keeps crashing on start with the following message. It crashed after I was trying to batch upload data into a table and now it can't start since. This is an already existing database so it ...
10
votes
3answers
17k views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB sql dump that I need to import into MySQL. I haven't had to import such a huge sql dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
0
votes
2answers
157 views

How does InnoDB access disk?

I know that block(multiple disk sectors) is the basic unit of data transfer for the virtual file system so, if NTFS uses 4 KB cluster size for files how does InnoDB access disk if it uses 8 KB page ...
0
votes
1answer
118 views

Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output

I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS ...
1
vote
1answer
62 views

MySQL lost connection during query after specific record

I started getting the usual Error Code 2013 Lost connection to MYSQL server during query at a random moment when working on a PHP script. Some stuffs in the software still works, but the specific ...
0
votes
1answer
403 views

Large long-running reporting queries are causing very slow new mysql connections

Usually, mysql's connecton time is in the 0.001 second range. However, when I run a large reporting query (that spans multiple tables with aggregates), I get tons of warnings saying that new mysql ...
0
votes
1answer
34 views

Can InnoDB use a clustered index as a covering index?

The scenario I have in mind is a query like: SELECT id FROM table WHERE id = ? or equivalently, SELECT 1 FROM table WHERE id = ? So here's some background: In a B+ tree (which InnoDB uses as ...
6
votes
1answer
24k 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) ...
1
vote
2answers
2k views

Which one to use? InnoDB Replication or MySQL Cluster?

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual ...
1
vote
1answer
320 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: ...
7
votes
2answers
96 views

MySQL Innodb_row_lock_current_waits is 2^64 - 1

I have a MySQL 5.6 server which we monitor using Nagios. Over the weekend, it's started complaining about the Innodb_row_lock_current_waits, which currently has a value of 18446744073709551615, yes, ...
1
vote
1answer
27 views

MYSQL TABLESPACE = innodb_file_per_table GIVING ERROR 1030 (HY000): Got error 168 from storage engine

I have tried this statement to place a tables in different disks. Operating system is Linux Oracle 7, I've changed permissions in the directory with chmod -vR 777, to have total access to the disk ...