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
43 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
21 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
227 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
26 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 ...
0
votes
1answer
55 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 ...
0
votes
0answers
41 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
45 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. ...
0
votes
1answer
143 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
31 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
37 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
0
votes
0answers
82 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
71 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
599 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
57 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
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 ...
0
votes
1answer
118 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
35 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
1answer
47 views

Hierarcy Query Mysql

I have a table like this... ========================================================================== UID || PARENT ID || SPONSOR ID ...
0
votes
0answers
32 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
65 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
27 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
30 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
1answer
57 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 ...
0
votes
1answer
356 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 ...
0
votes
0answers
373 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
20 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
27 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
87 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 ...
0
votes
0answers
645 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 ...
0
votes
0answers
74 views

MySQL Crash due to “InnoDB Assertion” after Query

Current setup: Ubuntu 12.04 LTS MySQL 5.5.31 Current Use: Slave - Only ETL queries 64 GB RAM Intel(R) Xeon(R) CPU X5670 @ 2.93GHz - 12 Cores - Hyperthreaded (24 Logical) MySQL Main Settings: ...
0
votes
0answers
44 views

Last synchronization time between master and slave (replication)

During replication process where a Master is connected with slave. Some times connection between these two goes down. Any options to get the details of the last synchronization time?
0
votes
0answers
189 views

MySQL “ERROR 1005 (HY000): Can't create table 'foo' (errno: 13)”

Windows Server 2008 MySQL 5.5.39 using the root user with full privileges granted to the DB. I know errno: 13 is a permissions issue. I've seen several answers to this for linux where setting ...
0
votes
1answer
104 views

mysql autoincrement counter

I need a way to keep auto-increment counter between mysql service restart. As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
0
votes
0answers
477 views

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

CREATE TABLE approvalreasonexemption ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `personId` INT(10) UNSIGNED NOT NULL, `reasonId` INT(10) UNSIGNED NOT NULL, `createdBy` INT(10) ...
0
votes
1answer
2k views

Executing try catch block in mysql stored procedure

In my mysql stored procedure I want try catch block but it gives error in my mysql stored procedure . I want my select query to be in try block and if it executes correctly I want other query to ...
0
votes
1answer
89 views

Myloader command: Table doesn't exist

I took the backup of mysql database using mydumper command: mydumper -B mydb -o /backup/oct3 -s 500000 -c -m -S /tmp/mysql.sock -u root -p password@123 -v 3 Now, I am restoring mysql database ...
0
votes
1answer
53 views

Mysql remove binary log

Hello everyone we got situation over here. So, our mysql server is running out disk, after some investigation we found out that our mysql binary file have eat too many space. In order to purge the ...
0
votes
1answer
12 views

MYSQL variable in function

Can anybody tell why the value of @URlID is always -1 even when i know the query will return a result? I have tried any possible type of variables and any possible way of inserting into the variable ...
0
votes
0answers
633 views

MySQL server: Unable to connect with root because of skip-name-resolve option

We had our MySQL server on an old server. We decided to move all of the databases to our new server. Old MySQL Server engine 5.5.31 New MySQL Server engine 5.5.38 We created one general dump file ...
0
votes
0answers
62 views

Best upgrade path for MySql 4.1 to 5.5?

Background Our production environment runs on a system that nobody wanted to update for a long period of time. With a new sherrif in town, we're going to update it. Production is MySql 4.1 with ...
0
votes
0answers
9 views

Why pages not made young is zero?

In results of Show INNODB Status showed me very weird result: Pages made young 81821279, not young 0 The official statement says: Pages made young, not young: The number of old pages that were ...
0
votes
1answer
285 views

Change MAMP to use a lower Mysql 5.1 version

I need to have the same development environment for my PHP program. My clients test server is running on Mysql 5.1 while my local is using MAMP running Mysql 5.5. How do downgrade my mysql database ...
0
votes
1answer
96 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
0answers
78 views

Soundex on a Large Table

I had a table of names, containing 200,000 rows. This has now been updated to include over 11 million rows. The table is queried using a SOUNDS LIKE 'str' and the result is then passed through ...
0
votes
0answers
22 views

Unaccounted for MySQL Processes

I am running a MySQL 5.5.38 on a VM running Ubuntu server 12.04. At certain times of the day around 12 mid day, CPU Utilization and loads spikes to over 90% with the MySQLD Process accounting for most ...
0
votes
0answers
51 views

Mysql server stops responding quickly after +- 200 connections

We have a mysql instance, 5.5.28. Its working as intended, it has during normal operations perhaps around 200 to 300 connections on it. These connections are usually "idle" waiting for something to ...
0
votes
0answers
31 views

(libmysql): (filename not available): mysql_query

Hi everybody im running a game server connected to mysql and it crashes only because of mysql connection with the error in title.. when i dont use mysql and save data as files on harddrive it dont ...
0
votes
1answer
33 views

Simple Query optimization using ORDER BY

The following is a simple query that is taking more than 10 seconds to execute: select * from inventory.asset ass where ass.product_id=83 and ass.expiry_date > now() and ass.status=1 order by ...
0
votes
0answers
108 views

problem with localhost or path on xampp instalation

I have xampp installed on my E: drive, windows 7 system the control panel works fine, both apache and mysql start normally ports for apache are 81 and 444 port for mysql is 3306 My wordpress ...
0
votes
0answers
517 views

Moving datadir OK start, but socket error for mysql client

I'm trying to move my MySQL datadir from /var/lib/mysql over to /home/mysql (an ssd drive as I'm having iowait issues on /var/lib/mysql when MySQL is running on that drive). The OS is CentOS 5.6 ...