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

15
votes
3answers
16k 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 ...
15
votes
3answers
7k 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 ...
15
votes
3answers
12k 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 ...
15
votes
5answers
22k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
15
votes
7answers
13k 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 ...
15
votes
4answers
31k 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 ...
15
votes
4answers
4k views

How to create a conditional index on MySQL?

How to create an index to filter a specific range? AFAIK it's impossible to create directly but I think it's possible simulate this feature. Example: I wanna create an index for NAME column just to ...
15
votes
3answers
17k views

Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?

I have a unique compound key like fr(fromid,toid) in the table, when I run the query with explain I get the following result: Impossible WHERE noticed after reading const tables` The query I ran: ...
15
votes
3answers
35k views

How to make MySQL table name case insensitive in Ubuntu?

I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default. Now, I want to make MySQL work as case ...
15
votes
1answer
26k 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 ...
15
votes
2answers
2k 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 ...
15
votes
1answer
476 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 ...
14
votes
6answers
23k views

slow load speed of data from mysqldump

I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe ...
14
votes
4answers
20k views

How to estimate/predict data size and index size of a table in MySQL

I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer For an example we have a table City with InnoDB ...
14
votes
2answers
4k 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 ...
14
votes
2answers
13k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
14
votes
7answers
90k views

How to import a .sql file in MySQL?

I am trying to import a .sql file using MySQL Workbench and I get this error: ERROR 1046 (3D000) at line 28: No database selected I have first created an empty database called with the same name as ...
14
votes
5answers
14k views

Why using innodb_file_per_table?

There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
14
votes
4answers
95k views

MySQL: how to restore table stored in a .frm and a .ibd file?

I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-...
14
votes
1answer
14k views

Dump only the Stored Procedures in MySQL

I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
14
votes
2answers
6k views

Reasons for occasionally slow queries?

We are running MySQL 5.1 on Windows Server 2008 R2. We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
14
votes
1answer
10k views

MySQL transaction size - how big is too big?

I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction. There are many aspects, and the imports may yield anywhere between 100k-...
13
votes
5answers
119k views

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT ...
13
votes
5answers
16k 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 ) ?
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 ...
13
votes
2answers
12k views

What is the optimal way to upgrade production RDS instance?

I have MySQL small RDS instance as part of my production system and I want to upgrade it to medium instance with provided IOPS. As old-school DBA I'm aware about "add slave; promote to master; ...
13
votes
3answers
27k views

How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production. So I would just like to make all collations which are utf8_bin to utf8mb4_bin....
13
votes
3answers
4k 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 ...
13
votes
3answers
13k views

innodb_file_format Barracuda

I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda. I was looking to play around with some compresses innodb ...
13
votes
2answers
37k views

What is the difference between optimize table and analyze table in mysql

What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.
13
votes
5answers
17k 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 ...
13
votes
2answers
14k 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 ...
13
votes
2answers
2k views

Database redesign opportunity: What table design to use for this sensor data collection?

Background I have a network of approximately 2000 sensors, each of which has about 100 data points that we collect on 10 minute intervals. These data points are typically int values, but some are ...
13
votes
5answers
2k 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 ...
13
votes
2answers
29k 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 (...
12
votes
5answers
16k views

How to manage millions of users?

I am about to launch something really big. I need to prepare my server and database. I would like to group each set of 100,000 users in separate user tables but I do not know how to associate one ...
12
votes
2answers
16k 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.
12
votes
3answers
14k views

Is innodb_file_per_table advisable?

We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with innodb_file_per_table or leave ...
12
votes
6answers
9k views

Splitting Tables in MySQL. Good practice?

I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data. The tables look like this: [...
12
votes
2answers
22k views

What is the safest way to switch the binlog format at runtime?

Because of the following warning in mysqld.log: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it ...
12
votes
4answers
25k views

Modify DEFINER on Many Views

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result. Got error: 1449: The user ...
12
votes
3answers
2k 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 ...
12
votes
5answers
32k 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 ...
12
votes
5answers
27k views

Cannot open table mysql/innodb_index_stats [duplicate]

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well. The errors I ...
12
votes
3answers
23k views

MySQL Error Reading Communication Packets

In MySQL error logs, I see these quite few a warnings like these: 120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading ...
12
votes
3answers
11k views

Best of MyISAM and InnoDB

Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
12
votes
3answers
23k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
12
votes
2answers
17k views

kill all queries - MySQL

Sometimes during a SNAFU I have to run kill query xxxxxxx twenty or thirty times. Any sort of kill all command I am missing? On account of how I don't like typing.
12
votes
4answers
14k views

Mysql int vs varchar as primary key (InnoDB Storage Engine?

I am build a web application (project management system) and I have been wondering about this when it come to performance. I have an Issues table an inside it there are 12 foreign keys linking to ...
12
votes
2answers
10k views

Percona vs MySQL

What is Percona? How does it differ from MySQL? When should we consider switching (or upgrading) from stock MySQL to Percona? To add some specifics in our situation we almost exclusively use ...