MySQL version 5.5 - please also tag with mysql for search purposes.

learn more… | top users | synonyms

4
votes
1answer
291 views

Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 ...
0
votes
1answer
91 views

Slow query while selecting a higher offset from millions of rows

I have sort of security log files containing millions of rows. On my current testing I imported the data for one month which is the sum of ~14,000,000 records. The table contains several set of fields ...
0
votes
0answers
23 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
771 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
1answer
47 views

MySQL - Is it possible to assign permission to count the number of tables in a database without permissions on individual tables?

As part of a script I am trying to count the number of tables that exist in a particular MySql schema. Using my own user I am using the query SELECT COUNT(*) FROM information_schema.tables WHERE ...
1
vote
0answers
31 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 ...
5
votes
1answer
290 views

SELECT RELEASE_LOCK causing “Mysql has gone away”

During a long running process (90m+), our code calls SELECT RELEASE_LOCK('..');. However it always gives a "Mysql has gone away" error when we do this. We never got to see the return value of the ...
0
votes
1answer
153 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
67 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
2answers
129 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
103 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
333 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
61 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
299 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
82 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
106 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
157 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
73 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
218 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
129 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
91 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
129 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
224 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
107 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
61 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
1k 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
380 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
184 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
1k 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
1answer
203 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
337 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
833 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
58 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
421 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
620 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 ...
3
votes
1answer
827 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 ...
1
vote
1answer
534 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 ...
1
vote
1answer
108 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
10k 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
91 views

Hierarcy Query Mysql

I have a table like this... ========================================================================== UID || PARENT ID || SPONSOR ID ...
1
vote
0answers
57 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
1answer
52 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
139 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, ...
1
vote
1answer
42 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. ...
1
vote
2answers
279 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
173 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
227 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 ...
2
votes
1answer
3k 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
1k 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
219 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 ...