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

learn more… | top users | synonyms

0
votes
0answers
68 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 ...
2
votes
4answers
847 views

Getting 0KB of size file while backup mysql database using mysql dump [closed]

I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions? $dbhost = 'localhost'; $dbuser = ...
10
votes
2answers
147 views

Find total duration of each consecutive series of rows

MySQL Version The code will run in MySQL 5.5 Background I have a table like the following one CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT , patient_id INT NOT NULL , bed_id INT NOT NULL , ...
0
votes
0answers
184 views

Are there any caveats to installing MySQL 5.5 on CentOS 5?

I need to install MySQL 5.5 in CentOS 5. As CentOS 5 is a little bit old is there any odd behavior when you want to install MySQL 5.5 on CentOS 5?
2
votes
1answer
68 views

How can I determine which Innodb table is being written?

I am running Mysql 5.5. I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The ...
-1
votes
2answers
94 views

MYSQL: Join with many to many

I'm trying to create a view and trying to see what can be done, I have a main table a table I want to join to and also a junction table. Event table (it's very big so it's just dummy data): ...
5
votes
2answers
119 views

Is there a (non-binary) MySQL collation that doesn't treat different mathematical symbols as the same character?

I've run into a real headache with MySQL's collations and non-BMP characters (ones with Unicode codepoints above U+FFFF). Basically, given a table and data like: CREATE TABLE `math` ( `id` int(11) ...
0
votes
0answers
62 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
1answer
42 views

Unique constraint for new values only

I have a large table with a column that contains duplicates. I want to add a unique constraint on this column for future entries only (not the old data). Is this possible?
2
votes
1answer
91 views

MySQL - Get Minimum Value, Data From Multiple Tables

Good evening all, first post on the DBA forums. I am having real issues working out how to accomplish a seemingly simple task within MySQL as a model for PHP code later on. I am not exactly a MySQL ...
0
votes
0answers
543 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
1answer
70 views

MySQL Workbench - “deploying” schema into a real database

a noob here, finding my way around. So, how can I "transfer, deploy or whatever the right term is" the database tables, fields, etc. that I have created with Workbench into a real database? The dB I ...
0
votes
1answer
43 views

import text file getting command is not allowed with this MySQL version

here is my table CREATE TABLE cities ( country CHAR(2), city_ascii VARCHAR(100), city VARCHAR(255), region CHAR(2), population INT UNSIGNED, ...
0
votes
2answers
217 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
69 views

Is it possble to replicate MySQL server on Azure virtual machines

I have two Windows Server 2012 R2 virtual machines on Azure. I need to replicate the database on each of the windows servers. I have followed this link but I am not getting the connection between the ...
1
vote
0answers
268 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 ...
0
votes
0answers
45 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
1answer
46 views

Hierarcy Query Mysql

I have a table like this... ========================================================================== UID || PARENT ID || SPONSOR ID ...
1
vote
3answers
127 views

mysql replication master 5.5 slave 5.1 error on create database

I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we ...
0
votes
1answer
89 views

When try to do Mysqldump Backup from console then getting such as error [closed]

mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect when i am tring to do MySQLDump backup from console then getting such as above error. I am ...
2
votes
1answer
68 views

View last several innodb deadlocks

I see that I can view the latest deadlock in mysql/innodb, but is there a way to view past deadlocks? We have two deadlock issues, one being important and the other not. The less important deadlock ...
0
votes
0answers
30 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?
1
vote
1answer
43 views

Finding out the exact size of the database after inserting records?

I have this query: SELECT table_schema "db.name" , Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; Does this ...
1
vote
0answers
18 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 ...
0
votes
0answers
27 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
55 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
26 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 ...
1
vote
1answer
34 views

Queries that ran, and the number of times they ran

I want to profile a running web application in dev on a mysql 5.5 database. The web application uses hibernate and I am able to see that a rather large number of queries are run against the database. ...
0
votes
0answers
23 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 ...
1
vote
2answers
108 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
61 views

MySQL Latest Deadlock Explanation

Can someone explain this deadlock to me? It seems as if there are 2 inserts happening within milliseconds of each other with the same Primary Key. I'm not sure how this can cause a deadlock. At most a ...
0
votes
1answer
51 views

SQL INSERT and UPDATE queries run far to slow

I have two queries that run far too slow and I have no idea why. Both tables contain about 500000 rows. Each call needs ~20 minutes and the they have a really high CPU usage (~90% on Mac). I have ...
1
vote
1answer
714 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
4answers
110 views

How to backup live mysql database?

I have 12 GB MySQL InnoDB Database with around 800 queries per second. No sooner I start 'mysqldump' over it to make backup I start getting '503' on my website. How do I make backup of my live ...
-2
votes
1answer
13k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
0
votes
2answers
69 views

Mysql replication from existing slave to new slave

I'm hoping you experts can help advise me, I've got this topology: Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB Backup server is a slave of Live DB, and Test DB is a slave of ...
0
votes
0answers
316 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) ...
3
votes
2answers
114 views

Why wouldnt Mysql use index on the Primary query in a nested query

I have two tables: search_criteria and pricing. There is an index on search_id column in search_criteria table and on pricing_id column in pricing table. But running this nested query does not use ...
1
vote
0answers
189 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. ...
0
votes
0answers
19 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
1answer
76 views

Adding a primary key to an existing table in mysql is taking time

22 hours ago I started a script that executes an alter table to add a primary key to a table which has 225 millions of records. MSISDN is the only column. In showproceslist its process status is ...
1
vote
2answers
276 views

avoiding unique key collisions across tables

This might be related to the idea of data warehousing, but i'm not sure, though it is extreme theory. We have a table where records get written to first, let's call it tbl_recording which looks like: ...
2
votes
3answers
1k views

how to install mysql on mac?

My Mac runs Mac OS X, version 10.7.5., so it's a 64 bit system. First, I downloaded mysql-5.5.28-osx10.6-x86_64.dmg. Then I double clicked mysql-5.5.28-osx10.6-x86_64.dmg, it's mounted, and shows ...
2
votes
1answer
130 views

How much space is needed to reclaim space from a table in INNODB

I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the ...
2
votes
3answers
240 views

Bulk Delete for Large Table in MySQL

I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month. If I do DELETE FROM NOTIFICATION WHERE CreatedAt ...
0
votes
0answers
81 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 ...
1
vote
1answer
25 views

Failing to get multiple values using join

I have below scenario: create table t1 (id int, title varchar(20), description varchar(20)); create table t2 (id int, info1 varchar(20), info2 varchar(20)); create table t3 (id1 int ...
-1
votes
1answer
238 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
1
vote
0answers
300 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 ...
0
votes
0answers
480 views

Following mysqltuner suggestions: query_cache_size and join_buffer_size

So I am following the suggestions of mysqltuner, and so far I've had great results, the server is performing well (dedicated VPS running a large Magento eCommerce store) There is only two issues left ...