InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
226 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
2answers
2k views

MySQL 5.5 Fail start Fedora 16

I installed mysql and mysql-server from the repos (MySQL version 5.5). Then tried to start it, but got an error. [root@server]# service mysqld start Redirecting to /bin/systemctl start ...
2
votes
1answer
24 views

Too many TEXT fields leads to record too big

I have a table with multiple 9 TEXT columns, each containing JSON data. I usually UPDATE all the columns at once. I can't seem to add a 10th TEXT column. The table is InnoDB. What can I do?
4
votes
2answers
180 views

MySQL 1264 warning from libmysql

I'm using the libmysql.dll library to connect to my database from a 3rd party application (metatrader). I managed to connect and things are going almost fine. I use mysql_real_connect and ...
1
vote
2answers
1k views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE ...
0
votes
2answers
31 views

MySQL InnoDB data restore using 3 files

I am able to restore MySQL table data along with structure with indexes (InnoDB Engine) using 3 files. MyTable.frm, MyTable.ibd and ibdata1. Can any body confirm, will these 3 files suffice to ...
0
votes
1answer
176 views

Lowering MySQL Memory Usage without Restart

We are using Percona Server 5.5.35-rel33.0-611.precise with InnoDB in our production server. Recently Mysql memory went high and it did't drop from there. We are afraid whether it would result in OOM ...
0
votes
2answers
626 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
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 ...
0
votes
4answers
19k views

1286 - Unknown storage engine 'InnoDB'

I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
-1
votes
1answer
30 views

Mysql: How does a rollback operation be performed inside InnoDB

I would like to hear in simple words Please answer me in simple words to the question on the below scenario. ============== Engine: Innodb Trx1 : started and a DML is executed at 10:00 AM and rolled ...
1
vote
1answer
159 views

mysql autoincrement counter

I need a way to keep auto-increment counter between mysql service restart. As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
2
votes
2answers
1k views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
1
vote
3answers
1k views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
2
votes
2answers
19 views

Should the Redo log on a production MySQL server be configured to another location?

Concerning the innoDB storage engine, should the innodb_log_group_home_dir option (The directory path to the InnoDB log files) be configured to another disk, other than the default directory, in order ...
2
votes
0answers
99 views

Two queries usually run fast but get slow when joined

There are those 2 tables CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
1
vote
2answers
82 views

HeidiSQL: Import CSV

A few lines of my CSV: "id","date","date_","pow","ene","reac" "ES0134000563024539AM0F","2013-05-02","2013-06-05","62","43","43" "ES0134000573024539AM0F","2013-05-02","2013-06-05","62","43","43" ...
2
votes
1answer
55 views

MySQL server won't start with foreign_key_checks=0

I'm running Ubuntu with MySQL version 5.5.44 mysqld Ver 5.5.44-0ubuntu0.14.10.1 for debian-linux-gnu on x86_64 ((Ubuntu)) I have a DB with foreign keys. I would like to have the foreign key ...
2
votes
1answer
128 views

MySQL Delete Rows from Slave

I want to truncate the majority of data on a slave such that it only has the most recent records. I'll eventually demote the current master, making it a read-only archive, and promote the truncated ...
11
votes
3answers
439 views

MySQL instance stalling “doing SYNC index”

Problem An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, ...
1
vote
2answers
64 views

Auto incrementing a column based on another

I have an application which generates invoice_id's. These id's need to be unique. The issue is that a client can have more than one invoice depending on what plan they are on. It could be 2, 3 or 4. ...
1
vote
1answer
1k views

How to recover .ibd and .frm files in MySQL

I am developing a Java application with MySQL 5.6.13. A few days earlier I purchased a new laptop. I forgot to create a dump of application from previous laptop and instead I copied folder of my ...
2
votes
1answer
2k views

ibdata1 corrupted, recover from ib_logfile?

A failing hard drive have left me with a corrupted ibdata1 file. I know where the file starts but not where it ends. The hard drive recovery software claims the file is larger than the hard drive ...
0
votes
1answer
367 views

Recovering from removal of ibdata1 [duplicate]

I just upgraded from Debian Squeeze to Wheezy and mysql stopped working. Blindly following some advice without any understanding of what I was doing, I removed ibdata1 in an attempt to solve the ...
0
votes
2answers
550 views

Recovering mysql innodb table from table.ibd backup file

Just practising DB disaster recovery. As per our back up policy, we do complete disk image back ups every half an hour (on our slave server). Now for a scenario, where some how a table has been ...
0
votes
0answers
44 views

Tuning MySQL with InnoDB engine for my application

I've read through a lot of answers, and I realize this is mostly application specific, so I wanted to give some background as to what I'm doing with the database and hopefully get some tips on tuning ...
1
vote
1answer
68 views

MySql Innodb corruption recovery

I have a MySql database, just containing Innodb tables, that seems to have corrupted itself. It replicates to another server and it has corrupted that one too! The symptom is that I can restart the ...
0
votes
1answer
22 views

Is my import still committing?

I'm importing around 9 million rows into my MySQL DB using the following command: SET autocommit=0; SOURCE my_import.sql; COMMIT; The command line is no longer outputting lines of Query OK, X rows ...
0
votes
1answer
271 views

MySQL Entity Relationship Problem - 3-way join table?

This is a db relationship question. I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows: ...
1
vote
1answer
58 views

Sudden MySQL freeze, with spike in dirty pages, but without obvious write queries

I'm systematically (1/2 times per day) experiencing a freeze in the DB server. Suddenly, all the COMMITs get stuck in 'init' state; they pile up (up to 20/30), an almost no other queries are executed ...
0
votes
0answers
46 views

MariaDB error on alter table - table removed - constraint name is blocked

Today, we experienced a strange behavior in 5.5.41-MariaDB (InnoDB): An ALTER TABLE (add constraint) statement failed with error (mysql server has gone away after that). Then the database table ...
87
votes
7answers
84k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
0
votes
2answers
2k views

Lock wait timeout exceeded on insert despite low lock_time in the slow query log

Our Java/Spring application reports occasional lock wait timeouts on single record INSERTs to a MySQL 5.5 InnoDB table. Here are a few slow query log records for some of the failed INSERTs. # ...
1
vote
1answer
38 views

Int comparison very slow in query

I have this for simplicity fictional table which explains my problem: CREATE TABLE IF NOT EXISTS `posts` ( `pid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `posts` int(11) NOT ...
3
votes
0answers
80 views

Which is the right way to fix “Error: 1205 Lock wait timeout exceeded; try restarting transaction”

I have a PHP script that sync a local DB against external datasource. Each time the script runs I perform some triggers on the DB where INSERT or UPDATE happen. These are the the triggers for that ...
1
vote
1answer
42 views

Clarification on mysql deadlock situation

Following deadlock is encountered while load testing a application. After going through MySQL documentation and other online resources what I figured out was that 1) Transaction (2) holds exclusive ...
2
votes
2answers
3k views

MySQL / InnoDB: Does daily optimization improve performance?

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
0
votes
1answer
30 views

Which are the differences and advantages on single INDEX or compound INDEX?

I have two INDEX on a n:m relationship table as you can see on pics below: Now I can have this also: My questions: What is the advantage from last way(pic compound) over the two first? Can ...
2
votes
2answers
37 views

Information schema not showing reliable information?

I'm currently migrating from SQL Server to MySQL. When i try to match the number of rows i query information_Schema.tables. I noticed that there are some differences between the migrated information ...
1
vote
1answer
37 views

Hardware specs for MySQL database with millions of records

For 35 million records I have this table CREATE TABLE IF NOT EXISTS `records` ( `code` varchar(12) NOT NULL, `type` varchar(255) NOT NULL, `used` set('y','n') NOT NULL default 'n', PRIMARY ...
1
vote
0answers
35 views

Migrated servers… Innodb tables stayed innodb, but database is showns as MyISAM. Problem?

I moved servers. I exported DB on old, imported to new. Kept getting syntax error on import until I tried no compression. Weird, but it worked. I was skeptical so I compared the two carefully and ...
10
votes
2answers
5k views

What does 'ibfk' stand for in MySQL?

If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
2
votes
1answer
83 views

Why is ORDER BY LIMIT 1 so much faster than MAX?

Here are my two queries: select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; And select max(sts_in) from ...
0
votes
2answers
59 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 ...
2
votes
0answers
59 views

Flexible and robust way to cull older data?

I am no DBA and that is becoming more apparent daily, as such I am having trouble keeping my query times reasonable. I have about 3.57 Million records ( at about 3.43 GB ) and it takes about 2 minutes ...
5
votes
1answer
108 views

'SELECT my_table.id' slower than 'SELECT my_table.*'

Running MySQL with InnoDB: I have a SELECT wide_table.* query that I want to refine to SELECT wide_table.id since that's all the calling code needs. Testing it out I've found that the execution time ...
2
votes
1answer
89 views

MariaDB 10 InnoDB Partition with Data Directory

mysql --version mysql Ver 15.1 Distrib 10.0.20-MariaDB, for Linux (x86_64) using readline 5.1 I have a MariaDB 10 installed, though I tried with the 5.5 release in the CentOS repos. I'm ...
7
votes
3answers
603 views

Full text search results in a large amount of time spent in 'FULLTEXT initialization'

I'm currently trying to run some queries against a data dump of Stack Overflow's comments. Here's what the schema looks like: CREATE TABLE `socomments` ( `Id` int(11) NOT NULL, `PostId` int(11) ...
2
votes
2answers
115 views

Would increasing innodb buffer pool size prevent a mysql crash?

My server keeps running out of memory for mysql. I keep getting. 150708 3:39:09 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258064 bytes) When I looked in my.cnf I saw that ...
0
votes
1answer
80 views

Cannot Start mySQL Daemon with Error “mysqld from pid file … ended”

I cannot get MySQL to run. It was working just fine hours ago, but upon starting, repeatedly ends without telling me very much. Here are my logs [myusername@location ~/mysql]$ ./bin/mysqld_safe ...