MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
3answers
1k views

MySQL and PostgreSQL Licence issue

Our Company wants to use MySQL for one software product. This product is not open-source. Is it legal to use it freely ? If not, how about PostgreSQL ? Is this okay for our purpose ?
4
votes
2answers
6k views

MySQL Event does not run

i am running mysql 5.1.41 that came bundled with xampp on windows. the problem is that the event doesnt run automatically even when the event scheduler is ON. i have a table named ta_table using ...
3
votes
2answers
98 views

Exclude dataset from a query

I have 2 tables EXAM (StudentID, SubjectID, SchoolYear, Period, Mark) and CANCELED_EXAM (StudentID, SubjectID, SchoolYear, Period). Primary key for both tables are (StudentID, SubjectID, ...
10
votes
1answer
4k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
19
votes
1answer
4k views

Is it possible to have passwords configured per database or per host in .my.cnf

I have the following in my ~/.my.cnf [client] password="somepass" but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different ...
2
votes
2answers
126 views

Database Agnostic Conditional

I'm writing a Drupal module and I want it to be agnostic between PostgreSQL and MySQL. I have a field that gets its value from a conditional, and I originally wrote it as an IF(). Then I found out ...
2
votes
1answer
113 views

MySQL user account 'From Host' setting

I've just installed Wamp server 2 and I get the following 4 default MySQL users: USER FROM HOST <anonymous> localhost root localhost root 127.0.0.1 ...
5
votes
2answers
685 views

Is it a good idea to split MySQL DB into two servers

I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate: I ...
2
votes
3answers
6k views

MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST

I'm trying to figure out why the reporting of slave lag is different in SHOW SLAVE STATUS and SHOW PROCESSLIST, in MySQL 5.5.13. The only difference to other slaves of this master is that it is ...
2
votes
2answers
1k views

mysql inner join producing more results than it should

I am trying to get the roles name, verbs name, and privileges enabled value where privileges is a join table joining verbs and roles through a verb_id and roles_id foreign key. But I am having ...
4
votes
2answers
1k views

MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */

Using mysqldumpslow, I can see that the most common entries in a MySQL slow query log are of the following form: SELECT /*!N SQL_NO_CACHE */ from wp_posts (or wp_comments). I can't seem to find ...
3
votes
1answer
9k views

How do I open a MySQL Database Dump?

I work for a doctor's office, and we are in the process of switching EMR systems. The previous system has provided a physical 'dump' of the database. I however, don't know how to open this database ...
1
vote
3answers
736 views

Best architecture solution for heavy data mining system, that can run on cloud

We are currently hosting our MySQL database on a dedicated server with lots of memory and processors. We would like to push it to a different architecture that would allow us to scale it easily, ...
4
votes
3answers
711 views

MySQL very slow query when changing one WHERE field despite no index/key

This is quite a confusing issue for me. I have a database full of baseball statistics. Running this query: SELECT * FROM hits JOIN stadiums ON stadiums.gameName = hits.gameName JOIN players ON ...
5
votes
2answers
2k views

What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES?

In MySQL's table INFORMATION_SCHEMA.TABLES, there's a column named 'TABLE_CATALOG'. The documentation is sparse on this column and I'm wondering what the heck is the purpose of this? Any killer-app ...
7
votes
3answers
7k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
1
vote
2answers
2k views

Distributed Transactions between SQL Server 2000 & MySQL Stopped Working

We have been successfully copying data from a MySQL Slave database into a SQL Server 2000 database. The MySQL server is a linked server. I have tried using v3.51 and v5.1.8 of the ODBC connector ...
3
votes
2answers
627 views

Testing Query Speed

When I'm running a query for the first time, it takes x miliseconds to be executed. When I run it the second time it takes a lot less time. I assume that it is some caching involved in this mechanism, ...
2
votes
1answer
2k views

Suggest a MySQL book for me [closed]

I have recently joined a team in my corp office that has mysql as a part of their work profile. Though the mysql work not often in this team, but when they do it comes in big scale. The main mysql ...
8
votes
9answers
2k views

Which DBMS are fast enough for an online game (few thousand players)?

I'm currently making an MMORPG game, which could have a few thousand players online at the same time (probably not; just wishful thinking). First we wanted to use MySQL, but I have heard it isn't fast ...
3
votes
2answers
5k views

Can't connect to MySQL DB

I've been trying to install Apache because I want to do PHP development work. The install didn't work so I aborted it and wanted to make sure that my other installs using MySQL were still working. I ...
4
votes
1answer
486 views

MySQL Replication: Insert generating VERY huge temporary tables

our replication slave is generating ~300GB temporary files while running relatively simple inserts. Our setup: Master: 5.0.45-0.sles10.x86_64, Slave: 5.0.85-win32, also tried with 5.1.57-win64, ...
5
votes
1answer
471 views

How can I create and initialize a schema for a embedded MySQL DB?

I want to create an embedded MySQL database for running tests, but I'm not sure how to do it. How do I initialize the database? How do I run my create table statements? I'm using Perl, so it ...
4
votes
2answers
2k views

Query problem: there can be only one auto column

Can someone please tell me what's wrong with this table definition. mysql version is 5.1.52-log root@localhost spoolrdb> create table spoolqueue ( queue int, ...
6
votes
3answers
3k views

query regarding combining an update and an insert query into a single query in mysql

i want to track history of changes for a user, so that whenever he changes his profile, i need to take the old data and store in history and update with new data. I can use a select to get the old ...
5
votes
3answers
10k views

How do I output MySQL logs to syslog?

I want to use syslog for logging MySQL(5.1.41) on Ubuntu(10.04 LTS). I found information that output error log to syslog. [mysqld_safe] syslog But I want to use syslog for logging general logs and ...
6
votes
1answer
4k views

Benefits of running the OPTIMIZE TABLE Query in MySQL DB Server

I would like to know what are the benefits [really practical] that can be reaped by running the OPTIMIZE TABLE tbl_name query in MySQL Server. I checked this once and found that after this is run, ...
2
votes
1answer
1k views

Tool to Convert MySQL DB to MS SQL 2005 (Especially stored procedures) [closed]

Is there any tool to convert MySql db to MS SQL 2005. I am mostly interested in converting Stored Procedures. There are 100s of procedures and writing them again in T-SQL is a big job. Thanks.
4
votes
4answers
4k views

Storing arrays of integers in database (for efficient select)

I am creating a database that will store 100.000 (and probably more in the future) users. While this obviously happens in a table with 1 row per user, every user can (and will) store hundreds of ...
15
votes
6answers
47k views

Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect

I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on? I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL ...
1
vote
1answer
8k views

store result set in temporary table, variable or separate variables within a trigger

I'm in the process of creating some triggers to store the the average rating for a given product on the product table. I've also been asked to store the total number of reviews for the product. So - I ...
5
votes
4answers
10k views

MySQL: Why is auto_increment limited to just primary keys?

I know MySQL limits auto_increment columns to primary keys. Why is this? My first thought is that it's a performance restriction, since there probably is some counter table somewhere that must be ...
2
votes
1answer
450 views

Lost tables in Drupal site database

I have Drupal site on my local server build using pressflow I had problems with the xammp 1.7.4 server, so I delete everything except mysql and htdocs folders. I installed the server again, but the ...
7
votes
3answers
4k views

From where does the MySQL Query Optimizer read index statistics?

I'm trying to determine from where the MySQL optimizer obtains the list of indexes that are available for a table when it estimates the cost of (prepares) a query from.
4
votes
2answers
3k views

Using MySQL triggers or transactions?

I want to ask your opinion in using MySQL triggers or transactions in a website. Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
17
votes
2answers
6k views

How do I properly perform a MySQL bake-off?

I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
4
votes
1answer
765 views

What are your use-cases for HandlerSocket?

There's a bit of hype around: HandlerSocket It seems like faster, nosql access to MySQL data is becoming more desirable. Percona Server is now shipping with HandlerSocket built in. My question is, ...
5
votes
1answer
2k views

What is the difference between XtraBackup and rsync?

Yes, I'm going to run strace on them both... However, I would like to know ... What is XtraBackup is doing over and above rsync -ac src dest ? What's so special about XtraBbackup ? How is XtraBackup ...
5
votes
3answers
707 views

Would it be sensible to create a MySQL user for each user account of a web application?

The thought occurred to me recently, but I have a suspicion the idea might be insane. A couple benefits I'm perceiving are: facilitate automated logging via triggers control mysql connections on a ...
4
votes
1answer
649 views

Speeding SQL queries for a large(?) WordPress database?

I am using a WordPress plugin called feedwordpress in order to run a planet like website (it takes content from other websites, with their permission) on WordPress (See it here). The plugin is great ...
10
votes
1answer
10k views

How is INFORMATION_SCHEMA implemented in MySQL?

The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL? When I connect to a fresh ...
2
votes
4answers
3k views

Connect to a remote MySQL Server from a dynamic IP address

My ISP at home uses a dynamic IP address. I'm trying to access a MySQL database at work. How can I connect to it (using MySQL Workbench)? I could grant all privileges to % so that any IP address will ...
11
votes
2answers
5k views

Reasons for occasionally slow queries?

We are running MySQL 5.1 on Windows Server 2008 R2. We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
3
votes
2answers
3k views

Creating and editing my.cnf by MySQL Workbench?

I just installed MySQL Server and MySQL Workbench on a clean Snow Leopard MacBook. I hooked up Workbench with the server and was able to start the MySQL Server from within the Workbench. I enter the ...
3
votes
4answers
416 views

How to get a column A which has the smaller column B grouping by column C?

I'd like to get the ID of a dataset for each category (my grouping column) which has the smallest "order" column. Here a set of data to explain my thoughts: CREATE TABLE DATAS ( ID INT(2) , ...
5
votes
2answers
504 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 ...
3
votes
1answer
381 views

Mysql fulltext boolean ignore phrase

I'm trying to come up with a mysql boolean search that matches "monkey" but ignores / doesn't take into account any matches of "monkey business". Something like the following is the general idea but ...
6
votes
2answers
3k views

Will these two queries result in a deadlock if executed in sequence?

This is almost certainly the cause of my other question, but I thought it was worth separating the two as I have a hypothesis based on the following log that I would love to have falsified or ...
13
votes
2answers
23k views

How to SELECT from SHOW TABLE STATUS results

I'd like to limit the rows and columns that come back from the SHOW TABLE STATUS command in MySQL 5.1. Is there a way to get this same information through a SELECT statement so I can manipulate ...
6
votes
2answers
260 views

How should I Optimize storage for this table?

I'm using MySql to store a basic table of this format: id int(11) //Auto-Incrementing ID data varchar(5120) //Random input data, compressed by a program, not mysql. ...