MySQL version 5.1 - please also tag with mysql for search purposes.

learn more… | top users | synonyms

17
votes
4answers
47k views

Why use WHERE 1 or WHERE 1=1?

Usually, if conditions are not required in our query statements, we don't use a WHERE clause. But I see a WHERE 1 clause being used in many places, even where other conditions are not present. Why ...
13
votes
2answers
27k 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 ...
8
votes
1answer
30k views

Change existing datadir path

I want to change the datadir path in the my.ini file. The existing datadir path is C:/ProgramData/MySQL/MySQL Server 5.1/Data/ The size of my C: drive is 30 GB and the MySQL data folder is occupying ...
8
votes
1answer
9k views

How soon after updating expire_logs_days param and restarting sql will old binlogs get deleted?

MySQL 5.1.x | InnoDB | Windows My mysql data directory is starting to fill up with bin logs. I currently have the following settings configured in my windows mysql server: [mysqld] log-bin server-...
7
votes
1answer
276 views

What is the expected performance impact when dropping a large table

I have a 100G+ innodb table that needs to be dropped in a production environment. I am trying to do this without interruption to service. The table is no longer being written to, and needs to be ...
6
votes
1answer
255 views

Why does Oracle keep working on MySQL 5.1

2 years ago the first version of Mysql 5.5 was released. What is the reason to continue working on MySQL 5.1 - the current release is Mysql 5.1.62 It looks like there are should be fundamental ...
6
votes
1answer
6k views

What memory should be given to the variables of my.cnf ?

Installing a MySQL server is quite easy. However, after installing, configuring the server is an important task for a DBA. Having 2GB of RAM and 250GB of disk space, how should I tune MySQL server so ...
6
votes
1answer
23k views

MySQL case sensitive table names on Linux

Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't ...
5
votes
2answers
21k views

innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster. We have 125G main memory with 24G InnoDB buffer pool ...
5
votes
1answer
292 views

Migrating MySQL to new server

We have a MySQL 5.1 instance running on Windows Server 2008 R2 x64 (Server A) replicated to another MySQL 5.1 instance on Windows Server 2003 R2 (Server B). We have a new MySQL 5.5 instance on ...
4
votes
3answers
16k views

Is there any command to optimize all MySQL tables one by one automatically?

I execute below command line statement to optimize a table: optimize table tablename; Is there any command or statement which can optimize all the tables one by one of the selected database?
4
votes
1answer
381 views

If MAX(id) != COUNT(id), does that mean rows have been deleted?

The question pretty much says it all. We're talking MySQL here, version 5.1. What I'm wondering is whether this would strictly means rows have been deleted, or if there are other possible scenarios ...
4
votes
2answers
3k views

Distinct Combination of Two Columns

I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other. The data is all around backup policies being run on against ...
4
votes
1answer
2k views

Does `Seconds_Behind_Master` show exact Slave Lag from Master?

Does Seconds_Behind_Master always show the correct number of seconds the slave is behind the master? I have also Implemented the mk-heartbeat replication monitoring. It was showing the other result ...
4
votes
1answer
90 views

How can I get this query to use it's index?

Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime). As I don't really know what I'm doing I tried adding indexes to all columns in ...
4
votes
2answers
213 views

Problem in Fetching Result set in Desired Format

I have 2 tables having the following columns Person ------ person_id, first_name, middle_name, last_name Items ----- person_item_id, person_id, item_type, status, ordered_date The query is ...
4
votes
1answer
312 views

Clarification about master slave configuration in mysql

We are planning to migrate an existing MySQL database to a new server using the MySQL replication master slave procedure. Unfortunately this will take a lot of time so we were thinking of doing a ...
4
votes
1answer
2k views

Take individual MySQL database offline

MySQL does not have any SQL commands or internal mechanisms for making an individual database unavailable / offline moving an individual database Thtis being the case, how can you take an ...
3
votes
1answer
7k views

Mysql master-master replication auto flush old logs

I have mysql-5.1.41 master-master setup running on ubuntu machines and below is the configuration of a master: skip-host-cache skip-name-resolve event_scheduler = ON max_connections = 500 ...
3
votes
2answers
92 views

Moving an ibd file temporarily to free up disk space

I want to optimize an InnoDB table but I do not have enough disk space. I have enabled innodb_file_per_table, so each table has *.ibd for each of their partitions. For example: 512MB /var/lib/mysql/...
3
votes
1answer
1k views

How to migrate from MySQL to MariaDB or Percona with minimum downtime?

I want to migrate my MySQL 5.1 database to one of these databases: MariaDB, PerconaDB or MySQL 5.6 Is there any way to do this operation with no downtime or with minimum downtime possible? How? The ...
3
votes
1answer
6k views

Is there a progress indicator for OPTIMIZE TABLE progress?

MySQL 5.1.4x (Windows) | Innodb I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3; to reduce the ...
3
votes
1answer
589 views

Is there any difference between these two version of MySQL - 5.1.73-community-log Vs 5.1.73-community

I have installed MySQL 5.1.73 in Oracle LINUX 5 server. Just after RPM installation, I checked system variables and found that version is 5.1.73-community. When I copid /usr/share/doc/MySQL-server-...
3
votes
1answer
591 views

Merge difference MySQL DBs into a single new server

I have 4 servers which has it's own MySQL server along with databases and users. Now we have a new server dedicated only for MySQL and we would like to merge those MySQL servers into this new one. ...
3
votes
3answers
976 views

Mysql select ends with 'killed'

Okay, I'm sure this will turn out to be something dumb, but then I'm a dumb n00b DBA. I've got a largish table, about 163 million rows and something like 44 GiB. After some experiments with ...
3
votes
2answers
6k views

MySQL temporary column in SELECT statement

I'd like to do some calculations in a MySQL query. I've created some temporary columns which I'd like to use for the calculation. SQL fiddle for currently working (but discouraged) SQL query: http://...
3
votes
2answers
4k views

Can I do anything about a very long running KILL command?

I've got a table with 103M+ records in it and takes up 70GB on the disk. I decided to DELETE (in one statement) 70% of the records. As expected, this took an awfully long time to run. I decided to ...
3
votes
2answers
3k views

Can we assign mysql Key_buffer_size value for particular table?

In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps: SET GLOBAL myisam_table.key_buffer_size = 4194304; CACHE INDEX test.myisam_table ...
3
votes
1answer
1k views

Is it possible to compare timestamps between rows?

I have a MySQL table reqs with (this is simplified from actual): | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------+-----...
3
votes
1answer
170 views

Get problem with Binlog_Do_DB in Master configuration

I have followed all the rules for replication. The steps are I have changed the my.cnf file. My db name is new_db. server-id = 1 log_bin = D:/xampp/mysql/mysql-bin.log binlog_do_db = new_db Grant ...
3
votes
1answer
7k views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
3
votes
1answer
12k views

MySQL : if server_id is not explicitly set, then get error `ERROR 1200 (HY000) at line 1: The server is not configured as slave;`

Our mysql version is "mysql Ver 14.14 Distrib 5.1.35, for portbld-freebsd7.2 (amd64) using 5.2" We have two database servers with replication, in a simple master/slave relationship. mysql2 is the ...
3
votes
1answer
308 views

MySQL v5.1.73 - can the binlog_format betweem Master & slave be different?

We are in the process of testing MySQL replication before using it in production. The MySQL is being setup by Puppet in our case and I noticed that the binlog_format setting is different between ...
3
votes
0answers
75 views

creating deadlock for distributed database in mysql-(Testing purpose)

I need to create a deadlock for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. ...
2
votes
2answers
4k views

Difference between show status and show global status in mysql

When I execute "show status" and "show global status" in MySQL at interval of 5 seconds , I got set of 291 results each time. But, value of some parameters are different. Below are the list of those ...
2
votes
3answers
4k views

Can I make sure two columns dont have the same value

If I have a table that looks like this CREATE TABLE foo ( id INT NOT NULL AUTO_INCREMENT, aa INT NOT NULL, bb INT NOT NULL, PRIMARY KEY (id), UNIQUE KEY (aa, bb), CONSTRAINT aa_ref ...
2
votes
1answer
12k views

Why does max_used_connections status not get refreshed automatically once it reaches max_connections, even though the Threads_connected falls down

I always wonder, why does the max_used_connections value not come down, once it reaches the peak value of max_connections? I always flush the status and bring down this value to avoid DB connection ...
2
votes
1answer
4k views

Repairing Myisam Table when there was no additional Disk space & Table corrupted

I had a table which MyIsam as storage Engine contains million rows. There is a purging task and need to remove 35 days old data. i measured up the rows in following manner : mysql> select ...
2
votes
2answers
80 views

Will hacking away ibdata1 destroy databases with only MyISAM tables?

I made a mistake. I have a huge database of MyISAM tables that I was playing with converting [a copy of] to InnoDB. I didn't set innodb_file_per_table=1 before doing so. I know that safely shrinking ...
2
votes
3answers
736 views

mysql replication master 5.5 slave 5.1 error on create database

I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we ...
2
votes
1answer
8k views

Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21. I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone ...
2
votes
1answer
5k views

Upgrading MySQL 5.1 to 5.5 on Centos 6 without removing dependencies

I'm trying to upgrade MySQL 5.1 to 5.5 using yum/rpm combo (yum to remove MySQL 5.1 and rpm to install MySQL 5.5). Most instructions I've found suggest to do first: yum remove mysql mysql-server ...
2
votes
1answer
2k views

Innodb Slow queries since convert from MyISAM

a few days ago we converted some write intensive tables from MyISAM to InnoDB hoping to have a better performance due the better locking system of InnoDB, but instead of gain performance, we start to ...
2
votes
1answer
1k views

How to properly secure MySQL database?

We have a web application based on the famous triad apache + php + mysql which we sell to our customers and gets installed on their servers. Currently, we are using MySQL 5.1.41 which has only a ...
2
votes
2answers
1k views

Does pt-table-checksum point out rows that are different between Master and Slave?

Trying to verify replication integrity of my replica. And I found that pt-table-checksum is one of the best ways of doing it. I have a simple Master->Slave set up. Have created a sample table with 10 ...
2
votes
1answer
18k views

Error Code: 1205 Lock wait timeout exceeded in MySQL

I have set InnoDB lock wait time-out to 120 seconds. When I run the query below for first time, it works fine. However, the second time, the same query with a different value gives me: Error ...
2
votes
2answers
87 views

What are some known hazards with cyclical replication between two MySQL master servers?

If I were to set up cyclical replication between a MySQL 5.6 master server and a MySQL 5.1 master server, what are some things that could potentially go wrong? Also, the 5.6 MySQL does not have GTID ...
2
votes
1answer
33 views

Unable to start mysql

I am using Oracle LINUX5. I installed MySQL 5.1.73 community edition through RPM command and it was working. Now, I stop mysql by running below command: $ mysqladmin -u root -ptmpassword shutdown I ...
2
votes
1answer
1k views

MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Can someone tell me how to prevent the following commands DROP TABLE <tbl-name>; TRUNCATE TABLE <tbl-name>; from being replicated to slaves?
2
votes
1answer
1k views

MySQL: What is the most optimized way to use datetime type field in query

I am working on optimization of some slow queries in my application. I found a query in which datetime comparison has been used. Below is the subquery: select * from jos_content c where c.visible = 1 ...