InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
2answers
516 views

MySQL Enterprise Backup error: Log scan was only able to reach xxxx

I use MySQL Enterprise Backup to make incremental backups, but when I apply a restore (copy-back), I can't remake a backup. I get this error: mysqlbackup: ERROR: Log scan was only able to reach ...
0
votes
1answer
548 views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
3
votes
2answers
444 views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
2
votes
2answers
254 views

Bin logs with high i/o

I'm DBA (IBM Informix) and already use MySQL with Drupal , where have low workload. So, my knowledge of MySQL is minimal. Few days ago I start use MySQL with a basic configuration as back-end for a ...
3
votes
1answer
2k 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 ...
1
vote
1answer
147 views

mysql freeze/crash on JOIN

here is query: SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image, nw.status, src.title as src_title, src.keyword as src_keyword, src.url as src_url, src.icon as ...
3
votes
3answers
946 views

InnoDB insertion faster

I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
6
votes
1answer
552 views

LOAD DATA INFILE blocks on concurrent use against MyISAM tables

I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks ...
1
vote
0answers
779 views

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2?

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2 ? updated to answer the 3 questions: mysql> show variables like 'version%'; ...
3
votes
2answers
153 views

Tablestructure for fast inserts/deletes with foreign keys

Current Situation We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product. Example: id | competitors | some infos about competitor | ...
6
votes
1answer
2k views

MySQL INNODB corruption after server crash during concurrent truncate command

My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
2
votes
3answers
4k views

How can I configure MySQL Innodb to handle 1000s of inserts per hour?

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour. This one error is crippling the site: PDOException: SQLSTATE[40001]: Serialization failure: ...
3
votes
2answers
476 views

How to concatenate a column after x rows?

I have a table with columns object varchar(255), frequency tinyint(3) unsigned, I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as .. ...
1
vote
1answer
460 views

InnoDB, What would cause: “db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL”

MySQL 5.5.24 . We have InnoDB tables that grew almost by double (from 50GB to 100GB - file-per-table enabled) in a few weeks due to a project we are working on. The tables were freshly dumped and ...
5
votes
2answers
195 views

Resolving DUPLICATE for a column with UNIQUE

I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test ...
3
votes
0answers
137 views

“InnoDB: error clustered record for sec rec not found” MySQL 5.5.28

We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28. During high load, randomly we got the below error. MySQLcheck, innochecksum shows no ...
4
votes
1answer
163 views

How badly does innodb fragment in the face of somewhat out-of-order insertions?

Let's say there's a composite primary key on (field1, field2) Also, let be X a theoretical set of n numbers Inserts look like this: INSERT INTO table(field1, field2) VALUES([a number picked at ...
3
votes
1answer
2k views

Innodb, MySQL 5.5.28 - Segmentation Signal 11 faults, on high load. .. my.cnf file included

We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28 . Doing random imports to large InnoDB tables, over 50+ gigs, randomly after a few hours of ...
2
votes
2answers
144 views

Is it always bad to use SELECT * FROM?

It is widely recommended NOT to use SELECT *. I understand this, as it is not a good idea to bring the entire row into memory, when only a few cells are needed. Is it still bad to use SELECT * when ...
4
votes
1answer
278 views

I have multiple ibdata files : What should I do with them?

I have 3 different ibdata files on disk. What is the difference between them? Can I remove some of them? The goal is to migrate the files from C: to E:... I'm on MySQL 5.0.x... Directory of C:\MySQL ...
2
votes
1answer
383 views

inner join on PK with extra criteria slow despite indices?

Given the two tables below I am struggling to understand: why is the third query slow even though first two queries are fast what exactly is EXPLAIN saying can I do anything to significantly speed ...
1
vote
1answer
741 views

How to know the current log sequence number in 5.0.X

I would like to test the optimal value for iblog files on my mysql which is 5.0.77 I found the below procedure in mysqlperformance blog: ...
2
votes
1answer
1k views

InnoDB Performance Tuning

I'm new to database administration so please excuse any omissions of what would seem like basic info to provide for this problem. I'm a RoR developer and our team is currently short a DBA, so we're ...
-1
votes
1answer
52 views

What is an accepted method of keeping old data in MySQL?

I think this may be more of a programming question, but since it directly relates to MySQL, I wanted to ask it here. Some background: I am running an application whereby users can take tests using ...
2
votes
1answer
173 views

Does having Default Database Engine MyISAM and keeping InnoDB tags in my.ini create any problem?

Recently I am getting table corruption issue very often and every time when I check the logs it says "Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL" ...
2
votes
1answer
540 views

Monstrous query hanging on “copying to tmp table” in MySQL

I have a huge query being run by one of my developers which seems to hang no matter which performance tunables I adjust. Though I'm somewhat of novice at writing SQL queries, at first glance the ...
4
votes
3answers
572 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
5
votes
3answers
993 views

InnoDB Master-Master replication goes inconsistant after power failure tests

Afternoon gents, I'm currently stress testing a Master-Master replication setup using InnoDB as database engine. We're using this simple script for testing which we run in Linux CLI from a remote ...
1
vote
2answers
2k views

How to analyse innodb status on deadlock in insert Query?

I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status. OS WAIT ARRAY INFO: reservation count 247864, signal count 247470 Mutex ...
0
votes
1answer
146 views

What MySQL storage engine and table schema should I use?

I am making a web project and I created necessary and the most important table, but I am not sure that I did it right. In this site users will post data like text, picture, text's category, etc. I ...
2
votes
2answers
1k views

Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE

If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction. This is done in order to "lock ...
2
votes
2answers
1k views

MySQL 5.1.63 - using tuning-primer - Table Cache question

tuning_primer says the following: TABLE CACHE Current table_open_cache = 812 tables Current table_definition_cache = 256 tables You have a total of 498 tables You have 812 open tables. Current ...
1
vote
1answer
158 views

Unit testing with InnoDB transactions

So, I'm running some unit tests against some InnoDB tables in MySQL. Each unit test is wrapped in a transaction: setUp: SET autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ...
2
votes
1answer
233 views

Recovering from a stuck query

I have a big DELETE query (deleting 20M rows from a 600M row table with condition according to indexed column) that is stuck - running already for 17hrs. Because it's an InnoDB table, killing it will ...
2
votes
2answers
536 views

MySQL corruption even after full dump and re-import

We have a production issue where MySQL has started spitting InnoDB corrupt page errors and falling over. The error message seem to relate to indexes so I assume the data itself is ok and when I ...
4
votes
3answers
3k views

Mysql backup strategies?

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
10
votes
5answers
14k views

MySQL server has gone away obstructing import of large dumps

I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a ERROR 2006 (HY000) at line 7758: MySQL server ...
2
votes
2answers
132 views

MySQL Server Config, Small Timeframe, Extreme Usage-Scenario

I want to configure a MySQL server for the following scenario. A large amount of users will need to sign up for a university course selecting individual "timeslots". The signup page will open at a ...
1
vote
1answer
745 views

MySQL: Unique constraint on large column

I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column. MySQL appears to ...
2
votes
1answer
309 views

MySQL Unique Index Keylength

I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key. The engine of choice here is InnoDB. There are ...
1
vote
1answer
2k views

What exactly are iblog files in mysql

I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.
3
votes
1answer
2k views

error 1118: row size too large. utf8 innodb

I have tried to find a solution guys, and I am guessing that someone has met this before. But I cannot get hits down below 5000... most of which don't match my question. Using MySQL 5.6 - windows ...
1
vote
1answer
196 views

Index strategy for a Queue Table, getting rid of filesort

I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is ...
2
votes
1answer
255 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
3
votes
1answer
183 views

Find least recently used mysql table index

I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables. While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if ...
3
votes
3answers
6k views

ERROR Cannot find or open table?

MySQL version: 5.5.24 Due to the following problem: mysql> desc reportingdb.v3_zone_date_cpm7k; ERROR 1146 (42S02): Table 'reportingdb.v3_zone_date_cpm7k' doesn't exist ...
5
votes
2answers
645 views

Choosing MyISAM over InnoDB for these project requirements; and long term options

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific. My project's aim is to let users search a (huge) database of variety of products. ...
1
vote
1answer
633 views

ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)

I get an error when I try to create a table: ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121) CREATE TABLE IF NOT EXISTS `cache` ( `cid` varchar(255) NOT NULL ...
3
votes
1answer
598 views

InnoDB “corruption”

I use: 5.1.63-0ubuntu0.11.10.1 Apache/2.2.20 (Ubuntu) MySQL client version: 5.1.63 The system is continuously displaying the following error during reading data from a MySQL database: ...
3
votes
1answer
2k views

Is Percona xtrabackup the right choice for my 500GB MySQL backup?

I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...