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

learn more… | top users | synonyms

1
vote
2answers
211 views

Using MySQL/PHP PDO, is there a 'simulate' or dry run option?

Does MySQL (or, ideally, PHP PDO) have some way of simulating transactions without actually committing them? (Can I get MySQL or PDO to act as if data is inserted/deleted/updated, without actually ...
1
vote
1answer
247 views

Group by age range and gender with aggregation function on some columns

In Mysql, I have a patient table with id, birth_year and gender. Next table is transactions in which I have id, patient_id [FK from patient] and checkup_date. I need to select rows such that I get ...
1
vote
2answers
60 views

MySQL: How to sync on demand beetwen two database in the same server

I'll like to know if it's possible to have a systeme on synchronisation beetween two database in the same server. I have a production database in my server , i want a dev database (same database with ...
1
vote
3answers
242 views

How to get “mysqlbinlog” utility for Ubuntu?

I can't find "mysqlbinlog" utility in my Ubuntu server, i can see mysql, mysqldump, mysqlshow etc... command line utilities but not mysqlbinlog utility. Can someone please help me from where I can ...
1
vote
1answer
185 views

unable to import the file

Gone through the below link also but i am unable to import CSV file in mysql. Even i had tried by using > LOAD DATA LOCAL INFILE but same error persisting. Please help me in this. queries i tried had ...
1
vote
2answers
125 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
1
vote
1answer
154 views

Safely changing innodb_buffer_pool_size on MySQL replication master?

We have a MySQL server which is a replication master. We need to change the innodb_buffer_pool_size and innodb_flush_method to (hopefully) improve performance as the database has grown. What is the ...
1
vote
1answer
127 views

database design for dynamic menu bar [closed]

I am trying to create a menu bar which will load dynamically on user log in, based on the user roles. Say if the user is an admin he can see all the items listed under the header and if a user is a ...
1
vote
1answer
80 views

Migrating MyISAM tables from 5.5 => 5.6

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync? Is this safe? I know we need to do a full ...
1
vote
1answer
126 views

How to store temp tables in memory during table “optimize”?

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to ...
1
vote
1answer
123 views

What is the difference between uniqe index/unique/unique key/unique constraint keywords?

I am using mysql 5.5.16 What is the difference between the following DDL statements? 1) create table sql_query ( id int not null , query_text varchar(21826) not null , query_md5 ...
1
vote
2answers
118 views

Indexes slowing Insertions(Can't use Explain Insert to Analyse)

I have a MySQL database, I am parsing files from a log and inputting it inside. So far including a simple 4MB file in the database could take me about 4 minutes to insert ! and i have about 10,000 ...
1
vote
1answer
331 views

char vs varchar in mysql

I was reading the book MySQL high performance, it has mentioned that : CHAR is also better than VARCHAR for data that’s changed frequently, because a fixed-length row is not prone to ...
1
vote
1answer
2k views

MySQL - Server collation utf8_unicode_ci vs table collation utf8_bin: compatibility and performance

I'm not sure whether this belongs to SE DBA, but it sounds more logical ... Our MySQL 5.5.32 on Ubuntu 12.04 64 uses utf8_unicode_ci for the Server collation and some tables, like phpBB3, uses ...
1
vote
1answer
228 views

Squeezing Maximum Out Of My Server

I have a dedicated server only built to hold MySQL databases. Currently I have a database that has 173 million records and increasing by 5-15+ million records very day. My question is what and why I ...
1
vote
2answers
488 views

MySQL Synchronous Replication on Ubuntu 12.04 with DRBD

I have been looking around for some easy and open-source ways to handle database synchronous replication. DRDB on a Linux server seems to be one of the fastest and easiest methods to handle database ...
1
vote
2answers
2k views

ESCAPED BY clause in LOAD DATA INFILE

Can somebody please help me understand what is ESCAPED BY clause means in LOAD DATA LOCAL INFILE statement MySQL 5.5. I am a newbie to MySQL so please bear with me.
1
vote
1answer
447 views

Large table delete in mysql

I want to delete a table with 10,000,000 records. Due to some restrictions I can't do truncate on that table (I don't have the DROP privilege), I can think of two options: DELETE QUICK To set some ...
1
vote
2answers
784 views

Multiple table rows in one row

I have a table from where want to get such classes where 3rd position candidate got more marks than difference of 1st and 2nd. class position name total_marks 10 1 a 100 10 2 ...
1
vote
1answer
356 views

Is there a way to find the least recently used tables in a schema?

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...
1
vote
1answer
294 views

MySQL localhost vs Amazon RDS instance

I am seeing unexpected behavior with some MySQL performance. When I run a simple query SELECT 1; on my local host (MySQL 5.6.x) using workbench, it executes in 0.000s, but the same query run on ...
1
vote
1answer
56 views

Identical subquery optimisation in an update

update activitybooking set `submitted`='1' where id='958' and (select SUM(pool1_count) from (select pool1_count from `activitybooking` where `abt`='12' and (id='958' or ...
1
vote
1answer
328 views

Creating a partial unique constraint for MySQL

I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL. My ...
1
vote
1answer
135 views

Data usage increases initially when reorganize partition mysql cluster

In my MySQL cluster, the data usage of 4 data nodes is approximately 77%. I added a new data node online, and attempted to reorganize partition to redistribute data among all data nodes. But before ...
1
vote
2answers
88 views

Database and query optimizacion

I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
1
vote
3answers
2k views

MySQL Table not repairing

Table info: Database name: user_motiva Table name: wp_options.frm wp_options.MYD wp_options.MYI wp_options.TMD when I do a mysqlcheck -r --all-databases it gets hung on that table even if you ...
1
vote
1answer
481 views

Is it okay to have different lower_case_table_names value on master and slave?

I have a OSX MySQL master with lower_case_table_names set to 2. I have just setup a Linux MySQL slave with lower_case_table_names=1. I haven't started replication on the slave, but everything looks ...
1
vote
1answer
6k views

Connect Error (2054) - mysql_old_password issue still not solved

i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way: $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); ...
1
vote
3answers
70 views

Contains statement Query

I need to check the string with the column if the column contains that string or not. example:String 'how to make pizza' and the column if has 'pizza' i need to fetch that.. like SELECT * FROM ...
1
vote
1answer
315 views

mysql session logging tables

I've been building a session logging system for our sites. We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites. Our table ...
1
vote
1answer
911 views

`show procedure status` doesn't list all of the procedures?

mysql-server-5.5.28-1.el5.remi I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
1
vote
1answer
5k views

Some MySQL slave errors?

Master version: mysql-server-5.5.14-1.el5.remi Slave version: Percona-XtraDB-Cluster-server-5.5.24-23.6.340 Binlog format: ROW based I'm using PRM to setup HA for MySQL. Sometime, MySQL slave ...
1
vote
1answer
139 views

How we can enable index_condition_pushdown in MySQL..?

I have optimizer_switch variable as mysql> show variables like 'optimizer_switch'; +------------------+-------------------------------------------------------------------------------------------+ ...
1
vote
2answers
831 views

String comparison with integer is not working sometimes in MySQL 5.5

I have upgraded MySQL to version 5.5. Before upgrading when we used string comparison with integer, it was working. Code is "value = 1", here 'value' is declared as varchar(45) and its value can be ...
1
vote
1answer
3k views

Sudden MySQL error: Unable to connect to any of the MySQL Hosts

I am using MySQL Community Server 5.5.18 on Windows 7. My application was running absolutely fine without any troubles but since morning it is giving strange messages while opening reports. Reports ...
1
vote
1answer
5k views

Function returning “No data - zero rows fetched, selected, or processed” in MySQL 5.5

I have upgraded MySQL version to 5.5.16 After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed. In those functions, cursors are there, but ...
1
vote
1answer
9 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 ...
1
vote
2answers
59 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
146 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
106 views

How to take physical backup of MYSQL database

I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone ...
1
vote
1answer
33 views

How to cache query for re-use in following UNION of queries?

I need to perform query, save the result and re-use it multiple times in one session (something like WITH in Oracle DB). This is the query I want to save: SELECT `event`.id, event.name FROM ...
1
vote
2answers
137 views

Identifying cause of high I/O

I'm trying to diagnose the bottleneck for high-write I/O activity on a percona master (with replication to a slave). The pattern of behaviour is roughly cyclic and regular: 60 mins of 75-90% ...
1
vote
1answer
71 views

Error 2003: Setting up MySQL Replication

Trying to setup MySQL Replication using this guide here : http://wpguru.co.uk/2013/05/how-to-setup-mysql-masterslave-replication-with-existing-data/ I was able to do this on two random VMs running the ...
1
vote
1answer
65 views

Different column for different conditions when joining

I am using MySQL database and in that two tables, relation is one to many. One table is product table, sample data: +----+----------+ | id | name | +----+----------+ | 1 | product1 | | 2 | ...
1
vote
1answer
365 views

Master-Master Replication Duplicate Key

I have set two servers master-master replication mysql , i manage to set successfully but the problem is the duplicate key i got MASTER 1 Configuration /etc/my.cnf [mysqld] server-id=1 bind-address ...
1
vote
1answer
55 views

How can I make multiple joins on the same table?

I have a table (signal) which contains references to two other tables (ee and eeav). I want to perform an SQL Query which combines the data from ee and eeav tables. The situation is as follows: The ...
1
vote
1answer
81 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
1
vote
1answer
26 views

Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE ...
1
vote
1answer
51 views

What's the complexity of renaming a column in MySql?

We want to rename a column in our MySql (version 5.5) database with something like this: ALTER TABLE t1 CHANGE a b INTEGER; Is this an O(N) (N=number of rows in the table) or an O(1) operation? I ...
1
vote
1answer
72 views

Immigrate to file-per-table mode in MySQL

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini ...