MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
3answers
867 views

Database Performance Tuning

I have a MySQL backend, MS Aaccess front-end system that is nice and responsive over a local network but becomes sluggish over a broadband connection. I haven't done any performance tuning as of yet. ...
4
votes
2answers
202 views

Concurrency question?

I have a MySql (5.5) database with a MS Access frontend. If two users are viewing the same record at the same time how can I make each user aware of each other? For example how would I go about ...
2
votes
1answer
275 views

MySQL Update: why would rows matched change based on the set clause?

I ran an update query joining across a huge number of tables that updated one field, and it matched 29k some-odd rows. Running the same query with only an addition in the Set clause increased the ...
3
votes
1answer
89 views

Is it possible to find the values of MySQL variables if I do not have SELECT privileges on mysql.user?

I am getting a 'msyql server has gone away' error sometimes when doing a large binary insert, so I wanted to check the settings on mysql, things like wait_timeout and max_allowed_packet. However show ...
0
votes
1answer
136 views

Derived value not calculating as expected using combination of FK and Triggers (MySQL)

I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints. There are 3 tables involved in this arrangement responses ------------------------ ...
4
votes
3answers
346 views

Help with complicated MySQL query

I asked an earlier question about modeling MySQL data modeling help. The recommendation was the diagram labeled Multiple Tables for Role and Sponsor. Now I'm trying to write the queries and ...
4
votes
1answer
833 views

Yum update mysql from 5.1 to 5.5

I was just in the process of 'yum updating' a server. One of the updates its suggesting to carry out is an upgrade from mysql 5.1 to 5.5. The mysql server operates in a master-master replication ...
5
votes
1answer
445 views

Dealing with temp tables when I have not control over db variables

I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically. I am wondering if there ...
5
votes
1answer
2k views

What are user connections - when are the created and destroyed?

We've been getting an intermittent error on out site: Warning: mysql_connect() [function.mysql-connect]: User '557574_prod' has exceeded the 'max_user_connections' resource (current value: 10) ...
3
votes
2answers
653 views

Should I move to InnoDB during a planned migration?

I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...
7
votes
3answers
1k views

I am using the MEMORY storage engine but MySQL still writes to my disk…Why?

I am using the MEMORY Engine for all tables associated with a particular MYSQL query because speed of access is paramount to my project. For some reason, I have noticed that a large amount of disk ...
8
votes
4answers
1k views

How can I optimize this MySQL query further?

I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
3
votes
5answers
3k views

Why is MySQL caching data on our dynamic PHP site?

We are having an annoyance with our PHP/MySQL site in that it seems to cache data from the database and our PHP pages are showing out of date records. In fact, there are pages that link to URLs that ...
10
votes
3answers
2k views

Why don't my .my.cnf settings work?

I have a .my.cnf file in my home directory on my desktop that includes: [dbid] user = myusername password = mypassword database = dbname host = server.location.com If I recall correctly, before ...
3
votes
1answer
171 views

Mysql constraint problem

I'm using MySQL5.5 and for this question I have 3 simple tables t1, t2 & t3 designed like this: +------------+ +------------+ +------------+ | t1 | | t2 | | t3 | ...
1
vote
1answer
169 views

What is the 'repair code' in MySQL?

OK I was adding an index to a table, and I checked on progress using show processlist and under state it output Repair by sorting. From the MySQL docs: Repair by sorting The repair code is ...
6
votes
3answers
648 views

Do I need to add a new single column index to a table if a multi-column index on that field already exists?

I've a table with a multi-column UNIQUE index on _job_id__ and __keyword_id__. Would I also need to add another index to __job_id__ if I have a frequent query which performs a GROUP BY on that ...
7
votes
3answers
3k views

Does making a field unique make it indexed?

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly ...
4
votes
3answers
11k views

Set “lock wait timeout” in MySQL

Where would I set the maximum time a query will wait for a lock in MySQL 5.0.68 before timing out?
20
votes
4answers
6k views

Why does MySQL not have hash indices on MyISAM or InnoDB?

I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
2
votes
2answers
3k views

MySQL won't start after upgrading to PHP 5.3

Just upgraded to PHP 5.3 with the latest version of mysql, but now mysql won't start. Below is mysqld.log 110516 22:24:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 110516 ...
7
votes
3answers
11k views

How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline?

MySQL Server 1 is running as Master. MySQL Server 2 is running as Slave. With both DBs online, they are in "perfect sync". If Slave goes offline, there's no problem if Master still online; they will ...
5
votes
1answer
724 views

Does MySQL still handle indexes in this way?

Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index. If a ...
8
votes
2answers
8k views

How do I show the binlog_format on a MySQL server?

How do I show the binlog_format on a MySQL server? And if I dont like it how do I set it to XX permanently? Where XX is STATEMENT, ROW or MIXED.
1
vote
3answers
123 views

Can I automate the “on” statement from MySQL queries?

Background In the process of QA/QC, I am writing lots of queries with multiple joins. I am using MySQL. I would like to know how (if) I can simplify join statements, e.g. by setting default fields ...
6
votes
5answers
2k views

From a set of values, how do I find the values not stored in a table's column?

I have a table which will potentially store hundreds of thousands of integers desc id_key_table; +----------------+--------------+------+-----+---------+-------+ | Field | Type | ...
4
votes
1answer
219 views

What program to use to change settings on db servers?

Having decided to set up a Master-Slave replication I found this article, which seems exactly what I want. So I fired up the MySQL Console in Navicat to give everything a try, and the commands do ...
15
votes
3answers
20k views

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
9
votes
2answers
8k 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.
4
votes
3answers
569 views

Separate databases for front-end and back-end

The site I am working on has a lot of backend/cron activity, so there are between 5 and 15 queries being executed every second of the day. This can slow down page loads by a second or more. I was ...
3
votes
1answer
2k views

Amazon SimpleDB or Amazon RDS?

I'm at a deciding point in my project of whether to implement the database using SimpleDB or Amazon RDS. The database needs to be accessed through an iPhone app and as well through a website. Here are ...
3
votes
1answer
88 views

Pure (My)SQL transformations

I'm trying to promote the usage of a database versioning system (http://code.google.com/p/flyway/) on the project I'm currently working on - and to that end when we make changes to the database, we're ...
4
votes
4answers
456 views

How much will using a varchar(15) PK affect my tables performance?

I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. ...
5
votes
2answers
261 views

Database replication with ability to revert to previous states

I want to setup a development database such that it'll initially be a mirror of the database used in production. At fixed points during the day (maybe midnight or something) and/or manually, I want ...
0
votes
1answer
911 views

Entity Relationship Model for linked tables

Certainly a noobish question, but I'll ask it anyway. Perhaps someone else will be thankful. ^^ I am getting warmed up with MySQL and I try to understand the concepts of creating well structured ...
2
votes
3answers
2k views

How do you restore a particular table in MySQL?

One of our users has deleted a table by mistake. How can I restore only that table?
6
votes
4answers
487 views

Purpose of IDs in a User Table Database

In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
4
votes
1answer
302 views

How do I use the POSIX character classes upper and lower to search text in MySQL?

I would like to search a body of text to find out when a specific word has been upper cased. MySQL has a REGEXP function with the POSIX character class [[:upper:]], but for the life of me I can't get ...
4
votes
2answers
5k views

How do I find the median value of a column in MySQL?

I can only imagine doing this with two database queries. The first finds the number of rows in the database, and the second selects with an ORDER BY on the column I am interested in and LIMIT X, 1 ...
1
vote
1answer
185 views

MySQL queries in SQL-Server?

How can we best use our existing MySQL queries with SQLserver? Alternately, what type of changes do we need when we are converting our database server from MySQL to SQLServer?
3
votes
2answers
394 views

What are the key points to avoid table locks?

MySQL is using at my company and most of the database administration works on trying to solve the table locks. What are the key points to avoid table locks(I mean at database side and even at software ...
1
vote
1answer
414 views

Can I export a MySQL schema to latex? [closed]

I have asked this question on SO before, but did not get a good answer. I would like to be able to export a MySQL schema to a set of Latex tables. phpMyAdmin does this, but it is buggy and does not ...
4
votes
2answers
2k views

Trigger in MySQL

Please help me for few question in SQL.. I want to create the trigger which prevents insertion in the reservation table of flights (numflights), which does not exist. Tables ...
1
vote
1answer
952 views

mysql /dev/shm question

I have this in my.cnf: tmpdir = /dev/shm and I'm getting this in my error logs: [23-Apr-2011 10:22:08] WordPress database error Incorrect key file for table '/dev/shm/#sql_30f1_0.MYI'; try to ...
1
vote
0answers
460 views

Which database technology for big structured data? [closed]

Scenario: Think you have got 100TB of text in 200 tables. This is structured related data. compareable to dbpedia only more data. Any really relational and distributed and performant database would do ...
1
vote
2answers
578 views

Sizing MySQL Database Servers for Drupal

What are some guidelines to follow when it comes to sizing MySQL Servers? CPUs, RAM, Disk Space, Clock Speeds, etc? We're going to go with either the community edition or the enterprise edition, and ...
6
votes
4answers
5k views

MySQL gives “Out of Memory” error when inserting a large file. From where is this filesize limitation arising?

I'm using a MySQL database to store files. The table I'm using is structured as follows: +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default ...
3
votes
1answer
67 views

Database appearing in one tool but not another

Can anyone think of a reason why a mySQL database would be accessible from one tool (phpMyAdmin, logged in as db_user) but not from another (Sequel Pro, logged in as db_user). The user is the same in ...
2
votes
1answer
1k views

No results for match…against in boolean mode search with fulltext index (MySQL)

We have a column with a fulltext index, and are matching 'asp.net' against the column in boolean mode. We know there are records where this column contains the value 'asp.net', but the query is ...
6
votes
3answers
7k views

How do I remove backslashes from records in MySQL

Long story short, I have backslashes in my data due to PHP magic quotes. I have straightened out what was wrong there, but have some records now which have unnecessary backslashes. 1 | This is an ...