MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

5
votes
1answer
6k views

Mongodb - Returning the points nearest to a location, with distance

What is the MongoDB's equivalent to this query: SELECT 111151.29341326 * SQRT( pow(-6.186753-`Latitude`, 2) + pow(106.772835-`Longitude`, 2) ...
3
votes
1answer
7k views

Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
5
votes
1answer
4k views

Best solution for cross-datacenter MySQL master-slave replication

We are working on a new system architecture for our company. We have a HPC, which runs in our own data-center and we are planning our front-end and a fallback system on Amazon Web Service. System ...
3
votes
1answer
1k views

How do I reindex a MYSQL table using a different disk?

I have my mysql data and index files on disk1 I need to reindex the table and do NOT have enough disk space on disk1 How do I reindex a table on disk1 and use disk2 for the reindex disk space? For ...
1
vote
1answer
161 views

Optimizing table and choosing storage engine

Having the follow MyISAM table: +----------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
2answers
189 views

Large scale, hard to model design advice needed

I would love to hear some opinions or thoughts on a MySQL database design. Basically, I have a tomcat server which receives different types of data from about 1000 systems out in the field. Each of ...
1
vote
1answer
1k views

MySQL INSERTs waiting for UPDATEs (MyISAM)?

In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables. We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. ...
2
votes
1answer
169 views

How to use MySQL Server 5.6 for GeoIP replication?

I have downloaded the new MySQL Community Server 5.6 and I dont quite understand how to use it properly for GeoIP based replication across 20 nodes. I want to have one central node, then three smaller ...
6
votes
1answer
414 views

Can spatial index be a part of a multiple columns index?

This manual over here is extremely unclear and doesn't even provide some sample SQL statements: http://dev.mysql.com/doc/refman/5.6/en/column-indexes.html Another way to rephrase the question is the ...
2
votes
2answers
3k views

How do I replicate a MySql server's data without using mysqldump?

I've got a working server with a lot of data on it. I've built a new server that will be lots faster. Both are running x686 code on Debian Squeeze. A quick test using one of the tables shows that it ...
0
votes
1answer
47 views

Does a numeric field always use the same disk space?

When you have a numeric field in MySQL(MYISAM) (E.g. Integer) will it always use the same disk space, no matter what number is actually stored?
1
vote
2answers
2k views

Database table data transfer between different servers using bcp.exe and mysqldump

I have 2 servers, both are exact clones of another, one is PRODUCTION live use and the other is strictly for backend DATAENTRY. Using MSSQL on two different servers I am using the following command ...
4
votes
2answers
148 views

Optimizing my update sequence

Whenever a user updates his profile, we conduct a set of queries like so: DELETE FROM `user_likes` WHERE `ID` = $id INSERT INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name) Is ...
5
votes
1answer
3k views

Difference between count(*) and count(1) in mysql?

In MySql we can count the total number of records by using count(1) or count(*). Is there any technical difference between them?
5
votes
1answer
82 views

Will these queries return the same results?

I have this simple query: SELECT friend_id FROM default_friend WHERE user_id = 1 And the results are below: +-----------+ | friend_id | +-----------+ | 2250 | | 4901 | | ...
1
vote
1answer
3k views

How to log logon logoff and user activity on mySQL server for audit?

My boss wants to have some kind of log on the server running mysql which would log user activity. Kind of like: User blah logged in User blah ran some query User blah logedd off User asdf ...
7
votes
1answer
5k views

When to switch from MyISAM to InnoDB?

We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM. My original plan was to upgrade ...
2
votes
1answer
680 views

create trigger which inserts rows depending on enum values?

I'm quite new to triggers and I ran into general problems. I'd like to achieve this: CREATE TABLE `searcharticles` ( `articleID` int(11) unsigned NOT NULL, `ean` char(13) COLLATE utf8_unicode_ci ...
3
votes
1answer
3k views

MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos

I am trying to repair one MySQL replication setup. One of the database's table at slave side is deleted and the updates from master side are received but because of missing table these updates are not ...
4
votes
3answers
4k views

Mysql backup strategies?

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
2
votes
2answers
130 views

How to see what was executed in the database via binlogs for specific date?

I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had ...
1
vote
0answers
320 views

MySQL GROUP BY and ORDER BY giving inconsistent results

I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the ...
1
vote
1answer
186 views

Views missing on MySQL database

We have a MySQL database that was restarted a few hours ago When it restarted, the views were missing. The tables all seem intact, and a check on the mysql tables seems all good. Any ideas what ...
2
votes
1answer
168 views

Need some help with this query get all status and their associated comments of me and all my friends

I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was ...
2
votes
2answers
174 views

Beginner problems administering users on mysql

Very new MySQL user here. Two questions for the community. I seem to have created an account that I can't delete/drop. I can't seem to get MySQL to actually require newly created accounts to require ...
2
votes
2answers
878 views

Why restoring a large sql file via SSH seems to quit partially through the process

I have a large (~5GB) .sql file I am attempting to restore via SSH, it seems to quit in the middle of the restore process. When I come back hours later after issuing the command (the file has 7 ...
3
votes
3answers
378 views

Is this database fine for calculation?

I am doing an application called as Invoice Application.For that I have my database is like this. Now my problem comes when I am taking the tax part in my database.As I have made tax as an option ...
2
votes
0answers
186 views

How to TRIM and CONCAT at the same time [closed]

I have a query am working on where I want to Trim a leading character and the concat other three characters my query is as follows SELECT CONCAT ('255', TRIM(LEADING '0' FROM `mobile`) FROM ...
3
votes
1answer
7k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
3
votes
1answer
2k views

Optimizing MySQL for Read-Only?

We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key ...
2
votes
3answers
3k views

How do I calculate COUNT(*) for a range without needing to use multiple queries in mysql

I am using MySQL and I need help using COUNT(*) for a range of values within a table. I have a table named PERSON (In the live system I expect several hundred thousand records or more). Name ...
2
votes
1answer
126 views

Several values from different tables

I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest ...
1
vote
1answer
902 views

“Mysql has gone away” heavy multiple insert

I am stuck because Mysql server will go away after executing this part of code this function will update the database with the new RSS feeds which are stored in ToUpdate but Mysql stops working. Every ...
2
votes
1answer
133 views

MySQL: self-administration for multiple users

My MySQL users have rights to administer their own databases, including create/drop database. I do not have a shared phpMyAdmin installation, however I do let users install it themselves. Most of the ...
6
votes
1answer
1k views

Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
2
votes
0answers
200 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
1
vote
2answers
210 views

Is possible to optimize this query or it's already optimized?

I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one: -- -- Table structure for table `default_users` -- CREATE TABLE IF NOT EXISTS `default_users` ( ...
2
votes
1answer
1k views

query generate the information one column in two columns in one query

I have a question related with a query in sql. I have the next table: id name 1 name1 2 country1 3 name2 4 country2 I need to call the results so: ...
2
votes
3answers
326 views

designing multiple tables while eliminating duplication?

As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating. What I'm trying ...
0
votes
2answers
92 views

Tiered permissions to generate conditional data access

I am working to improve the confidentiality and access level of the information presented within a program interface over top of a MySQL database. My goal is to create a two tier system: one based on ...
3
votes
2answers
2k views

mysql transaction vs lock

I have a question regarding MySQL locking and transaction management. My Question is ..does mysql takes lock on the the tuple/table on which I am doing select/update in a transaction ?
3
votes
3answers
2k views

Does the MySQL primary key affect the efficiency of a SELECT query?

Does the primary key serve any purposes apart from uniquely identifying a specific row? For example, a table with an autoincremented primary INT key could greatly aid in search due to the option of ...
2
votes
3answers
3k views

Memory problems with MySQLWorkbench

I'm currently using MySQLWorkbench 5.2.40 on my PC (8GB rRM, Corei7). I have a table with almost 1.5 million records, which I need to visually look over. Surprisingly, the total size does not exceed ...
1
vote
2answers
2k views

Similar function NOWAIT in MySQL

Select for update nowait doesn't work in MySQL 5x (innodb). Is there any function like NOWAIT in MySQL?
3
votes
1answer
4k views

error 1118: row size too large. utf8 innodb

I have tried to find a solution guys, and I am guessing that someone has met this before. But I cannot get hits down below 5000... most of which don't match my question. Using MySQL 5.6 - windows ...
5
votes
1answer
298 views

Query for data that is not there

I have this setup attendance table: ------------------------------------------ | member_id | attendance_date | event_id | ------------------------------------------ attendance indeces ...
4
votes
1answer
120 views

Problem joining two tables in MySQL

I'm new to MySQL. I'm having trouble in joining two tables: SELECT e.resourceName FROM resources_in_room r JOIN resource e ON r.resourceId=e.resourceId WHERE r.roomId=2; Table resources in room: ...
3
votes
1answer
999 views

How do you extend the tablespace when using innodb_file_per_table?

With innodb_file_per_table off, you can create multiple tablespaces on multiple devices if necessary to manage growth, balance I/O, etc. With the option on, how do you control growth of the files? Do ...
0
votes
1answer
748 views

InnoDB - Use combined index with primary key on GROUP BY

As far as I know, an InnoDB index always contains the primary index. I cannot get the following to work: SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid WHERE t1.b = 99 GROUP ...
2
votes
0answers
1k views

MySQL re-install fails under Mountain Lion

I have problems to reinstall MySQL on Mac OS X after it was running on my system. I uninstalled it because the PreferencePane wasn't working as expected, using the procedure described here: ...