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
66 views

How can I determine which Innodb table is being written?

I am running Mysql 5.5. I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The ...
2
votes
1answer
85 views

MySQL - Get Minimum Value, Data From Multiple Tables

Good evening all, first post on the DBA forums. I am having real issues working out how to accomplish a seemingly simple task within MySQL as a model for PHP code later on. I am not exactly a MySQL ...
2
votes
2answers
140 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
1answer
771 views

Auto_increment value needs to be set as S1

Hi I need to set auto increment for a table as S1. I tried by altering the table and also by creating by new table I am aware how to set auto_increment but when i try to set auto increment as S1 i am ...
2
votes
2answers
402 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
44 views

Will composite indexes or unique indexes help with query performance?

I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The ...
2
votes
1answer
943 views

Space not reclaimed after dropping MyISAM table

I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I ...
2
votes
2answers
38 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...
2
votes
1answer
69 views

Unknown column error with an index

I created a simple index associated with a timestamp column CREATE TABLE `data` ( `id` INT(11) UNSIGNED NOT NULL, `ticker` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci', `comment` TEXT ...
2
votes
1answer
398 views

datetime vs timestamp in mysql 5.5

What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this? I have a situation where I am trying ...
2
votes
1answer
361 views

select count(*) in mysql 5.5 innodb— rewrite advice?

I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow... select count(*) from mails3 join questions using (question_id) where ...
2
votes
1answer
256 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
6k views

MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

When reading online about innodb_log_file_size, it says The combined size of the log files must be less than 4GB So what happens if it needs to be larger? In books and online, it says to set ...
2
votes
3answers
4k 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
1answer
1k views

Issue with connecting to the mysql database

We have an outstanding issue with VCC in chicago, there is an issue connecting to the mysql database If an agent does a refresh of the monitor they will be logged out. This is the error we ...
2
votes
1answer
618 views

Does mysqld wait for init-script code to complete before opening the instance?

I'm trying to automate the execution of an application code at MySQL startup. The init-file server option seems handy and I need to know if mysqld waits for all the code in the init-file to complete ...
2
votes
1answer
402 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

Extract some columns data from one table and insert into multiple tables

I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables tbl_1: id, name, lastname tbl_2: id, ...
2
votes
2answers
3k views

Need suggestions for best way to archive MySQL (InnoDB) tables

Problem: I have two fairly large tables. A 'wall' table for messages between users with 9 million rows at 2 GB and a 'chapter' table at 2 million rows and 18 GB. I want to keep the number of active ...
2
votes
1answer
548 views

“set-variable = innodb_thread_concurrency=4” or “innodb_thread_concurrency=4”

Based on this question, the asker wrote: set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = ...
2
votes
1answer
2k views

MySQL Workbench ERROR 1064

I have a problem with my database. I have created the model and now I want to sync it with my database on my WAMP Server (local) but it keeps giving me an error. I searched for the cause of the ...
2
votes
1answer
335 views

Referring to super parent or as a separate entity?

I have this person table as super parent, id firstname lastname email telephone ... ... and user table as a child id person_id (FK) password username screenname ... ... They must be 1:1 ...
2
votes
1answer
281 views

mysql emits BINLOG rows even though binlog_­format=­STATEMENT

We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default) According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in ...
2
votes
1answer
3k views

Is there a PPA for MySQL 5.5 for ubuntu 10.10 and 11.10?

I want to install MySQL server 5.5 on ubuntu 10.10 and 11.10 servers but have not yet found a problem free PPA or installation method as of yet. Has anyone managed to do this with the said versions of ...
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
1answer
4k views

Fixing MySQL errors recorded in error logs

Below is the extract of the most common lines of error being recorded in error logs on my PC: 110905 16:16:54 [Note] Plugin 'FEDERATED' is disabled. 110905 16:16:54 InnoDB: The InnoDB memory heap is ...
2
votes
1answer
122 views

How much space is needed to reclaim space from a table in INNODB

I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the ...
2
votes
1answer
1k views

Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

I dropped my UNIQUE KEY index 'myindex' on a table and I wanted to change the UNIQUE KEY to something else, so after dropping it I did: alter table mytable add unique key anykey (add1, city, state); ...
2
votes
2answers
61 views

How do you update a large MySQL table that's on a server?

I have an application on a web server. The PHP front end for the application queries a MySQL database which has a table that contains 60 million taken domain names. My application queries this ...
2
votes
2answers
249 views

How to generate auto increment values for my duplicate column in my sql?

How to display when i was run to find duplicate values from my table at this time i want to add new column in select statement with auto increment values SELECT * FROM tutorials_tbl WHERE Lscno IN ...
2
votes
1answer
64 views

WHERE attribute having two relation lines in the same SQL

I have a database designed with tree tables: checklist (id, name) tag (id, name) checklist_tag (checklist_id - FK, tag_id - FK) Running this SQL: SELECT cc.id as checklist_id, cc.name as ...
2
votes
2answers
273 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
1answer
39 views

How to decipher the transaction message in innodb status log.

I have several transactions that seem have stalled. Looking at the innodb status log, I found several messages similar to the following: ---TRANSACTION 69DBBB, ACTIVE 947 sec starting index read, ...
2
votes
3answers
189 views

How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

I have two tables 1. Customer_details and 2. Order_details. 1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name, 2.Order_details table --> fields-- Ord_Id(Primary ...
2
votes
1answer
770 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 ...
2
votes
2answers
1k views

changing TEMPORARY TABLE default collation

when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
2
votes
1answer
71 views

Make sure slave has finished reading from master

I am planning to have a master-slave system in which occasionally the master and slave will reverse roles. To do this, I will stop further updates to master, and redirect traffic to the slave. But, ...
2
votes
1answer
169 views

MySQL Subquery hangs / terrible perfomance

I am using MySQL 5.5.16 @ CentOS. Please have a look at this query: SELECT inventory_ID, ( SELECT price_brutto FROM _inventory_prices ...
2
votes
1answer
77 views

System to track what Zip Codes my vendors service

I am designing a table configuration that will contain all of the zip codes in the United States. I have a .csv file of all the zip codes in the U.S., so thats at least a start. This will end up as a ...
2
votes
1answer
3k views

MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server. Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write. Currently we are running on the default ...
2
votes
1answer
333 views

Null Values and Performance

I always try to build the database avoiding any NULL values in my tables, and I have successfully had done this so far. (Hey, I am very new to database design.) But still there are times when saving ...
2
votes
1answer
92 views

Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0

I didn't know if this is possible using just a query or if I need some procedural logic on this one. I have this table: CREATE TABLE `default_messages_users` ( `id` int(11) NOT NULL ...
2
votes
2answers
125 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 ...
2
votes
1answer
380 views

Assertion failure from InnoDB while restoring using Innobackupex

I thought I would give back to all those people who help me when I have a problem. This might be something old, but I did not find it anywhere so here goes: We recently upgraded our production DBs ...
2
votes
1answer
599 views

Database Design - Creating Multiple databases to avoid the headache of limit on table size

I need to design database to store information contained in millions of log files generated by devices on trial on network. My first approach was storing all the information in tables contained in a ...
2
votes
3answers
1k views

InnoDB UPDATE slow need a better option

I'm using MySQL 5.5 with a table in InnoDB running: deleteme tinyint NOT NULL DEFAULT 0;` UPDATE `import` SET `deleteme` = 1; -- Set the delete field LOAD DATA LOW_PRIORITY LOCAL INFILE ...
2
votes
1answer
3k views

What is the optimal way to select the parent and child rows on a table with a self-relationship?

Sample data from the genres table: id | genre_name | genre | parent_id --------------------------------------------- 2 | Blues | MAIN | NULL 6 | Folk | MAIN ...
2
votes
1answer
2k views

Ubuntu Multiple MySQL instances and Apparmor

I am trying to set up multiple instances of MySQL on an Ubuntu dev machine and running into AppArmor problems. I get this error in the syslogs: kernel: [227631.776360] type=1400 ...
2
votes
1answer
226 views

Innodb mutex locking ?!? Inserts start stacking after 18-24hours, restart required

We have a fairly high end box running mysqld 5.5.20. The cpu configuration is dual quad core xeons. It has 32 gb ram. It has a quad intel ssd configuration running raid 0+1. On a fairly busy ...
2
votes
1answer
65 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 ...