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.
2
votes
1answer
1k views

LOCK IN SHARE MODE

On dev mysql LOCK IN SHARE MODE is described as... SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify ...
1
vote
1answer
2k views

Copy blob from one row to another

I have a corrupt blob in a row. I'd like to replace that with another blob from the same table. How can I do that with sql statements only? (No file system access if at all possible.)
3
votes
2answers
2k views

pt-table-checksum with slaves using non-standard ports

Trying to run pt-table-checksum to detect slave drift in MySQL replication. My slaves are not using the standard TCP listening port however, and so when I run the tool I get an error: Cannot connect ...
1
vote
2answers
1k views

How to keep one query from slowing down entire database?

Using MySQL 5.0.x, we have a process to generate benchmarks for our business that consolidates a bunch of data, then pull out data based on certain criteria. This process used to take 18 hours, it's ...
5
votes
3answers
3k views

How much memory will a MEMORY table take up?

Let's say I have a MyISAM table with a data length of 4.8GB and an index length of 6.2GB. So, a total data size of eleven gig. How much memory would this require, were I to convert it to a MEMORY ...
4
votes
1answer
1k views

Not enough space for MySQL optimize; do new rows overwrite existing data?

My MyISAM table consumed most of my disk space. I deleted a significant portion of the table, but the optimize command (to clean out the deleted data) does not have sufficient space. Is there any ...
4
votes
3answers
5k views

Concurrent MySQL updates hang with InnoDB (on Amazon RDS)

I'm running into a problem where multiple MySQL updates being performed at the same time will lock up and takes several minutes to finish. I'm using InnoDB, so I'm confused as to why this could be ...
2
votes
1answer
2k views

importing myisam 5.0 database into a 5.5 innodb server

I have MySQL 5.0 and 5.5 servers (but not a 5.1 server). The 5.0 server is myisam only, the 5.5 default is innodb. I need to move a database from the 5.0 server to the 5.5 server. The database is ...
7
votes
1answer
7k views

MySQL column defined with NOT NULL is allowing nulls

I had created a table a while back and started adding data to it. Recently I added a new column (address) to it with NOT NULL as part of the new column. The old rows (pre-addition) are still null, ...
3
votes
1answer
573 views

mysql change schema on the fly

Recently I faced the problem that my Db became very huge and I cannot allow to myself downtime every time when I want to change the schema. This caused me to start looking for solution that do schema ...
7
votes
1answer
2k views

Unexplained InnoDB timeouts

I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example: //# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 UPDATE photos ...
3
votes
1answer
4k views

I have lots of free memory. How do I use it to increase performance?

I am running Litespeed (with suExec, PHP SAPI), MySQL, DirectAdmin, named on a VPS with a 16-core Xeon CPU and 2GB RAM. Currently, only 300 MB out of 2048 are used. It might sound a stupid, but how ...
2
votes
2answers
8k views

MySQL - CSV UPDATE (not INSERT) into existing table?

I have a table with the following columns: id | name | city The table has 1000 entries in it. I have a CSV file with a limited number of lines like this: id,city 34,Denver 45,Kansas City 145,New ...
1
vote
1answer
1k views

updating mysql primary keys

I need to be able to swap values that are used in a primary key. Meaning, in one update statement, I need to change id 1 to id 2, and id 2 to id 1. In other relational databases, (Sql Server and ...
3
votes
1answer
152 views

How can I find the user who performed a database transaction?

I am using MySQL database. Suppose a database "X". It is used by multiple users. I want to find those users who performed any transaction of data into this database. Is it possible? Can I find it ...
2
votes
1answer
783 views

MySQL Tuner OPTIMIZE and Defragmentation

I have just started using mysqltuner to help keep my mysql server fast and healthy. However, I am having a little trouble optimizing my tables. When I execute mysqltuner, it all works fine and returns ...
2
votes
1answer
155 views

MySQL optimization question — how to speed up a simple query

I have a very simple database, one table with five columns (one INTEGER key, two VARCHAR, a DATE, and a DOUBLE). The table contains 909,000 records. A simple query like SELECT t.price FROM ...
2
votes
2answers
242 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?
0
votes
0answers
379 views

Oracle Flashback Archive aka Total Recall in MySQL

What would you suggest as a replacement system to achieve the same functionality of Oracle Flashback Archive aka Total Recall in other DBMS, in general or specifically in MySQL or MariaDB? ...
3
votes
1answer
340 views

MySQL text field without quote

I've just run into something strange. The base_id is a varchar(255). When I do a SELECT without quote, it scans the whole table: mysql> EXPLAIN SELECT sid FROM rf_fo.scald_atoms WHERE base_id = ...
5
votes
1answer
1k views

Substantial difference between database size under MySQL and actual size on disk

When I check the size of my databases under MySQL I get this: MariaDB [(none)]> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", ...
1
vote
1answer
847 views

mysql error log issue

I have mysql server running successfully. The error log location is as.. mysql> show variables like '%log_erro%'; +---------------+------------------------+ | Variable_name | Value ...
6
votes
3answers
261 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 ...
4
votes
2answers
2k 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 ...
3
votes
1answer
4k 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 ...
7
votes
3answers
249 views

Using Replication for Scale-Out

Having read Using Replication for Scale-Out, how can I route different queries to different servers, for example, SELECTI want to route to slaves and NON-SELECT to master. I assume as loadbalancer I ...
1
vote
2answers
128 views

Improving MySQL efficiency

I have a table now containing over 43 million records. To execute SELECT, I usually select records with the same field, say A. Will it be more efficient to divide the table into several tables by ...
6
votes
4answers
3k 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 ...
2
votes
3answers
885 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 ...
28
votes
3answers
13k views

Advantages and Disadvantages to using ENUM vs Integer types?

Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled. Is it better for this column's data type to be an int/bool (1 or zero) or to ...
3
votes
1answer
322 views

Is there a generic framework/design pattern/library for real time replication of MySQL database onto other servers?

Summary I need to backup in real time updates/inserts to a MySQL database. Cron/batch jobs appear to be unsuitable for this particular requirement due to the time interval meaning that the data would ...
3
votes
2answers
4k views

MySql: Delete all rows greater than n entries ordered by datetime

Can someone help me with a MySql query to delete all rows greater than n entries ordered by date? I.e. say I have 1200 rows of data with a timestamp column. I need to order it by date and preserve ...
3
votes
1answer
5k views

MySql - ERROR 1071 Specified key was too long; max key length is 1000 bytes while restoring dump

while trying to restore a MySql Dump, i get the following error: ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 1000 bytes any ideas?
1
vote
2answers
11k views

How to join two table and show one query result in MySQL?

I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there ...
2
votes
1answer
3k 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 ...
4
votes
3answers
188 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 ...
4
votes
2answers
7k views

Is InnoDB Engine up to speed against Memory Engine?

I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows: SELECT P.col1, P.col2, ...
3
votes
1answer
693 views

Can you have a WHERE clause with a greater than operator on a composite key?

I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table : CREATE TABLE `bbcmapimp_pc_raw` ( `postcode` char(8) NOT NULL, ...
10
votes
1answer
6k 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 ...
1
vote
2answers
641 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 ...
3
votes
3answers
3k views

Confirm that my.cnf file has loaded OK

Does anyone know if there is a way to confirm if any my.cnf file is being read by MySQL? I only have the one my.cnf on the machine (and there are no my.ini files) which I have in /etc/my.cnf. When I ...
1
vote
2answers
585 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 ...
0
votes
4answers
143 views

Resetting MySQL Password [closed]

Please see the following link from Super User. I cannot get anything working: http://superuser.com/q/342609/93569 Update: This link has been dramatically changed! Please see the link.
3
votes
2answers
1k 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 ...
1
vote
1answer
177 views

Appropriate Windows version for Mysql

I have a HP ProLiant DL380 G5 server and I would like to install mysql on it . My questions are: Which MySQL version should I install from this link for better performance, 32 bit or 64 bit? Which ...
3
votes
2answers
2k 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 ...
3
votes
1answer
1k 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 ...
7
votes
1answer
1k 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 ...
5
votes
1answer
5k 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. ...