MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

5
votes
2answers
794 views

Getting recovered db files to work on rebuilt server

I've had to rebuild my server and have tried to copy the database files from the old drive to /var/lib/mysql/. I have restarted apache and run 'show databases', this list all the databases. I have ...
1
vote
1answer
123 views

Printing \G on shell using mysql client..?

It is really a a headache for me from some hours i am struggling on the problem.I have also asked it on stack-overflow but not got any answer yet so i am asking here.Sorry for Not a good question. I ...
-1
votes
3answers
72 views

email a database made by My SQL [closed]

I want to email a MYSQL database I created with all tables and queries to other person to check do I use the Import and Export Wizard or there are other ways?
2
votes
1answer
445 views

How to prevent high memory, CPU and time consumption by MySQL restore?

I have a local test machine with 2 GB RAM and a dual core processor. I imported a dump on that using mysql -uuser -psecret < script.sql script.sql is a 700 MB file which contains a single ...
5
votes
2answers
2k views

How could one monitor MySQL replication?

I am not a DBA, but I am interested in knowing how replication monitoring is done on MySQL severs. In order to know if a slave is in sync with its master, one would typically check ...
13
votes
7answers
23k views

Get the rank of a user in a score table

I have a very simple MySQL table where I save highscores. It looks like that: Id Name Score So far so good. The question is: How do I get what's a users rank? For example, I have a users ...
7
votes
2answers
1k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
1
vote
1answer
232 views

MySql cluster data node replication

What I'd like to do is to replicate data nodes within a cluster not only for backup/failover purposes but also for scale-out reasons. Is this possible? If not: why? If yes: how do I do it? Actually, ...
2
votes
1answer
4k views

MariaDB XtraDB Performance Tuning

I believe that innodb has a single read thread. However, with MariaDB you can set multiple read threads. I'm unsure what the recommended value would be. Questions Does anyone have any experience ...
10
votes
3answers
6k views

Where can I find example databases for MySQL, SQL Server and Oracle?

I want to experiment with the 3 large database management systems. For this purpose it would be cool to have the same database (the same tables, etc.) for each of those. Where can I find sql scripts ...
5
votes
2answers
6k views

Taking mysql dump from another machine

I have MySql db on Machine A , I have Mysql credentials. And accessing this MySQL from machine B which I have crdential's. But I couldn't take mysqldump from Machine B. Please help me. Both ae Linux ...
6
votes
2answers
5k views

Ibdata usage and Recommendations?

I have a Production server On November 5,2011 the ibdata size was 100G. In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G. I ...
3
votes
4answers
7k views

Synchronizing data between MySQL and Oracle databases?

I want to sync data between Oracle & MySQL databases. What is the best possible way to achieve this? Are there any tools/software available? This is not a one-time but an ongoing operation. The ...
1
vote
1answer
152 views

Mysql database design with XML

I intend to create a wiki webpage, but it has some few more limitations than regular wikis. The content is completely user-editable, but the structure of the page is determined previously. To make ...
1
vote
2answers
909 views

Very slow performance from MySQL

I have the following procedure: drop procedure if exists deletebatches; create procedure deletebatches() BEGIN declare acount int default 0; declare done INT default 0; declare abatchno ...
2
votes
2answers
2k views

Limit WHERE to MAX() & COUNT()

This is my current mySQL query: SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread FROM TAB_EVENT e LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id WHERE ( m.`read` IS NULL OR ...
1
vote
2answers
3k views

Usage of RESTRICT or No ACTION in FOREIGN KEY Constraints

Sorry if this question is naive, but I was unable to understand possible usage of RESTRICT or No ACTION. Consider a simple database of CREATE TABLE column1 ( first_id int(11) NOT NULL AUTO_INCREMENT, ...
5
votes
1answer
5k views

How should I interpret the “lock time” within mysql slow query log?

I'm trying to understand how to best interpret the lock time for the queries which show up on our MySQL slow query log. For instance if an UPDATE query has 10 second lock time. I presume it is the ...
1
vote
3answers
630 views

GRANT command not working in MySQL Server

I'm trying to execute the below command as root user to provide accessiblity from my application. GRANT ALL ON communitycourts.* TO 'root'@'%' WITH GRANT OPTION; But it returns 0 rows affected, but ...
1
vote
1answer
2k views

SQLSTATE[HY000]: General error: 1025 Error on rename of

I'm getting this error: SQLSTATE[HY000]: General error: 1025 Error on rename of './salt/ghost' to './salt/#sql2-4ab-630b' (errno: 152). Failing Query: "ALTER TABLE ghost DROP FOREIGN KEY ...
10
votes
7answers
16k views

slow load speed of data from mysqldump

I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe ...
1
vote
1answer
1k views

Screwed up MySQL init_connect

I executed an incorrect SQL command: SET GLOBAL init_connect='SET CHARACTER SET=utf-8'; Now, when I try and run a script that connects to this database, I get the following error: ...
3
votes
1answer
3k views

How do I update a table with the aggregted values (min, max) of a related table, fast?

I have a table create table w ( id integer primary key, -- min_xyz double, max_xyz double, min_abc double, max_abc double ); with the min* and max* values currently being null. I have ...
4
votes
1answer
223 views

Need to find duplicate entries

I received a database with a few million records in it, but apperently there might be duplicate records in them. A user enters data into the database and a primary key is generated, however if the ...
1
vote
2answers
275 views

How to recover previous values in Mysql Table?

I have MySQL Innodb table of 14M rows. There was a column called is_disabled which had different values. By mistake I ran the query without the where clause: update url_queue set is_disabled=1 ...
1
vote
1answer
269 views

What is the difference between setting the directive default-character-set under [client] as opposed to [mysqld]?

Reference: http://stackoverflow.com/a/8690422/784637 Correct me if I'm wrong, but the best answer suggests a way to force everything being inputted into the database to be encoded in utf-8 and ...
1
vote
1answer
609 views

Can I use the “import” tool on PHPMyAdmin multiple times?

I am moving databases between a couple of shared hosting accounts, and both of them only give access to the MySQL databases through phpmyadmin. The importing account's upload limit is 50MB. I would ...
19
votes
1answer
4k views

MySQL : Why are there “test” entries in mysql.db?

Recently, I posted an answer to a question about mysql.db. Then, I got to thinking I should ask everyone this question: I have noticed for years that upon installation of MySQL 5.0+, mysql.db is ...
3
votes
1answer
290 views

Does MVC architecture design pattern effect MySQL database performance?

Our current site is entirely developed in the cakePHP framework with the MVC architecture. If we notice more than around 19-20 users viewing the site at once, it begins to crawl to a hault and begin ...
4
votes
3answers
5k views

What is the mysql.db table used for?

Related to my MySQL issue I have a user with USAGE grant in mysql.user, but in mysql.db this user has Select, Insert, Update, Delete. Thus the user can successfully query the database. I can't find ...
5
votes
2answers
7k views

SQL command to remove prefix from a set of tables

I am no DBA. I have a situation with a MySQL database (5.0.51a) where I want to remove a 'dr_' prefix to all tables having this prefix (about 110 tables). I could rename them one by one of course, but ...
1
vote
1answer
2k views

When to open your MySQL cursor

A relatively simple question but from all my research I have been unable to find a decent answer. Which is technically more correct, open your cursor after your handlers have been defined or before ...
1
vote
4answers
3k views

How to migrate from MySql to HSQLDB?

I have large amount of data in a MySQL database. There are many databases schemas in the database. How I can migrate from MySQL to HSQLDB? Is there some tool for this? I've exported the data ...
1
vote
2answers
1k views

Client to use the slave when the master is down in MySQL replication

Is it possible for the client to use the slave when the master is down in mysql replication? I have already setup a simple mysql replication having a master and a slave. The client connects to the ...
2
votes
2answers
304 views

Database structure for a mailing list.

I am designing a database structure for a mailing list. The data I need to record is the mail recipient data: Since the mail recipient data is not predefined, it is causing a problem. For example, ...
17
votes
5answers
16k views

MySQL IS NULL / IS NOT NULL Misbehaving?

Please have look at this table: mysql> desc s_p; +-------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | ...
5
votes
2answers
3k views

Does the max_allowed_packet variable have any effect on LOAD LOCAL INFILE?

As the question states, what (if any) is the effect of max_allowed_packet on LOAD LOCAL INFILE? Does setting it to a large value load data files of large size such as 700MB into the db quickly?
2
votes
1answer
698 views

How to serialize a record / row in SQL?

Is there a way to serialize a record/row in SQL? And conversely, to deserialize something that has been serialized. By serialize, I mean put all of the fields in a record into one field or string. ...
0
votes
2answers
204 views

How to implement Draft functionality, with attached files?

Should allow for: User can save an entity as a draft, that would normally not be valid in its table (ie some missing values) User can attach files to the draft User can find, sort, and discard their ...
6
votes
2answers
2k views

How to implement soft deletes?

What is your method of implementing soft deletes in a database? Ideally the solution would allow for decent performance on large tables take relationships into account take unique keys into account ...
4
votes
2answers
7k views

Why is my MySQL dump file and my phpMyadmin export file different sizes?

I am looking to create a cron job to backup my MySQL database. I am using this command: $ mysqldump --user="User" --password="Password" -A --single-transaction > /home/user/Export-`date ...
3
votes
2answers
2k views

MySQL replication to slave having VPN connection to Master

I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network ...
1
vote
1answer
508 views

Does replication with SSL encryption work on MySQL 5.5?

I am trying to set up replication with slave on Amazon EC2 and master in our local environment. The setup went fine without SSL encryption. But when I set up the replication to be SSL encrypted, it is ...
4
votes
2answers
2k views

MySQL replication: “Houston, We've Got a Problem”

I ran into a problem with our replication server. Essentially, we have 2 databases (database1 and database2). Master server has both. Slave has only database1. There is a Replicate_Do_DB: database1 ...
7
votes
3answers
10k views

can't remove “GRANT USAGE”

I was testing some stuff, and added a: grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password'; so now when I do show grants for cptnotsoawesome@localhost; I can see ...
1
vote
3answers
3k views

How to change data location on Ubuntu

I already saw this question here: change mysql data directory but it's not very helpful. What I want - to move my sql data folder to my different place (/media/data) What I did: a) sudo service ...
8
votes
1answer
4k views

Generating Invoices and Tracking

Every 2 weeks, the system will generate the invoices for the companies. Company will receive an invoice on the 1st and 16th every month. (It will run via Cron Job every 2 week. It scan through the ...
2
votes
1answer
2k views

Is it possible to convert a .mysql file to a .sql file?

I am facing an issue with a MySql dump file (i.e mydump.mysql) generated by a Drupal export module. I am trying to import it on a local Drupal installation via Acquia Dev Desktop. When browsing for ...
2
votes
1answer
2k views

MySQL: What field types for large numbers?

I need to store some high numbers, some with decimal points (2 digits). 965.630,5 INT 47.320.500 INT -62.188,0 INT 32,8% FLOAT 225.165,7 DOUBLE 715.100,7 DOUBLE ...
4
votes
4answers
3k views

Is raid 5 suitable for a mysql installation?

Is raid 5 suitable for a mysql installation? Let me explain my application further. My application is a socket programming which will connect with gps device to receive gps string and there after do ...