mysql-5 : Version of MySQL Starting with MySQL 5.0

learn more… | top users | synonyms

72
votes
6answers
59k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
14
votes
5answers
7k views

In MySQL, does the order of the columns in a WHERE clause affect query performance?

I am having performance issues on certain database queries that have large possible result sets. The query in question, I have three ANDs in the WHERE clause Does the order of the clauses matter? ...
13
votes
1answer
24k views

How long is “too long” for MySQL Connections to sleep?

I logged onto my server to check the status of the database and noticed on the SHOW PROCESSLIST; that there are a large number of connections sleeping that are very old. What duration should the ...
12
votes
3answers
4k views

Why does importing a 12 GB .sql file take more than 36 hours?

I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB. Considering the ...
10
votes
3answers
1k views

Speeding up conversion of MyISAM to InnoDB

I've got a mysql 5.1 server with a database of approximately 450 tables, taking up 4GB . The vast majority of these tables (all but 2) are MyIsam. This has been fine for the most part (don't need ...
9
votes
3answers
6k 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 ...
9
votes
4answers
13k views

Restore mysql database with different name

How can we restore mysql database with different name from mysqldump file. I dont't want to open dump file and edit it. Any other better methods?
8
votes
4answers
794 views

Is splitting a 'users' table for authentication purposes a good idea?

Suppose I have a user table in my site in which there are around 2-3 Million users (records) in the table. For speeding up my login process, is it a good approach to split my user table, one for ...
6
votes
2answers
14k 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 ...
6
votes
3answers
3k views

Users complain that system runs slow when mysqldump is in progress.

The MYSQL database (ibdata1) is of size 73 GB and is configured to run as a dedicated database server on Windows 2008 O/S for INNODB tables. We are running the backup using mysqldump mysqldump ...
6
votes
1answer
6k views

Deleting bin logs in replication environment

I have a question about deleting binary logs in the replication environment: We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy ...
5
votes
2answers
5k views

Query runs a long time in some newer MySQL versions

I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware ...
5
votes
3answers
9k views

Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*

Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions: Attempting to view or run ...
5
votes
1answer
669 views

will replication from 5.5.20 to 5.0.XX server work?

I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible? Because I remember when I transferred ...
5
votes
1answer
3k views

MySQL INNODB SELECT query hanging on copying to tmp table

I have a query that is hanging (seemingly) randomly on a specific query. Most of my website users do not have a problem when this query runs but a few users are having timeout issues on the website ...
5
votes
3answers
5k views

Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB

I need to design database to store information contained in millions of log files generated by devices on trial on network. I have designed 5 different tables to store log file information. But, as ...
4
votes
1answer
1k views

PostgreSQL versus MySQL for EAV structures storage

Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
4
votes
3answers
12k views

Set “lock wait timeout” in MySQL

Where would I set the maximum time a query will wait for a lock in MySQL 5.0.68 before timing out?
4
votes
2answers
5k views

Performance settings for MyISAM tables - keep everything in memory

I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data). Since the databases size is several times smaller than the available memory, how can I make full usage ...
4
votes
3answers
3k views

Changed max_allowed_packet and still receiving 'Packet Too Large' error

I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
4
votes
3answers
1k views

Why query_cache_type is disabled by default start from MySQL 5.6?

We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6. We enabled it again and ...
4
votes
1answer
13k 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 ...
4
votes
2answers
767 views

Read mysql config (my.cnf) using mysql console

I have a mysql server that is not under my control (I don't have ssh on that machine). I only have access to one database using mysql client. How can I have access to config options that are declared ...
4
votes
1answer
4k views

What is the use of ibdata1 file in mysql?

I have application which has the backend db as Mysql(InnoDb engine), and currently my client wants to decrease the size of the ibdata file to some fixed size. I found the three below solutions. ...
4
votes
1answer
1k views

Is there a way to find my.cnf in shared hosting?

I'm curious is there a way to find my.cnf in shared hosting or is this file can be configured just by DBAs and no one can set his/her own settings?
4
votes
1answer
1k views

Determining max database and table size supported and present size

One of my MySQL databases has been running on a single PC from the last four years. Here is my setup: MySQL 5.5.14 Windows XP on NTFS partition All of my tables are using InnoDB engine Here are ...
4
votes
1answer
3k views

Setting up MySQL Cluster on Two Systems

I am attempting to setup a MySQL Cluster. I have the folllowing setup one management node two data nodes another machine on which two more nodes reside Below is the config.ini file: [ndbd ...
3
votes
1answer
116 views

count(id) on Char(0) column does not work! [duplicate]

Possible Duplicate: What is the difference between select count(*) and select count(any_non_null_column)? I have a column with the type of char(0). some of the rows are NULL and some of ...
3
votes
3answers
2k views

MySQL optimization - year column grouping - using temporary table, filesort

I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
3
votes
2answers
2k views

MySQL replication to slave having VPN connection to Master

I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network ...
3
votes
1answer
379 views

How can I get MySQL to use the value returned by a function as a default?

I have a pre-existing table that has a column to that can hold a UUID. I'd like to be able to define a default such that new rows coming in (from legacy systems) will still get a UUID defined, via ...
3
votes
2answers
3k views

ALTER TABLE - Rename a column

This got me nuts! I want to rename a column from read-more to read_more in blog table Tried all this: ALTER TABLE blog RENAME COLUMN read-more to read_more; ALTER TABLE blog CHANGE COLUMN ...
3
votes
1answer
2k views

how to import mysql database structure?

I have a database on Machine A. What I want is to dump my database in such a way that when I import it on another machine (Machine B) only the structure of the tables and Database are created. I want ...
3
votes
3answers
500 views

Storing a large number of small size entries in MySQL

I have a database that stores readings from numerous gauges. Depending on the gauge, there may be data for that gauge every 1-5 minutes with multiple readings (i.e.- weather station and it stores 5 ...
3
votes
2answers
82 views

Find problematic MySQL users and databases

We have a MySQL 5.1 server that is an "open" service: anyone on campus can request a database. As such, we have several thousand databases on this one server. As you might imagine, sometimes one ...
3
votes
1answer
3k views

Allocating 8GB memory to MySQL on a 64bit system

Specs - MySQL 5.0.x , Redhat 5.9 , Physical memory - 16GB I am trying to set innodb buffer pool size to 8GB (innodb_buffer_pool_size=8G). When i do, and start mysql, i get following error - ...
3
votes
2answers
1k 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

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

How to grant access for multiple tables to a user in MySQL DB?

I have mysql db named "greats" with four tables as below +---------------------+ | Tables_in_greatstat | +---------------------+ | log | | sitedet1 | | siteindex ...
3
votes
1answer
1k views

Are many NULL columns harmful in mysql InnoDB?

As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance. Does the presence of excess ...
3
votes
1answer
834 views

Keeping MySQL staging database on same server updated from production

I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly ...
3
votes
1answer
50 views

Is time to deliver data included in time measured for a Slow Query

I'm looking at some horrible numbers in a SLOW_QUERY_LOG (one example) Count: 2563 Time=23.21s (59498s) Lock=0.00s (1s) Rows=21.4 (54857) This is for a simple SELECT statement that runs for less ...
3
votes
1answer
1k views

Master and Slave having same id's

I am working as a remote DBA for some of the production servers. There was replication from Master to Slave. There is something changed in the scenario they have made one of the Slaves as master ...
3
votes
1answer
825 views

Duplicate UNIQUE KEY error after ROLLBACK in MySQL

I have a table like this mysql> describe seudonimos; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
3
votes
2answers
2k views

How do you copy a table from MySqlServer_A to MySqlServer_B?

I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to ...
3
votes
1answer
3k views

PostgreSQL 9.1 vs MySQL 5.6 with InnoDB ? Which is better for scaling?

PostgreSQL 9.1 vs MySQL 5.6 with InnoDB. I do know that PostgreSQL offers better SQL features. I am currently using MySQL 5.6 lab release (the one with InnoDb full text search feature)(not in ...
3
votes
1answer
514 views

why is mysql still writing data two hours after all transactions have stopped

I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data). Lately, I noticed something peculiar. After I ...
3
votes
1answer
585 views

Do I have duplicate key indexes?

CREATE TABLE relations ( object_id BIGINT(20) NOT NULL PRIMARY KEY, object_term BIGINT(20) NOT NULL, UNIQUE KEY link(object_id, object_term), CONSTRAINT fk_term FOREIGN KEY(object_term) ...
3
votes
2answers
168 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 ...
3
votes
2answers
158 views

MySQL 1264 warning from libmysql

I'm using the libmysql.dll library to connect to my database from a 3rd party application (metatrader). I managed to connect and things are going almost fine. I use mysql_real_connect and ...