MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
2answers
786 views

MySQL UPDATE Performance Issue

I'm running out of ideas on a performance bottleneck I'm seeing on one of my projects. The table in question is pretty small, with only 5396 records. However, I'm seeing queries like this show up in ...
8
votes
3answers
6k views

One Big Database vs. Several Smaller Ones

We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g., ...
3
votes
1answer
495 views

How can I get MySQL to use the value returned by a function as a default?

I have a pre-existing table that has a column to that can hold a UUID. I'd like to be able to define a default such that new rows coming in (from legacy systems) will still get a UUID defined, via ...
5
votes
1answer
2k views

Error on import of mysqldump file - Illegal double value found during parsing

I am currently getting the following error while trying to import / restore a mysqldump file: ERROR 1367 (22007) at line 445: Illegal double '1.79769313486232e+308' value found during parsing I ...
11
votes
3answers
5k views

Best way to design tournament database

I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase. I have created a mockup ...
2
votes
1answer
433 views

MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP

In MySQL InnDB engine I created TableColumn with data type TIMESTAMP and put there as default value CURRENT_TIMESTAMP, but this value are always changed to CURRENT_TIMESTAMP when I update that by ...
2
votes
1answer
3k views

When are we supposed to set default-character-set for the client?

I'm trying to understand when I'm supposed to set/force default-character-set for the client. The documentation states: You can force client programs to use specific character set as follows: ...
4
votes
2answers
26k views

“Copying to tmp table” extremely slow

This is my example of query: SELECT nickname, CASE class_id WHEN 1 THEN 'Druid' WHEN 2 THEN 'Necromancer' WHEN 3 THEN 'Mage' WHEN 4 THEN 'Priest' WHEN 5 THEN ...
6
votes
2answers
2k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
3
votes
2answers
974 views

MySQL vs SQLite on Amazon EC2

I have a Java program and PHP website I plan to run on my Amazon EC2 instance with an EBS volume. The program writes to and reads from a database. The website only reads from the same database. On ...
5
votes
4answers
3k 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. ...
5
votes
2answers
4k views

Write a slow query to test slow query logging?

Is there a simple query that would take > 2 sec so that I can test the slow query logger? I am looking for something like a generic recursive or iterative statement.
4
votes
2answers
244 views

MySQL aggregate anomaly

I was checking selectivity of some columns for an index. Where is this "ignore what I give you" behaviour documented? This gives 4,851,908, 4,841,060, and 1,000,052 SELECT COUNT(*), ...
4
votes
2answers
3k views

Incremental backups using MySQL Workbench

I need to start doing regular backups of my databases, and I'd like them to be incremental; that is, only backing up the tables that have been changed. I use MySQL Workbench, and I've used the export ...
2
votes
1answer
496 views

Can I choose which CPUs / Cores to be used by MySQL?

I have a Windows VPS (Windows 2008 R2 x64) that runs a couple WordPress blogs and other few ASP.NET things. The VPS runs 2 CPUs (for example). With ASP.NET, in such situation, I usually set the app ...
3
votes
2answers
2k views

MySQL encryption

I am thinking about encrypting some fields in MySQL database using MySQL's encryption functions. I can't find any good sources on how to properly implement it. Has anyone done this and have a good ...
1
vote
1answer
1k views

Help optimizing MySQL slow query

i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort" query: EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* ...
9
votes
7answers
48k views

importing large SQL file phpmyadmin

I am trying to import this SQL file in phpmyadmin and got this message #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right ...
1
vote
4answers
6k views

Connect to MySQL (WAMP) via SSH

Hi I installed the latest WAMP on Windows 7. I would like connect via SSH to MySQL database and create dump. As SSH client I use PUTTY. I tried using the root account and also other accounts, but I ...
4
votes
2answers
996 views

High Disk IO, How to mitigate?

I'm a programmer, not a DBA. Be gentle :) Overview InnoDB, MySQL mod_perl script, persistent connections script called every 20 seconds by thousands of users Problem High Disk IO (presumably ...
2
votes
5answers
554 views

Help optimizing query in the MySQL slow-query log

Our database is set up so that we have a credentials table that hold multiple different types of credentials (logins and the like). There's also a credential_pairs table that associates some of these ...
1
vote
1answer
2k views

MySQL Very Slow for REALLY Simple Operations

Something appears to be wrong with my MySQL even with very, very simple operations on almost empty (~100) tables E.g. DROP TABLE IF EXISTS hookup_gender takes 10+ seconds mysql> show processlist; ...
9
votes
3answers
5k views

Monitor MySQL activity per database?

What is the best way to monitor activity on each database of my server ? I tried mtop/mytop which display activity in real time but aren't very practical to see all the queries executed on it. I ...
3
votes
1answer
578 views

Configuring MySQL slave to use SSL

I am trying to set up a MySQL slave to use replication via SSL. The my.cnf file contains the following: server-id = 1012 master-host = host master-port = port master-user = user master-password = ...
3
votes
2answers
5k views

MySQL “server has gone away” using SQLyog

I'm trying to copy a database from a server to my local machine, I've installed Wamp 2.2, mysql 5.5.16, but during copying db I m getting "mysql server has gone" 2006, I've tried to fix it by changing ...
1
vote
1answer
6k views

Function returning “No data - zero rows fetched, selected, or processed” in MySQL 5.5

I have upgraded MySQL version to 5.5.16 After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed. In those functions, cursors are there, but ...
1
vote
2answers
957 views

Will improper bin-log purging leads to deletion of mysql database

We had master to master and master to multiple slave environment in one of our client location. In one of the slave Db host we had disk space issue with bin-logs. At first i deleted the bin-log files ...
4
votes
1answer
3k views

How do you read MySQL InnoDB Monitor output?

I am new to DB Administration. I am trying to figure out the cause of deadlock exceptions, but I am unable to make sense of the logs. The log says that "transaction 1 is rolled back". Why is this the ...
2
votes
1answer
1k views

Does mysqld --ndb-cluster need to be run on MySQL Cluster data nodes?

I have my management node and sql node on the same server, and 2 data nodes (3 machines in total). Do I need to run ./mysqld --ndb-cluster (or /etc/init.d/mysql-server start) on 1) the data nodes, or ...
7
votes
2answers
3k views

How can I disconnect clients from MySQL?

I need an efficient way to disconnect all clients with a given username from MySQL. I thought about changing the users password but I think that is only checked when the connection is made. Ideas?
1
vote
1answer
425 views

MySQL: How do I limit or prevent caching for specific tables?

I'm currently working on a database schema for tables to hold user information on a website with integrated forums. Right now my idea is to have all the data in two separate tables: one table with ...
3
votes
1answer
2k views

Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts?

We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM ...
1
vote
0answers
122 views

MySQL Cluster — Storing table data on data nodes [duplicate]

Possible Duplicate: MySQL Cluster — Storing table data on data nodes I have setup MySQL Cluster with the MySQL server node and the Management node on a single server, and 2 data nodes ...
2
votes
1answer
811 views

MySQL Cluster — Storing table data on data nodes

I have setup MySQL Cluster with the MySQL server node and the Management node on a single server, and 2 data nodes (each on a different server for 3 servers in all). After installing, I run ndb_mgm ...
2
votes
1answer
1k views

MySQL: trying to create root user

upgrading mysql database to 5.5 the root user has disappeared. I'm trying to create it again using this commands: javier@javier-mbp:~$ sudo pkill mysqld [sudo] password for javier: ...
3
votes
2answers
1k views

Concurrently update with limit mysql

I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db. After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned. I ...
6
votes
1answer
1k views

Using FORCE INDEX

I was in a presentation recently given by a Facebook MySQL engineer and he mentioned there that using FORCE INDEX reduces I/O. It was something to do with the MySQL Query Optimizer not needing to go ...
8
votes
1answer
4k views

What is the usage of performance_schema in MySQL 5.5?

I have upgraded to MySQL version 5.5. There, I had observed performance_schema in the schema list. What is the usage of this new schema..? How can we monitor using this schema..? What are the ...
5
votes
2answers
11k views

Optimizing MySQL query_cache_size

Is my query_cache_size too big? mysql> SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+-----------+ | Variable_name | Value | ...
1
vote
1answer
726 views

storing a mirror of IMAP/pop3 folder/inbox in a local DB, what choices are there?

I have a email inbox that will soon hit 4 million messages, I have an app that runs different policies over the inbox to associate emails with each other much like the conversation plugin for ...
3
votes
1answer
617 views

Solution for deploying multiple instances of Django Server with Database replication

have written a Django application that reads / writes data into a postgres database, at the moment I have been deploying one of these devices per client. I am faced with having to deploy multiple ...
3
votes
1answer
61 views

MySQL Download Question

I want to download MySQL and use it on CentOS. But I found the downloading selection menu on the MySQL website is confusing. Which one should I choose? See the attached image. Thanks.
8
votes
6answers
3k views

What could be causing strange query timeouts between PHP and MySQL?

I am the senior developer on a Software-as-a-Service application used by many different customers. Our software runs on a cluster of Apache / PHP application servers, powered by a MySQL backend. On ...
2
votes
1answer
2k views

Host Name Resolution in mysql.db and mysql.user tables

We're seeing something peculiar about the way MySQL does name resolution -- or peculiar to us at least (-: We have two data centers, A and B. A is primary, B is used in case of failover. We have two ...
3
votes
2answers
282 views

MySQL: 2 separate queries run very fast, combined query with OR runs very slow

EXPLAIN SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest, firewall.name as fw, proto.proto, service, action.action, sum(count) as hits, max(date) as lastused, rule_uid.rule_uid FROM ...
1
vote
1answer
181 views

Appropriate Windows version for Mysql

I have a HP ProLiant DL380 G5 server and I would like to install mysql on it . My questions are: Which MySQL version should I install from this link for better performance, 32 bit or 64 bit? Which ...
3
votes
2answers
455 views

PHPMyAdmin for multiple servers w/ single pma database

We have PHPMyAdmin setup for our various MySQL instances. The annoying part is the current setup is requiring an individual pma database on each instance. Because of this the severs that are slaves ...
3
votes
2answers
671 views

MySQL corruption even after full dump and re-import

We have a production issue where MySQL has started spitting InnoDB corrupt page errors and falling over. The error message seem to relate to indexes so I assume the data itself is ok and when I ...
4
votes
1answer
961 views

How to best store the Google Web Ngram data?

This is a continuation of How to best store Google ngrams in a database?, which covers how to store the Google Ngram Book data. I'm looking to store the Google NGram Web data, which is slightly ...
26
votes
3answers
28k views

Find highest level of a hierarchical field: with vs without CTEs

note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I ...