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

learn more… | top users | synonyms

2
votes
2answers
347 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
1k 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` ...
2
votes
3answers
814 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 ...
1
vote
2answers
136 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
711 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 ...
0
votes
1answer
2k views

Use of log_bin_trust_function_creators in MySQL

What is the use of log_bin_trust_function_creators? Also, Is the value of the parameter dynamic? Will it change if I restart my MySQL service?
8
votes
3answers
26k views

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
14
votes
1answer
10k views

MySQL transaction size - how big is too big?

I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction. There are many aspects, and the imports may yield anywhere between 100k-...
6
votes
4answers
19k 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 ...
5
votes
2answers
22k 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 ...
7
votes
1answer
12k 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 ...
4
votes
3answers
9k views

Issue after moving the ib_logfile1 and ib_logfile0 files

I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of ...
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, ...
3
votes
2answers
8k 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
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
42k 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
2answers
4k 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 ...
4
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. ...
4
votes
1answer
6k 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 ...
4
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
2answers
7k 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 ...
3
votes
1answer
10k 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 ...
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: ...
2
votes
1answer
568 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 ...
2
votes
2answers
955 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 ...
1
vote
1answer
919 views

#1041 - Out of memory issue in mysql [closed]

I am using Amazon RDS db.m1.medium instance. It has at least 40-50 database and atleast 10k table. For last one year it was working fine, but today I was altering one table It started giving error. I ...
1
vote
2answers
237 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
0answers
31 views

MySQL: excessive memory usage

In our production system, MySQL service uses above 95 % of the available memory. System Info: OS: CentOS 6 RAM: 32 GB CPU: 4 core MySQL version: MySQL 5.5 Database Size: 158 GB my.cnf info: # ...
0
votes
1answer
215 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
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 .
0
votes
1answer
384 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | +---------...
-1
votes
2answers
4k 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
3k 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
254 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. ...
4
votes
2answers
1k 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
8k 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
1answer
49 views

User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a ...
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
2answers
808 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
10k 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
3answers
2k 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
2k 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
568 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
1answer
137 views

Several values from different tables

I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest ...
2
votes
0answers
40 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
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
2answers
134 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 ...