MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
209 views

Unit testing with InnoDB transactions

So, I'm running some unit tests against some InnoDB tables in MySQL. Each unit test is wrapped in a transaction: setUp: SET autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ...
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 ...
2
votes
2answers
1k views

Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB?

Title says it all. However i have to enter at least 30 characters.
0
votes
2answers
72 views

I couldn't compile this little query (needs 2 tables)(mysql) [closed]

i have 2 tables as like seen : user table : id, userid , country friends table : id, userid , friendid so i want to get friendid rows from friends table where friends.userid == user.userid and ...
2
votes
1answer
87 views

if-clause in select query

I want to fetch some persons data from two tables via equi join. Some persons have address data and some have not. For those persons who haven't the addresses_id from table persons is NULL. For all ...
4
votes
3answers
5k 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 ...
9
votes
4answers
11k views

Why using innodb_file_per_table?

There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
3
votes
2answers
94 views

How to make MySQL 5.1 connection ignore kills?

According to MySQL 5.1's manual, section 13.7.6.4 KILL Syntax, when you kill a connection, a thread-specific kill flag is set for the thread. The flag will then be checked from time to time, even ...
2
votes
1answer
91 views

Change-set SQL optimization

I'm trying to clean up a change-set query that returns rows from a table that have changed since a given time and also do not exist in a known list of already previously returned results. select ID, ...
8
votes
2answers
7k views

PHP -> Mysql persistent connection pooling WITHOUT mysql_pconnect - Possible?

I have been trying to figure out a nice way of doing this for a while now. But have had a hard time finding the right pieces to do this. I am guessing this must be possible. To put it in simple terms ...
6
votes
1answer
17k views

MySQL case sensitive table names on Linux

Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't ...
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 ...
1
vote
1answer
66 views

Joins in MySQL database

I am a newbie to the MySQL database.I want to know different types of joins in MySQL and any link or any other kind of documentation with examples which will clear the doubts will highly appreciable.
2
votes
1answer
4k views

Concept to keep track of changes in all tables

In my current project I use about 10 MySQL tables (Doctrine as DBAL). I'd like to keep track of all changes in the databases (insertions, updates and deletions). At the moment there are two ...
2
votes
1answer
732 views

How to speed up audit trigger?

The task: write a trigger to log all updates within tbl1 table for future audit users' actions. The tbl1 table: id INT(11) y SMALLINT(6) country SMALLINT(6) -- really here ~20 fields but only some ...
1
vote
1answer
283 views

Creat Temporary Id in SQL Statement

I have a table id name 1 Alpha 2 Alpha 3 Beta 4 Charlie 5 Charlie I want to assign a temporary id at the run time in the SQL statement, So the desired result should be like myid name ...
2
votes
2answers
1k views

Trying to optimize query for performance (MySQL)

So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense. The basic ...
0
votes
1answer
36 views

MySQL Conversion to DATE Type

What is the difference between the return values for these two expressions in MySQL: date('2011-01-01') cast('2011-01-01' AS DATE)
2
votes
3answers
3k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
3
votes
2answers
291 views

Database choices for big data

I have many text files, their total size is about 300GB ~ 400GB. They are all in this format key1 value_a key1 value_b key1 value_c key2 value_d key3 value_e .... each line is composed by a key and ...
3
votes
2answers
695 views

MySQL cache is not getting hit by subqueries

I noticed something weird on MySQL query_cache behavior and I would like to know if this is a normal behavior. Let's say I have an item table ID | Item ----------- 1 | Item_1 2 | Item_2 3 | ...
1
vote
1answer
860 views

How does innodb_stats_on_metadata relate to query execution plan stability?

The MySQL documentation for innodb_stats_on_metadata says "It can also improve the stability of execution plans for queries that involve InnoDB tables." That is a little vague. What is an example ...
4
votes
3answers
241 views

Most efficient (or meaningful) way to index grid-based data

I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have ...
1
vote
4answers
4k views

Should I use the Infobright database or is there a better aggregation solution?

We are a MySQL front-end shop and have a system currently logging event data in a highly structured format. Think apache traffic log. We need to be able to aggregate counts of this data (cube) vis a ...
2
votes
2answers
176 views

Seconds Behind Master in Cloud Service

I have few mysql servers in replication master-salves in cloud service. The problem is even thought all configuration, mysql versions, server's resources are the same, still I have inconsistency in ...
4
votes
2answers
2k views

What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?

I wish to future-proof my application. What is the best type of identifier to use? I am considering: 64 bit Integer UUID 64 character SHA256 I am not developing a distributed system, so is ...
3
votes
1answer
158 views

Distribute MySQL in different cities?

We have our main MySQL server in city A. We have the bulk of our customer service staff in city B. The staff are complaining that the connection is too slow to the server. The options are: Have them ...
1
vote
1answer
243 views

time executing inserts + slow SSH

_ Hello, my setup is a slow SSH to some linux machine. From there I start a mysql terminal client and execute about 200k inserts (in a while loop inside a stored procedure). This takes about 3 hours ...
2
votes
0answers
588 views

mysql pre-start process (23901) terminated with status 1 [closed]

I installed a new Mysql server version 5.1 on machine which was running on 5.0. Now when I try to start the mysql server using command service mysql start it gives me an error start: Job failed to ...
1
vote
2answers
145 views

Is this an appropriate development environment MySQL backup strategy?

I'm a web developer and I use primarily my own laptop at work. We use an SVN repository for our project codebases, and deploy our changes to an internal development server for testing. Our system ...
2
votes
1answer
204 views

Query results in a strange explain output

So I have a medium sized query that hits 15 tables. The query takes 36 sec on MySQL to complete. I ran EXPLAIN on the query and one of the tables returned this. '1', 'SIMPLE', 'mount_adapters', ...
2
votes
2answers
76 views

Delete all highscores from a certain country

I have two tables: tbl_players tbl_highscores I need to delete all highscores that are registered from a certain country. My problem is that the language are set per player, rather than by ...
2
votes
1answer
950 views

How to use the 'select benchmark' command on a procedure

I am trying to check which of my procedures is slowest and thus needs optimization. I currently have around 160 procedures and about 30 functions. I am using the select benchmark() to check the ...
1
vote
2answers
184 views

MySQL CLuster SETUP

Good day! About MySQL Cluster, is it possible to set it up on only two machines? Is it advisable? We currently have MySQL replication setup on our two databases, and we would like to implement R/W ...
6
votes
1answer
1k views

Designing a database for a site that stores content from multiple services?

I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem ...
2
votes
2answers
1k views

Request suggestion for a database between MySQL and Postgresql

We maintain a list of all the SAN Switch Ports that are either free or allocated to hosts. Currently, we enter the information on the company portal. But we need to move if off to a database because ...
4
votes
2answers
385 views

Foreign key constraints with MySQL/InnoDB

I am try to do some performance testing with varchar foreign keys vs int foreign keys and was wondering how foreign key constraints effect performance of MySQL. I assume that they effect ...
11
votes
3answers
10k views

Is innodb_file_per_table advisable?

We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with innodb_file_per_table or leave ...
6
votes
2answers
964 views

Master-Slave Database. How to activate a secondary database whenever the primary database is down?

I need some idea on how to accomplish a master-slave database in MySQL. It's like, there's a primary server which the system connects then when it is down, it will use its local server. Here's the ...
11
votes
4answers
11k views

Mysql int vs varchar as primary key (InnoDB Storage Engine?

I am build a web application (project management system) and I have been wondering about this when it come to performance. I have an Issues table an inside it there are 12 foreign keys linking to ...
4
votes
1answer
6k 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 ...
2
votes
0answers
249 views

why is MySQL 5.1 on ubuntu 10.10 not starting? [closed]

I just installed a MySQL 5.1 server on Ubuntu machine. after installation the server should automatically start but it does not start and gives me an error start: Job failed to start . Even when I ...
4
votes
3answers
3k views

MySQL slow log configuration

Is there a way of making MySQL slow logs to start new log file every day ? At the moment its just a single large file, and have to grep lines for every day. It would be much more convenient to have ...
5
votes
3answers
17k views

InnoDB row locking - how to implement

I've been looking around now, reading the mysql site and I still can't see exactly how it works. I want to select and row lock the result for writing, write the change and release the lock. ...
-3
votes
2answers
619 views

SQL Server equivalent to MySQL UNIQUE index?

In MySQL (5.1) I am able to create a Index on a field that is type UNIQUE. This ensures that the field contains only unique items. Is there something equivalent to that feature in SQL Server 2008? ...
1
vote
2answers
947 views

Commercial database systems versus open source database systems [closed]

Can anyone tell me what are the reasons for buying a commercial database system and not using an open-source free version? The reason for my question is that: Commercial systems cost upwards of ...
5
votes
1answer
3k views

Bit vs. Boolean columns

Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way. Does it actually provide any benefit using a bit field for a boolean value? From what I ...
5
votes
1answer
5k views

mySQL query optimisation — multiple joins or select … where not in (select distinct…)?

Background I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
2
votes
1answer
798 views

InnoDB - How to get top locked tables and rows which are locked

I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?
2
votes
1answer
331 views

Redesigning a multi-instance MySQL server for performance and resiliency

I've got a single MySQL server which currently runs N separate MySQL instances on, which are rapidly growing beyond the capabilities of that box. Is it possible to have an active-active MySQL ...