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

1
vote
2answers
1k views

All columns of table should be indexed or not mysql database?

I am having one table with 4 columns. I that one is primary key and other three columns are references to columns of other tables. I am using join of these four tables for search filter. These joins ...
13
votes
2answers
13k views

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
3
votes
1answer
2k views

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777). When i tried to open the file ...
1
vote
2answers
836 views

Mysql master-master permission replication issue

I came to know it's safe to replicate mysql database to have the users and permissions replicated, from a post in ServerFault is-it-ok-to-replicate-mysql-db. I have setup master-master replication ...
6
votes
1answer
9k views

Will mysqldump command also backup all the views that are present in DB or there is a special command for it?

I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it. ...
4
votes
1answer
670 views

How do I replicate Mysql DB server to new server?

Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration). I want to replicate ...
7
votes
1answer
2k views

recover a single mysql database on a busy master-slave replicated system

Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system. I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave ...
4
votes
2answers
3k views

MySQL: Storing unique URLs

I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
5
votes
2answers
3k 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 ...
6
votes
2answers
3k views

Insert-heavy InnoDB table won't use all my CPU

I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
3
votes
2answers
2k views

MySql replication with Raw Data and InnoDB tables

I want to start replication on DB that has many InnoDB tables and MyISAM tables. I did everything according to manual Creating a Data Snapshot Using Raw Data Files. Firstly, I tried copied all ...
3
votes
1answer
122 views

Does combining columns into 1 column helps to optimize a large table?

I have user, items and user_items tables, and as usual user has many items. Tables contains: User: ~50K Items: ~3.5M UserItems: ~5M I am using MySQL and most of my queries are selecting items of a ...
4
votes
2answers
211 views

MySQL: Is it safe to have a table with multiple millions of rows?

I have a very complex search query to write that will touch many tables, have conditions and joins. For efficiency reasons, I am strongly considering creating a "cache table" where I would maybe once ...
9
votes
3answers
8k 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 ...
16
votes
4answers
124k views

MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

I use the root account created the account 'a'@'%'. But I can't use the account to connect to MySQL server when I specify the host parameter. I can successfully connect without the -h parameter. ...
3
votes
1answer
3k views

Any disadvantages to enabling federated storage engine?

We've encountered a couple use cases where having federated tables prove useful, despite their limitations and caveats. As best I can tell this must be enabled with a federated declaration in the ...
4
votes
2answers
965 views

How do you migrate a database from SQL Server 2008 to MySQL?

I'm just getting into MySQL, so forgive me if this is a basic question. For my application I need to migrate my database from SQL Server 2008 R2 to MySQL. Is there any mechanism or technique or ...
3
votes
2answers
8k views

Is there a way to tell your hostname within a stored procedure?

Is there a way to determine the hostname of the server you're running in w/ in a stored procedure? You can run system hostname from a client but you can't make system calls from SP. I don't see it ...
2
votes
1answer
7k views

How can I optimize my mysql setup to create my index faster?

I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package. The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored. I have an MyISAM like ...
2
votes
1answer
5k views

How to check the consistency of tables in mysql

I have few questions regarding consistency in Mysql in two scenarios 1st scenario: How can I verify a restored backup matches the original DB data on all the tables? We have two hosts with the same ...
4
votes
1answer
3k views

Mysql Memory table getting many locks

On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table. This table has the following activity: 1 SELECT per minute 1 DELETE per minute no UPDATE queries Lots of ...
4
votes
3answers
216 views

mysql implementation of ORDER command

Sorry for a silly question, I tried query with WHERE clause and ORDER at the end, which actually took very long time to implement, even thought WHERE clause was on indexed field. It looks like Mysql ...
2
votes
2answers
2k views

Problem with InnoDB “per table” file sizes

I Have configured the mysql server with innodb per table. There was a table watchdog having approximate 19GB of watchdog.ibd file. I have truncated the table now it has zero record. But the ...
4
votes
1answer
295 views

Type safety in WHERE clauses

I've spent the most part of yesterday tracking down a strange bug in our PHP/MySQL application and the culprit seems to be a poor query. We have a table like ...
1
vote
1answer
396 views

xtrabackup “failed to read metadata” [closed]

Doing a staging run setting up a new xtrabackup restore script I'm running into a somewhat cryptic error I can't readily google a solution for. I'm working with an incremental backup design. During ...
10
votes
2answers
6k views

PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
2
votes
2answers
1k views

mysql user creation with % to host , can only log on from remote host

Using MySQL v5.1.52 After creating a mysql user with the following commands: create user 'user1'@'%' identified by 'pass1'; grant select on db1.* to 'user1'; flush privileges; Then doing a 'mysql ...
2
votes
0answers
113 views

MySQL MEMORY table has high Lock Wait Ratio [duplicate]

Possible Duplicate: Mysql Memory table getting many locks on my site I log every pageview (date,ip,referrer,page,etc) in a simple mysql table. This table gets very little selects (3 per ...
1
vote
0answers
425 views

MySql shell command not replicated to slave - “LOAD DATA INFILE” [duplicate]

I have a shell script that looks something like that: #!/bin/bash mysql -udb_user -pdb_password -e "LOAD DATA LOCAL INFILE '/srv/some_file.csv' INTO TABLE db_name.db_table FIELDS TERMINATED BY ...
0
votes
1answer
225 views

MYSQL - 'Select distinct' - different results on two exact versions of Mysql, databases synced

Same versions of Mysql and the OS (Ubuntu), Databases synced fully. Even the replica gives different results. One mysql instance is showing 11 results (from a Select distinct statement) and all the ...
2
votes
3answers
1k views

MySql shell command not replicated to slave

I have a shell script that looks something like that: #!/bin/bash mysql -udb_user -pdb_password -e "LOAD DATA LOCAL INFILE '/srv/some_file.csv' INTO TABLE db_name.db_table FIELDS TERMINATED BY ...
0
votes
1answer
383 views

InnoDB table vs MEMORY table for a mysql environment with many inserts

I run my sites all on InnoDB tables which is working really well so far. Now I like to know what is going on in real-time on my sites, so I store each pageview (page, referrer, IP, hostname, etc) in ...
1
vote
3answers
648 views

Should I split a single table with an AUTO_INCREMENT PRIMARY KEY into two tables that can be joined on that PRIMARY KEY?

I have a table that has 46 fields with an AUTO_INCREMENT PRIMARY KEY. It's hard to look at a table that has this many columns, so I frequently use a SELECT statement to look at 34 of the fields that ...
10
votes
4answers
9k views

Adding an index very slow…is there a mysql cmd to get an ETA or show progress?

I'm currently running an alter query on my table (20M entries) in order to add an index. it's running for more than 3 days already (stuck on 'copy to tmp table'). is there a way I can see the ...
6
votes
1answer
1k views

Loyalty reward - record for every day of a week

I have a table that contains every login a user makes. I am trying to make a loyalty system that rewards users if they've logged in every day for a week. So, the real question, how do I get every ...
4
votes
2answers
3k views

Separate tables or partition one huge table?

I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
8
votes
4answers
43k views

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- ...
1
vote
1answer
2k views

MySQL - MySqlDump uses \n instead of null

Using Windows with MySQL 5.1. I am running a script that uses mysqldump to backup a database. The mysqldump syntax used is: mysqldump --user=root --port=3306 --password=topsecret some_database ...
2
votes
2answers
460 views

MySQL: Slow log; append CPU usage

I have the MySQL slow log feature enabled: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html But sometimes the query_times are high simply due to high CPU load. How can I append the current ...
5
votes
2answers
3k views

How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB. I basically did this: set sql_log_bin = 0; set sql_mode = 'STRICT_ALL_TABLES'; ALTER TABLE ...
0
votes
1answer
247 views

Why mysqld server service gets aborted when i start mysql client service? [closed]

I am installing mysql-5.5.7rc from source code. After installation when i start mysqld service is being run but as soon as i run mysql client service server service gets aborted.. And also ...
2
votes
3answers
4k views

How to detect a running mysqldump?

Here are the things I can't change: We got a mySQL myISAM replication running a master and some slaves. One slave is being used for nightly backups using mysqldump. The backups is using heavy locks ...
3
votes
2answers
3k views

What is the right time to use an ENUM versus a normalized types table in MySQL? [duplicate]

Possible Duplicate: Advantages and Disadvantages to using ENUM vs Integer types? Considering MySQL only... I'm curious to know what the community thinks the proper use-case for an ENUM ...
2
votes
1answer
1k views

Mysql Impossible WHERE and enum

I'm using enum as an alternative to check constraint which is still not supported by mysql. Could you clarify why optimizer doesn't take into account allowed values for enum ? For example, CREATE ...
8
votes
1answer
21k views

Client times out, while MySQL query remains running?

We experienced an issue in which a read-only query, run through MySQL workbench, timed out from a user's UI perspective and remained running on the server (and apparently consuming more and more ...
1
vote
1answer
866 views

MySQL not reading my.cnf in MAMP

Running MAMP (not PRO) 2.0.5, MySQL 5.5.9, locally on OS X and I cannot get MySQL to read a my.cnf file. I've tried throwing it in the following directories with no results: /Applications/MAMP/conf ...
4
votes
1answer
7k views

Query performance with subquery and IN clause

I am trying to select a range of data for multiple devices (unique serial numbers) from a historical table and was wondering why there is such a big difference in time for the following queries: ...
5
votes
1answer
450 views

Tools for cnf management, generation? [closed]

Does anyone know of any good tools for cnf configuration management? It's often the case I have a group of machines that has a very similar config except for a few differences such as server id or ...
1
vote
2answers
549 views

Moving MySQL database from UniServer to Ubuntu Server?

I have set up Ubuntu Server 11.10, and plan to host my website on it. Until this point I was using UniServer to host it in a portable fashion. Using the built in MySQL. This is fine, and through ...
2
votes
1answer
5k views

16 Cores 12 GB RAM server MySql Configuration - my.cnf

The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...