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
3answers
2k 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 ...
1
vote
1answer
235 views

mysql session logging tables

I've been building a session logging system for our sites. We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites. Our table ...
1
vote
1answer
559 views

`show procedure status` doesn't list all of the procedures?

mysql-server-5.5.28-1.el5.remi I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
1
vote
1answer
3k views

Some MySQL slave errors?

Master version: mysql-server-5.5.14-1.el5.remi Slave version: Percona-XtraDB-Cluster-server-5.5.24-23.6.340 Binlog format: ROW based I'm using PRM to setup HA for MySQL. Sometime, MySQL slave ...
1
vote
1answer
95 views

How we can enable index_condition_pushdown in MySQL..?

I have optimizer_switch variable as mysql> show variables like 'optimizer_switch'; +------------------+-------------------------------------------------------------------------------------------+ ...
1
vote
2answers
675 views

String comparison with integer is not working sometimes in MySQL 5.5

I have upgraded MySQL to version 5.5. Before upgrading when we used string comparison with integer, it was working. Code is "value = 1", here 'value' is declared as varchar(45) and its value can be ...
1
vote
1answer
2k views

Sudden MySQL error: Unable to connect to any of the MySQL Hosts

I am using MySQL Community Server 5.5.18 on Windows 7. My application was running absolutely fine without any troubles but since morning it is giving strange messages while opening reports. Reports ...
1
vote
1answer
4k views

Function returning “No data - zero rows fetched, selected, or processed” in MySQL 5.5

I have upgraded MySQL version to 5.5.16 After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed. In those functions, cursors are there, but ...
1
vote
1answer
19 views

Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE ...
1
vote
1answer
13 views

Migrating MyISAM tables from 5.5 => 5.6

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync? Is this safe? I know we need to do a full ...
1
vote
1answer
33 views

What's the complexity of renaming a column in MySql?

We want to rename a column in our MySql (version 5.5) database with something like this: ALTER TABLE t1 CHANGE a b INTEGER; Is this an O(N) (N=number of rows in the table) or an O(1) operation? I ...
1
vote
1answer
48 views

Generate INSERT statement from SELECT query

I have two databases, in first database I have a table called zoom_city and in the second database I have a table called courrier_city. The SQL for each is below: CREATE TABLE IF NOT EXISTS ...
1
vote
2answers
55 views

MySQL replication: slave not updated

This has been asked many times over but we really cannot get a simple Master-Slave Mysql (5.5.25) replication to work for our two dbs (FatTree and Torus). And no trace of error in the logs. The ...
1
vote
1answer
40 views

Can I disable an account in mysql after x failed login attempts?

The question is in the title. I would like to disable accounts after x failed login attempts. Is it possible? Thank you
1
vote
1answer
44 views

Poor query performance in MySQL

I have a 10 million record table in a MySQL 5.5 InnoDB. It's running in a 16GB RAM server with good CPU and fast HD. When I run the following query SELECT DISTINCT knowledge_id, ...
1
vote
1answer
88 views

How to use LOW_PRIORITY for heavy queries?

I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources. I do not want them to stop end users from ...
1
vote
1answer
55 views

Query result set

I've 1 query written in our legacy system. (using MySQL 5.5) Now, with growing data - the below mentioned query taking huge time. In our system, we've somewhat 2,000,000,000 (2 billion rows) approx ...
1
vote
1answer
42 views

MySQL geospatial query optimisation suggestions

I am new to DB Administration. Trying to sort out this query which is the heart of our application. I am searching google on similar questions, but would like an experts opinion on my particular ...
1
vote
1answer
272 views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
1
vote
1answer
74 views

How to prevent Cartesian joins when value can be in either field?

I have a table of articles where the same article may be presented to different audiences with slightly altered text depending on the user's access level. When this occurs, the article will have the ...
1
vote
1answer
78 views

Change normal user to admin in MySQL

I have set up a MySQL database. My problem now is I can't add new users with the user I have created. I try to grant access but I can't still add new users to my system. I use Workbench to add new ...
1
vote
2answers
182 views

How can I return column names in dot notation in MySQL?

I want to return column names into a table. But I also want to avoid redundancies like 'id' and 'id'. I did: INSERT INTO columns(name) SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE ...
1
vote
1answer
710 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 ...
1
vote
1answer
48 views

Create user for alternate port

I am running a private MySQL instance on WebFaction WebFaction is a shared host who provides both for their public MySQL instance and for your own private MySQL instance. The private instance is a ...
1
vote
2answers
164 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 ...
1
vote
1answer
95 views

Is there any query to fetch the a single row data in chunks?

The data stored in a row is just like "Phone Number:--- xxxxxxxxxxx Call Type:--- MISSED Call Date:--- 2013-06-13 Call duration in sec :--- 0 Phone Number:--- xxxxxxxxxxx Call Type:--- OUTGOING Call ...
1
vote
1answer
3k views

BEFORE INSERT trigger in MySQL

I am new to MySQL and I'm having problems creating a BEFORE INSERT trigger. I get an unexpected END error. I have a table named verlof_aanvragen with a column datum (and 6 more columns). What I ...
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
1answer
155 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 ...
1
vote
2answers
366 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 ...
1
vote
2answers
310 views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
1
vote
1answer
62 views

ORDER BY optimization issue

I have a query: SELECT user_details.id , user_details.first_name , user_details.last_name, user_accounts.name account_name, jtl0.account_id account_id, user_details.title , ...
1
vote
1answer
137 views

Unable to use mysql_upgrade:

I updated mysql recently and found problem related to privileges and backup. When I am using mysql_upgrade command it says- mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check ...
1
vote
1answer
365 views

Table corruption: How to perform Innodb Checksum checks in MySQL 5.5 for Windows?

Having a corrupted Mysql 5.5.31 (Windows) database, my question relates to the the top solution provided in How do you identify InnoDB table corruption? , more precisely to the following script that ...
1
vote
1answer
452 views

MySQL replication — issue with mysql.proc during replication from 5.0 to 5.5 host

We are replicating from a mysql 5.0.45 host to a 5.5.26 host over WAN. Every once in a while one of our databases does not accept a query and halts the replication. "'Column count of mysql.proc is ...
1
vote
1answer
93 views

Changing TEXT to VARCHAR

In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type text. Currently the longest entry has length 360 (found using SELECT MAX(LENGTH(value)) ...
1
vote
1answer
149 views

Unique index on 2 columns in mysql

I have one table in mysql named 'UserFriends' where I am updating my websites user's friends details. here is the schema of the table (UserFriends) id int, Userid int, friendid int, createdate ...
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
1answer
164 views

Create a mysql slave database from master database

I have a mysql master database running. I want to have a slave database. So, I stopped the master mysql database and copied it to the slave machine. I had bin log enabled on master so even those files ...
1
vote
2answers
138 views

Should master and slave have same mysql version?

I have a master database running mysql 5.5.11, and I am planning to setup a new slave. I am planning to use the 5.5.30 as it is the latest version. For setting up new slave, I am copying files from ...
1
vote
1answer
87 views

In Mysql 5.5, why is the upper bound of a character range not inclusive?

So I'm trying to query on a character range, i.e. all manufacturers with names starting with a or b: SELECT manufacturer FROM warehouse WHERE manufacturer >= 'a' AND manufacturer <= 'b' or: ...
1
vote
1answer
824 views

MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server. Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write. Currently we are running on the default ...
1
vote
1answer
107 views

Server load discrepency in Slave with Master load being low and constant

We have MySQL Replication Master/Slave Setup db01 Master db02 Slave (ReadOnly) We are getting load discrepency on the Slave (db02), but the Master's (db01) server load is low. MySQL is the only ...
1
vote
2answers
573 views

Trouble with triggers and insert .. on duplicate key update

I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
1
vote
1answer
902 views

Retrieve double data type value in non exponential form

Is it possible to retrieve non-exponential value in double data type column? After the width of the column is increased to 15 it shows the values in exponential form. Now I get the following: ...
1
vote
1answer
133 views

MySql - selecting into file and re-loading it with special characters

I have a MySql table called twitter_statuses which as the title suggests holds twitter statuses. here is the table structure. this table contains all sort of characters in the text column, and some ...
1
vote
1answer
124 views

MySQL Cluster Steps To Recover Data If Catstrophic Failure Occurs

I want to know if a catstrophic failure occurs (like a power outage), and all nodes in MySQL Cluster are shutdown in cluster then data will be lost even though a backup was taken? Or it possible that ...
1
vote
1answer
281 views

MySQL-5.5 InnoDB memory issue

Version in use is mysql-5.5.24. In the Enterprise version of MySQL innodb_buffer_pool space is released after a while, however this does not appear to be happening in the community version. I am ...
1
vote
1answer
254 views

Mysql 5.0 on Windows 2008

I have a problem with MySQL on Windows Server 2008 x64. This is the first time I've installed MySQL 5.5 with performance problems. I decided to go back to the older version that worked well with ...
1
vote
1answer
517 views

Does TRANSACTION/COMMIT/ROLLBACK only work on certain MySQL engines such as InnoDB?

Are transaction functions only relevant for particular database engines on MySQL, or are transactions supported under all MySQL engines, including MyISAM and InnoDB? someone says here that ...