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
1answer
22 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
40 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
177 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
104 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
176 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
53 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
3answers
264 views

What happens when we take a timediff between timestamp and datetime

Say I have following table in mysql (5.5+): CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL, `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ...
1
vote
1answer
253 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
71 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
62 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
533 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
248 views

wordpress table is crashed and should be repaired

I run a wordprees website and use a managed VPS server with cPanel/WHM. Got bellow error log and check the table. I repaired the table with repair table command from phpmyadmin. But this problem occur ...
1
vote
1answer
258 views

Two different MYSQl instances on same machine?

I have MySQL 5.6 installed (Linux). /var/lib/mysql /etc/my.cnf I have a cold backup that I am recovering: /var/lib/mysql_backup /etc/mybackup.cnf In April I was able to get the backup started ...
1
vote
1answer
97 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
100 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
305 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
66 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
280 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
110 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
4k 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
245 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
455 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
472 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
66 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
200 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
515 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
640 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
104 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
183 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
145 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
231 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
200 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
2answers
5k views

MySQL Installation error: Unable to update security settings

I tried to configure MySQL server 5.5 on Windows and this is what I get mysql-server-5.5-winx64:56 - Attempting to update security settings. mysql-server-5.5-winx64:100 - Unable to update security ...
1
vote
1answer
101 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
1k 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
139 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
950 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
1k 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
162 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
143 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
302 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
299 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
644 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 ...
1
vote
1answer
617 views

MySQL Master-Slave Replication Cross Database

I am setting up Replication for cross Database as i have set 2 variables in Slave' s my.cnf as replicate-do-db=database_name and Replicate-wild-do-table=database_name.% ,But i am not able to get ...
1
vote
1answer
529 views

MySQL Replication Error, can't stop slave

When I run command SHOW SLAVE STATUS\G, I see Slave_IO_Running : 'Yes' Slave_SQL_Running : 'No' What I've usually done to fix this is to run STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; ...
1
vote
1answer
157 views

Get and count all blog posts and all users that fits a LIKE condition (two sepparated queries)

I've this tables: -- -- Table structure for table `default_cmvblog` -- CREATE TABLE IF NOT EXISTS `default_cmvblog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) COLLATE ...
1
vote
1answer
743 views

MySQL INSERTs waiting for UPDATEs (MyISAM)?

In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables. We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. ...
1
vote
1answer
120 views

Several values from different tables

I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest ...
1
vote
2answers
2k 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 ...