MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

8
votes
2answers
9k views

How do I show the binlog_format on a MySQL server?

How do I show the binlog_format on a MySQL server? And if I dont like it how do I set it to XX permanently? Where XX is STATEMENT, ROW or MIXED.
8
votes
2answers
10k views

What is the safest way to switch the binlog format at runtime?

Because of the following warning in mysqld.log: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it ...
8
votes
3answers
4k views

How to stress test a SQL server?

I've been tasked to stress test our MSSQL Server and MySQL Server. I would like to know if there are any tools or scripts which I could use on our current systems and the new system to compare ...
8
votes
2answers
5k views

Percona vs MySQL

What is Percona? How does it differ from MySQL? When should we consider switching (or upgrading) from stock MySQL to Percona? To add some specifics in our situation we almost exclusively use ...
8
votes
6answers
3k views

What could be causing strange query timeouts between PHP and MySQL?

I am the senior developer on a Software-as-a-Service application used by many different customers. Our software runs on a cluster of Apache / PHP application servers, powered by a MySQL backend. On ...
8
votes
4answers
1k views

How can I optimize this MySQL query further?

I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
8
votes
4answers
12k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
8
votes
9answers
8k views

What tools are there for data masking? (MySQL, Linux) [closed]

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 ...
8
votes
3answers
3k views

Dynamic SQL in MySQL stored routines

According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And ...
8
votes
2answers
290 views

Why would MySQL do serial synchronous I/O?

When looking at a particularly annoying query over MyISAM tables which takes a long time to execute on a number of occasions, I have noted that MySQL seems to expose a rather strange I/O pattern: when ...
8
votes
1answer
11k 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 ...
8
votes
2answers
7k views

MySQL and window functions

It seems that MySQL does not support window functions. E.g. the simple: COUNT(*) OVER() AS cnt does not work. What I am not sure is if this applies to commercial version as well (I assume the ...
8
votes
2answers
19k views

How do I shrink the innodb file ibdata1 without dumping all databases?

InnoDB stores all tables in one big file ibdata1. After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and ...
8
votes
3answers
16k 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". ...
8
votes
3answers
5k 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 ...
8
votes
3answers
12k views

How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline?

MySQL Server 1 is running as Master. MySQL Server 2 is running as Slave. With both DBs online, they are in "perfect sync". If Slave goes offline, there's no problem if Master still online; they will ...
8
votes
1answer
8k views

Error creating foreign key from MySQL Workbench

I'm trying to synchronize schema changes from MySQL Workbench to my database. I'm getting the following error when it tries to create a foreign key: Executing SQL script in server ERROR: Error 1005: ...
8
votes
2answers
8k views

Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?

I have a unique compound key like fr(fromid,toid) in the table, when I run the query with explain I get the following result: Impossible WHERE noticed after reading const tables` The query I ran: ...
8
votes
1answer
5k views

Does mysqldump export indices, by default?

I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option: --disable-keys, -K which suggests, ...
8
votes
6answers
12k views

Alternatives to MySQL Workbench

I've been running into some problems with MySQL Workbench that I'm not sure will be able to be fixed (namely, what appears to be an old bug that may have resurfaced), so I'd like to be able to find a ...
8
votes
1answer
1k 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 ...
8
votes
4answers
122 views

What are the trade offs for incrementing ids vs. fulltext keys for foreign key designs?

In a lot of relational database designs there are fields that are referenced in other tables. For example consider a user table with a unique username and a second table storing address data. One ...
8
votes
1answer
3k views

Is there such thing as Custom Data Types?

Does MySQL have any support for custom data types? For example, zip codes might be stored in a varchar(10) field, but they could be compacted in to an int, with options for blank, and a flag as to ...
8
votes
2answers
3k views

What are the benefits of using MySQL Cluster?

I have never dipped my hands in MySQL Cluster and want to know how it can benefit me. When is it best to use it, and does it have performance benefits?
8
votes
1answer
3k views

Generating Invoices and Tracking

Every 2 weeks, the system will generate the invoices for the companies. Company will receive an invoice on the 1st and 16th every month. (It will run via Cron Job every 2 week. It scan through the ...
8
votes
1answer
4k 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... ...
8
votes
1answer
4k views

What is the usage of performance_schema in MySQL 5.5?

I have upgraded to MySQL version 5.5. There, I had observed performance_schema in the schema list. What is the usage of this new schema..? How can we monitor using this schema..? What are the ...
8
votes
3answers
7k views

Audit logins on MySQL database

Is there a way to audit logins to MySQL? I'd like to be able to create a username for each employee and thereby create an audit trail of logins. However, googling has turned up no good results. The ...
8
votes
3answers
1k views

MySQL LDAP authentication

Is LDAP authentication and authorization in MySQL possible? What are the requirements (MySQL version, third-party software etc.)? How it's organized? Thanks.
8
votes
3answers
6k views

MySQL status variable Handler_read_rnd_next is growing a lot

In MYSQL status, Handler_read_rnd_next value is very high. I am aware that, this value will be incremented when a query is executed which is not having proper indexes. But, even when we execute ...
8
votes
1answer
2k views

MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
7
votes
4answers
649 views

How to create a database for unknown kinds of data?

I am in the process of designing a database for a new PHP/MySql-based application. My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and ...
7
votes
5answers
14k views

MySQL : DROP TABLE Starting with a Prefix

EXAMPLE : I have more than 30 tables starting with a prefix "dp_" and about 12 starting with "ex_". QUESTION : How can I drop all tables starting "dp_" in one query ?
7
votes
6answers
13k views

slow load speed of data from mysqldump

I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe ...
7
votes
3answers
4k 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
2answers
415 views

Data Compression Through Base36 Encoding

I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type. I'm thinking about using Base36 encoding to accomplish this. Are ...
7
votes
2answers
600 views

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

In SQL queries, we do use Group by clause to apply aggregate functions. But what is the purpose behind using numeric value instead of column name with Group by clause? For example: Group by 1.
7
votes
1answer
1k views

Is PostgreSQL replication production ready?

How does PostgreSQL native replication compare to MySQL? I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects? ...
7
votes
5answers
12k 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 ...
7
votes
3answers
15k 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 ...
7
votes
5answers
28k 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 ...
7
votes
2answers
18k views

MySQL Set UTC time as default timestamp

How do I set a timestamp column whose default value is the current UTC time? MySQL uses UTC_TIMESTAMP() function for UTC timestamp: mysql> SELECT UTC_TIMESTAMP(); +---------------------+ | ...
7
votes
2answers
15k 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?
7
votes
2answers
438 views

What is the purpose of the USING parameter in SQL?

Working with some code on a standard MySQL database, and I came across this statement: SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 What's throwing me is: what is USING for, ...
7
votes
4answers
12k 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.
7
votes
3answers
7k views

Best of MyISAM and InnoDB

Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
7
votes
4answers
2k views

For a MySQL query, how do you determine physical and logical I/O?

I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
7
votes
2answers
823 views

How to drop empty tables

Is it possible to drop all empty tables from my huge database (mysql)? I'm looking for a sql command to automatically remove all those empty tables. Currently, I have 305 tables in my dataset, and ...
7
votes
2answers
16k views

What is the difference between optimize table and analyze table in mysql

What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.
7
votes
3answers
7k views

MySQL replication - slave is continuously lagging behind master

I am using MySQL-5.1.50 with a Master-slave replication setup. Most of the time the slave is lagging behind the master. When I run show processlist;, there is no query that's taking a long time. ...