All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

4
votes
2answers
4k views

LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the “logging slow query” process?

MySQL version: 5.5.13 The query that I'm using: SET AUTOCOMMIT=0; LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ','; COMMIT; ...
4
votes
2answers
10k views

Is InnoDB Engine up to speed against Memory Engine?

I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows: SELECT P.col1, P.col2, ...
3
votes
1answer
5k views

Is there a progress indicator for OPTIMIZE TABLE progress?

MySQL 5.1.4x (Windows) | Innodb I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3; to reduce the ...
3
votes
3answers
13k 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 ...
2
votes
1answer
314 views

mysql emits BINLOG rows even though binlog_­format=­STATEMENT

We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default) According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in ...
5
votes
1answer
4k views

Is there a MySQL engine or trick to avoid writing so many temp tables to disk?

I've got a web application connected to a database running MySQL. Here's the problem: a not insignificant number of queries create temporary tables and many of the tables involved have BLOB fields. ...
4
votes
4answers
18k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
4
votes
1answer
2k views

How can I prevent gaps in a MySQL table AUTO_INCREMENT field?

For example, I have a table for user registration: CREATE TABLE IF NOT EXISTS test_user ( user_id int(10) unsigned NOT NULL AUTO_INCREMENT, user_name varchar(50) NOT NULL, PRIMARY KEY ...
4
votes
3answers
2k views

MySQL tables/databases into different disks

I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks. Lets say you have 5 ...
4
votes
2answers
800 views

Less RAM than Index_length MyISAM

I'm working on a database to store "time series" data (the value of X was Y at this time). The rows themselves are very small and static in size, with the primary key consisting of two smallint ...
4
votes
1answer
5k views

Can I rename the values in a MySQL ENUM column in one query?

Suppose I have a Database table with an ENUM('value_one','value_two'). I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows: ALTER TABLE `table` MODIFY ...
4
votes
1answer
10k views

Can mysqldump dump triggers and procedures?

Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db? Some time ago I read that mysqldump will also save my triggers, but it doesn't look like ...
4
votes
4answers
2k views

Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?

I am considering scaling up instead of scaling out. Therefore, I am interested to hear whether it is feasible to utilize the MySQL in-memory storage engine for a database that is 500+ GB, given that ...
3
votes
2answers
9k views

MySQL - Autoincrement does not increment sequentally if last row was deleted

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to ...
3
votes
1answer
507 views

Limiting database security

A number of texts signify that the most important aspects offered by a DBMS are availability, integrity and secrecy. As part of a homework assignment I have been tasked with mentioning attacks which ...
3
votes
1answer
2k views

MySQL Server Performance

I have a few questions regarding MySQL server running under Windows Server 2008 R2: How do I check the number of allowed connections? How do I turn on query logging to see performance of queries ...
2
votes
1answer
129 views

How can I determine which Innodb table is being written?

I am running Mysql 5.5. I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The ...
2
votes
2answers
517 views

MySQL 5.6 showing wrong “second_behind_master”

mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.7.98 ...
2
votes
1answer
6k views

Recursive Query in MySQL using stored proceedure and CURSOR

I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships. For simplicity sake, lets say users ...
2
votes
1answer
331 views

optimal table design mysql with primay key and varchar value

I have a table: id (PRIMARY KEY AUTO_INCREMENT) value (UNIQUE KEY) which currently holds over 50 millions rows (and growing fast) and is hit hard: process 1 queries this table (>200/sec) with a ...
2
votes
2answers
292 views

MySql - find redundant indexes

is there a tool that spots redundant indexes in MySql? e.g. if I have the following indexes: index1(col1) index2(col1,col2) then index1 should be flagged as redundant. any ideas?
1
vote
1answer
651 views

Is there a way to find the least recently used tables in a schema?

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...
1
vote
2answers
2k views

How can you monitor if MySQL binlog files get corrupted?

I was wondering if there was any nice way to continuously monitor mysql master binlog files and detect if get corrupted.
8
votes
4answers
3k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
5
votes
3answers
7k views

Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB

I need to design database to store information contained in millions of log files generated by devices on trial on network. I have designed 5 different tables to store log file information. But, as ...
5
votes
2answers
4k views

How to handle the My SQL DB Master to Master replication when there was a disk space outage

We had master to master replication with two node nodes A & B, both are in virtual environment. Initially there was an outage (diskspace issue) in node A and stopped replication. Application level ...
4
votes
1answer
12k views

What can cause TRUNCATE TABLE to take a really long time?

I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves). I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands ...
4
votes
2answers
912 views

How to resolve the master server shut down/unavailability in mysql with master - slave replication

Let us suppose that we have a master/slave setup and the application is running fine for the time being. All of a sudden the master server suddenly shuts down due to some problem. Now how can I ...
4
votes
1answer
622 views

MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob

I have a mysql insert on update query like so insert into table (col1, col2, col3) values (1,2,'huge blob with 12524 chars') on duplicate key update col3 = 'huge blob with 12524 chars'; col1, col2 ...
4
votes
3answers
1k views

With MySQL Replication, what level of resilience is possible?

I've found lots of good material on setting up MySQL replication, but not much on what to do in the event of failures. Understanding the degree of resilience I can achieve is fundamental to designing ...
4
votes
1answer
888 views

What are your use-cases for HandlerSocket?

There's a bit of hype around: HandlerSocket It seems like faster, nosql access to MySQL data is becoming more desirable. Percona Server is now shipping with HandlerSocket built in. My question is, ...
3
votes
5answers
325 views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
3
votes
1answer
3k views

MySQL crashes frequently

I am facing frequent MySQL crash problem. Not sure how to fix the problem. Attaching the mysql error log. Suggest me a solution. Server Config Digitalocean SSD server 1GB RAM CentOS 6.5 64-bit ...
3
votes
1answer
886 views

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
3
votes
1answer
3k views

MySQL Slave Relay Logging but not logging Binary Log

I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of ...
3
votes
1answer
674 views

Setting up MySQL circular replication in existing replication topology?

I have the following MySQL replication topology set up for fault tolerance/disaster recovery with master server A and slaves B and C: A replicates to B A replicates to C Both B and C are fully up ...
3
votes
1answer
4k views

Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL

I wanted to capture only status/result of Slave_IO_Running and Slave_SQL_Running column values, which I will be using to integrate into our application. Is there any way to get only the values of ...
2
votes
1answer
359 views

How big can innodb_doublewrite_file grow?

When I use the enable innodb_doublewrite_file ... How big can this file grow ? How can I calculate the optimal disk size, if it would be used only for this file?
2
votes
2answers
4k views

/usr/libexec/mysqld: Normal shutdown, but my team doesn't do that?

One of my databases has been shutting down abnormally, but the log said "normal": 130422 13:23:01 [Note] /usr/libexec/mysqld: Normal shutdown 130422 13:23:01 [Note] Event Scheduler: Killing the ...
2
votes
2answers
2k views

Loading data in mysql using LOAD DATA INFILE, replication safe?

I am trying to load data into mysql database form CSV file. I found that we could use LOAD DATA INFILE command to do it. But as per the mysql documentation it is not replication safe. (See here) Is ...
2
votes
3answers
2k views

Problem with nested UPDATE queries

I cannot make an update query work. As a first step, the following one works correctly: UPDATE Tab1 SET Tab1.a = '3' WHERE Tab1.id IN ( 123, 456 ); where id is the primary key of Tab1. ...
2
votes
1answer
433 views

select for update gives error on indexed column

here is my code: mysql_query("start transaction"); $q="select max(id) from test for update"; $r=mysql_query($q); $row=mysql_fetch_array($r); for ($k=0;$k<100000;$k++) { $q="insert into test values ...
2
votes
2answers
618 views

get column from too many tables in mysql

.I have too many tables X1,X2,...Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company. every table has structure something like this desc X1; ...
2
votes
1answer
1k views

Understanding mysqltuner Recomendations w/ Unused Catalogs

I have a MySQL 5.0.77 instance running with three catalogs in it. Two of the catalogs contain all InnoDB tables and are actively used. The third is all MyISAM and is basically unused, and is only ...
2
votes
1answer
2k views

LOCK IN SHARE MODE

On dev mysql LOCK IN SHARE MODE is described as... SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify ...
2
votes
1answer
5k views

Fixing MySQL errors recorded in error logs

Below is the extract of the most common lines of error being recorded in error logs on my PC: 110905 16:16:54 [Note] Plugin 'FEDERATED' is disabled. 110905 16:16:54 InnoDB: The InnoDB memory heap is ...
1
vote
1answer
165 views

Schema for multi-account database

I'm building an app which will have accounts for organisations leading to organisation.myapp.com subdomains for their account access. I'm putting together the schema for it and I have a few ...
1
vote
1answer
81 views

Adding new tables — memory usage increases

I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema. Does adding more tables increase memory usage? I am using about ...
1
vote
2answers
1k views

MySQL Community Edition: Can I replicate my entire server with another server in one go?

I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites. ...
0
votes
1answer
5k views

The InnoDB log sequence number is in the future

Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I ...