MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

6
votes
3answers
17k views

How to view contents of table in mysql-workbench

Hi everyone am fairly new to mysql, I made a java program that stores data in mysql database. I am using this tool called mysql-workbench that can create databases, tables and much more. What I wanted ...
3
votes
1answer
148 views

mysqlservice command syntax

I'm new to MySQL. I've found myself in the position of having two different versions running on one host. I'm a little unsure of how these are working together. Do I issue >service ...
4
votes
3answers
3k views

AWS RDS MYSQL How do I reduce the DB size? [duplicate]

Possible Duplicate: How do I reduce the database size on AWS RDS MySQL? I have a mysql DB on Amazon. It is bigger than I need and I don't want to pay so much. Q: How do I make it smaller? ...
0
votes
1answer
216 views

Mobile Phone & Tariffs

I have designed database tables of Mobile Phones & Tariffs. It should support multiple mobile networks, sale type and the related affiliate. Points (for operators) are vary depending on the ...
2
votes
1answer
2k views

change encoding of database from latin1_swedish_ci to utf8

I have an huge database in latin1_swedish_ci. Now i need to convert all data to utf8 collation. My question is about the consistency of the information. When I do this change it is possible corrupt ...
2
votes
0answers
995 views

MySQL Server hitting 100% unexpectedly (Amazon AWS RDS)

We've been struggling with this issue for months. This week we upped our RDS instance to the highest performing instance and although the occurrences have reduced, we're still seeing our database ...
4
votes
1answer
16k views

Change existing datadir path

I want to change the datadir path in the my.ini file. The existing datadir path is C:/ProgramData/MySQL/MySQL Server 5.1/Data/ The size of my C: drive is 30 GB and the MySQL data folder is occupying ...
2
votes
1answer
288 views

What logical structure is used for storing rows in Oracle?

From the logical data separation point, there are tablespaces that consist of segments that consist of extend that consist of oracle blocks that consist of OS blocks. What logical structure actually ...
5
votes
2answers
4k views

MS Access / MySQL: unlock locked Records

I have a Microsoft Access database that is connected to a MySQL server via ODBC and is accessed by multiple users. Over time many records are locked and editing is not possible anymore. Is there a ...
1
vote
1answer
481 views

Merge 2 tables and fetch data one by one

I have two tables: product & offers. products has (prod_id, category_id, prod_name, brand, price, status) offers has (offer_id, prod_id, offer_type, discount_amt, validity) I want to display ...
4
votes
1answer
772 views

Does creating an index on BIT(n) attribute in MySQL make sense?

I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2. I've seen some mentions on the internet that BIT ...
1
vote
0answers
99 views

How to profile individual queries using PERFORMANCE_SCHEMA

I've been looking at SHOW PROFILES, which seems ideal for what I am doing. However, I've noticed a warning that SHOW PROFILES is being deprecated and that PERFORMANCE_SCHEMA should be used instead. ...
1
vote
1answer
1k views

MySQL query show times in milliseconds, not seconds?

In psql, with \timing on, query times are displayed in milliseconds + hundredths of milliseconds. In MySQL, they are only displayed in seconds + hundredths of seconds. Is there a way to get MySQL to ...
1
vote
2answers
187 views

What MySQL storage engine should I choose?

I have a database for my catalog webpage. Some tables are about 3GB with millions of rows. What engine should I choose for my database, InnoDB or MyISAM? There will only be about 1,000 new records ...
3
votes
2answers
338 views

Do MySQL sub-queries essentially use as much overhead as separate queries?

If I have a query like this: SELECT name FROM ( SELECT * FROM `table` WHERE id IN (1,40,300) ORDER BY AnotherColumn ) From a bandwidth and overhead standpoint, is that the same thing as doing ...
0
votes
1answer
59 views

Searching through lots of applications

Google search has instant search feature. While you are typing, it finds the pages. Apple.com search box. StackExchange ask question part finds similar questions while you are typing question title. ...
2
votes
2answers
3k views

Looking for an efficient way to fix “Could not parse relay log event entry…” error

I have a corrupt relay log in MySQL 5.0 and looking for the steps to fix replication without having to reprocess all the binary logs from the master again. Here is the full error message if you are ...
1
vote
1answer
1k views

procedure is not executing

Problem I am getting when I execute this proc: call `sp_frm4_px_prc_calc`(); Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version ...
1
vote
2answers
301 views

User-defined sort order for a query

I'm storing user preferences for sort order in a table: $queryHistory = "SELECT orderby,ascdesc FROM user WHERE UserID= ".$id; $resultHistory = doQuery($connection, $queryHistory); $numHistory = ...
6
votes
7answers
48k views

How to import a .sql file in MySQL?

I am trying to import a .sql file using MySQL Workbench and I get this error: ERROR 1046 (3D000) at line 28: No database selected I have first created an empty database called with the same name as ...
1
vote
1answer
275 views

query optimization delete nested query [closed]

Can the following query be optimized ? DELETE FROM COLOR_TAGS WHERE COLOR_PROJECTID = $id AND NoteID IN( SELECT ID FROM note_note WHERE BibID=$bid AND isdel=0);
8
votes
1answer
2k views

MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
2
votes
2answers
5k views

How does one change the default socket used by mysqladmin?

How does one change the default socket used by mysqladmin? I don't see a section for [mysqladmin] in /etc/my.cnf. It tries by default to connect to /tmp/mysql.sock, however, this is not the socket ...
2
votes
0answers
80 views

MySQL Partioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
3
votes
1answer
6k views

Get two weeks of data but group by a period of 7 days

I am looking to run a query that collects the number of results over the last 14 days, this is fairly easy and achieved like so: SELECT COUNT(id) FROM table_name What I'd like to do is then group ...
2
votes
1answer
2k views

Slave I/O thread dies very often

I have MySQL Replication working between two servers in master-master mode. Replication has been working from day 1. The IO Thread and the SQL Thread has never died. However, the Slave SQL thread ...
5
votes
1answer
1k views

Mysql sub query returns more than 1 row

SELECT * FROM wp_posts WHERE ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
4
votes
1answer
208 views

Can I partially invalidate the MySQL table cache?

I want to set a fairly large table to MyISAM mode to keep it cached proactively. The table file is approximately 3GB. After several hours, MySQL invalidates the entire table from cache, while < ...
3
votes
1answer
2k views

MySQL Insert into two tables using new IDs

I'm trying to select data already in the database and insert it into two tables using the generated ID from table one. table1 contains configuration options, table2 contains pricing data. Table2 uses ...
7
votes
1answer
3k views

How should I migrate a large MySQL database to RDS?

I've already looked into this a little bit. I realize there are similar questions on Stack Overflow, and Amazon themselves have a helpful document giving advice here: ...
4
votes
1answer
977 views

MySQL Import CSV with Multiple Field Separators

I'm trying to import this CSV file into MySQL that appears to be optionally enclosed by more than one character. Unfortunately, MySQL only supports one character as a field separator. I am stuck ...
2
votes
2answers
13k views

Declaring and using variables in triggers

I have been testing something with triggers today, but I am stuck with it and I do not have any idea what is causing my problem. DELIMITER $$ USE `pucko`$$ CREATE TRIGGER `after_delete_Alien` AFTER ...
4
votes
1answer
631 views

MySQL: Delete a record from all tables in one shot

I have a MySQL database, with one table that has a primary key, f_id that is referenced in multiple table as a foreign key. I want to delete all records where f_id is a specific value from all ...
0
votes
1answer
285 views

What would be possible reasons for a DeadLock to occur in Mysql Database?

If some one has a Table structure some thing like this, CREATE TABLE IF NOT EXISTS `domain_source` (`nid` int(11) NOT NULL DEFAULT '0', `domain_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`nid`) ) ...
2
votes
1answer
1k views

ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)

I get an error when I try to create a table: ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121) CREATE TABLE IF NOT EXISTS `cache` ( `cid` varchar(255) NOT NULL ...
10
votes
3answers
1k views

Where is the INNODB Performance Leak?

I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me. The Situation I am working on a system which handles a lot ...
4
votes
1answer
111 views

Is it possible to encapsulate a table in MySQL?

I am new to Databases. I want the following: UPDATEs in a database to happen only through a stored procedure The user does not have GRANT UPDATE The user has GRANT EXECUTE ON PROCEDURE I tried it ...
3
votes
3answers
406 views

An “unlimited” data model

I need to store an unknown amount of fields. Some are numbers (like IDs, zip codes and times), some are short strings (like names and states), and some are longer strings (such as descriptions). All ...
7
votes
2answers
512 views

What is the purpose of the USING parameter in SQL?

Working with some code on a standard MySQL database, and I came across this statement: SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 What's throwing me is: what is USING for, ...
1
vote
1answer
442 views

MySQL to add to cart 1-4 Socket Server

When I check for price tags for MySQL, I see a clause called "1-4 socket server" or "5+ Socket Server".. what does this mean? "Socket" in generic term means Inter Communication Protocol. For MySQL, ...
0
votes
2answers
955 views

limit and then group by in MySQL

I have a mysql table having following structure : payment_id(int auto_increment), emp_id(int), chargeTime(datetime), payment(double) I need to have three latest (with respect to chargeTime) ...
2
votes
3answers
692 views

How to select data in a sub query only once?

I have the following query: SELECT ordno from orders where ordno NOT IN (SELECT ordno FROM invoices) There are about 25000 records in each table. I have indexed the ordno column. The query ...
2
votes
1answer
92 views

Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0

I didn't know if this is possible using just a query or if I need some procedural logic on this one. I have this table: CREATE TABLE `default_messages_users` ( `id` int(11) NOT NULL ...
3
votes
1answer
2k views

Does the Amazon RDS backup/snapshot service lock tables?

We have a database that is 100+GB. Most tables use the InnoDB engine. A regular dump takes 1-2 hours. If we moved to Amazon RDS, would this backup cause table locks? Does Amazon use a simple ...
8
votes
1answer
1k views

Friends relations in MySQL

I'm developing a friendship relation in MySQL where friend relation is mutual. If A is friend of B, then B is friend of A. If one of the users end friendship then relation drops. I want to learn which ...
13
votes
1answer
10k views

Scalability limitations of PostgreSQL and MySQL

I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB. I suspect that limits as such do exist as one would not come up with Netezza, ...
5
votes
1answer
284 views

Prevent users deleting more than a certain number of rows in MySQL

The problem: I have a table with rows, nothing special about it When a database user removes a row, it should increment a number A database user is only allowed to remove 3 rows max in the table A ...
7
votes
2answers
1k views

MySQL : perform a big data migration between tables

I want to migrate data between 2 InnoDB tables. Currently I'm running this query: INSERT INTO table_a SELECT * FROM table_b; If the dataset grows, what's the best way to avoid CPU overload? ...
4
votes
2answers
9k views

How do I get the current and next greater value in one select?

I have a InnoDB table 'idtimes' (MySQL 5.0.22-log) with columns `id` int(11) NOT NULL, `time` int(20) NOT NULL, [...] with a compound unique key UNIQUE KEY `id_time` (`id`,`time`) so there can ...
3
votes
3answers
4k views

MySQL: selecting arbitrarily ordered rows after a specific id

Please excuse the awkward wording of my title, I'm not quite sure how to phrase what I'm asking. Suppose I have a table like this: id value 1 10 2 40 3 20 4 50 5 40 Running this query: ...