MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

14
votes
2answers
26k views

Multi cores and MySQL Performance

Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
14
votes
2answers
19k views

When to use views in MySQL?

When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table? One reason that I would prefer to use views is that the database schema ...
14
votes
0answers
3k views

MySQL 5.1.66 SSL connection error ERROR 2026 (HY000)

UPDATE2 Using WireShark I found out the problem string (I hope I did): 28 | 9.582638 | 192.168.18.128 | 192.168.18.129 | MySQL Response Error 1043 And the error is (according to docs): Error: ...
13
votes
4answers
34k views

How do I change the DEFINER of a VIEW in Mysql?

When I run mysqldump, I get an error: mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES This makes sense because foobar is a legacy ...
13
votes
5answers
6k views

How do I force one record to have a true value for a boolean column, and all others a false value?

I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table. Basically, I want to guarantee that this query will always ...
13
votes
3answers
8k views

What are reasons *not* to use the MEMORY storage engine in MySQL?

I recently discovered that MySQL has a "memory" engine that I wasn't aware of (most of my database work is for hobby projects so I learn what I need as I go). It seems like this option should give me ...
13
votes
3answers
4k views

Is it common practice to mix InnoDB and MyISAM tables on same server?

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
13
votes
1answer
22k 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 ...
13
votes
6answers
36k views

Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect

I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on? I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL ...
13
votes
3answers
6k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
13
votes
2answers
5k views

How do I properly perform a MySQL bake-off?

I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
13
votes
2answers
17k views

How can I change the default storage engine in phpmyadmin?

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
13
votes
4answers
31k views

Access denied for user 'root'@'%'

I used to access the root user in MySQL just fine. But recently, I am no longer able to. I am able to login fine : mysql -u root -p Here is the mysql status after login : mysql> status ...
13
votes
1answer
356 views

Does anyone have a good recommendation for a book which teaches sharding?

Does anyone have a good recommendation for a book which teaches db sharding (from scratch at best) I have read like 40 different websites talking about sharding. I'm not saying online sites/blogs ...
12
votes
7answers
13k views

Get the rank of a user in a score table

I have a very simple MySQL table where I save highscores. It looks like that: Id Name Score So far so good. The question is: How do I get what's a users rank? For example, I have a users ...
12
votes
2answers
17k views

Good explanation of cascade (ON DELETE/UPDATE) behavior

I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is ...
12
votes
2answers
3k views

Rows missing after online conversion from MyISAM to InnoDB

We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down. Now that the ...
12
votes
2answers
3k views

Can I create index on a table in a MySQL database while in use

And if I can, any reason (performance/stability) why I shouldn't?
12
votes
1answer
15k views

INT(5) vs SMALLINT(5)

In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?
12
votes
4answers
17k views

How do you identify InnoDB table corruption?

I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
12
votes
7answers
16k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
12
votes
5answers
13k views

Why do MySQL threads often show “freeing items” status when the query cache is disabled?

When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the "freeing items" state. The MySQL manual suggests that at least part of the reason for a thread to be in ...
12
votes
1answer
3k views

Is it possible to have passwords configured per database or per host in .my.cnf

I have the following in my ~/.my.cnf [client] password="somepass" but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different ...
12
votes
2answers
1k views

What can we do in MySQL 5.0 Replication to address bandwidth concerns?

I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of ...
12
votes
2answers
11k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
11
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 ...
11
votes
3answers
1k views

What to do when a field in a table approaches the max signed or unsigned 32 bit integer?

In any given database that hold user records in the form of an unique auto-increment field (for the sake of the example, inter-user messages)... what to do when the time comes and it approaches the ...
11
votes
5answers
20k views

MySQL server has gone away obstructing import of large dumps

I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a ERROR 2006 (HY000) at line 7758: MySQL server ...
11
votes
3answers
2k views

Why doesn't InnoDB store the row count?

Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row ...
11
votes
2answers
29k views

Safest way to perform mysqldump on a live system with active reads and writes?

I'm not sure if this is true but I remember reading if you run the following command in linux mysqldump -u username -p database_name > backup_db.sql while reads and writes are being made to a ...
11
votes
4answers
4k views

Are there any MySQL Benchmarking tools?

I've heard a long time ago that there is this tool that helps you tweek mysql settings for better performance but i can't seam to find it. I am aware that I can use ab for apache to simulate high ...
11
votes
1answer
14k views

Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week. I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual ...
11
votes
2answers
14k views

What is the recommended way to backup a MySQL/Amazon RDS database to S3?

I have two purposes for this: To have an offsite backup in case of a region wide problem with Amazon Web Services. To copy production data from a production billing account to a beta billing ...
11
votes
3answers
1k views

Use MySQL to regularly do multi-way joins on 100+ GB tables?

Background: I’ve created a web application that I would like to be able to scale reasonably well. I know I'm not Google or Twitter, but my app uses a fairly large amount of data for each user and thus ...
11
votes
2answers
1k views

Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
11
votes
5answers
1k views

Infrastructure for Highly Concurrent, High Write DB

My requirements are: 3000 Connections 70-85% Write vs Read Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of ...
10
votes
3answers
7k views

check constraint does not work?

I have the following table. create table test ( id smallint unsigned AUTO_INCREMENT, age tinyint not null, primary key(id), check (age<20) ); The problem is that the CHECK ...
10
votes
5answers
5k views

Where can I find the mysql slow log?

Just as the title says, where can I see it ? Are there any config options for it ( like how many ms would determine if a query is slow or not ) ?
10
votes
3answers
11k views

Why is 'LOAD DATA INFILE' faster than normal INSERT statements?

I've read an article that mentioned we can achieve 60,000 inserts per second by LOAD DATA IN FILE statement that read from csv files and insert it into database. Why should it differ from normal ...
10
votes
1answer
9k views

How is INFORMATION_SCHEMA implemented in MySQL?

The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL? When I connect to a fresh ...
10
votes
4answers
1k views

eCommerce orders table. Save prices, or use an audit/history table?

Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product A product can ...
10
votes
1answer
3k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
10
votes
2answers
20k views

How to SELECT from SHOW TABLE STATUS results

I'd like to limit the rows and columns that come back from the SHOW TABLE STATUS command in MySQL 5.1. Is there a way to get this same information through a SELECT statement so I can manipulate ...
10
votes
3answers
2k views

Why don't my .my.cnf settings work?

I have a .my.cnf file in my home directory on my desktop that includes: [dbid] user = myusername password = mypassword database = dbname host = server.location.com If I recall correctly, before ...
10
votes
1answer
7k views

Possible INDEX on a VARCHAR field in MySql

I am working in a MySql database, with a table like this: +--------------+ | table_name | +--------------+ | myField | +--------------+ ...and I need to make a lot of queries like this (with ...
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 ...
10
votes
3answers
12k views

MySQL table_cache and Opened_tables

I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so ...
10
votes
2answers
12k views

MySQL: Create index If not exists

Is there a way to create an index in MySQL if it does not exist? MySQL does not support the obvious format: CREATE INDEX IF NOT EXISTS index_name ON table(column) ERROR 1064 (42000): You have an ...
10
votes
1answer
4k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
10
votes
1answer
6k views

Clarification on MySQL innodb_flush_method variable

Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in ...