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

learn more… | top users | synonyms

3
votes
3answers
2k views

MySQL optimization - year column grouping - using temporary table, filesort

I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
3
votes
1answer
61 views

where varcharcol = 0 returns all rows

Suppose I have a table like this: create table mytable(cola int primary key, colb varchar(10)); Let's put some data in it: insert into mytable values(1, 'abc'); insert into mytable values(2, ...
3
votes
2answers
2k views

MySQL replication to slave having VPN connection to Master

I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network ...
3
votes
2answers
82 views

Is it possible to block a specific query, or return a static result?

Before you yell at me for doing it wrong, I know I'm doing it wrong. I'm using WHMCS (source code is not available), and we have a quarter of a million invoices. One particular query, which runs on ...
3
votes
2answers
2k views

Fastest way to create local copy of live InnoDB MySQL database?

We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+). Every month or so we need to copy this database to our development machines to work on locally. ...
3
votes
1answer
1k views

Which is more efficient, trigger or PHP code?

My requirement is to move EMR data for a patient to a set of history tables when the patient has been discharged. I proposed to use an after trigger on the table housing the discharge flag, checking ...
3
votes
1answer
1k views

MySQL 5.5 InnoDB = OFF?

We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine. However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF ...
3
votes
2answers
41 views

How to convert timezone in MySQL using the timezone name?

I want to convert date from one timezone to another timezone in MySQL, MySQL query which I am trying is SELECT DATE_FORMAT(CONVERT_TZ('2015-12-18 06:00:00', '+0:00', '-05:00' ),'%b %d %Y %h:%i %p') ...
3
votes
1answer
1k views

mysqldump doesn't restore database. why not?

Why doesn't my restore operation work? I created a dumpfile for a database using: mysqldump -u root -p databasename > /home/databasename_bkup.sql I then opened the dumfile and confirmed ...
3
votes
2answers
63 views

Fetch all words from column with like

I have a column with description and inside it can contain # value . what I want is all the words searched from that column with #tag This is my query SELECT description FROM photo where ...
3
votes
1answer
464 views

How to store static list of hashes in MySQL effectively?

Problem: I have a list of pairs (md5_hash, id). I want to store the data in MySQL and I want to make queries like this SELECT id FROM table WHERE md5_hash = <some_hash> The number of pairs ...
3
votes
1answer
5k views

Recover the disk space after deleting rows from table

I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the ...
3
votes
3answers
3k 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 ...
3
votes
1answer
170 views

Backup and off-site restore setup

I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one. Our current situation is ~100 GB of InnoDB and MySAM data in ...
3
votes
1answer
3k views

Slow insert performance in mysql innodb table for solr indexing

I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow. ...
3
votes
1answer
234 views

Get profiles values where message.sender_user_id = profile.user_id

I have this tables: default_messages CREATE TABLE IF NOT EXISTS `default_messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `message` text ...
3
votes
2answers
2k views

Can we assign mysql Key_buffer_size value for particular table?

In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps: SET GLOBAL myisam_table.key_buffer_size = 4194304; CACHE INDEX test.myisam_table ...
3
votes
1answer
1k views

Keeping MySQL staging database on same server updated from production

I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly ...
3
votes
1answer
51 views

MySQL replication - invalidating query cache entries (table)

We lately moved to MySQL V5.5 on CentOS release 6.7 with master and 2 slaves. Since then the replication process lags a lot behind the master with the line in the process list of: invalidating ...
3
votes
1answer
33 views

why my MySQL binary log file size is so small?

I have a lot of small binary log files , each of them just only 126 Bytes -rw-rw----. 1 mysql mysql 126 Jan 6 16:13 mysql-bin.000001 -rw-rw----. 1 mysql mysql 126 Jan 6 16:16 mysql-bin.000002 ...
3
votes
1answer
37 views

Query about MySQL Semaphores in Engine Status

During a recent problem / slow down with one of our slave databases I noticed that when I ran SHOW ENGINE INNODB STATUS. I am getting the following: ---------- SEMAPHORES ---------- OS WAIT ARRAY ...
3
votes
3answers
1k views

Bulk Delete for Large Table in MySQL

I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month. If I do DELETE FROM NOTIFICATION WHERE CreatedAt ...
3
votes
2answers
140 views

Is this substring_index behaviour documented or a bug?

mysql> show variables like 'version'; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | version | ...
3
votes
1answer
2k views

MySQL: Alter Table on very active table gives “Waiting for table metadata lock”

At my work, we've recently upgraded to MySQL 5.5 and since then, have been bitten a few times by the new (and intended) behavior of metadata locking. Basically, whenever we run an ALTER TABLE ...
3
votes
1answer
498 views

MySQL / Fusion IO Configuration Question

I need a little configuration optimization. I think what I have is pretty good but feel like there's still room for improvement. Current Config [mysqld] user=mysql datadir=/var/lib/mysql ...
3
votes
1answer
171 views

MySQL Replication not proceeding

I have a weird replication problem I have not seen before. It's basic mysql replication with single master and two slaves. One of the slaves is not executing replication events and seems just stuck ...
3
votes
1answer
737 views

How this simple Stored Procedure ended up in Deadlock

Thanks for all and this forum is really helpful. After few minutes of system running we get “Try restarting the transaction.” And its end up in deadlock. We use MySQL 5.5.28 and the Java code ...
3
votes
2answers
10k views

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
3
votes
1answer
3k views

MySQL Slave Relay Logging but not logging Binary Log

I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of ...
3
votes
2answers
761 views

How to concatenate a column after x rows?

I have a table with columns object varchar(255), frequency tinyint(3) unsigned, I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as .. ...
3
votes
1answer
539 views

how to add removed partition?

This is the structure of my table: -- Table: t_keyword_conversion_ga -- Create Table: CREATE TABLE `t_keyword_conversion_ga` ( `id` int(11) NOT NULL AUTO_INCREMENT, `own_domain_id` int(11) DEFAULT ...
3
votes
1answer
2k views

What does the base64 BINLOG statements in mysqlbinlog output mean?

I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0. When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed ...
3
votes
1answer
825 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 ...
3
votes
1answer
2k views

MySQL RDS Instance eating up memory and swapping

I have an issue with memory on a RDS instance which is of db.m2.4xlarge class. Below are the specifications of the RDS instance Instance Class: db.m2.4xlarge ( 68.4 GB of RAM ) Engine: ...
3
votes
2answers
577 views

Is there a rule of thumb regarding the size and number of a buffer pool instances?

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to ...
3
votes
1answer
1k views

How to modify `proxies_priv` in MySQL

I recently received a warning from MySQL that stated: [Warning] 'proxies_priv' entry '@ root@serverdomain' ignored in --skip-name-resolve mode. I looked in mysql.proxies_priv and there is ...
3
votes
1answer
381 views

How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option ...
3
votes
1answer
853 views

Getting Random Sample from large database MYSQL (No Auto Inc Field)

You were so helpful with my last question, I thought id throw another one at you thats stumping me. Basically I got a table, requirements from company was it was all supposed to be 1 table so I got ...
3
votes
2answers
620 views

MySQL users corrupt

I have a strange situation here: From time to time I cannot log in with any of my mysql users. I even cannot make a mysql dump. So I started searching in mysql files and I found that users.MYD and ...
3
votes
1answer
273 views

MySQL Stress testing issues?

I had gone through MySQL High Performance book Chapter 2 for MySQL Bench marking and Profiling,I have some questions about benchmarking How can we test an existing database and queries against it ...
3
votes
1answer
1k 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 ...
3
votes
1answer
340 views

What's the most unsafe MySQL configuration?

I have MySQL 5.5.27-29.0-log instance running specifically for Jenkins. Since tests data is always recreated during setup, ACID is unnecessary and I'm hoping to sacrifice it to gain more performance. ...
3
votes
1answer
253 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 ...
3
votes
1answer
2k views

Configured MySQL replication but its not working

I have configured MySQL master and slave replication setup on 5.5.x. But for some reason data is not getting replicated properly. What might be the reason? At Master: /etc/my.cnf [mysqld] ...
3
votes
1answer
261 views

mysqld-multi with first DB as Slave and second DB as Master

Yesterday, I setup mysqld-multi in my.cnf. It works fantastic, but there is still a further Problem. The first DB on socket 1 running as Slave from a other server. The second one from socket 2 ...
3
votes
1answer
26 views

How we can recover MySQL database (Innodb,myisam)

I have found some recovery tool innodb-recovery but it works only on linux machine, I would like to know how to recover innodb tables on windows. Transaction ID based recovery Cancel based, Change ...
3
votes
1answer
328 views

Stuck in “query end” status every 5 minutes

I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about. An UPDATE query that is running up to tens of times per second is sometimes really slow. Here is ...
3
votes
0answers
7k views

Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example: $ mysql -u root -p --column-type-info Enter password: --- Copyright and help message snipped for brevity --- ...
3
votes
1answer
177 views

MySQL table architecture

Background information: I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; ...
3
votes
1answer
1k views

Convert Oracle Query for Date to Mysql queries

Need to convert the following oracle queries to MYSQL queries. 1) select to_char(to_date(?,'DD-MM-YY')) from dual; 2) select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY')) from dual; 3) ...