All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

7
votes
1answer
786 views

Application Settings in Database

In the past I have stored all generic settings and configuration items such as: custom application title, home address, version, debug, etc.. all in a "settings.php" file that I would include into all ...
7
votes
1answer
6k views

Why does that query cause lock wait timeouts?

From time to time, I find a lot of these errors in my PHP error log: MYSQL.1213: Deadlock found when trying to get lock; try restarting transactionSQL The problem persists for about 2 or 3 minutes. ...
7
votes
1answer
7k views

How do you disable anonymous login? - MySQL

According to the MySQL documentation, you can harden a MySQL server by adding passwords, or removing the anonymous accounts. If you want to prevent clients from connecting as anonymous users ...
7
votes
4answers
5k views

Incomplete mysqldump

I'm trying to run mysqldump to create a database snapshot, and I'm finding it will randomly stop midway, without reporting any error. My database is relatively small (about 100MB) and is using InnoDB. ...
6
votes
1answer
1k views

MySQL Import CSV with Multiple Field Separators

I'm trying to import this CSV file into MySQL that appears to be optionally enclosed by more than one character. Unfortunately, MySQL only supports one character as a field separator. I am stuck ...
6
votes
1answer
1k views

How can I get status messages during a large re-indexing operation with MySQL 5.5?

I get large data transfers periodically that I used to completely replace the contents of a MySQL table. Each time I do this, I truncate the table, disable keys, add several million rows of data, and ...
6
votes
4answers
18k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
6
votes
2answers
12k views

UPDATE table based on the same table

I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same ...
6
votes
2answers
616 views

Estimating distribution of row access in mysql

I am trying to get an idea of the size of the "hot data" part of a rather large table, and I was wondering if this could be done directly in mysql. I know that with the percona version of mysql, I can ...
6
votes
2answers
2k views

Amazon RDS I/O requests - 1 query = 1 billable I/O?

I have an InnoDB database that I'd like to migrate onto Amazon RDS. My current implementation, hosted on my own server, shows roughly 8M queries per month. The RDS site say that I/O Rate $0.10 ...
6
votes
1answer
3k views

What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?

What are the drawbacks of using Galera Cluster instead of regular Master/Slave Replication? Galera's 0 slave lag time, Synchronous replication and no Single point of failure seem very appealing then ...
5
votes
2answers
3k views

How to insert values in junction table for many to many relationships?

I have three tables in database: trips(trip_id(pk), trip_name(unique), user_id(fk)) places(place_id(pk), place_name(unique)) trips_places_asc(trip_id(fk), place_id(fk)) Since many trips can ...
5
votes
1answer
7k views

How can I find who holds the lock based on the hex-dumped?

I've been reading the Diagnosing MySQL InnoDB Locks article. Karl E. Jørgensen writes on 2008 so I'm consfusing it is in effect. I would like to supply a snippet of the SHOW ENGINE INNODB STATUS: ---...
5
votes
1answer
5k views

Rebooting a MySQL Replication Slave

For the first time since it was set up I need to reboot a read-only MySQL replication slave. I found this article about downing a slave for maintenance (albeit he's just describing stopping the mysql ...
5
votes
2answers
1k views

MySQL queries are 10 to 100 times slower after OS reboot

I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to ...
5
votes
4answers
3k views

Exporting/Importing a large MySQL database without mysqldump

I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy. After running a simple --all-databases dump succeeds, but the import ...
5
votes
3answers
2k views

CPU usage on RDS instance monotonically increasing with no change to query volume

I have two RDS instances: a R/W master and a read-only replica. On 29th June, the replica stopped registering replication data - not sure if this is related. On July 3rd, the master's CPU usage ...
5
votes
3answers
2k views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
5
votes
1answer
340 views

Does MySQL exectution plan depend on available indexes?

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available? Motivation:...
5
votes
4answers
3k views

Slave SQL thread got hanged

We have a master - slave setup with ROW based replication. We are seeing huge delay's on the salve even though there is no activity running wither on master or slave. When we looked in, we observed ...
5
votes
1answer
2k views

Possibilities to speed up InnoDB INSERTs and UPDATEs

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower. I'm monitoring when, and it ...
5
votes
1answer
807 views

Database as a service (DbaaS) over dimensions--performance, scalability, & reliability?

MySql Database as a service: Amazon Xeround Another eg. PostgreSQL Database-as-a-Service. You can get the list of similar products here. Does anyone have details on their performance, reliability,...
5
votes
1answer
3k views

Find out MySQL index size for a concrete index

In MySQL, for InnoDB engine, how do I find out the size of one of the indexes I have in the same table ? I know I can select the index_length from information_schema.tables, but this gives the total ...
5
votes
2answers
966 views

Best way to design a database and table to keep records of changes?

I need to setup a history feature on a project to keep track of prior changes. Let's say I have two tables right now: NOTES TABLE (id, userid, submissionid, message) SUBMISSIONS TABLE (id, name, ...
5
votes
2answers
42k views

Grant table privileges to a user connecting from any host

I have a user account - let's call it 'wordpress' - that I need to allow to access a few catalog tables in another e-commerce database on the same server. I've configured the user with three host ...
5
votes
3answers
12k views

Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*

Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions: Attempting to view or run ...
5
votes
2answers
738 views

More table or more database for better performance?

Lets say 1 website need 10 database table to work and i want a database pool that 40.000+ website will be inside. Which option is better for server performance? 1) 400 databases each have 1000 table?...
5
votes
1answer
11k views

SELECT LIMIT 1 per column value?

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 | ...
4
votes
4answers
3k views

Why would I use the MySQL “system” command?

What can I do with the MySQL client's system command? Why does it exist? The most productive use I've seen is to look around the file system (e.g., to remember the file name you want to SOURCE or ...
4
votes
1answer
670 views

How do I replicate Mysql DB server to new server?

Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration). I want to replicate ...
4
votes
2answers
2k views

Does an empty column value occupy same storage space as a filled column value?

I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not ...
4
votes
3answers
770 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
4
votes
2answers
2k views

How to keep one query from slowing down entire database?

Using MySQL 5.0.x, we have a process to generate benchmarks for our business that consolidates a bunch of data, then pull out data based on certain criteria. This process used to take 18 hours, it's ...
4
votes
2answers
4k views

Large files in wamp\bin\mysql\mysql5.5.24\data directory

I have discovered these files located in the wamp\bin\mysql\mysql5.5.24\data folder on my Windows 2008 server. They are consuming about 22 GB on a 40 GB drive. I am using a LMS called moodle, that ...
4
votes
1answer
4k views

Is Percona xtrabackup the right choice for my 500GB MySQL backup?

I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...
4
votes
1answer
9k views

Deleting & Reclaiming space from InnoDB table

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete ...
4
votes
1answer
2k views

How do uninitialized variables differ from NULL in Mysql?

Related, please read first for context: MySQL stored procedure: loop through table, delete rows. Logic problem: won't exit loop because of LIMIT option in query I think this is really strange. I ...
4
votes
2answers
4k views

How to convert mysql database charset to utf-8

I have a vb forum with a huge amount of data I need a way to convert this forum database tables charset with thier data from latin1_swedish_ci to utf8-general-ci How to do this ?
4
votes
1answer
3k views

Slow insert performance in mysql innodb table for solr indexing

I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow. ...
4
votes
1answer
11k views

how to retrieve rows of different dates using IN clause

My table structure: +-----------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------...
4
votes
1answer
2k views

Which are the minimum privileges required to get a backup of a MySQL database schema?

Which are the minimum privileges required to get a backup of a MySQL database schema? I am interested only on the schema, not on the data. Are SELECT privileges required?
4
votes
3answers
5k views

InnoDB insertion faster

I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
4
votes
1answer
6k views

Recover the disk space after deleting rows from table

I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the ...
4
votes
2answers
3k views

Can a trigger access the query string?

I'm thinking about using triggers as a logging mechanism in MySQL (v. 5.1) and therefore I'd like my trigger to get the query string in order to store it in another tabe. I couldn't find anything ...
4
votes
2answers
11k views

MySQL got signal 11 error and slave is down

My slave server is down and mysqld unable to start. What should I do? Below follows the error logs: 2014-09-26 12:51:53 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2014-09-26 12:51:53 ...
4
votes
1answer
7k views

Reloading MySQL my.cnf changes

Do you have to restart MySQL to edit changes? Or can you edit them on the fly and restart MySQL or make the changes inside MySQL? This server runs 100s of website that are active and I don't really ...
4
votes
2answers
725 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 ...
4
votes
1answer
4k views

Setting up replication for MyISAM tables?

Before setting up a replication within MySQL, the data first needs to be transferred to the slave. Since MyISAM tables can be copied as files. Would it be okay to copy MyISAM files, .myd .myi .frm, ...
4
votes
1answer
1k views

InnoDB “corruption”

I use: 5.1.63-0ubuntu0.11.10.1 Apache/2.2.20 (Ubuntu) MySQL client version: 5.1.63 The system is continuously displaying the following error during reading data from a MySQL database: ...
3
votes
2answers
1k views

Is there a Successor or an Alternative to mk-parallel-dump?

Just read about mk-parallel-dump in a possibly-outdated version of High Performance MySQL. Sounded great, but when I went to download it I found out that it's deprecated with no equivalent command in ...