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

learn more… | top users | synonyms

1
vote
0answers
27 views

table scheme definiation it this way that insert duplicate into another table

I have a table in which there is a column name with SP varchar(10) NOT NULL. I want that column always to be unique so i created unique index on that column . My table schema as follows : CREATE ...
1
vote
1answer
2k views

How to run a SELECT query within while loop in PHP?

Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as $result1 = $mysqli->query("SELECT * FROM table1"); while ( $row = $result->fetch_assoc() ) { if ( $row['X'] == ...
1
vote
2answers
129 views

Breaking Semisynchronous Replication in MySQL 5.5

I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7. My application is running and updating the database of the master server and same is being updated in the ...
1
vote
2answers
1k 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 ...
1
vote
1answer
946 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 ...
1
vote
0answers
320 views

MySQL InnoDB slow inserts

I'm currently struggling with an MySQL Community Server 5.5.27 when inserting large numbers of rows into a table. We're running a 24 core Intel Xeon machine with an SSD software raid array. I did some ...
1
vote
1answer
2k views

Enabling LOAD DATA LOCAL INFILE in mysql

LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian ...
2
votes
1answer
673 views

Recover the disk space after deleting rows from table

I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the ...
2
votes
1answer
195 views

MySQL / Fusion IO Configuration Question

I need a little configuration optimization. I think what I have is pretty good but feel like there's still room for improvement. Current Config [mysqld] user=mysql datadir=/var/lib/mysql ...
1
vote
0answers
20 views

How do I configure MySQL to store specific entire InnoDB tables on a different drive in Windows ( Server )?

After skimming the MySQL ( 5.5 ) docs on Partitioning, that seems to deal with breaking parts of the same table off onto other drives. I'm struggling to find information on how I pinpoint an ...
0
votes
1answer
83 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
3
votes
4answers
6k views

How to Add Federated engine after installing Mysql

I have Mysql 5.5.18 and upon show engines: show engines; +--------------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine ...
0
votes
2answers
187 views

Which is the best tool to monitor replication on MySQL 5.5.20?

I have just installed and setup replication for 3 MySQL 5.5.20 server( 1 Master/ 2 slaves). I intend to add more slaves to the equation. Which is the best software tool to monitor the replication ...
0
votes
1answer
48 views

Optimizing a union query

SELECT id FROM user WHERE id IN ( 2,3,4,7,9,21,23,45,324,12,43,254 ) UNION SELECT DISTINCT user_id FROM workgroup_users WHERE workgroup_id IN ( 1,345,34,654,213,123,5,2,3,4 ) I know we should not ...
3
votes
3answers
160 views

Why set up static data in views vs. using tables in mysql?

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields. I have the following too: LDAPIMPORTANT - view that ...
0
votes
1answer
82 views

Whether or not to create separate tables

I am working on a project, where I need to store the information related to A and B, the A and B both have identical columns.There might be thousands of As and Bs. Should I use "type" column to ...
-3
votes
1answer
3k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
1
vote
3answers
1k views

How do I use subquery on the same table in MySQL?

I have a query like this which takes a really long time to run. The table is around 4 million rows. DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now; I'm hoping I could ...
0
votes
0answers
56 views

Percona mysql server slow queries

Recently I bought new database server and installed percona mysql server 5.5.32-31 and transferred my /var/lib/mysql directory from old database server having Percona mysql server 5.5.28 to new ...
2
votes
1answer
74 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 ...
0
votes
0answers
46 views

Is using “read-committed” isolation level save on *slave*?

I saw a few articles which discuss how different transaction isolation levels affect replication. If I understand properly, all these related to transaction isolation settings on master, not on slave. ...
5
votes
1answer
6k views

Should I increase max_connections in AWS RDS t1-micro for MySQL?

I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
0
votes
1answer
80 views

Practices to keep SQL Server databases easy to port to MySQL?

We've developing an ASP.NET Web application that is to be designed for MS SQL Server 2008. I'd like to minimize my future dB admin's headaches should there be a need to migrating over to MySQL. With ...
1
vote
0answers
351 views

SELECT + INSERT + Query Cache = MySQL lock up

MySQL server seems to constantly lock up and stop responding on certain types of queries and eventually (after couple of minutes of not responding) give up with an error "MySQL server has gone away", ...
0
votes
2answers
65 views

Mysql Command required for getting values from my.cnf

I am new to MySQL DBA, i have a doubt, Is there any command for finding my.cnf configuration file variables and its respective value which is mention in my.cnf file in mysql prompt itself. like we ...
1
vote
1answer
154 views

Is there a way to find the least recently used tables in a schema?

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...
2
votes
2answers
2k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
2
votes
2answers
416 views

changing TEMPORARY TABLE default collation

when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
1
vote
3answers
2k views

MySQL: Trying to update to 5.5

I've followed this instructions, but after mysqld --skip-grant-tables --user=mysql, I'm getting this: 110913 20:29:18 [Warning] Can't create test file /var/lib/mysql/my-laptop.lower-test 110913 ...
0
votes
3answers
115 views

Database migration, how to

I have two databases DB1 and DB2. In both databases exists this two tables mo_sms and mt_sms. This is the structure of those tables: CREATE TABLE IF NOT EXISTS `mo_sms` ( `id_MO` int(11) unsigned ...
3
votes
2answers
655 views

Replication error

We have a slave server that has stopped replication due to the following error: Slave SQL: Query caused different errors on master and slave. What could be the cause of this error? And what would ...
0
votes
2answers
219 views

How can we check for data integrity when we upgrade from MySQL 4.1 to MySQL 5.5?

By data integrity, I mean: Schema integrity Data (record level) integrity Checksum was an option, but since the storage format for some data types in MySQL 5.5 has been changed, it won't be ...
3
votes
1answer
79 views

MySQL Replication not proceeding

I have a weird replication problem I have not seen before. It's basic mysql replication with single master and two slaves. One of the slaves is not executing replication events and seems just stuck ...
0
votes
1answer
3k views

Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?

I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation. Would it be useful to run -check with this option or does the -optimize already take care of that? I am only ...
0
votes
0answers
94 views

Importing large .sql file stops before even midway and starts deleting

Okay so i'm trying to import my minecraft servers .sql file into database and after first 1GB of importing data it stops without error and starts deleting what already has been imported any ideas? ...
0
votes
0answers
28 views

Merge rows with common field value in MySQL [duplicate]

I have a table which was imported from MS-SQL, it has about 1.9 million rows. After importing I realized that a lot of the rows are duplicates and there are no primary key constraints. The rules to ...
-3
votes
3answers
72 views

Listing databases that are not in use

I want to show databases that are not used. For that I tried this query, but the result set is empty: mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME NOT IN ...
1
vote
2answers
637 views

ESCAPED BY clause in LOAD DATA INFILE

Can somebody please help me understand what is ESCAPED BY clause means in LOAD DATA LOCAL INFILE statement MySQL 5.5. I am a newbie to MySQL so please bear with me.
1
vote
2answers
65 views

need help in Query optimization

I need help in query optimization,I am having table with structure Create Table: CREATE TABLE `ip_country_mapping` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `cron` bigint(20) NOT NULL, ...
1
vote
2answers
58 views

Need some support on MySQL Query

I have a query SELECT leads.id, sfav.id is_favorite FROM leads INNER JOIN teams tst ON tst.team_set_id = leads.team_set_id INNER JOIN members members ON tst.team_id = ...
0
votes
0answers
21 views

mySql Event gets expired on closing of the command prompt

As I am quite new to server side, I tried to create a event using CREATE EVENT enetname ON SCHEDULE AT '2013-06-13 18:00:00'+INTERVAL 30 SECOND DO update tablname set status= 'Expired' where ...
0
votes
1answer
183 views

Export just rows modified in last month (date defined by user)

I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?
3
votes
3answers
1k 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 ...
-1
votes
1answer
186 views

Having too many connection problem

We have an magento store and having 9K products and 2 store views the trouble is we are getting too many conection error while the site is in normal loads. 1) We have 100 connectoin open in mysql. 2) ...
1
vote
0answers
92 views

MySQL Replication - Used to work, now get error 2026

I've had replication running now for about 2 years, with 1 master and 2 slaves. Both are through an SSL connection. I'm using MySQL 5.5.15 on the master (CentOS), and MySQL 5.14.46 on the slaves (on a ...
1
vote
1answer
134 views

Large table delete in mysql

I want to delete a table with 10,000,000 records. Due to some restrictions I can't do truncate on that table (I don't have the DROP privilege), I can think of two options: DELETE QUICK To set some ...
1
vote
2answers
383 views

Changing the mysql.proc table directly after upgrade

Our host upgraded our mysql install without us knowing from roughly 5.1 to 5.5. This caused a problem because the table definition of mysql.proc is different as discussed here: ...
1
vote
1answer
434 views

MySQL equivalent of WITH in oracle

I need the mysql equivalent of the WITH clause in Oracle. Please help. Thank You
1
vote
0answers
29 views

MySQL - Randomly long DESCRIBE queries

I have several queries like this (running on MySQL 5.5): DESCRIBE `TABLE`; And it randomly takes 5-10 seconds on my production servers. We have an ORM that caches this data once per day (per web ...
1
vote
2answers
365 views

MySQL+Web-App Performance Issues using different servers

We are having a performance issue with our MySQL servers that does not make any sense. I have read countless articles from many people (mostly Percona) and have made my.cnf tweaks. We have even manage ...