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

learn more… | top users | synonyms

2
votes
1answer
128 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
3answers
339 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
2answers
1k views

Fastest way to create local copy of live InnoDB MySQL database?

We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+). Every month or so we need to copy this database to our development machines to work on locally. ...
2
votes
2answers
868 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
2answers
1k views

How to use OR in query with priority?

In a simple query like SELECT * FROM table WHERE id='x' OR id='y' How to give a priority to get only one of the WHERE clauses? I mean getting rows with id='x'. Only if there is no row, get row ...
2
votes
1answer
4k 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
113 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
1answer
2k views

Dealing with empty strings while loading a table from a CSV

I am trying to load a CSV file into a table. The CSV file contains some empty strings for example: 1,2,,4 I want to load the empty field as null. One way I know is to use \N i.e: 1,2,\N,4 but it ...
2
votes
2answers
4k views

Unable to drop the table since its showing waiting for meta data lock

We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on ...
2
votes
2answers
3k views

Need suggestions for best way to archive MySQL (InnoDB) tables

Problem: I have two fairly large tables. A 'wall' table for messages between users with 9 million rows at 2 GB and a 'chapter' table at 2 million rows and 18 GB. I want to keep the number of active ...
2
votes
2answers
3k 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
546 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
1answer
29 views

Is there an easy way to update a mysqldump file from version 9.11 to 10.11? [closed]

We have a client that has had their servers for soo long, they were still running MySQL 4.0.24. Now that they are refreshing their servers, we took a mysqldump of their current database to put into ...
2
votes
1answer
174 views

mysqldump doesn't restore database. why not?

Why doesn't my restore operation work? I created a dumpfile for a database using: mysqldump -u root -p databasename > /home/databasename_bkup.sql I then opened the dumfile and confirmed ...
2
votes
1answer
77 views

How can I determine which Innodb table is being written?

I am running Mysql 5.5. I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The ...
2
votes
1answer
184 views

MySQL - Get Minimum Value, Data From Multiple Tables

Good evening all, first post on the DBA forums. I am having real issues working out how to accomplish a seemingly simple task within MySQL as a model for PHP code later on. I am not exactly a MySQL ...
2
votes
2answers
205 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
2
votes
1answer
974 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
500 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
2answers
365 views

MySQL user without SUPER privilege

This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question. When creating users in mysql using grant all ...
2
votes
1answer
45 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
1answer
1k views

Space not reclaimed after dropping MyISAM table

I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I ...
2
votes
2answers
39 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
3answers
1k views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
2
votes
1answer
78 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
2answers
676 views

MySQL Synchronous Replication on Ubuntu 12.04 with DRBD

I have been looking around for some easy and open-source ways to handle database synchronous replication. DRDB on a Linux server seems to be one of the fastest and easiest methods to handle database ...
2
votes
2answers
595 views

Errors while creating multiple mysql-5.5 instances

I have installed 3rd mysql instance on my testing server. 2 instances already running without any issues. When I installed 3rd instance by mysql-5.5.30 zip source, it installed successfully but when ...
2
votes
1answer
436 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
397 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
330 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
3answers
4k views

Automatic MySQL Replication Fail-over Strategy

I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the ...
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
713 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
421 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
2k 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, ...
2
votes
1answer
580 views

“set-variable = innodb_thread_concurrency=4” or “innodb_thread_concurrency=4”

Based on this question, the asker wrote: set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = ...
2
votes
1answer
2k views

MySQL Workbench ERROR 1064

I have a problem with my database. I have created the model and now I want to sync it with my database on my WAMP Server (local) but it keeps giving me an error. I searched for the cause of the ...
2
votes
1answer
756 views

InnoDB - How to get top locked tables and rows which are locked

I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?
2
votes
1answer
348 views

Referring to super parent or as a separate entity?

I have this person table as super parent, id firstname lastname email telephone ... ... and user table as a child id person_id (FK) password username screenname ... ... They must be 1:1 ...
2
votes
1answer
289 views

mysql emits BINLOG rows even though binlog_­format=­STATEMENT

We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default) According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in ...
2
votes
1answer
3k views

Is there a PPA for MySQL 5.5 for ubuntu 10.10 and 11.10?

I want to install MySQL server 5.5 on ubuntu 10.10 and 11.10 servers but have not yet found a problem free PPA or installation method as of yet. Has anyone managed to do this with the said versions of ...
2
votes
1answer
1k views

How to upgrade MySQL to a New version..?

I have installed mysql using the package management system.I am using ubuntu machine. After that i have very well configured the server for InnoDB and other server related parameters. I am using the ...
2
votes
1answer
4k views

Fixing MySQL errors recorded in error logs

Below is the extract of the most common lines of error being recorded in error logs on my PC: 110905 16:16:54 [Note] Plugin 'FEDERATED' is disabled. 110905 16:16:54 InnoDB: The InnoDB memory heap is ...
2
votes
1answer
202 views

How much space is needed to reclaim space from a table in INNODB

I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the ...
2
votes
3answers
415 views

Bulk Delete for Large Table in MySQL

I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month. If I do DELETE FROM NOTIFICATION WHERE CreatedAt ...
2
votes
1answer
1k views

Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

I dropped my UNIQUE KEY index 'myindex' on a table and I wanted to change the UNIQUE KEY to something else, so after dropping it I did: alter table mytable add unique key anykey (add1, city, state); ...
2
votes
2answers
66 views

How do you update a large MySQL table that's on a server?

I have an application on a web server. The PHP front end for the application queries a MySQL database which has a table that contains 60 million taken domain names. My application queries this ...
2
votes
2answers
320 views

How to generate auto increment values for my duplicate column in my sql?

How to display when i was run to find duplicate values from my table at this time i want to add new column in select statement with auto increment values SELECT * FROM tutorials_tbl WHERE Lscno IN ...
2
votes
1answer
76 views

WHERE attribute having two relation lines in the same SQL

I have a database designed with tree tables: checklist (id, name) tag (id, name) checklist_tag (checklist_id - FK, tag_id - FK) Running this SQL: SELECT cc.id as checklist_id, cc.name as ...
2
votes
1answer
42 views

How to decipher the transaction message in innodb status log.

I have several transactions that seem have stalled. Looking at the innodb status log, I found several messages similar to the following: ---TRANSACTION 69DBBB, ACTIVE 947 sec starting index read, ...