MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

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
1answer
4k 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
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
1answer
449 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 ...
3
votes
1answer
8k 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 ...
2
votes
2answers
336 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
3answers
239 views

MySQL data view

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
2
votes
1answer
317 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
270 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?
6
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
6k 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
3k 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
10k 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
786 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
2answers
733 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
3answers
915 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
809 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
1answer
2k 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
544 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
545 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 ...
3
votes
1answer
3k 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
89 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
1answer
2k 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 ...
2
votes
2answers
7k 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 ...
2
votes
1answer
303 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
3k 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
1k 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
392 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
544 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
938 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
1k 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
505 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
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. ...
11
votes
2answers
6k views

MySQL Sharding vs MySQL Cluster

Considering performance only, can a MySQL Cluster beat a custom data sharding MySQL solution? sharding = horizontal partitioning When I refer to sharding, I'm considering sharding made in the ...
22
votes
6answers
8k views

What problems will I get creating a database per customer?

I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases. We are just ...
32
votes
2answers
10k views

NULL or NOT NULL by default?

In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls? Or doesn't it matter? I know in some DBMSs they ...
10
votes
3answers
20k views

Any better way out of MySQL InnoDB log “in the future”?

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
29
votes
9answers
12k views

Is there a tool like Microsoft's “SQL Server Profiler” for MySQL?

While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler. For ...
12
votes
1answer
10k views

Is there a graceful or safe restart for mysql like for apache httpd?

I would like to restart mysql gracefully just like httpd where threads are served before restart. I would dislike queries breaking.
9
votes
3answers
13k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
8
votes
3answers
6k views

One Big Database vs. Several Smaller Ones

We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g., ...
7
votes
4answers
19k views

Why does MySQL say I'm out of memory?

I was trying to execute a fairly large INSERT...SELECT in MySQL with JDBC, and I got the following exception: Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes) ...
14
votes
4answers
23k views

How do you identify InnoDB table corruption?

I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
10
votes
2answers
15k views

Should I increase max_connections in AWS RDS t1-micro for MySQL?

I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
9
votes
2answers
15k views

InnoDB create table error: “Row size too large”

We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing ...
6
votes
2answers
2k views

MySQL warm procedure

How do you usually warm up your database ? I run a similar queries to this for every table in DB: SELECT * FROM ip_log ORDER BY ID; SELECT ip, member_id FROM ip_log ORDER BY ip, member_id; Is that ...
5
votes
1answer
3k views

What are user connections - when are the created and destroyed?

We've been getting an intermittent error on out site: Warning: mysql_connect() [function.mysql-connect]: User '557574_prod' has exceeded the 'max_user_connections' resource (current value: 10) ...