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
0answers
44 views

Emulating WITH clause in MySQL/query perfomance review

I have never really worked with MySQL queries till this last week. I was tasked with making a simple tool for my department that tracks contacts. Here are the 2 tables and the type of data they ...
2
votes
0answers
3k views

Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example: $ mysql -u root -p --column-type-info Enter password: --- Copyright and help message snipped for brevity --- ...
2
votes
0answers
97 views

Understanding Indexes and using them better

I've been spending some time trying to optimise our MySQL queries that we use which essentially joins one table with 2 million rows onto another which has 1-2 million. I know MySQL is able to easily ...
1
vote
0answers
17 views

Does creating an empty table in MySql have any effect(downtime) on production?

Disclaimer: I am, in all probability, paranoid about pushing a patch on a production server. The patch alters the schema, by creating a new table (which is empty), and changes some stored procedures ...
1
vote
0answers
113 views

Server version (5.5.40-MariaDB) does not match with the version of the server (10.0.15-MariaDB) with which this program was built/distributed

I just upgrade MariaDB (Mysql) 5.5.40 to MariaDB 10.0.15 by following this step by step. I have failed to enable the mysql and also mysql_upgrade with the following errors mysql_upgrade -u root -p ...
1
vote
0answers
10 views

Can MySQL Index Merge an Composite index with a single column index?

I am tuning a query and currently have an index as following : Index 1 : (col1, col2) An example query would be : SELECT * FROM t1 WHERE col1 = '1' AND col3 = '2'; I have created an index on ...
1
vote
0answers
153 views

How do I recover from MySQL InnoDB table/index corruption?

While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible. ...
1
vote
0answers
230 views

Lost Connection to mysql server at 'reading authorization packet', system error 0"

OK, this has been bugging me all afternoon now, I have a new MySQL Database '5.5.40-0+wheezy1-log' running on Debian Linux. It is to all extents and purposes a clean install. I have created two ...
1
vote
0answers
36 views

pt-online-schema-change with circular replication

So I have an alter table (adding 4 columns) to do on a fairly large table (74 million rows). The problem is this table has firstly to remain online and writable during the whole operation and ...
1
vote
0answers
440 views

What to do with unused RAM on a MySQL database server?

For years I have been running dedicated servers with more or less limited ressources, resulting in unsatisfying performance of the web application (Drupal). Tools like Matthew Montgomery's MySQL ...
1
vote
0answers
70 views

Refactoring IN Subquery, Avoiding Query in a Loop

I've got a table which was designed as both the current data set for participants, as well as being the historical changeset for those participants. This table has been in use for approximately three ...
1
vote
0answers
88 views

MySQL query with order by decreased performance

Here is my schema: CREATE TABLE `UserMst` ( `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `EMailID` varchar(50) DEFAULT NULL, `UserName` varchar(20) DEFAULT NULL, `FullName` ...
1
vote
0answers
57 views

crossplatform mysql_install_db script in mysql 5.5.34 on Windows

I downloaded mysql-5.5.34-win32.zip and found out there is mysql_install_db.pl script. However mysql 5.5 documentation says: mysql_install_db is a shell script and is available only on Unix ...
1
vote
0answers
241 views

Upgrading from MySQL 5.5 to 5.6 - timestamp issue?

I'm looking at upgrading my Percona Server setup from 5.5 to 5.6. Reading the upgrade docs from MySQL sounds mostly straightforward except for anything involving time/date columns. As far as I can ...
1
vote
0answers
756 views

Fastest way to perform simple operations on 1 million rows

I have a simple table with more than 1 million rows in an InnoDB MySQL table. Here are the steps that I need to perform on each row: SELECT * FROM tbl where work_done=0 make some data processings ...
1
vote
0answers
34 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
0answers
548 views

Incorrect information in .frm file after a power outage?

CentOS release 6.3 (Final) x86_64 mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1 SHOW SLAVE STATUS\G: Slave_IO_Running: Yes Slave_SQL_Running: No ...
1
vote
0answers
534 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
0answers
22 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 ...
1
vote
0answers
530 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", ...
1
vote
0answers
128 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
0answers
49 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
0answers
181 views

innobackupex: What causes Slave_open_temp_tables did not become zero after waiting 15 minutes?

As you might know, in order to assure a consistent replication state, the slave should be backup only when there are no open temporary tables: ...
1
vote
0answers
159 views

Error in alter table reorganize partition for MySQL Cluster

My cluster contains 48 million rows and cluster contains 4 storage nodes . After that I add online storage node and executed alter table 'customer_tbl' reorganize partition. Some parameters of ...
1
vote
0answers
1k views

“Lost connection to MySQL server during query” error

I have MySQL 5.5.20 and this table: mysql> desc country; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
0answers
42 views

Is there a canonical list of MySQL defaults which should be changed

I'm setting up a new mysql server as we're building a new app at work. Most of the developers/operations guys are familiar with mysql. Many of mysql's settings (eg strict mode) are set badly by ...
1
vote
0answers
597 views

MySQL cluster stuck in start phase 4, how to proceed?

Over the weekend our cluster we set up for evaluation had one of its data nodes lose network connectivity briefly. This was while another data node was in the middle of a restart (which had been going ...
1
vote
0answers
110 views

How to create multi volume file backup using symantec backup.exec

I am using symantec backup .exec for backup and restore in mysql server . Now I am in need to take larger database backup in tape .But it exeeds the tape storage and I am trying to create a multi ...
1
vote
0answers
91 views

How to profile individual queries using PERFORMANCE_SCHEMA

I've been looking at SHOW PROFILES, which seems ideal for what I am doing. However, I've noticed a warning that SHOW PROFILES is being deprecated and that PERFORMANCE_SCHEMA should be used instead. ...
1
vote
0answers
88 views

MySQL replication backup

How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu. What happens if, I issue or schedule ...
1
vote
0answers
190 views

How to speed up this query

MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200. That way innodb doesn't have to compute ...
1
vote
0answers
252 views

mysql server is not sending result set back after query execution to client and staying in sending data state

When I pass a query from my Java application to mysql server, it stays in Sending Data state and not returning result set back to application. When I execute the same query in SQLYog directly, it ...
0
votes
0answers
16 views

Alternative to using subqueries

Anyone please help me to optimize this query SELECT COUNT(csd.search_id) AS searches,count(distinct cd.cust_phone) as unique_searches, (SELECT COUNT(csd.product_type) FROM ...
0
votes
0answers
28 views

Centos 5.11 mysql-server 5.0.95 upgrade to mysql55w-server

Here's my thought process on my upgrade: mysqldump --all-databases > backup.sql service mysqld stop yum remove mysql mysql-server yum install mysql55w mysql55w-server service mysqld start ...
0
votes
0answers
16 views

Different Auto-increment values Mysql schema Replication

I have 6 Slave set up (EC-2, linux) and a Master ; 2 schemas getting replicated. Version info: mysql Ver 14.14 Distrib 5.5.34, for Linux (x86_64) using readline 5.1 Once in few months in one of the ...
0
votes
0answers
15 views

Use join to aggregate data from various normalized tables

I have a database as follows: tbl_clients (Primary Key: client_id, Foreign Keys:place_id,district_id,zone_id,country_id) tbl_places (Primary Key: place_id) tbl_districts (Primary key: district_id) ...
0
votes
0answers
21 views

MySQL: querying json

I have json text inside a MySQL table : [{"key_fiyat":"100.00"},{"key_fiyat_primi":"USD"},{"key_il":"33"}] now I want to select ... from table where key_il = 33 How can I do it in php ?
0
votes
0answers
42 views

MySQL Dynamic columns duplicate value workaround

As the title suggests i have the following code which transposes certain row values into field names, however the problem im having is that the values "closed" & "reopened" may appear in several ...
0
votes
0answers
47 views

How to use a subquery that returns multiple rows?

I am getting Error 1242 : Subquery returns more than 1 row. Basically, I have to do this : select data from my database based on a time criterion (between 2 dates) and then apply a filter on the ...
0
votes
0answers
171 views

Combine and display multiple row data with same column data in MySQL

I have a menu, from where every item ordered is stored in a row. For a single order, there can be multiple rows.What I want is that when I display it, for same order_number ( number) all rows data is ...
0
votes
0answers
39 views

Access Denied to users after upgrading mysql to 5.5.40

I recently upgraded mysql from 5.1.0 to 5.5.40 but suddenly some users seem to have lost the previlages. All the projects are live and those with issues are showing database connection error. How can ...
0
votes
0answers
22 views

MySQL JDBC Driver puts transaction in read only. Why?

Using the MySQL JDBC connector with a MySQL 5.6.22 data source defaults the transaction mode to read only. Anyone know why?
0
votes
0answers
23 views

Mysql Master - Master Replication

i have established a master A and master B replication between two servers.Both are getting replicated.But it is limited with only one database. My questions 1.How to create a new database in ...
0
votes
0answers
36 views

Slow Query Performance - Can't Debug

I have a MySQL query that I am trying to debug the performance issue but cannot. I know that the majority of the query is reading/processing rows (Sending Data state). However, in the explain output, ...
0
votes
0answers
21 views

Oddness with MySQL with InnoDB - CPU capped at 50%

I have MySQL 5.5.22 with InnoDB, running on multiple c3.8xlarge EC2 instances. Which is to say, the VMs are set up with 32 CPUs & 59 GB RAM There are no CPU controls in MySQL, right? Then why ...
0
votes
0answers
16 views

unable to add data source on idashboard admin

I am in the process of adding data source on iDashboard , Data Source section. So, for that I downloaded the driver from here which is mysql-connector-java-5.0.8-bin-jar and currently trying to enter ...
0
votes
0answers
189 views

How to solve MySql Slave Error 1452 Foreign key constraint fails?

I am site administrator. We use to have a mysql 5.5.40 master(db01) - slave(db02) setup. One day our master server (db01) got down due to hardware failure. So we switched our site to slave (db02) ...
0
votes
0answers
15 views

Trying to create a trigger to fill two columns (MySQL)

I am trying to create a trigger to update columns on a table when a new record is added to another table. This is the code I am using CREATE TRIGGER `hire` AFTER INSERT ON `hire` FOR EACH ROW BEGIN ...
0
votes
0answers
16 views

Daemon Waiting for activation in mysql event

When I do, SHOW PROCESSLIST; on mysql, for my event, I see the following result: Q1 )Why does it shows Waiting for next activation all the time? Q2) At times my scheduler turns off which causes my ...
0
votes
0answers
70 views

Configuration help my.cnf Mysql

Is the following my.cnf file OK for a dedicated server with 56GB of RAM (on ubuntu server and 8 processors)? I'm running just one website on the server and there is a high demand on my cpu from ...