MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
803 views

Too many records on database and how to handle it? MySQL is it good to clone?

I am doing a social network and came up with the following concern regarding database storage. Let's put facebook as an example, imagine 2,000 photos per user, just 1,000 users, it would equal to ...
1
vote
1answer
595 views

compare/sync databases from different computers

I have a server online and I have installed MySQL and database A server on it. I also have mysql installed on my local computer and have a copy of database A on it. I want to compare the contents ...
3
votes
1answer
532 views

Complex SQL Query

I'm trying to formulate a single query to return a nice list of a host's interfaces and the interfaces attributes from the Cacti MySQL DB. Here are the tables I am referencing: host_snmp_cache: ...
0
votes
1answer
90 views

Two slaves using the same server-id

I have a MySQL master with two slaves. Due to poor configuration both slaves had the same server-id. My questions are: Is the slaves' data fully synced with the master or did they miss transactions ...
1
vote
1answer
286 views

Database inserts/updates slow only in Production

We have a int/test/staging/production Mysql database setup. A Java application(using Hibernate) that periodically generates up to 200,000 updates/inserts (mainly updates by 20 to 1) completes in 10 ...
0
votes
1answer
40 views

Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

I am trying to create a new table new_table from t1 by comparing primary key values of another table t2 ie, 1) created table new_table 2) gave the query insert into new_table select a.* ...
-1
votes
1answer
588 views

When to use MySQL handler syntax?

http://dev.mysql.com/doc/refman/5.5/en/handler.html Seems to be very useful for PK lookups, but I haven't seen much information regarding this. What's the pros and cons of the handler syntax vs. ...
2
votes
1answer
408 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; ...
0
votes
0answers
47 views

mysql select query optimization [duplicate]

Possible Duplicate: mysql select query optimization I have two tables, testa and testb. CREATE TABLE `testa` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT NULL, ...
-3
votes
0answers
35 views

Doubt :: my sql performance tuning [duplicate]

Possible Duplicate: MySQL performance tuning I would like to optimize my MySQL DB and I identified some important points as follows: enabling query cache, using memory storage (I know ...
0
votes
0answers
303 views

Custom doctrine default character set [closed]

In my.ini mysql file, i have this : [mysqld] collation-server = utf8_general_ci character-set-server = utf8 When i create my database with "doctrine:database:create", my collation db is in ...
-1
votes
1answer
229 views

Exporting .txt file (tab-delimited) to mysql

Is there any ways that I can replace my exported .txt file in mysql database table with an updated .txt file without making duplicates? Step 1: after creating table in my database in mysql, I ...
3
votes
2answers
190 views

Is it always bad to use SELECT * FROM?

It is widely recommended NOT to use SELECT *. I understand this, as it is not a good idea to bring the entire row into memory, when only a few cells are needed. Is it still bad to use SELECT * when ...
1
vote
1answer
501 views

My SQL server showing different result with same query execution at different time

When I execute below query from MySQL server, it shows me different output for each time even though database is stable and not used by anyone except me. SELECT TABLE_NAME , table_rows FROM ...
3
votes
1answer
180 views

Referential Integrity in schema, cascading the renaming

In phpMyAdmin, while renaming a column/table which is referenced by some foreign key, I get an error. I can not rename a column until I remove all its relations. There should be facilitated integrity ...
4
votes
1answer
162 views

How can I improve this DB query? it takes too much time

The query is this: $today = $date->getTimestamp(); SELECT t.*, u.screen_name, u.profile_image_url FROM table AS t LEFT JOIN users AS u ON t.user_id=u.id_str WHERE ...
4
votes
1answer
511 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` ...
0
votes
2answers
335 views

Need advises for a new MySQL architecture

I need to deploy a MySQL-based application with the following requirements and would love to receive some guidance (any) as to how I could set things up: Scale of deployment 5 servers with a ...
1
vote
1answer
188 views

Why does a MySQL MEMORY-backed table take so much more space than on disk?

I have a single InnoDB table on disk, with ~1,107,423 rows and taking up 45.6 MiB disk space. The structure is: messageid int(11) signature varchar(255) When I create a MEMORY-backed version of ...
4
votes
1answer
81 views

Selecting Without Repititions

I have table with observations of objects moving along edges in a graph, this table has the following form: PK | TIMESTAMP | object_id | from_id | to_id where object_id is the id of some object and ...
2
votes
1answer
144 views

Ranking System with Different Categories

I want to create a system where the user can be ranked by several different factors. For example, the user competes in the following categories Wins Points I need to know what the position of the ...
2
votes
1answer
686 views

Update ranking on table

I have two tables: mysql> desc rank ; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
4
votes
1answer
478 views

I have multiple ibdata files : What should I do with them?

I have 3 different ibdata files on disk. What is the difference between them? Can I remove some of them? The goal is to migrate the files from C: to E:... I'm on MySQL 5.0.x... Directory of C:\MySQL ...
0
votes
2answers
67 views

View for a MANY_MANY_MANY relationship

Let's say I have 3 main tables: category, tag, url category and tag have a MANY_MANY relationship (category_tag). tag and url have a MANY_MANY relationship (url_tag). I would like for a given ...
2
votes
1answer
634 views

inner join on PK with extra criteria slow despite indices?

Given the two tables below I am struggling to understand: why is the third query slow even though first two queries are fast what exactly is EXPLAIN saying can I do anything to significantly speed ...
-1
votes
2answers
293 views

How to update an SQL column using a known perm link while appending a value from another column within the same table? [closed]

Larger view of the issue is here: http://i1268.photobucket.com/albums/jj572/LinuxOwner/question.png
6
votes
2answers
5k views

How to correctly implement optimistic locking in MySQL

How does one correctly implement optimistic locking in MySQL? Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but ...
0
votes
0answers
36 views

How does one correctly implement optimistic locking in MySQL? [duplicate]

Possible Duplicate: How to correctly implement optimistic locking in MySQL Our team has deduced that we must do #4 below when doing optimistic locking (all data is read and update using a ...
2
votes
2answers
117 views

HELP with SQL WHERE syntax

I have to pull out a range of entries of a database, all based around two pieces of data. In itself thats fine. An example of the data is: Record ID Tags 1 1423 Day, Sun, Warm 2 ...
1
vote
0answers
115 views

How to create multi volume file backup using symantec backup.exec

I am using symantec backup .exec for backup and restore in mysql server . Now I am in need to take larger database backup in tape .But it exeeds the tape storage and I am trying to create a multi ...
0
votes
1answer
154 views

SQL Table Indexing - Database Overloaded With Queries

I'm a bit of a newbie to SQL who has been thrown in on the deep end with a server that gets about 2 million queries an hour. I'm trying to optimise our database and I noticed this in our database ...
2
votes
2answers
178 views

measure the traffic throughput on mysql table

In order to split mysql database into two different databases on different hosts. I would like to know few things: what is the size of the tables. what is the traffic throughput to those tables. ...
3
votes
4answers
4k views

mysql : inner join takes 3 minutes

I had a similar question open on SO for postgres - now having the same issue with mysql.. I have two tables - Table A : 1MM rows, AsOfDate, Id, BId (foreign key to table B) Table B : 50k rows, Id, ...
4
votes
1answer
161 views

Do isolation levels only apply to SELECTS and not UPDATES? [closed]

Scenario that might demonstrate different behavior for SELECTS depending on the isolation level: 1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete 2) 0:02 Thread B ...
1
vote
1answer
1k views

How to know the current log sequence number in 5.0.X

I would like to test the optimal value for iblog files on my mysql which is 5.0.77 I found the below procedure in mysqlperformance blog: ...
3
votes
2answers
28k views

Select column names whose entries are not null

I would like to have a list of those columns of a table that have at least one not-NULL data entries in them. In other words, I would like to get the column names for which the following returns at ...
1
vote
2answers
2k views

Cache all mysql table

Maybe it sound's like i'm an idiot... But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been ...
1
vote
1answer
1k views

SQL query to get the syntax of an existing table

Is there any SQL query to get the syntax of an existing table in my MySql database? Intent: Iam in the early stages of working with databases and I am using the MySql workbench for creating tables ...
3
votes
1answer
1k views

does max_connect_errors not check Aborted_connects?

In MySQL (5.5) which status var reflects the current number of connection errors that are compared against max_connect_errors by the server? I thought it was Aborted_connects but in my testing I can ...
2
votes
1answer
2k views

InnoDB Performance Tuning

I'm new to database administration so please excuse any omissions of what would seem like basic info to provide for this problem. I'm a RoR developer and our team is currently short a DBA, so we're ...
0
votes
1answer
804 views

MySQL daemon keeps terminating unexpectedly

The MySQL daemon on my CentOS server keeps crashing, i got the logs from /var/logs/mysqld but still i am not sure how to fix this: 121114 16:22:56 mysqld_safe mysqld from pid file ...
1
vote
1answer
237 views

CREATE TABLE query with SELECT having NOT NULL fields

I tried searching on this topic but I couldn't find any help on it. I want to write a CREATE TABLE query with SELECT having NOT NULL fields. The query I am using to create the table is as follows: ...
1
vote
1answer
6k views

Why does max_used_connections status not get refreshed automatically once it reaches max_connections, even though the Threads_connected falls down

I always wonder, why does the max_used_connections value not come down, once it reaches the peak value of max_connections? I always flush the status and bring down this value to avoid DB connection ...
4
votes
2answers
535 views

MySQL check duplicate with group by using wildcard?

+----+--------------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+--------------+-----+-----------+----------+ | 1 | Ramesh Olive | 32 | ...
-1
votes
1answer
62 views

What is an accepted method of keeping old data in MySQL?

I think this may be more of a programming question, but since it directly relates to MySQL, I wanted to ask it here. Some background: I am running an application whereby users can take tests using ...
2
votes
1answer
211 views

Does having Default Database Engine MyISAM and keeping InnoDB tags in my.ini create any problem?

Recently I am getting table corruption issue very often and every time when I check the logs it says "Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL" ...
2
votes
1answer
286 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
108 views

Created databases and inserted data on MySQL slaves in Master-Slave replication - how to fix?

I got MySQL Master-Slave replication running on 1 master with 2 slaves. SHOW MASTER STATUS / SHOW SLAVE STATUS says they are in sync, but databases have been created on the slaves and filled with data ...
2
votes
1answer
705 views

Monstrous query hanging on “copying to tmp table” in MySQL

I have a huge query being run by one of my developers which seems to hang no matter which performance tunables I adjust. Though I'm somewhat of novice at writing SQL queries, at first glance the ...
1
vote
1answer
652 views

processlist showing '%' as host from trigger

I'm trying to populate a certain column in a table with the host/IP of the caller. I have an insert trigger on the table which sets the column like so: CREATE TRIGGER access_insert_trg BEFORE INSERT ...