MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
2answers
522 views

Monitoring InnoDB Index Utilization

I would like to monitor how many queries are utilizing the indexes. Is there any program that can show me live query performance and index utilization? Note: I am already aware of slow log file and ...
4
votes
2answers
2k 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 ...
3
votes
1answer
727 views

MySQLDump wrong dump

i'm trying to generate a dump from my database but when i try to load the dump file it gives me an error. What should i do ? My MySQL ( loader) version is mysql "Ver 14.14 Distrib 5.1.57, for ...
2
votes
1answer
848 views

Create a custom log table in MySQL

Is it possible to create a custom log table. For example I have 10 schema and I would like to log if somebody is using the "areva" schema. I want to know the last executed command time and the user. ...
2
votes
4answers
1k views

INFORMATION_SCHEMA Selection Error

I'm trying to select data from information_schema but I'm getting the following error. How can I fix this? mysql> SELECT * FROM information_schema.tables ; ERROR 1018 (HY000): Can't read dir of ...
5
votes
2answers
9k views

How to cache mysql database in server memory

I am having problems with on website with 600MB of mysql database. The website is way too slow and i noticed the bigger the mysql database gets the slower it gets. When it was like 5MB website was ...
2
votes
1answer
804 views

Getting the last executed command time

I have tables stored in a Trucrypt partition and I would like to disconnect that if the users not using the mysql. There is the INFORMATION_SCHEMA.PROCESSLIST but I would like to know the users last ...
2
votes
3answers
175 views

Database-backed website configuration

I'm planning on adding storage for configuration options to the website I'm working on (php + MySQL). The problem is that I cannot decide between having a 2-column table (with key and value fields) ...
0
votes
3answers
676 views

How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?

I want to design a db schema concerning a schedule. I. I can use all fields in the same table, for example: id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su 1 | 1 | ...
9
votes
3answers
18k views

Any better way out of MySQL InnoDB log “in the future”?

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
2
votes
1answer
3k views

What's the differences between utf8_general_ci and utf8_unicode_ci and utf8_binary collation in MySQL?

I'm can't find the documentation of MySQL on this topic. Anyone can give some explanations please?
4
votes
3answers
1k views

Log MySQL DB changing queries and users

For security reasons I need a server-side (running Debian 6.0 Squeeze) logging of all queries that may have changed the content of a MySQL DB (v. 5.1) and the user who issued it. I had to rule out ...
4
votes
1answer
315 views

Speed inserts in database with a table with +47 million data per each year, containing more than 10 years?

I'm moving my database to mysql but there is one table that is pretty big (more than 47 million rows for each year) and it takes years to insert all the data. I already tried everything I found in ...
1
vote
2answers
781 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
5k 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
435 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
430 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
24k 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
930 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
239 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
490 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
45k 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
5k 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
976 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
532 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; ...
8
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
554 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
5k 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
932 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 ...
6
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
412 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
121 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
782 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 ...