MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

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 ...
2
votes
2answers
966 views

How to enable all the possible backup options of MySQL

I normally use MySQL Administrator to backup my database. I want to enable all the backup options for the available backup types in MySQL. I noticed that Binary Logging is not running on my PC. How to ...
1
vote
1answer
152 views

Does MySQL compress the stored data?

When I export a database in MySQL, it gives me a .sql file. Let's say this .sql file is 80 MB. Are they compressed in order to reduce disk space usage? Is that 80MB real physical-storage size?
4
votes
1answer
1k views

What's the fastest way to replicate a MySQL database from one machine to another?

What's the fastest way to replicate a MySQL database from one machine to another? The two machines are connected via LAN. How to do it without mysqldump? If using mysqldump, is the fastest way $ ...
7
votes
3answers
8k views

How to convert a MySQL database to PostgreSQL?

Is there any command line tool for converting the MySQL database to PostgreSQL? I'd also like to know if there is a way to convert the database using the normal mysqldump command.
1
vote
1answer
599 views

How mysqldump replaces special characters during dumping records?

i am trying to dump a particular row using java. So that i retrieve the data by using record sets and i manually written sql script code by appending the dynamic records. But my problem is i could ...
2
votes
2answers
481 views

mysqldump reimport isnt working correct

I am trying to restore this database with my sql file I have...yet just get this output with it not importing anything into my db. Thoughts? [root@domU-12-31-39-0B-06-AA data]# mysqldump ...
21
votes
2answers
24k views

When to use views in MySQL?

When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table? One reason that I would prefer to use views is that the database schema ...
1
vote
2answers
1k views

remove almost duplicates based on insert time

Have a MySql table that records magnetic stripe swipe times from identity cards: CREATE TABLE `swipes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `personalid` varchar(20) NOT NULL, `swipe_time` ...
2
votes
1answer
9k views

mysqldump: Error: 'Can't create/write to file

Can't create temporary files on /var/tmp. mysqldump: Error: 'Can't create/write to file '/var/tmp/mysql.k6NKGk/#sql_5dc_2.MAI' (Errcode: 2)' when trying to dump tablespaces On my OS. id mysql ...
1
vote
1answer
1k views

How to restore a MySQL file to SQL Server 2008

I have a MySQL file (apple.sql) that I need to restore to a SQL server 2008 database or at least be able to access the database through mySQL. I have created a linked server mySQL, created an empty ...
1
vote
1answer
3k views

MySQL: how to optimize the stored procedure?

MySQL 5.5.28 Running pt-query-digest on a slow query log, I get something like this: # 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz # Current date: Wed Oct 24 23:44:05 2012 # ...
0
votes
1answer
320 views

how to pass url value to index [closed]

How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next ...
11
votes
5answers
24k views

MySQL server has gone away obstructing import of large dumps

I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a ERROR 2006 (HY000) at line 7758: MySQL server ...
2
votes
2answers
4k views

The best MySQL configuration for my server

I'm little newbie in configuring mysql and I need some advices. Few days ago I made little home server on Ubuntu Server 12.04 with samba, mysql 5.5, apache 2.2, PHP 5.4.6 etc. I'm using it to ...
1
vote
1answer
174 views

MySQL Cluster Steps To Recover Data If Catstrophic Failure Occurs

I want to know if a catstrophic failure occurs (like a power outage), and all nodes in MySQL Cluster are shutdown in cluster then data will be lost even though a backup was taken? Or it possible that ...
2
votes
1answer
581 views

3 Databases, Best Replication Setup?

We have 3 DB servers, two of them i.e. db1, db2, are located in the same data center. The other one i.e. drdb, is located in a different site. Currently, the replication setup for these servers is of ...
2
votes
2answers
358 views

Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?

SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in. I'm looking for the equivalent in MySQL - ...
5
votes
2answers
730 views

Fetch the newest row grouped by a column in MySQL

My problem seems like it should have a much simpler solution than what I have come up with. Starting with this data set: log_table +--------+-----------+------------------+---------+ | log_id | ...
2
votes
1answer
644 views

Populating an empty table using loops, conditionals, and accessing multiple tables

This question pertains to a MySQL database, although, in the end, this may be database neutral. I am trying to populate an empty table (let's call it NewTable) that I created. The data population is ...
1
vote
1answer
221 views

MySql - selecting into file and re-loading it with special characters

I have a MySql table called twitter_statuses which as the title suggests holds twitter statuses. here is the table structure. this table contains all sort of characters in the text column, and some ...
0
votes
1answer
67 views

Mysql restore all data from Master

MySQl Slave replication server got crashed and I am missing around 60 tables, some table structure and data which present in master and by DB size is more than 25Gb, How I Can restore to resolve ...
2
votes
2answers
167 views

MySQL Server Config, Small Timeframe, Extreme Usage-Scenario

I want to configure a MySQL server for the following scenario. A large amount of users will need to sign up for a university course selecting individual "timeslots". The signup page will open at a ...
2
votes
2answers
2k views

MySQL order by - one column conditional on another column

In MySQL I have a table (simplified for this question) CREATE TABLE `projects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `priority` int(11) DEFAULT '1000000', ...
2
votes
1answer
2k views

MySQL: Unique constraint on large column

I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column. MySQL appears to ...
1
vote
1answer
457 views

Optimizing User Defined Custom Columns

I am building a web based app that has the potential to have very high traffic and collect a lot of data. Most of this traffic will be utilizing the database. Database is MySQL InnoDB. I want to give ...
1
vote
1answer
1k views

Restoring MySQL database with db dump and binlogs

I have a database running on our production web server that I have set up as a master with a single slave in another datacenter. When reading through the MySQL replication setup instructions, they ...
1
vote
2answers
1k views

How can Stored Procedures and Triggers be Automatically Loaded When MySQL Starts

Is there any straightforward method of doing as the questions asks? We are using MySQL 5.5 & Linux.
6
votes
2answers
3k views

Scaling Percona datacenters: setup and replication

Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you. We have already scaled alot of our application stack: we offloaded ...
2
votes
1answer
506 views

MySQL Unique Index Keylength

I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key. The engine of choice here is InnoDB. There are ...
1
vote
2answers
104 views

Will changing server_id on master break replication?

I have few MySql absolutely isolated trees, each one of them has one master and few slaves. For purposes of automation I need to set the same server_id to all masters (this will simplify the usage ...
3
votes
2answers
968 views

Blocking drop database command

I created a MySQL database and created a new user dedicated to that database with all permissions. The new user then proceeded of dropping the database in addition to all tables. I need to block that ...
1
vote
2answers
231 views

SQL query based on foreign key relationship

I have two tables, a Project and an Action: Project has the following fields: project_name Action has the following fields: action_name project status There is a one-to-many relationship ...
0
votes
1answer
2k views

Partitioning not working need primary key?

We are trying some partitioning as below. ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate)) ( PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')), PARTITION p2 VALUES LESS THAN ...
3
votes
1answer
394 views

How does R-Tree outperform B-Tree for simple check if a point is within a rectangle

For things like find nearest neighbors, I can undertand that R=Tree can outperform B-Tree. R-Tree can kick out more obviously false points. However, for simple check of a point in a rectangle, it's ...
1
vote
0answers
63 views

Partition guides and possible solutions?

We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be ...
-2
votes
1answer
132 views

percona innobackup failed

I'm trying to get percona innobackup but I'm struggling with this since 4hrs. Tried all possible ways from many forums. interms of changing my.cnf, parsing arguments but still not able to get this ...
2
votes
0answers
414 views

MySQL database structure: more columns or more rows [closed]

I collect how people tag topics with categories in table like: ID | topic_id | votes_Category_1 | votes_Category_2 |.......... | votes_Category_12 This table holds 1 row per topic and dynamically ...
0
votes
1answer
264 views

Most efficient way to structure segmented data

I am faced with a set of data which I am currently unsure on how to store efficiently. The data in question relates to a calendar which stores information on two different categories of event which ...
5
votes
1answer
3k views

Disable console output when executing a SQL file using MySQL source command

I begin by launching the MySQL command line tool: mysql --user=myusername --password=mypassword --silent --force -b The last -b option is used to disable beep on error. Then I choose a database: ...
4
votes
1answer
247 views

MySQL table design of logging tables

I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization. Currently we have a table that logs session data, it writes about ...
1
vote
1answer
1k views

`show procedure status` doesn't list all of the procedures?

mysql-server-5.5.28-1.el5.remi I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
1
vote
1answer
139 views

Slow-running query on a new VM

The below query is taking a lot of time, more than an hour. Is there anything I could to to tune it, server-wise or query-wise? Its just 23000 records. EXPLAIN SELECT bc.charge , ...
1
vote
1answer
981 views

calculate the difference between two column aliases in a SELECT statement?

I'm not sure how to do the last part of my query, which is doing calculations directly with the aliases: (SUM(task_avg - minutes) AS difference. I don't think I can do it as easy as I'd like, but ...
0
votes
1answer
152 views

Handling error of foreign key

How to set a FK value NULL upon receiving error for foreign key constraint? Consider a simple table a CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT ex_key int(11), FOREIGN ...
3
votes
1answer
5k views

What exactly are iblog files in mysql

I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.
1
vote
0answers
123 views

MySQL: Fixing Unique key constraint violation

I have a database with an unique key constraint. When I import this database from a dump created by mysqldump I get '14698-3' for key 'UNQ_CATALOGS EARCH_FULLTEXT_PRODUCT_ID_STORE_ID' But when I ...
0
votes
2answers
1k views

how to estimate rebuild index on 140 million records?

My existing project has data of about 140million records in Contentq table. And I have planned to rotate the table just incase not to face any load on the table. So I have planned to rotate data older ...
1
vote
1answer
2k views

Slow complex query with group/order

I have a problem with a very important query in my application. It is very slow when adding group and order to it. So the full query is: SELECT m.id, m.title, m.title_text, ...
0
votes
1answer
278 views

How to avoid certain characters in MySQL full text search?

I have a column named parcel_number of varchar type. It contains values like ab-cd-ef and uvwxyz. A search for abcdef or uv-wx-yz does not return those records. How should I write the query to meet ...