MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.

learn more… | top users | synonyms

3
votes
2answers
455 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
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
2k views

MySQL information_schema doesn't update

I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true. After I run the query delete from abc.test, I want ...
3
votes
3answers
352 views

Finding differences between a master and slave when pt-table-sync fails

I have a table that's showing some differences on a master and slave according to pt-table-checksum (PTC). I've found some verifying differences beyond that. Certain tables in the mix have composite ...
3
votes
1answer
617 views

Convert Oracle Query for Date to Mysql queries

Need to convert the following oracle queries to MYSQL queries. 1) select to_char(to_date(?,'DD-MM-YY')) from dual; 2) select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY')) from dual; 3) ...
2
votes
4answers
3k views

Creating Archiving database for old data in MySQL

I Have a Master and Various Slave Servers. There is a huge amount of the data on all the servers and I wish to create an Archiving system for our Data. Scenario is : I want that only Last one year ...
2
votes
2answers
6k 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 ...
2
votes
2answers
521 views

Tool to match mysql password encryption

I've created a user ... but forgotten the password mysql> create user 'blayo'@'%' identified by 'right'; Which Linux command line tool can encrypt the password the same way mysql does ? mysql> ...
2
votes
2answers
113 views

How to store logs correctly?

(This is somewhat a follow up on my last question) I am using MySQL 5.5 with innodb. My Log table consists of two columns. One for user number, and the other for a string consisting of IP, request ...
2
votes
2answers
202 views

Tinyint structure is confusing

I am confused to see this tinyint(3) structure. If tinyint can store only 1 byte then what is the purpose of giving the size in brackets? Is there something special with this parameter in MySQL?
2
votes
2answers
815 views

How to GROUP_CONCAT between given rows?

I have a table as id diary 1 breakfast 2 walk 3 start 4 office works 5 office projects 6 end 7 taxi 8 start 9 preparing for meeting 10 doing the meeting 11 end 12 night I want ...
2
votes
4answers
307 views

Does my table violate normalization rules?

I am developing an emulator for a game. My item table looks like this: -- ---------------------------- -- Table structure for `items` -- ---------------------------- DROP TABLE IF EXISTS `items`; ...
2
votes
3answers
4k views

innodb_buffer_pool_size not changed

I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf default-storage-engine = innodb innodb_buffer_pool_size = 3G innodb_log_file_size = 256M innodb_thread_concurrency = 8 ...
2
votes
2answers
5k views

“Could NOT find Curses” error when installing MySQL from source

I am installing MySQL Server from the source distribution which I downloaded from: http://dev.mysql.com/downloads/mysql/#downloads. I have downloaded the file (mysql-5.5.19.tar.gz). I am using ...
2
votes
2answers
130 views

Are there benefits to using more than 4 CPUs for MySQL?

We are running MySQL 5.5 on linux under VMWare, running on 2 CPUs. We are planning on increasing this to 4, but we have un-used CPUs available, and I'm wondering if there is any benefit to increasing ...
2
votes
2answers
700 views

Does MySQL 5.5 support shared database with separate schema?

I am developing a SaaS based product using MySQL 5.5 as the backend database. I want to keep shared database for all the tenants, but separate a database schema of each tenant. There would be a single ...
2
votes
2answers
508 views

MySQL - How much physical space will be reduced by changing the column type?

Lets say I have the following table: CREATE TABLE `my_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `relationship_status` varchar(48) CHARACTER SET utf8 COLLATE ...
2
votes
1answer
159 views

Need some help with this query get all status and their associated comments of me and all my friends

I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was ...
2
votes
3answers
1k views

MySQL backup with no downtime

I'm running a M/S replication of MySQL 5.5 / innoDB (over ubuntu). My DB is pretty large: ~250GB. I'm looking for a backup tool/service that will do the following: full backup no downtime / locks ...
2
votes
2answers
3k views

How to improve MySQL Server Performance..??

As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers. Now my question is where to start from like we will need to find out many things as 1.Find the ...
2
votes
2answers
3k views

MySQL Table level Replication

I have 3 servers, say A,B &C. All have the equivalent data(same data). Now what I want is to make A as the master and B,C the slaves. But the scenario is that I only want to replicate some of the ...
2
votes
4answers
245 views

Aggregate query optimization

Could you please help me to optimize this query - currently it takes ~4 minutes to run this query. It looks like keys: ip and timestamp are non-unique so shall I add some indexes? mysql> EXPLAIN ...
2
votes
1answer
291 views

MySQL with high CPU and memory usage

here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - ...
2
votes
1answer
151 views

Is there a way to achieve Workload Management in MySQL?

Is there a way to set up Workload Management in MySQL (in particular v5.5 on AWS RDS)? By Workload Management I mean the functionality implemented in e.g. Teradata, RedShift that allows ...
2
votes
2answers
3k views

MySQL equivalent of WITH in oracle

I need the mysql equivalent of the WITH clause in Oracle. Please help. Thank You
2
votes
3answers
899 views

how to install mysql on mac?

My Mac runs Mac OS X, version 10.7.5., so it's a 64 bit system. First, I downloaded mysql-5.5.28-osx10.6-x86_64.dmg. Then I double clicked mysql-5.5.28-osx10.6-x86_64.dmg, it's mounted, and shows ...
2
votes
1answer
73 views

Why does MySQL increment the auto_increment value when there's a constraint violation?

I have a table in a MySQL database with one column id, which has auto_increment and the Primary Key. There's also a Unique constraint in this table, on different columns. I noticed that when I try to ...
2
votes
2answers
665 views

High MySQL “InnoDB Writes Per Second” - Can't see the queries?

On our live MySQL 5.5 database server, we use MySQL Workbench for running manual queries, checking running queries to see what's going on, etc. I noticed today that on the "Server Status" tab there's ...
2
votes
3answers
117 views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
2
votes
1answer
778 views

Mysql INSERT INTO .. ON DUPLICATE KEY UPDATE

I'm trying to do the following query in mysql 5.5 INSERT INTO countingTable( image_count, article_id ) SELECT COUNT( article_id ) AS sum, article_id FROM imageTable ON DUPLICATE KEY UPDATE ...
2
votes
2answers
621 views

How to SELECT rows with time duration stored in varchar column?

There is a column in table defined like this : duration varchar(10) It has value for duration of a MP3 file stored in it like : 52:19 (Minutes : Seconds) How can I create a SQL query, for ...
2
votes
1answer
3k 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
95 views

Need to Rewrite/optimize Slow Query

I have a query with following explain plan EXPLAIN SELECT COUNT(applicant.id) as applicantCount, application.name,applicant.application_id,application.id FROM applicant LEFT JOIN application ...
2
votes
2answers
8k views

MySQL using too much CPU

I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server: CentOS 5.9 24gb Ram 8 ...
2
votes
2answers
2k views

mysql 5.5 corrupt - missing temporary table

One of our mysql instances crashed and failed to start: 120422 10:04:13 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'! ...
2
votes
2answers
509 views

Any problem will all InnoDB and one MyISAM table?

I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM ...
2
votes
2answers
2k views

Cannot configure nor start MySQL

On a brand new Kubuntu 14.04 install, I ran sudo aptitude install mysql-server-core-5.6. The install could not complete due to dependencies of some KDE packages on mysql-server-core-5.5, which would ...
2
votes
1answer
338 views

Auto_increment value needs to be set as S1

Hi I need to set auto increment for a table as S1. I tried by altering the table and also by creating by new table I am aware how to set auto_increment but when i try to set auto increment as S1 i am ...
2
votes
2answers
229 views

How to monitor InnoDB Disk I/O using Server Status Variables?

This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? " As mention in my previous question i want to count total number of disk i/o per query, but ...
2
votes
1answer
39 views

Will composite indexes or unique indexes help with query performance?

I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The ...
2
votes
2answers
36 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...
2
votes
1answer
62 views

Unknown column error with an index

I created a simple index associated with a timestamp column CREATE TABLE `data` ( `id` INT(11) UNSIGNED NOT NULL, `ticker` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci', `comment` TEXT ...
2
votes
1answer
2k views

MySQL: Alter Table on very active table gives “Waiting for table metadata lock”

At my work, we've recently upgraded to MySQL 5.5 and since then, have been bitten a few times by the new (and intended) behavior of metadata locking. Basically, whenever we run an ALTER TABLE ...
2
votes
1answer
362 views

datetime vs timestamp in mysql 5.5

What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this? I have a situation where I am trying ...
2
votes
1answer
307 views

select count(*) in mysql 5.5 innodb— rewrite advice?

I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow... select count(*) from mails3 join questions using (question_id) where ...
2
votes
1answer
225 views

How can I disable utf8mb4 entirely on MySQL 5.5?

I know you're not supposed to do this, but I have a MySQL 5.5 master and a MySQL 5.1 replication slave. That's supposed to be okay, unless I do something stupid like use utf8mb4 that isn't supported ...
2
votes
1answer
1k views

Issue with connecting to the mysql database

We have an outstanding issue with VCC in chicago, there is an issue connecting to the mysql database If an agent does a refresh of the monitor they will be logged out. This is the error we ...
2
votes
1answer
503 views

Does mysqld wait for init-script code to complete before opening the instance?

I'm trying to automate the execution of an application code at MySQL startup. The init-file server option seems handy and I need to know if mysqld waits for all the code in the init-file to complete ...
2
votes
1answer
340 views

Restriction on self referencing on insert in MYSQL?

How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself). mysql> SELECT * FROM Employee; ...
2
votes
1answer
965 views

Extract some columns data from one table and insert into multiple tables

I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables tbl_1: id, name, lastname tbl_2: id, ...