Tagged Questions

MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

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 ...
7
votes
3answers
653 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
725 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 ...
16
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
570 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
457 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
488 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
303 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
960 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
461 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
580 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 ...
4
votes
3answers
1k views

Connecting to MySQL database from Analysis Services

I need to know whether it is possible or not to get data from a MySQL database and create a Cube in Analysis Services 2008. If so, tell me how to connect to MySQL Database from Analysis Services.
9
votes
3answers
2k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...
4
votes
3answers
590 views

How can I import/export a MySQL database without redirection?

I've been told that redirection via shell (e.g. < and > ) can (not always) affect the collation of a database (.e.g utf8). How can I import an export without redirecting so that I don't have to ...
6
votes
3answers
5k views

Avoiding temporary tables while sorting by a column in a different table

So I have this MySQL InnoDB query here: SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt FROM stories s LEFT JOIN votes v ON s.vid = v.vid WHERE s.lv = 1 AND s.status = 1 GROUP BY s.vid ...
1
vote
1answer
387 views

Use order from another select clause

So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one. I have this statement to get the vid column order ...
3
votes
1answer
326 views

MySQL export/import yields error

When trying to export and import an 58G database yields the following error: ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes Dump created using mysqldump -q --opt ...
2
votes
2answers
351 views

How to get a user's friends' names?

I have a users table and friends table. The user with ID 1 has 4 friends (with IDs 2,3,4,13). I want to get names and surnames of users 2,3,4 and 13. I can to it with standard queries but it will slow ...
4
votes
2answers
692 views

How to retrieve granted privileges without access to the database “mysql”?

As a MySQL user without access to the mysql database, is it possible to retrieve my privileges?