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
6k 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
163 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
889 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
3k 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
6k 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
2answers
29 views

Why is restoration from binary logs so slow?

We have a MySQL 5.5 server with binary logging enabled and a backup script that collects the binary logs and, once a day, generates a complete database dump with mysqldump. In testing database ...
1
vote
2answers
56 views

Mysql causing high load average with large amount of server resources left

I've have 2 servers running a application and the server that runs the mysql sgbd is with the load average around 3 times more than the webserver. I think that is something wrong because i have a lot ...
1
vote
2answers
35 views

Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

We just migrated our web site database from a server running MySQL 5.5.11 to one running MariaDB 5.5.41. Everything is working mostly fine, except there's at least one query from our storefront that's ...
1
vote
1answer
27 views

phpMyAdmin and mysql not showing the same database

I have setup WAMP which I managed by using a tutorial and everything seems fine... I am logging into mysql as the root user with full privileges granted and I am able to create databases in mysql and ...
1
vote
1answer
21 views

Installed Percona-Toolkit, but no sign of xtrabackup

I've installed Percona-Toolkit on my Debian (Wheezy) test Server, using dpkg -i percona-toolkit.deb apt-get install --fix-missing -f dpkg -i percona-toolkit.deb However, there is no sign of ...
1
vote
2answers
46 views

Need to convert date to word using trigger

I have created a datetoword function in Mysql. Checked the function working fine. Now using this function need to convert the date from field1 (date-datetype) to field2 (Varchar-datatype) within the ...
1
vote
1answer
59 views

Can MySQL log lock waits?

Is it possible to have MySQL log queries or transactions where lock waits occur? One can debug specific application errors generated from lock wait timeout errors from the DB but without the ability ...
1
vote
1answer
44 views

Mysql Master-Slave replication catchup and bin_logs

I have set up MYSql Replication using 2x 128gig ram 24 core cloud servers. we have a massive live database that cannot experience down time due to the high volume of data input. I have found while ...
1
vote
2answers
165 views

DELIMITER issue in MySQL 5.6

Drop procedure if EXISTS rotatepjp; DELIMITER // CREATE PROCEDURE rotatepjp() BEGIN DECLARE u_id, settingval, pjp_userid, pjp_beatid, u_frequency INT; DECLARE u_pjpstartdate, pjp_fordate, ...
1
vote
2answers
128 views

One to many join, Sum from both table

I have two tables: orders and ordersdetail...(partial table) orders- id(primary key), amount, date( index) orderdeails- id(primary key), skunitid, quantity, order_id Each order has multiple order ...
1
vote
1answer
929 views

Why mysqldump is returning with error 1143: Couldn't execute 'show table status like

I ran the following command to create a mysqldump mysqldump -ubackup -psomething --single-transaction --quick somedatabase | gzip > 4_19.gz I got following error after 60% (estimating checking ...
1
vote
1answer
267 views

How to take physical backup of MYSQL database

I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone ...
1
vote
1answer
45 views

How to cache query for re-use in following UNION of queries?

I need to perform query, save the result and re-use it multiple times in one session (something like WITH in Oracle DB). This is the query I want to save: SELECT `event`.id, event.name FROM ...
1
vote
2answers
291 views

Identifying cause of high I/O

I'm trying to diagnose the bottleneck for high-write I/O activity on a percona master (with replication to a slave). The pattern of behaviour is roughly cyclic and regular: 60 mins of 75-90% ...
1
vote
1answer
177 views

Error 2003: Setting up MySQL Replication

Trying to setup MySQL Replication using this guide here : http://wpguru.co.uk/2013/05/how-to-setup-mysql-masterslave-replication-with-existing-data/ I was able to do this on two random VMs running the ...
1
vote
2answers
501 views

MySQL - grant all privileges on *.* brings back Access denied

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'somepassword'; Brings back error : ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) If ...
1
vote
1answer
94 views

Different column for different conditions when joining

I am using MySQL database and in that two tables, relation is one to many. One table is product table, sample data: +----+----------+ | id | name | +----+----------+ | 1 | product1 | | 2 | ...
1
vote
1answer
726 views

How to Reclaim Data Free in InnoDB Tables?

I came up with an idea on how to shrink ibdata1 in mysql database then i got this solution on stackoverflow STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql) STEP 02) Drop ...
1
vote
1answer
732 views

Master-Master Replication Duplicate Key

I have set two servers master-master replication mysql , i manage to set successfully but the problem is the duplicate key i got MASTER 1 Configuration /etc/my.cnf [mysqld] server-id=1 bind-address ...
1
vote
1answer
59 views

How can I make multiple joins on the same table?

I have a table (signal) which contains references to two other tables (ee and eeav). I want to perform an SQL Query which combines the data from ee and eeav tables. The situation is as follows: The ...
1
vote
1answer
96 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
1
vote
1answer
30 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
66 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
93 views

Immigrate to file-per-table mode in MySQL

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini ...
1
vote
1answer
780 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
1answer
196 views

Mysqldump --flush-logs results in small backup files?

New to MySQL and just learning how to setup a daily full backup (binary logs enabled). My goal is to run a daily full backup of the databases/schemas and reset the binary log. I'm currently using an ...
1
vote
2answers
294 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
64 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
499 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
1k 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
89 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
85 views

Installing MySQL in Solaris

I am trying to install MySQL 5.5 on Solaris 10. I executed the code useradd -r -g mysql mysql where a group called mysql is already created using groupadd command. I get the following error : UX: ...
1
vote
1answer
86 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
369 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
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 ...
1
vote
1answer
763 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
152 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
183 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
525 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
91 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
5answers
6k views

MySQL gives me:“Can't open and lock privilege tables: Table 'host' is read only”

I am facing problem restoring a MySQL database. My primary database was MySQL 5.1 and now I am trying to copy it to MySQL 5.5. The database was backed up by using Xtrabackup. I am using Ubuntu ...
1
vote
2answers
3k views

What is the difference between NULL and \N while loading data from a csv using load data local infile

I have a table which has four fields create table test_hc1(cola int,colb int,colc varchar(20),cold int); Now, I am trying to load following data 1,2,\N,4 and the third field in table is coming ...
1
vote
1answer
132 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
7k 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
4k 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'] == ...