MySQL : Open-Source, Relational Database Management System
6
votes
2answers
520 views
Master-Slave Database. How to activate a secondary database whenever the primary database is down?
I need some idea on how to accomplish a master-slave database in MySQL. It's like, there's a primary server which the system connects then when it is down, it will use its local server. Here's the ...
6
votes
1answer
265 views
MySQL database design
I could do with some help designing a database for a website I'm working on. I should mention that I am a beginner and I have never had to design a database in the past.
Ok, my implementation needs ...
6
votes
2answers
1k views
Why does MyISAM support FULLTEXT Searching and InnoDB does not?
I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
6
votes
1answer
100 views
Start/Stop MySQL
I'm seeking help to understand what happens when the following command line is executed:
root@prodn$ service mysqld stop
Yes, it shuts down the MySQL server so access to it is no longer available ...
6
votes
2answers
428 views
Investigate peak in MySQL throughput
Recently one of our servers has ran out of memory and crashed. After reviewing the munin graphs, it appears that the only metric (other than memory usage) that peaked just before the crash was the ...
5
votes
4answers
1k views
Why are so many MPP solutions based on PostgreSQL instead of MySQL?
Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there ...
5
votes
8answers
4k views
Load-balanced MySQL cluster without load balancer
I'm looking to create a load-balanced MySQL cluster, but without the actual load-balancer, in order not to add another point of failure or complexity.
What I was thinking was to have the following:
...
5
votes
4answers
2k views
What should I ask at a MySQL DBA interview?
I am a software developer and am helping my team hire a MySQL DBA. The core challenges that we are facing are:
Slower queries and performance due to Hibernate.
Database management (backups, tuning, ...
5
votes
3answers
1k 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.,
...
5
votes
3answers
3k views
How do I find out if a procedure or function exists in a mysql database?
How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)
5
votes
4answers
471 views
Does putting single quotation marks around numeric constants really protect from SQL injection in MySQL?
The manual seems to suggest that using quotes around numbers is sufficient to protect from SQL injection.
According to section 5.3.1. General Security Guidelines of the MySQL 5.1 Reference Manual:
...
5
votes
3answers
1k views
What is the default order of records for a SELECT statement in MySQL?
Suppose you have the following table and data:
create table t(k int, v int,index k(k)) engine=memory;
insert into t values(10, 1), (10, 2), (10, 3);
When issuing select * from t where k = 10 with ...
5
votes
3answers
229 views
What does the output of a JOIN statement look like?
I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use.
Let's say I have 2 tables:
CREATE TABLE Cities (
id INT UNSIGNED PRIMARY ...
5
votes
3answers
782 views
Differences between “Unique Key” and “Primary Key”
What are the major differences between Unique Key and Primary Key in MySQL?
5
votes
6answers
2k views
Get the rank of a user in a score table
I have a very simple MySQL table where I save highscores. It looks like that:
Id Name Score
So far so good. The question is: How do I get what's a users rank?
For example, I have a users ...
5
votes
5answers
11k views
Matching single column against multiple values without self-joining table in MySQL
We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data:
user_id ...
5
votes
2answers
6k views
Modify DEFINER on Many Views
I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result.
Got error: 1449: The user ...
5
votes
4answers
7k views
Replicating a remote MySQL database to MS SQL Server 2008
I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database.
Is this possible? Can anyone outline the steps required in order to achieve this?
Thanks.
5
votes
2answers
4k views
How to do a case sensitive search in WHERE clause?
I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings.
Any idea on how to do a case sensitive search in SQL query?
5
votes
6answers
415 views
Check if DISTINCT is really necessary
I have a query in mysql as follows.
SELECT DISTINCT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ...
FROM `tableA`
LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA`
WHERE ...
5
votes
6answers
6k views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
5
votes
3answers
410 views
Would it be sensible to create a MySQL user for each user account of a web application?
The thought occurred to me recently, but I have a suspicion the idea might be insane. A couple benefits I'm perceiving are:
facilitate automated logging via triggers
control mysql connections on a ...
5
votes
1answer
617 views
Does MySQL still handle indexes in this way?
Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index.
If a ...
5
votes
2answers
309 views
How do I search to see if a MySQL user exists on the system?
I'm not finding a user by show grants for 'username'@'localhost'' I'm thinking that perhaps the username is slightly different, so I want to do a wildcard search of all the database users. How can I ...
5
votes
4answers
450 views
Is there a way to measure replication lag in MySQL with a resolution less than 1 second?
Is there a way by which you can measure replication lag in MySQL with a resolution of less than 1 second?
That is, can replication lag be measured on the microsecond or millisecond level?
5
votes
2answers
657 views
MySQL: What is the maximum number of cores used by a single MySQL server?
What is the maximum number of cores used by a single MySQL server in a production setting?
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
4answers
2k views
MySQL Replication - Introduce new Slave to replication
I've recently taken on a sys admin position managing a group of 20 (or so) servers. One thing I haven't dealt with before (apart from in a test situation), is introducing a new slave to the MySQL ...
5
votes
4answers
1k views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
5
votes
3answers
3k views
MySQL general query log performance effects
One of our customers who has a somewhat problematic web application would like us to log all the queries for a period of 24 hours.
I wouldn't know how much data it will actually write.
The amount ...
5
votes
1answer
778 views
Error on import of mysqldump file - Illegal double value found during parsing
I am currently getting the following error while trying to import / restore a mysqldump file:
ERROR 1367 (22007) at line 445: Illegal double '1.79769313486232e+308'
value found during parsing
I ...
5
votes
4answers
14k views
Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect
I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on?
I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL ...
5
votes
2answers
3k views
Ibdata usage and Recommendations?
I have a Production server
On November 5,2011 the ibdata size was 100G.
In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G.
I ...
5
votes
1answer
6k views
Is there any best way to reduce the size of ibdata in mysql.?
I have some Production servers whose ibdata increases in size day by day.
It has already consumed 290GB of space.
The tables in the servers are mostly InnoDB and there are high read and write ...
5
votes
2answers
6k views
What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?
I am very new to Database Administration.
I face a lot of problems while setting up mysql master-slave replication.
I also face regular mysql replication troubleshooting issues.
Can anybody helps ...
5
votes
2answers
5k views
How do I output MySQL logs to syslog?
I want to use syslog for logging MySQL(5.1.41) on Ubuntu(10.04 LTS).
I found information that output error log to syslog.
[mysqld_safe]
syslog
But I want to use syslog for logging general logs and ...
5
votes
4answers
528 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
455 views
how do I model this in database?
I am using mysql as my backend database. Here is the use case;
A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. ...
5
votes
5answers
3k views
MySQL said “InnoDB: Initializing buffer pool, size = 128.0M”, does it mean it is using 128MB RAM?
I am just using a very few InnoDB tables (e.g. less than 1MB), but during MySQL startup, it said
InnoDB: Initializing buffer pool, size = 128.0M
Does it mean even I am using in such a small size, ...
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 ...
5
votes
1answer
324 views
Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, ...
5
votes
6answers
4k views
What tools are there for data masking? (MySQL, Linux)
I'm looking for (ideally free, open-source) data masking tools. Do any such exist?
Note: this related question deals with tools for generating test data, but in this question I'm more interested in ...
5
votes
2answers
201 views
Less restrictive query return less result due to simple removing one additional constraint
Look at this query
SELECT DISTINCT
TB.ID,
TB.Latitude,
TB.Longitude,
111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
...
5
votes
5answers
3k views
Why is DROP DATABASE taking so long? (MySQL)
New CentOS installation.
I was running an import of a large DB (2GB sql file) and had a problem. The SSH client seemed to lose the connection and the import seemed to freeze. I used another window to ...
5
votes
3answers
7k 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". ...
5
votes
2answers
7k views
How to use prepared statements within MySQL stored procedures?
I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
5
votes
4answers
7k 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)
...
5
votes
3answers
622 views
MySQL client believes they're in a transaction, gets KILLed, wreaks havoc
Open up two mysql command-line clients and connect to your database. In client #1, enter
START TRANSACTION;
In client #2, use SHOW PROCESSLIST, then
KILL [n];
where n is the id for client #1's ...
5
votes
3answers
578 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 ...
5
votes
2answers
98 views
Fetch the newest row grouped by a column in MySQL
My problem seems like it should have a much simpler solution than what I have come up with. Starting with this data set:
log_table
+--------+-----------+------------------+---------+
| log_id | ...
