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

learn more… | top users | synonyms

3
votes
0answers
384 views

Convert Oracle Query for Date to Mysql queries

Need to convert the following oracle queries to MYSQL queries. 1) select to_char(to_date(?,'DD-MM-YY')) from dual; 2) select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY')) from dual; 3) ...
2
votes
0answers
87 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

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
0answers
27 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
19 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
41 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
236 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
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
0answers
317 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
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 ...
1
vote
0answers
347 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
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
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
0answers
114 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
91 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
603 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
40 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
384 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
90 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
75 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
81 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
166 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
187 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
13 views

#2002 Cannot log in to the MySQL server / Connection for controluser as defined in your configuration failed

I use PHP/MySQL in Ubuntu 13. To reduce the size of ibdata1, i followed this post What is the best way to reduce the size of ibdata in mysql? and make necessary changes as told in the answer. I did ...
0
votes
0answers
12 views

How to acheive Concurrency in mysql when there is 500+ request simultaneously to the server

I am using Mysql (InnoDb), in this I am facing problem when we checked the curl request upto 500 connection at a time (simultaneously) but when we try more than that then my servers goes to idle ...
0
votes
0answers
34 views

MySQL: How to calculate potential wait time?

I'm trying understand the relationship between WAIT_MS and COUNT_STAR. Is this saying the average wait time is 3.2s and occurred 11,543 times for a total of approximately 10.5hrs of aggregate wait ...
0
votes
0answers
23 views

How to update Status using IF Condition in Mysql trigger

I'm trying to write an update trigger that will Design_Update_Status to Customized when a date is entered in Custom_Design_Update_Date field and if nothing is entered that field it should update ...
0
votes
0answers
14 views

Dynamic chart data from MySQL

I'm building a chart with JS, where i'm doing a query with PHP and pass it to chart in JSON format. The problem is the whole table exported in json file gets up to 10mb, and it takes too much time ...
0
votes
0answers
7 views

MySQL: Archival How to?

I would like to know the correct Archival Strategy for my DB Schema. We are using Mysql 5.5. Here is the full scenario: We have around 35 tables in DB out of which 10 would be transactional in ...
0
votes
0answers
20 views

On replicating all tables of a given database except for those starting with some pattern

I'd like a slave to replicate all tables of a given database except for those starting with some pattern, eg. those with names starting with 'nc' (non-critical, redundant denormalized tables). So I ...
0
votes
0answers
24 views

Request for Help Optimizing a Query in MySQL

I have a query that is being used to report on the number of times a trigger in Zabbix, was fired by an event. For example, an event (not enough disk space) fires a trigger that sends an email to ...
0
votes
0answers
37 views

InnoDB crashes frequently

My Innodb Engine is getting crashed frequently. I could not find the reason behind this. This is the error log: 140129 9:22:49 [Note] Plugin 'FEDERATED' is disabled. 140129 9:22:49 InnoDB: The ...
0
votes
0answers
30 views

Is this a normal query time for a left outer join type?

I have Employee Table - 80K records about 100 fields I have an app table - 8K records and 30 fields Then a subset of employee table also stored as table that has our divisions subset - call this ...
0
votes
0answers
26 views

Caching data with MySQL and asp.net 2.0

I want to cache frequently used data. Is there simplest way to do this with asp.net itself? I am using framework 2.0. If i do this with MySQL technique how about innodb?? will this allow me to cache ...
0
votes
0answers
31 views

Mysql query getting hung!

I am using mysql 5.5. I create following temporary table: CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s ( `count` int(10) NOT NULL, `m_class` enum('SMS','EMAIL') NOT NULL, ...
0
votes
0answers
16 views

How do I keep old employees from LDAP data?

I have an LDAP feed that I run nightly and it makes several tables that run a lot of our internal websites. I simply get about 40 fields times 80K employees. The tables get overridden every night ...
0
votes
0answers
274 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 --- ...
0
votes
0answers
12 views

Performing a dump breaks replication

I have a master/slave setup in our production environment (MariaDB). I've found that when a dump is done on a schema that is heavily used, it causes the replication between the master & slave to ...
0
votes
0answers
32 views

What happens when an SSH session is terminated while a query is running?

I have a huge database on a dedicated server, whenever trying to modify the db I use PUTTY to establish an SSH connection and then do the work. Yesterday I started an INDEX query on an innodb table ...
0
votes
0answers
35 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...
0
votes
0answers
24 views

Query for multi-table search with concatenated fields

Thank you in advance for bearing with me, as I am new to database design and implementation. The Setup: I have a MySQL 5.5 InnoDB database with several tables, 4 of which are relevant to the problem; ...
0
votes
0answers
56 views

Cannot find /etc/init.d file in opensolaris

While installing MySQL , I noticed a point, I mean to start the MySQL server manually , use the command cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql The error shown is : cp: ...
0
votes
0answers
35 views

I can't get MySQL server to stop running

The output from MySQL Workbench when I try to stop it is: Executing '/usr/local/mysql/support-files/mysql.server stop' 2013-12-23 21:08:29 - Stop server: ERROR! MySQL server PID file could not ...
0
votes
0answers
76 views

MySQL Load Avg High — CPU bound or IO Bound or Memory Issues

We are using MySQL 5.5 and the database is approximately 10TB. The load avg is constantly high during day time. Following are the configurations and a snap shot of a 'top' command. I wanted ...
0
votes
0answers
159 views

What is the ideal connection pool size for RDS MYSQL?

I have a tomcat server hosting REST APIs. It is connected to a MySQL (RDS Instance) server on the backend. A majority of the operations are write operations (Around 75%). I'm using the Tomcat's ...
0
votes
0answers
34 views

Is there any way to set server-wide and schema-wide default row formats for InnoDB tables in MySQL?

Is there a way to set server-wide and database-wide default row formats? Currently it seems that I must specify ROW_FORMAT in CREATE TABLE or ALTER TABLE statements. Am I reading the documentation ...
0
votes
0answers
69 views

How to set up a pivot table view?

I have three tables: Entry Manager Employee The Entry table has as date field and a bunch of other fields about the entry including the emp_id. Manager Table has columns: Manager_id ...
0
votes
0answers
20 views

Anyone know the fix for an older innodb inspection query

This post talked about an information_schema query to give you a nice report of how much percentage wise indexes are fitting into the buffer pool The query looked like > SELECT `schema` ...
0
votes
0answers
77 views

how to update current row value by selecting previous row value in php mysql

How can i update data of current row with selecting data of previous row in php mysql. I have tried following query but it showing me error. update sales_tracking set sales_tracking.opening_units = ( ...
0
votes
0answers
34 views

How to move partitions between filesystems

We have ~9TB MySQL database which consists from a few tables which are partitioned by day. Sum of daily information is ~20GB. Database is practically write only. There are at most tens of queries ...