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
1answer
45 views

How to only display a variable in PHP inside a MySQL query when it has a content or when a condition is true?

How do I only display $mon or any variable when it has content? whenever i use this syntax and for example, monday must contain the number 1 and tuesday would contain number 2 and the other ones have ...
1
vote
2answers
45 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 ...
0
votes
0answers
28 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
1answer
41 views

Large query with subquery runs indefinitely after mysql update

We recently did a big server update, in the process migrating from mysql-5.0 to 5.5.40 Several of the queries, which had been running well on the old server, now grind everything to a screeching halt. ...
1
vote
1answer
53 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 ...
0
votes
1answer
134 views

MySQL always writes simple temporary/memory tables to disk (both tmp_table_size and max_heap_table_size are set to 8GB)

I'm struggling with this issue and just can't figure out why this is happening. CREATE TABLE sitecache._test ENGINE=MEMORY SELECT * FROM website.gallery_likes WHERE 1=0; Every time I execute this ...
0
votes
0answers
28 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
1answer
64 views

Add linked server from SQL-Server to MySQL: “Data source name not found” (Error:7303)

I'm trying to link my SQL-Server 2005 on Windows Server 2008 R2 with MySQL 5.5.40 on a different computer. I have followed this guide: ...
1
vote
1answer
34 views

Purge mysql log bins older than two weeks automatically

I have to manually write purge binary logs bin to "mysql-bin.somenumbers" ; Once in a while, I need to reduce the spaces that binlogs are taking on my web server. Is it possible to purge binary ...
1
vote
1answer
46 views

MySQL Data Files Missing?

We are upgrading our website and I was checking on the size of the new database compared to the old one and was really surprised to find that in the /var/lib/mysql/ directory, the only files there ...
1
vote
1answer
34 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 ...
0
votes
1answer
27 views

how to overcome these type of issues where null value inserted into primary key not null column in table

how to overcome these type of issues where null value inserted into primary key not null column in table
1
vote
1answer
93 views

Why does this ALTER TABLE ADD FOREIGN KEY query create two foreign key constraints

I am attempting to add a foreign key constraint to existing tables via an ALTER TABLE statement: CREATE TABLE `blog` ( `blogid` int(11) NOT NULL AUTO_INCREMENT, `deptid` int(11) NOT NULL DEFAULT ...
0
votes
0answers
69 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 ...
1
vote
1answer
84 views

Why are tiny Innodb row updates causing so much disk IO?

This is a followup question to my earlier How can I determine which Innodb table is being written? Using the accepted answer from the above-referenced question I was able to determine which (two) of ...
2
votes
1answer
69 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 ...
10
votes
2answers
148 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
64 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?
0
votes
0answers
551 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
83 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 ...
5
votes
2answers
122 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) ...
1
vote
2answers
138 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, ...
0
votes
1answer
45 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
1answer
74 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
281 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
46 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 ...
2
votes
1answer
100 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 ...
2
votes
3answers
139 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 ...
2
votes
1answer
74 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
1answer
94 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 ...
0
votes
0answers
31 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
1answer
1k views

MYSQL query to find records between two date range

I am working in a web application using PHP and MySQL. I have two tables Employee employee_id email_id 3 tl1@jasmine.com 4 agent1@jasmine.com 5 ...
0
votes
1answer
46 views

Hierarcy Query Mysql

I have a table like this... ========================================================================== UID || PARENT ID || SPONSOR ID ...
1
vote
0answers
19 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
30 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
59 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
27 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
116 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
71 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
52 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
771 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 ...
0
votes
1answer
296 views

Many mysql queries stuck at sending data

I am using opencart and I can se many queries blocking the mysql server. All queries are the same except the p.manufacturer_id. Attached there is the query and the explain of the query. The query is ...
1
vote
4answers
118 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 ...
0
votes
0answers
325 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
116 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
192 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. ...