MySQL : Open-Source, Relational Database Management System
2
votes
2answers
2k 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;
...
2
votes
3answers
2k views
Disable trigger for just one table
Is it possible to disable a trigger momentarily but for just one table.
For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but ...
8
votes
3answers
2k views
Monitor MySQL activity per database?
What is the best way to monitor activity on each database of my server ?
I tried mtop/mytop which display activity in real time but aren't very practical to see all the queries executed on it.
I ...
6
votes
1answer
2k views
Is it possible to change ENUM() lists?
I wasn't sure if changing ENUM() list isn't possible so I made a test.
In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no').
Then I executed...
...
6
votes
1answer
2k views
Dump only the Stored Procedures in MySQL
I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
5
votes
1answer
4k views
intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”
Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
5
votes
2answers
423 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.
...
5
votes
3answers
6k views
Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql
I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log).
I have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log).
The DB on S1 was using one data file ...
4
votes
2answers
397 views
Implementing a Circular Buffer (Sliding window) in MySQL
I intend to store some Java objects in a MySQL database, accompanied by a timestamp. These objects should be kept in a Sliding Window fashion (also known as Circular Buffer), meaning that only the ...
4
votes
2answers
9k views
MySQL: how to restore table stored in a .frm and a .ibd file?
I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install ...
4
votes
3answers
632 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
3answers
4k views
How to recover MySQL table structure from FRM files
What I have learned from Google searching:
With MyISAM tables you can simply copy the FRM, MYD, and MYI files. In the folder for the DB I'm trying to recover, there are only FRM files and a "db.opt" ...
4
votes
2answers
409 views
Is it a good idea to split MySQL DB into two servers
I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate:
I ...
3
votes
2answers
929 views
Mysql load from infile stuck waiting on hard drive
I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
3
votes
2answers
421 views
Cannot update certain rows in innodb tables
I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html
When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are ...
3
votes
1answer
769 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
3answers
891 views
MySQL Query Optimization : Indexing and Pagination
I'm adding pagination to my 'latest news' php script and running in to an issue.
SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit
I ...
2
votes
1answer
532 views
When should I think about upgrading our RDS MySQL instance based on memory usage?
It seems like our DB server is doing garbage collection at a increasingly faster rate, which seem normal since it's growing. What's a good rule of thumb of when to switch to a bigger instance, I'm not ...
2
votes
2answers
160 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?
2
votes
1answer
2k views
In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Several Questions
Would the Slave entry remain until there is an insert/update/delete to the table on the Master that could affect the Slave?
If not, how do I ensure that the Slave is in-sync with ...
1
vote
1answer
84 views
How to solve “The table … is full” with “innodb_file_per_table”?
I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave ...
1
vote
1answer
2k views
InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
Server Specification: VPS with following info
model name : Intel(R) Xeon(R) CPU E5649 @ 2.53GHz
MemTotal: 2058776 kB
MemFree: 244436 kB
We are running IP.Board from Invision ...
1
vote
3answers
3k views
I've got 16GB of ram, how should I configure MySQL Server?
I've got a dedicated production server with multiple cpus and 16GB of ram.
I would like to know how can I configure it better to take advantage of the server resources for better performance because ...
1
vote
3answers
1k views
Monitoring replication on mysql
I have managed to setup master and slave replication.
It is working fine. What are the possibilities that it might go down?
Is there any alerting tool to monitor that?
Another thing: Can I run a ...
1
vote
1answer
775 views
How to make MySQL replication reliable?
Master version: 5.5.13-1
Slave version: 5.5.14-1
Binary log format: MIXED
My Slave database (~ 40GB) has been out of sync from the Master. I cannot find anything interesting in the error log. ...
1
vote
2answers
2k views
Set up a MySQL slave using mysqldump to get the initial data
I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
1
vote
2answers
447 views
How to enable all the possible backup options of MySQL
I normally use MySQL Administrator to backup my database. I want to enable all the backup options for the available backup types in MySQL. I noticed that Binary Logging is not running on my PC. How to ...
0
votes
1answer
129 views
MySQL performance tuning
I would like to optimize my MySQL DB. I noticed some important points as follows:
Enabling Query cache.
Using memory storage. (I know pluses and minuses of this. Being I am having static tables, I ...
0
votes
3answers
162 views
MySQL data view
I have a table that looks like this:
+----------------------------------------+
|Name | kode | jum |
+----------------------------------------+
| aman |kode1 ...
9
votes
1answer
165 views
Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE
I am trying to have a score for entire section of SELECT statements
SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
UNION
SELECT ...
9
votes
5answers
1k views
Is it unwise to run replication on same physical server?
I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
7
votes
1answer
263 views
Is there an execution difference between a JOIN condition and a WHERE condition?
Is there a performance difference between these two example queries?
Query 1:
select count(*)
from table1 a
join table2 b
on b.key_col=a.key_col
where b.tag = 'Y'
Query 2;
select ...
7
votes
2answers
789 views
Is the overhead of frequent query cache invalidation ever worth it?
I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
6
votes
2answers
1k views
MySQL deadlock - cannot restart normally?
MySQL version 5.5.13-1
A snippet from the SHOW ENGINE INNODB STATUS\G:
LATEST DETECTED DEADLOCK
------------------------
111218 10:22:34
*** (1) TRANSACTION:
TRANSACTION 1318D95B, ACTIVE 0 sec ...
5
votes
2answers
2k views
How to convert control characters in MySQL from latin1 to UTF-8?
While converting a database to UTF-8 I noticed a strange behavior regarding the control characters 0x80-0x9F. For example, 0x92 (right apostrophe) would not get converted to UTF-8 and truncate the ...
5
votes
2answers
510 views
Single Drive vs Multiple Drives
Generally bottleneck of RDBMS (I am a MySQL user) performance is disk access. SSD provides great performance compared with conventional spindle drives.
Question : Is it possible to improve ...
5
votes
1answer
2k views
Mail alert from MySQL when databases are edited
How do I get mail from MySQL when root or someone else edits or updates a particular database? It should trigger sending a mail and log the edits.
I have tried Triggers but its not working.
Server ...
5
votes
3answers
3k views
MySQL gives “Out of Memory” error when inserting a large file. From where is this filesize limitation arising?
I'm using a MySQL database to store files.
The table I'm using is structured as follows:
+--------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default ...
4
votes
1answer
463 views
Replicate MySQL 5.0 Master to 5.5 Slave?
We're planning out a series of server/software upgrades and were curious.
We have a RHEL5 install running MySQL 5.0 that we'd like to replicate to a new (virtualized) server running CentOS 6 and ...
4
votes
1answer
105 views
Installing and enabling both master and slave semisynchronous plugins on same server
I want to run a semisynchronous replication setup, and for simplicity I'd like to not have to INSTALL and UNINSTALL plugins when a slave is promoted to a master. So I'm wondering if I'll experience ...
4
votes
1answer
3k 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
4k views
Why is MySQL is creating so many temporary tables on disk?
Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows
Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were ...
4
votes
2answers
588 views
Why does MySQL choose this execution plan?
I have two queries,
select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;
and
select some_other_column
from `table`
order by secondary_index_column asc
...
4
votes
1answer
365 views
will replication from 5.5.20 to 5.0.XX server work?
I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible?
Because I remember when I transferred ...
4
votes
2answers
1k views
Which version of MySQL should I use?
For ages I have been using MySQL 5.1.37.
Recently I noticed that this version is no longer supported in repository, so I need to upgrade mysql.
I assume that changing the version should be ...
4
votes
3answers
549 views
Making a column immutable in MySQL
I want to make a column in a relation unmodifyable for consistency reasons.
The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that ...
4
votes
2answers
880 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 ...
3
votes
2answers
561 views
Is it bad to create many mysql temporary tables simultaneously?
I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better ...
3
votes
2answers
226 views
Save history editable data RDBMS
I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
3
votes
1answer
172 views
Resolving MySQL Process ID to page that ran it?
I asked this question on stackoverflow and was directed to ask this question here.
I have a couple of scripts in PHP that run MySQL queries. On occasion they cause the server to seize up or become ...
