MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
238 views

Replication with only update and insert

I have set up my replication but I want to delete some outdated data from my master tables. Is it possible that say if I have 10 records at master and I delete 5 of them but I want those 10 records to ...
2
votes
1answer
718 views

Poor MySQL for Drupal on Shared Hosting

My Drupal site is on a shared host where I cannot modify the my.cnf configurations but the database seems to be performing poorly. Here are some of the flagged issues that I can see... In ...
1
vote
1answer
1k views

--local-infile=1 using MySQL Workbench?

When I start the MySQL bash client, I have been starting it with --local-infile=1 so that I can import data from a .csv file. Is there any way of doing this within MySQL Workbench?
2
votes
3answers
266 views

How to get all non deleted messages from one user

I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like: CREATE TABLE IF NOT EXISTS ...
0
votes
1answer
221 views

Normalization and Breaking down tables for InnoDB

I'm designing a web application, something small but deals with lots of read and inserts. Let's say 60% read and 40% writes. My storage engine is InnoDB on MySQL. I have a table called User which has ...
3
votes
3answers
2k views

slow query with fulltext and left join

I have 3 tables about 250,000 records. table1 is MyISAM, add fullindex with title and content, add index with pid. table2 and table3 are InnoDB, add index with pid. Only query table3, just cost ...
2
votes
1answer
1k views

ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES

I'm trying to import a mysqldump backup of a drupal database taken from a server running MySQL 5.0 (which will be upgraded to 5.5) onto my local machine running MySQL 5.5.23. The import fails with the ...
1
vote
1answer
254 views

Mysql 5.1.X How to upload data from dump file without the partitioning commands?

I need to upload data from a dump file (large one) that includes partitioning info into a database that doesn't support partitioning. How can I tell the mysql command line to ignore the partitioning ...
1
vote
1answer
87 views

how to use mysql temporal cache tables

I took this example from a book where visitors_today is the cache table from visitor_stored. The idea is to store all the visitors per day (visitors_stored) using a cache table. Create table ...
7
votes
2answers
8k views

How to use Unique key via combinations of table fields?

Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1 CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, ...
3
votes
1answer
4k views

Mysql fulltext search my.cnf optimization

I have open a question on http://serverfault.com/questions/353888/mysql-full-text-search-cause-high-usage-cpu Some user recommended asking here. We built a news site. Every day we will input tens of ...
3
votes
1answer
975 views

Preventing replication of alter table command to change engine

I am in the process of switching a MySQL table from MyISAM to InnoDB and I do not want the slave to perform the alter table command until I execute it manually. The intention is to have a backup for a ...
1
vote
1answer
224 views

Getting slaves of a master-master setup stopped in sync

I have a master-master setup where each master has it's own slave. They layout might be illustrated as: M1 <===> M2 | | \/ \/ S1 S2 That is both S1 and M2 slave ...
1
vote
1answer
4k views

Do you still use MyISAM or prefer Aria storage engine?

If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery: Why still use MyISAM ? Should changing storage engine from MyISAM to ...
1
vote
1answer
418 views

What language and data structures does MYSQL uses as backend?

I am just curious to know what data structures and languages does MYSQL exactly use as back end language? and what does the file format .MYD , .MYI and .frm stand for and how does MYSQL use them? ...
1
vote
2answers
6k views

Script to show mysql health status?

Kev suggested this question ( http://stackoverflow.com/questions/10524981/php-script-to-show-mysql-health-status ) is better fitted here. I know phpMyAdmin can shows the lists of mysql vars and ...
1
vote
2answers
2k views

Slave settings not working

I have this machine as slave. Below is the command I ran and stuck. I done the change on the config file and set the id=2 too. Anything else I am missing here? slave start; ERROR 1201 (HY000): Could ...
1
vote
2answers
206 views

Why are my results returned twice?

I've this query: SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id, `default_wall_posts_comments`.`comments` as comments, UNIX_TIMESTAMP() - default_wall_posts_comments.date_created ...
2
votes
1answer
79 views

MySQL replication constantly checking tables

I recently moved one of our slave databases to a new server, the database is purely used for offsite replication/backup and doesn't handle any of the live traffic. Since moving, the new server seems ...
4
votes
3answers
4k views

Mysql users deleted

I have a fresh centos 6.2 with mysql in 5.1.52. Then I saw two empty user name and I deleted that then I saw 3 root user deleted that and created one like this CREATE USER 'root'@'%' IDENTIFIED BY ...
2
votes
1answer
7k views

How to properly stop MySQL server on Mac OS X?

I installed MySQL Community Server following instructions at http://dev.mysql.com/doc/refman/5.0/en/macosx-installation.html with the Startup Item and the MySQL Preference Pane. I can stop the ...
1
vote
1answer
810 views

Triggers vs Views in MySQL

We are in the process of denormalizing a large amount of data for performance reasons. Basically a Query that would normally JOIN over 10 tables is replaced with a Query that has no JOINs, yet all the ...
0
votes
1answer
334 views

ENABLE KEYS on a large table is crashing mysql

I am trying to copy our data over to a new server, and at the same time, testing our backup-and-restore processes. I have a 2.6GB file created by SELECT INTO OUTFILE, containing 19 million rows. I've ...
1
vote
2answers
3k views

Shell Script / Tool to check the MySQL Replication Status? [duplicate]

Possible Duplicate: Monitoring replication on mysql I am running with MySQL Master/Slave Replication on Linux. Is there any tool/Shell script which monitors the Replication Status and send ...
2
votes
1answer
1k views

Transaction Lock Timeouts When Updating a Row

I'm trying to determine the cause of an InnoDB transaction lock timeout. This one is very strange in that it appears to be a deadlock error instead - nonetheless, here's the output of my slow log ...
0
votes
1answer
251 views

Can't connect to MySQL Cluster Storage (data) nodes [duplicate]

Possible Duplicate: Can't connect to MySQL Cluster Storage (data) nodes I've deployed a MySQL Cluster with a few nodes, and I want to access the storage nodes directly from another ...
1
vote
1answer
215 views

Database for efficient retrieval of data from large data set

I am designing a new system that will be dealing with large data set. This system is expected to have over 10 million records. A user of the system needs to check for the availability of the name he ...
1
vote
2answers
4k views

In MySQL, how to manually edit values of a BLOB column in few tuples?

In MySQL, how to manually edit values of a BLOB column in few tuples? For convenience of manual curation of database like this, should the column be created as BLOB or TEXT, or something else at the ...
2
votes
1answer
122 views

SELECT for values in a table

I have this table structure: id1 id2 1 2 1 3 2 1 2 4 id1 and id2 are not unique as you can see in the values. I need to retrieve those values that exists in the two columns ...
1
vote
4answers
490 views

cross self join to get more rows - better solution?

I have a table employees: +----------------+------------------+ | Field | Type | +----------------+------------------+ | id | int(10) unsigned | | persons_id | ...
3
votes
2answers
2k views

Setting Circular Replication in mysql

I have 3 MySQL servers say A,B,C. What I want to do is that I want to make all of them master as well as slave.If there is update on any of the MySQL Servers it should be replicated to all of the ...
3
votes
1answer
2k views

MySQL Server Performance

I have a few questions regarding MySQL server running under Windows Server 2008 R2: How do I check the number of allowed connections? How do I turn on query logging to see performance of queries ...
1
vote
1answer
386 views

use of signal for insert statement in mysql stored procedure

I want to create a procedure in which all types of possible errors can be handled using SIGNAL. Below is my stored procedure: DROP PROCEDURE IF EXISTS `spItem`$$ CREATE PROCEDURE `spItem`( IN ...
5
votes
1answer
759 views

MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

I have a basic table : create table fullTextTest ( id INT(11) NOT NULL, superText CHAR(255) NOT NULL, superLongText TEXT NOT NULL, primary key (`id`), FULLTEXT KEY `superText` ...
4
votes
1answer
4k views

What is the use of ibdata1 file in mysql?

I have application which has the backend db as Mysql(InnoDb engine), and currently my client wants to decrease the size of the ibdata file to some fixed size. I found the three below solutions. ...
0
votes
2answers
3k views

Recover a MySQL InnoDB from ibdata1 that won't repair using innodb_force_recovery

We have a MySQL database that we have manage to grab the raw files from a server with a fatal hardware failure. However, this failure seems to have occurred when the DB server was running. As a ...
3
votes
1answer
1k views

Syntax to not write single statement to binlog?

I know you can SET SQL_LOG_BIN=0; to disable writing your session to the binlogs. I was wondering if there was some single serving query syntax to prevent just that one query from being written w/o ...
1
vote
1answer
536 views

MySQL CPU Usage

To remove duplicate rows in a table I did the following (about 200k rows) table1: id_a int id_b int val_a int val_b int pk (id_a, id_b), index id_a, index id_b Algorithm: created table2 as select ...
1
vote
3answers
389 views

Replication or Sharding for a huge table?

I have a table that I'm pretty sure in the very near future (within a year) will reach 14 million rows, and I'm trying to scale out my web app before hitting any bottleneck. I'm in dilemma whether I ...
5
votes
3answers
4k views

Differences between “Unique Key” and “Primary Key”

What are the major differences between Unique Key and Primary Key in MySQL?
1
vote
1answer
477 views

PostgreSQL vs MySQL benchmarks when a 2nd level cache is included

Has anyone been able to find any benchmarks comparing PostgreSQL and MySQL performance when they are used by a system through Hibernate (or another comparable ORM) and the 2nd level cache (backed by ...
2
votes
1answer
164 views

Why does OS login not exist in the user table, but I can still login to MySQL?

mysql> select user, password, host from user; +------+----------+---------------------------------------------+ | user | password | host | ...
1
vote
1answer
2k views

Keeping 2 tables in sync

I need to write a trigger that will keep one table synced with 2 other tables. I have table_a, table_b and table_c on slave. The master has only table_a and whenever this table is updated on master, ...
1
vote
1answer
60 views

Assignment of Thread IDs?

I'm in the process of decommissioning an old mysql server. Of course there's a few things left connecting to it periodically people had since forgotten about. I turned on general query logging to ...
9
votes
2answers
9k views

kill all queries - MySQL

Sometimes during a SNAFU I have to run kill query xxxxxxx twenty or thirty times. Any sort of kill all command I am missing? On account of how I don't like typing.
1
vote
1answer
461 views

Finding Duplicates in LEFT JOIN

Here is my table structure: create table table_a (emp_name varchar(100), dept_name varchar(100)); insert into table_a values ('amar', 'finance'); insert into table_a values ('akbar', ...
4
votes
1answer
3k views

I want to know about the IOPS (I/O Per Second) and How it influences the DB CRUD operation

I want to get a dedicated server for my Database (MySQL) which runs InnoDB Engine. There is this option in InnoDB capacity that you can define the IOPS in it. Let's say that I have a SATA 72K-RPM HDD ...
4
votes
3answers
4k views

Disable trigger for just one table

Is it possible to disable a trigger momentarily but for just one table. For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but ...
1
vote
1answer
828 views

MySQL hang in load data infile command

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process? I had changed the command order (unlock before commit + replace some ...
1
vote
2answers
2k views

Restore data from remote server using “.sql” file

I have a ".SQL" file which contains data at a remote server. I could download that file to my local machine and import data in my local DB, but because of network issues, and the size of ".sql" file ...