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

learn more… | top users | synonyms

5
votes
2answers
5k 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 ...
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. ...
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 ...
5
votes
1answer
290 views

SELECT RELEASE_LOCK causing “Mysql has gone away”

During a long running process (90m+), our code calls SELECT RELEASE_LOCK('..');. However it always gives a "Mysql has gone away" error when we do this. We never got to see the return value of the ...
5
votes
2answers
184 views

Is there a (non-binary) MySQL collation that doesn't treat different mathematical symbols as the same character?

I've run into a real headache with MySQL's collations and non-BMP characters (ones with Unicode codepoints above U+FFFF). Basically, given a table and data like: CREATE TABLE `math` ( `id` int(11) ...
5
votes
2answers
3k views

how to enable temporal database capability in mysql.?

How to enable the temporal database compatibility in mysql database? Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current ...
5
votes
1answer
2k views

Query not using index when it should

I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table? ...
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
1answer
391 views

MySQL overhead of calling a Stored Procedure

Within Java, we access MySQL (v5.5.40) via JDBC -- a prepare statement, setting parameters then calling the stored procedure. It appears that every execute() has the overhead of 4 meta-calls (per ...
5
votes
1answer
287 views

Migrating MySQL to new server

We have a MySQL 5.1 instance running on Windows Server 2008 R2 x64 (Server A) replicated to another MySQL 5.1 instance on Windows Server 2003 R2 (Server B). We have a new MySQL 5.5 instance on ...
5
votes
3answers
7k views

Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB

I need to design database to store information contained in millions of log files generated by devices on trial on network. I have designed 5 different tables to store log file information. But, as ...
5
votes
3answers
1k views

MySQL varchar(255) limitation & Anomaly

I have defined a column in my MySQL database as : ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL However, when I enter text ...
4
votes
3answers
7k 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 ...
4
votes
1answer
12k views

What can cause TRUNCATE TABLE to take a really long time?

I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves). I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands ...
4
votes
3answers
17k 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 ...
4
votes
1answer
9k 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 ...
4
votes
3answers
3k views

What are the limits on the amount of slaves per master?

I currently have a Master with 2 slaves, all running MySql 5.5. What are the limitations on the amount of slaves I can connect to a single master? what parameters should be taken into affect?
4
votes
1answer
726 views

mysqladmin - u root -p ping is not executing

I am getting the below error in mysql while trying to ping. i am using mysql version 5.5.37-0ubuntu0.12.04.1. when ever i use mysqladmin i am getting the below error. Please suggest how to clear this ...
4
votes
2answers
2k views

Replication error

We have a slave server that has stopped replication due to the following error: Slave SQL: Query caused different errors on master and slave. What could be the cause of this error? And what would ...
4
votes
1answer
7k views

How to grant access for multiple tables to a user in MySQL DB?

I have mysql db named "greats" with four tables as below +---------------------+ | Tables_in_greatstat | +---------------------+ | log | | sitedet1 | | siteindex ...
4
votes
5answers
1k views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
4
votes
1answer
643 views

Online Schema migration in MySQL : When to start?

I'm the tech lead of a Social Media SAAS, where we're currently experiencing a big increase in data amount we're storing in our mySQL-database. We're having a single MySQL db, and besides many ...
4
votes
1answer
12k views

increasing mysql table open cache?

I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"? Thanks!
4
votes
2answers
9k views

Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?

My Hardware Spec Technology: Ivy Bridge CPU: Intel Xeon E3 1245v2 Intel Smart Cache: 8MB Cores / Threads: 4 / 8 Frequency: 3.4GHz+ / 3.8GHz Turbo Boost RAM: 32 GB DDR3 Hard disk: 2x 2TB SATA3 Raid 1 ...
4
votes
2answers
6k views

Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?

If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find.. [mysqld] max_allowed_packet = 1M [mysqldump] max_allowed_packet = 16M What is the point? Because when you ...
4
votes
2answers
106 views

Query only for the first n results of like

What I'm trying to accomplish is a huge speedup (read: incremental results) when searching a database with a like operator which is kind of a last resort, if everything else already failed, to come up ...
4
votes
2answers
209 views

Problem in Fetching Result set in Desired Format

I have 2 tables having the following columns Person ------ person_id, first_name, middle_name, last_name Items ----- person_item_id, person_id, item_type, status, ordered_date The query is ...
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` ...
4
votes
1answer
666 views

How to removing or reduce the size of redo logs in MySQL cluster

Can anybody tell me that how can we reduce the redo log file size or can we remove the redo log file size in MySQL Cluster and also what will be the impact of it on performance of cluster.Also can we ...
4
votes
1answer
188 views

MySQL Write performance bottleneck

We are trying to diagnose a MySQL Bottleneck, specifically for data writes. Basically, we have a single process that picks files from a folder and load into a MySQL table X using LOAD DATA CONCURRENT ...
4
votes
1answer
127 views

Subtract time difference between two columns in a table

Problem: How can I subtract the TIMEDIFF between two sensors (sensor 1 and sensor 2 in the example below) and return the the value in mili seconds? How do I get with the resolution of milliseconds? ...
4
votes
1answer
912 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
4
votes
1answer
5k views

Setting up MySQL Cluster on Two Systems

I am attempting to setup a MySQL Cluster. I have the folllowing setup one management node two data nodes another machine on which two more nodes reside Below is the config.ini file: [ndbd ...
4
votes
1answer
292 views

Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 ...
4
votes
3answers
684 views

Finding differences between a master and slave when pt-table-sync fails

I have a table that's showing some differences on a master and slave according to pt-table-checksum (PTC). I've found some verifying differences beyond that. Certain tables in the mix have composite ...
3
votes
4answers
5k views

Creating Archiving database for old data in MySQL

I Have a Master and Various Slave Servers. There is a huge amount of the data on all the servers and I wish to create an Archiving system for our Data. Scenario is : I want that only Last one year ...
3
votes
3answers
416 views

Why set up static data in views vs. using tables in mysql?

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields. I have the following too: LDAPIMPORTANT - view that ...
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
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
2answers
6k views

“Could NOT find Curses” error when installing MySQL from source

I am installing MySQL Server from the source distribution which I downloaded from: http://dev.mysql.com/downloads/mysql/#downloads. I have downloaded the file (mysql-5.5.19.tar.gz). I am using ...
3
votes
4answers
13k views

How to Add Federated engine after installing Mysql

I have Mysql 5.5.18 and upon show engines: show engines; +--------------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine ...
3
votes
1answer
1k views

skip columns while using LOAD command mysql

I have a csv file having 5 columns and i want to load only one out of that, is there a way i can do it. I want to use LOAD statement of MySQL 5.5 Any pointers are welcomed, help would be much ...
3
votes
4answers
1k views

Stress Test to Saturate Memory for MySQL InnoDB

I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of ...
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
2answers
513 views

View with parameter IN

I'm creating a view in which I have a where with an in clause and I want it to be a parameter. Unfortunately views can't be created with parameters, so I tried replacing it with functions, but ...
3
votes
2answers
379 views

Why wouldnt Mysql use index on the Primary query in a nested query

I have two tables: search_criteria and pricing. There is an index on search_id column in search_criteria table and on pricing_id column in pricing table. But running this nested query does not use ...
3
votes
1answer
6k views

Mysql INSERT INTO .. ON DUPLICATE KEY UPDATE

I'm trying to do the following query in mysql 5.5 INSERT INTO countingTable( image_count, article_id ) SELECT COUNT( article_id ) AS sum, article_id FROM imageTable ON DUPLICATE KEY UPDATE ...
3
votes
1answer
1k views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
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
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 ...