MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
2answers
336 views

Managing features for a real estate agency [duplicate]

I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table. The data ...
1
vote
2answers
136 views

How to restructure this slow query

For the following query: SELECT l.* FROM `leads` AS `l` INNER JOIN `users` AS `u` ON l.client_id = u.id WHERE (`l`.`lender_id` = 1) AND (l.claimed_date IS NULL) AND (`l`.`disabled` = 0) AND ...
1
vote
2answers
1k views

Slave settings not working

I have this machine as slave. Below is the command I ran and stuck. I done the change on the config file and set the id=2 too. Anything else I am missing here? slave start; ERROR 1201 (HY000): Could ...
1
vote
2answers
3k views

Why is this MySQL proc using cursors failing to retrieve results?

I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, ...
1
vote
1answer
352 views

Using slash in MySQL database

I am using mysql with php Yii framework.When doing data modelling I came accross some doubt.I have a table for user details like this. CREATE TABLE IF NOT EXISTS `tbl_users` ( `id` int(11) NOT NULL ...
1
vote
1answer
2k views

How to recover the innodb when mysql data dir moved from one system to another system?

I have transfer the mysql data dir from one linux box to another box. When i start the mysql server. I can find that the innodb based DB are corrupted. So, may i know how to recover the corrupted ...
1
vote
1answer
97 views

How to determine if a column is the first part of an index?

Is there anyway to tell with SQL if an indexed column is the first part of an index. The closest thing looks to be information_schema.columns. But that seems to only tell me if it's a PRI or MUL key ...
1
vote
1answer
527 views

Usefulness of Foreign Key WITHOUT reference option

Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE I think this will avoid INSERT of a value ...
1
vote
2answers
3k views

How do you install MySQL 5.1.34 on CentOS 5.5?

I am trying to install Mysql 5.1 on cent OS. I researched a lot but could not find any link which gives step by step procedure. Everywhere, there are vague answers. Can someone please give me steps ...
1
vote
3answers
2k 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
4k views

What would be the fastest approach to bulk update this mysql table (Innodb)?

I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up? I am using following approach: mysql -uroot < query_file where query_file contains 1M ...
1
vote
2answers
911 views

MySQL Database and Table Loss

I have installed mysql and XAMPP on my system. I wanted to remove the mysql service of XAMPP. After removing it, I tried to install the new service for mysql using "MySQLInstanceConfig.exe". but now I ...
1
vote
3answers
145 views

how to set default charset while create new field

Is there any command in mysql that makes the following: if I create a new field and I set the type to TEXT than it automatically knows that by this I mean not latin1_swedish_ci but utf8_hungarian_ci ...
1
vote
2answers
9k views

How to join two table and show one query result in MySQL?

I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there ...
1
vote
1answer
3k views

16 Cores 12 GB RAM server MySql Configuration - my.cnf

The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...
1
vote
1answer
150 views

MySQL detect duplicate human names [closed]

I've been tasked with identifying a solution to help identify possible duplicates when inserting new user records into a MySQL database. Does anyone know of any cheap, preferably free, solutions? The ...
1
vote
1answer
4k views

MySQL Replication Error

I am getting an Error on Slave as: could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error ...
1
vote
1answer
1k views

Duplicate entry in mysql slave

I have a mysql master-slave replication setup. when I checked "show slave status", I got the result as "Slave_Sql_running:No" and Last error as "Duplicate entry 'x' on query." But when I tried to ...
1
vote
2answers
778 views

Update Large Table

I have these two tables: user_profile: user_profile_id_int int primary key user_profile_id varchar user_activity: user_profile_id_int int key user_profile_id varchar both fields in user_profile ...
1
vote
1answer
268 views

MySQL full text search ranking

I found in this article the formula that MySQL use to ranking in FTS w = (log(dtf)+1)/sumdtf * U/(1+0.0115*U) * log((N-nf)/nf) Where dtf is the number of times the term appears in the ...
1
vote
2answers
5k views

Set up a MySQL slave using mysqldump to get the initial data

I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
1
vote
2answers
231 views

Is it advisable to have MySQL Master/Slave running on the same physical server, on the same mysqld, sharing the same disk?

my Use-Case : The solution I am developing is very data oriented. I run a cron job every 3 hrs to read lots of records from a file, process them and store them in the database. I also need to ...
1
vote
2answers
3k views

Is there a max allowance for entries in a table in mysql?

Is there a set maximum (or even a recommended maximum) for how many entries that can/should be stored in a mysql table? How much is really too much?
1
vote
2answers
144 views

MySQL Records Turnover: How to do it?

I have a table of villas ordered by the UID as follow : UID, name, random_order: 4, villaA, 12 8, villaB, 5 9, villaC, 9 11, villaD, 2 I want to show my villas according to the random_order ...
1
vote
2answers
815 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 ...
1
vote
1answer
3k views

How to upgrade MySQL to newer version on existing database

I am having data of 20 GB with MySQL version 5.0.24. Now I want to update MySQL to newer version 5.5.16. We can do it by taking complete database backup and uninstall old MySQL and install new MySQL ...
1
vote
1answer
615 views

What is com_set_option?

As the title says, I'm wondering what is this command and why am I seeing a large amount of this query on my database? It's currently 13.30% of my total queries. I've tried googling this but there's ...
1
vote
2answers
2k views

How to set addtime(current_timestamp, '15:00:00') as MySQL timestamp column default value?

I want to set `addtime(current_timestamp, '15:00:00') as the default value of one MySQL table column. I tried to do this with the following command but failed: mysql> alter table mytable change c1 ...
1
vote
1answer
199 views

MySQL duplicate data entry issue

I know this issue can be solved using unique key constraint on column and some insert command variation. But I exactly want to know that at last why the following approach goes wrong sometimes and a ...
1
vote
1answer
492 views

Applying MySQL 5.1 bin log to MySQL 5.5

Is it possible to apply MySQL 5.1 bin log to MySQL 5.5. I am assuming that bin log is portable between two machines (same OS, Architechture but different machines running same version of MySQL)
1
vote
2answers
565 views

Sizing MySQL Database Servers for Drupal

What are some guidelines to follow when it comes to sizing MySQL Servers? CPUs, RAM, Disk Space, Clock Speeds, etc? We're going to go with either the community edition or the enterprise edition, and ...
1
vote
1answer
352 views

Use order from another select clause

So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one. I have this statement to get the vid column order ...
1
vote
4answers
414 views

Best way to automate running several MySQL commands

We have several core db tables on our system and allow our employees access to these tables via views that just show them the data they need to see. We created these views in a new db specific to ...
1
vote
1answer
124 views

Is there any tool to convert Oracle commands to MySQL?

Is there any tool which I can use to convert Oracle commands to mysql commands?
1
vote
1answer
496 views

Concurrent INSERTs in XtraDB (InnoDB)

I'm currently unable to insert rows concurrently in an XtraDB table on Percona Server 5.1.54. I had the same problem with a similar version of MySQL. My table definition is as follows. CREATE TABLE ...
1
vote
2answers
68 views

Online Hostel Management System ER Diagram

I have created an ER diagram for an online hostel management system. Please advise me if this diagram makes sense or needs improvement.
1
vote
1answer
45 views

join from different tables gives same result, but it shouldn't

This is the code I'm using and I'm having a problem with it. SELECT `disenos`.*, `votos`.`valoracion`, `usuario_contacto`.`nombres`, `usuario_contacto`.`apellidos`, `users`.`username`, ...
1
vote
1answer
25 views

Indexing the difference between two columns in MySQL

Is there a way to optimize/index a query that sorts the difference between two columns. I have a modest sized table of 5 million rows that I'm trying to do the following: SELECT pk_column, column_x, ...
1
vote
1answer
24 views

mysqldump - select table in database only if it contains a specific string

(I'm new to Database Administrators, and am not a Database Administrator. However I originally asked this question on Stack Overflow (here) but didn't get any answers. Sorry if this is not the right ...
1
vote
1answer
36 views

Revoke write privileges vs setting database to readonly

I am planning to do a database migration to a new server. During the transition stage, I do not want Apache user to be able to write anything to the database. I have two options. Revoke write ...
1
vote
1answer
20 views

MySQL Utilities seems outdated?

http://dev.mysql.com/downloads/utilities/ has MySQL Utilities 1.4.4 for Mac OS X 10.7. Is this still updated and recommended to use along with MySQL Workbench? I'm at the latest MySQL Workbench 6.1 ...
1
vote
1answer
56 views

Finding reason behind high write IOPS on MySQL (RDS) server

I am having performance issues with an AWS RDS instance (running MySQL 5.6). This is the backend db to an XMPP server called Tigase. According to Tigase Inc. this type of db instance should be able ...
1
vote
1answer
28 views

Fetch data from same table using two group by clauses in mysql

I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month. In my table, user_type is ...
1
vote
1answer
22 views

How to sort words in a column in mysql?

I have data as id action 1 a 1 b 2 b 2 a 3 c I wrote code as select id,group_concat(action SEPARATOR ' ') from table1; It ends up as id action 1 a b 2 b a 3 c Actually, ba and ab ...
1
vote
2answers
41 views

how to design a database's table to store mysql spatial data in a parent child relation?

I want to store locations and polygon data in a parent-child relationship. I am using spatial data in MySQL and Google maps for polygon. Examples: 1. World 1.1 US 1.2 China 1.3 Japan 1.4 South ...
1
vote
1answer
28 views

How Much Resources Does the BLACKHOLE Storage Engine Consume?

I'm going to add a MySQL Relay Slave which use BLACKHOLE Storage Engine. The relay slave will just filter some databases binlog to downstream slave. Our MySQL Master generates about 15G a day in ...
1
vote
1answer
38 views

Cartesian product of two tables not working as expected in MySQL but fine with SQlitee?

I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple table 1 (5 entries): (emp->{id,name,dep}) table ...
1
vote
1answer
24 views

MySQL: can a table get stuck in the table cache?

I've asked this question on the ServerFault stackexchange, someone directed me here. I'm a PHP developer and am responsible for a set of PHP scripts that run on a shared host running FreeBSD, that ...
1
vote
1answer
80 views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
1
vote
1answer
98 views

Summarize from unlimited level depth parent-child in mysql

I have 2 table master coa and coa transaction in MySQL like this, master : id | coa | id_parent | ----+------+-----------+ 1 | 1 | NULL | 2 | 11 | 1 | 3 | 111 | 2 ...