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

learn more… | top users | synonyms

2
votes
1answer
196 views

In MySQL, does the order of the columns in a WHERE clause affect query performance,why? [closed]

I have a query that doesn't use any indexes: SELECT 32, guid, 1, 1, 1, 0, 5 FROM test WHERE level >= 20 AND ( ( fun_GetIndexValue(data, 354) ...
2
votes
2answers
766 views

MySQL information_schema doesn't update

I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true. After I run the query delete from abc.test, I want ...
2
votes
1answer
231 views

MySQL specific database configuration file

In MySQL's configuration file I've globally disabled autocommit as so. [mysqld] autocommit=0 I need to turn MySQL's autocommit on for a specific Ruby on Rails database though. It could be for the ...
2
votes
1answer
168 views

innobackupex is failing while exporting backup

I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex. I have following ...
1
vote
2answers
624 views

Warnings: Why are statements with limit clause unsafe even if there isn't a limit?

I'm a bit irritated because of some warnings in mysql errorlog. Example 1: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement ...
1
vote
2answers
71 views

Is column order reliable in UPDATE?

Can we reply on the order of columns in an UPDATE? SET @x = 0 UPDATE table1 SET col1=IF(col1 IS NULL, NULL, @x:=@x+col1), col2=IF(col2 IS NULL, NULL, @x:=@x+col2), col3=IF(col3 IS NULL, NULL, @x:=0) ...
1
vote
2answers
7k views

What is the difference between optimize table and analyze table in mysql

What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.
1
vote
3answers
716 views

Please review the My.INI settings of my PC. File Attached

Below is the my.ini file of one of my PC running MySQL 5.5 with 1 GB RAM. It is not using concurrent connections and we are using a stand-alone PC for a single application and single user. # MySQL ...
1
vote
1answer
121 views

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' not working

I'm trying to install semi synchronous replication in my mysql 5.5 and trying to execute the below command: INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' but this command is ...
1
vote
3answers
2k views

MySQL: Trying to update to 5.5

I've followed this instructions, but after mysqld --skip-grant-tables --user=mysql, I'm getting this: 110913 20:29:18 [Warning] Can't create test file /var/lib/mysql/my-laptop.lower-test 110913 ...
1
vote
1answer
105 views

MySQL with high CPU and memory usage

here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - ...
1
vote
1answer
49 views

How are these two user drops different?

Other than the obvious, what is the difference between: drop user user_01@host01; and drop user 'user_01'@'host01'; In a sample test, they both achieved the same result. From Account Names docs ...
1
vote
1answer
395 views

Change root password of mysql database

This could be a Rookie problem , but it seems that i am not able to solve it. I have MySQL server installed on my windows machine(MySQL 5.5) under the Admin username root. Some How i lost the ...
1
vote
3answers
212 views

getting error while starting memecached service with ndb engine

i am installed memcached library by following the steps in the internet. now there is a binary file created in bin folder of mysql now i exicuting the command memcached i am getting error like this ...
1
vote
1answer
70 views

How and where do I upload a mysql database?

I am new to Ubuntu (server edition) and mysql. How do I upload (import) an existing database with a MYSQL or (mysql.zip) extension that was downloaded from a web host (cPanel?) I do not have any of ...
1
vote
1answer
2k views

Enabling LOAD DATA LOCAL INFILE in mysql

LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian ...
1
vote
3answers
548 views

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
1
vote
2answers
65 views

need help in Query optimization

I need help in query optimization,I am having table with structure Create Table: CREATE TABLE `ip_country_mapping` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `cron` bigint(20) NOT NULL, ...
1
vote
2answers
58 views

Need some support on MySQL Query

I have a query SELECT leads.id, sfav.id is_favorite FROM leads INNER JOIN teams tst ON tst.team_set_id = leads.team_set_id INNER JOIN members members ON tst.team_id = ...
1
vote
2answers
375 views

Changing the mysql.proc table directly after upgrade

Our host upgraded our mysql install without us knowing from roughly 5.1 to 5.5. This caused a problem because the table definition of mysql.proc is different as discussed here: ...
1
vote
2answers
3k views

MySQL using too much CPU

I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server: CentOS 5.9 24gb Ram 8 ...
1
vote
2answers
193 views

How to avoid lagging when enabling log-slave-updates?

Master: 48GB RAM 16-core my.cnf: https://clbin.com/hlkUo Slave: 64GB RAM 24-core my.cnf: https://clbin.com/viLm0 Both are running 5.5.28. For the incremental backup purpose, I need to enable ...
1
vote
1answer
675 views

How to Log IP Address for Inbound Connections in MySQL

Is there a way in MySQL 5.5 (on Redhat 6.x) to log the IP address from where a particular query has been run? And have that appear in the general log or even relay and bin logs? I am trying to track ...
1
vote
3answers
5k views

How to Restore data from binary log

I am trying to restore some lost data , but I am not having its backup .So I am trying to restore from the binary log .Please explain how to restore from the binary log in mysql .
1
vote
1answer
367 views

Query to take partial backup in MYSQL

I tried to take full backup in mysql using enterprise backup by using the command mysqlbackup -u root -p --backup-dir=g:\full_bck backup But I could not take backup of a single database , I ...
1
vote
1answer
642 views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
1
vote
1answer
181 views

How can I change the timezone in MySQL?

In a MySQL DB, I have two varchar fields. One is for date and one is for time. I need to change the time from CST to GMT. In MySQL how do we convert the combination of these date and time fields from ...
1
vote
2answers
143 views

Is there a way to get slow-query-log-like profiling information on a single query?

Specifically, I'm looking for this information: # Query_time: 0.011922 Lock_time: 0.000088 Rows_sent: 107 Rows_examined: 663 Rows_affected: 0 Rows_read: 107 # Bytes_sent: 4152 Tmp_tables: 3 ...
1
vote
1answer
499 views

How can I trigger this stored procedure on insert?

I have a table of error messages to which errors will be inserted. I also have a table defining interactions between errors. For example, when a "Power Restored" error occurs, it clears any previous ...
1
vote
1answer
504 views

when will Innodb support activation of triggers on cascade foreign key? [closed]

The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html Currently, cascaded foreign key actions do not activate triggers. Any Idea when will this be implemented. Is ...
1
vote
1answer
35 views

ALTER TABLE - Rename a column

This got me nuts! I want to rename a column from read-more to read_more in blog table Tried all this: ALTER TABLE blog RENAME COLUMN read-more to read_more; ALTER TABLE blog CHANGE COLUMN ...
1
vote
2answers
53 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
80 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
159 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
1answer
919 views

Upgrading MySQL 5.1 to 5.5 on Centos 6 without removing dependencies

I'm trying to upgrade MySQL 5.1 to 5.5 using yum/rpm combo (yum to remove MySQL 5.1 and rpm to install MySQL 5.5). Most instructions I've found suggest to do first: yum remove mysql mysql-server ...
1
vote
2answers
614 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
130 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
1answer
392 views

MySQL equivalent of WITH in oracle

I need the mysql equivalent of the WITH clause in Oracle. Please help. Thank You
1
vote
2answers
212 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
187 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
45 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
2answers
1k views

Unable to drop the table since its showing waiting for meta data lock

We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on ...
1
vote
1answer
89 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
84 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
1answer
314 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
3answers
64 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
3answers
2k views

Automatic MySQL Replication Fail-over Strategy

I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the ...
1
vote
1answer
235 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
554 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
3k 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 ...