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

learn more… | top users | synonyms

2
votes
3answers
592 views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
2
votes
2answers
319 views

Tinyint structure is confusing

I am confused to see this tinyint(3) structure. If tinyint can store only 1 byte then what is the purpose of giving the size in brackets? Is there something special with this parameter in MySQL?
2
votes
1answer
697 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 ...
2
votes
1answer
831 views

How to set unique columns combination

see this table: CREATE TABLE IF NOT EXISTS `default_messages_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `message_id` int(11) NOT NULL, `owner_user_id` int(11) NOT NULL, `to_user_id` ...
1
vote
2answers
106 views

MySQL query performance question

I have a MySQL database where I would like to return results based on multiple rows. id | key | value | selector --------------------------------------------------------- 1 | username ...
1
vote
1answer
424 views

Create a mysql slave database from master database

I have a mysql master database running. I want to have a slave database. So, I stopped the master mysql database and copied it to the slave machine. I had bin log enabled on master so even those files ...
0
votes
1answer
593 views

Dedicated mysql server slow response [closed]

I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ... With pingdom.com test i have for server: 10 sec and simple hosting: 2sec is ...
5
votes
2answers
19k views

innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster. We have 125G main memory with 24G InnoDB buffer pool ...
5
votes
4answers
16k views

How to change a MySQL previous slave to be a master and remove slave status information?

I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine. What I can't seem to do is to remove ...
8
votes
2answers
2k views

How do Application Like Yelp Retrieve distance information from data base efficiently

For example, say I have a table: Business(BusinessID, Lattitude, Longitude) All are indexed of course. Also there are 1 million records Say I want to find businesses closest to 106,5, for example, ...
7
votes
1answer
10k views

Deleting bin logs in replication environment

I have a question about deleting binary logs in the replication environment: We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy ...
3
votes
2answers
7k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
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 ...
5
votes
1answer
639 views

Is it Possible to Recover Data from a BLACKHOLE table? [closed]

I had created table with engine BLACKHOLE basically the BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty ...
5
votes
1answer
2k views

Possibilities to speed up InnoDB INSERTs and UPDATEs

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower. I'm monitoring when, and it ...
5
votes
2answers
3k views

Large files in wamp\bin\mysql\mysql5.5.24\data directory

I have discovered these files located in the wamp\bin\mysql\mysql5.5.24\data folder on my Windows 2008 server. They are consuming about 22 GB on a 40 GB drive. I am using a LMS called moodle, that ...
5
votes
2answers
38k views

Grant table privileges to a user connecting from any host

I have a user account - let's call it 'wordpress' - that I need to allow to access a few catalog tables in another e-commerce database on the same server. I've configured the user with three host ...
4
votes
1answer
680 views

How to Update same table on deletion in MYSQL?

In my Database I have a table: Employee with recursive association, an employee can be boss of other employee. Following is query I used to create table: CREATE TABLE IF NOT EXISTS `Employee` ...
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
2answers
1k views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
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
2answers
6k views

What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?

I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-) I ...
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 ...
2
votes
2answers
744 views

How to monitor InnoDB Disk I/O using Server Status Variables?

This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? " As mention in my previous question i want to count total number of disk i/o per query, but ...
2
votes
1answer
9k views

BEFORE INSERT trigger in MySQL

I am new to MySQL and I'm having problems creating a BEFORE INSERT trigger. I get an unexpected END error. I have a table named verlof_aanvragen with a column datum (and 6 more columns). What I ...
2
votes
1answer
516 views

Restriction on self referencing on insert in MYSQL?

How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself). mysql> SELECT * FROM Employee; ...
2
votes
1answer
1k views

How to upgrade MySQL to a New version..?

I have installed mysql using the package management system.I am using ubuntu machine. After that i have very well configured the server for InnoDB and other server related parameters. I am using the ...
1
vote
2answers
213 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 ...
0
votes
1answer
171 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
1answer
312 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | ...
0
votes
1answer
3k views

Restoring a single database from mysql enterprise full backup

I am having command to restore all databases from full dump taken by enterprise full backup .Can anyone help me how to restore single database from full dump .
-1
votes
2answers
3k views

how to autoincrement varchar datatype depending upon the product details name using trigger

I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id ...
5
votes
1answer
1k views

Percona mysqld got signal 11

I've googled and searched stackexchange, but can't find anything relevant to this particular issue (there are other error 11 issues, just not for this as far as I can tell). We have a server that is ...
5
votes
2answers
245 views

Is there a reason/benefit I am not aware of for this kind setup?

I now manage a MySQL database that was designed and created by another. I am confused about a certain setup that the original developer created.. He did not use auto increments for table IDs. ...
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
3answers
5k views

InnoDB insertion faster

I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
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
3answers
7k views

innodb_buffer_pool_size not changed

I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf default-storage-engine = innodb innodb_buffer_pool_size = 3G innodb_log_file_size = 256M innodb_thread_concurrency = 8 ...
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 ...
2
votes
0answers
37 views

Composite index works for larger set of data, but not smaller

This is a follow on question to optimise my.ini for key query Data model: DROP TABLE IF EXISTS `contract`; CREATE TABLE IF NOT EXISTS `contract` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
2
votes
2answers
523 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
2
votes
2answers
626 views

MySQL user without SUPER privilege

This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question. When creating users in mysql using grant all ...
2
votes
3answers
1k views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
2
votes
3answers
1k 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 ...
2
votes
1answer
2k views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...
2
votes
1answer
480 views

How can I disable utf8mb4 entirely on MySQL 5.5?

I know you're not supposed to do this, but I have a MySQL 5.5 master and a MySQL 5.1 replication slave. That's supposed to be okay, unless I do something stupid like use utf8mb4 that isn't supported ...
2
votes
3answers
5k 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 ...
2
votes
2answers
1k views

How to GROUP_CONCAT between given rows?

I have a table as id diary 1 breakfast 2 walk 3 start 4 office works 5 office projects 6 end 7 taxi 8 start 9 preparing for meeting 10 doing the meeting 11 end 12 night I want ...
2
votes
1answer
8k 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 ...
2
votes
2answers
133 views

How to see what was executed in the database via binlogs for specific date?

I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had ...