MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
31 views

MySQL 5.6.17 running out of memory when opening table with many partitions

We are currently setting up a MySQL database (version 5.6.17) on an Amazon db.m1.large RDS instance for storing time series data. Each time series property of an object is stored in a separate InnoDB ...
0
votes
1answer
33 views

Connecting to MYSQL on an EC2 instance

I'm trying to use Zapier to connect to our MySQL database in order to add results from forms (hosting with WordPress) directly into our internal database. The database is working properly and so are ...
0
votes
1answer
24 views

Restoring Mysql Table From ibdata1, ib_logfile0, ib_logfile1 (without frm)

How to recover an innodb table that only left with ibdata, ib_logfile0 and ib_logfile1 without any frm file? Thanks
1
vote
1answer
27 views

MySQL: Merge Same Database on Several Location into Single Location

I have registration form, which exactly same for several location. Each of them has REG column, which this field is unique for each row. Basically each data input have different REG number for it, ...
1
vote
1answer
11 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
0
votes
0answers
17 views

Text comparison select query needed that finds most whole word matches within larger string

I have a database of products, many with similar names. For example... iPhone 5s (GSM model) A1533 iPhone 5s (GSM model China) A1518 iPhone 5s (GSM model China) A1530 iPhone 5c (GSM model) ...
1
vote
0answers
39 views

Efficient query involving union and virtual/derived table [migrated]

Both of these mysql queries produce exactly the same result but query A is a simple union and it has the where postType clause embedded inside individual queries whereas query B has the same where ...
0
votes
1answer
25 views

Does column size increase break SELECT *?

I searched a little bit on this topic before realizing it is somewhat tricky (for me, at least) to come up with an effective search phrase for it because it overlaps with other common topics. So here ...
0
votes
1answer
36 views

mysql hardware refresh + version upgrade

currently I am running MySQL 5.0.80 on windows server 2003 (x64), 64gb memory, I have a master and 2 slaves. It's a large 400 gb Database. I am setting up all new hardware (SAN + dedicated MySQL ...
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 ...
0
votes
0answers
19 views

Markup Languages and SQL Queries

I need to store some metadata in an MySQL database. This data will need to be stored in some form of markup language and reside in a 'meta_data' field on a table. I will need to run searches ...
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 ...
2
votes
1answer
46 views

Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

I am working on the design of a Wordpress blog which when finished will almost-always serve pages from disk cache and this is because all posts will be published in batch and then cached, with at ...
1
vote
1answer
33 views

MySQL: Find ID with only 2 attached IDs in subtable

I've got 2 tables. 1 containing my Threads and 1 containing the users attached to these threads. I want to find the ThreadID with 2 specific UserIDs attached, no more no less. This is what I've come ...
0
votes
2answers
19 views

Restoring data from dumped sql file gives: mysql: unknown option '--innodb_file_per_table

I was trying to separate DB indexes from ibdata1 I took the steps in what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql I dumped data, dropped databases, and started mysql service. I got the ...
0
votes
0answers
39 views

Show single value of one column for multiple rows of other columns

I have a SQL query which displays my data results like this. date emp_id emp_name spent_hours 19-08-2013 1024 jack 10 19-08-2013 1024 jack 11 19-08-2013 1024 jack ...
0
votes
2answers
38 views

What's the use of assigning foreign keys on MySQL [duplicate]

Aside from indexing the value of the foreign key, what is/are the advantage of assigning foreign keys to databases? I've seen a lot of databases that does not assign foreign keys, but is relational by ...
0
votes
0answers
28 views

Auto increment holes in primary key - any issues?

I have an application that is creating large holes in the auto incremented primary surrogate key to the table as it uses lots of INSERT ... ON DUPLICATE KEY UPDATE queries. I'm looking into changing ...
0
votes
0answers
24 views

How do triggers work on partitioned tables?

I have partitioned a table on the basis of a column. This column can either have 0 or 1. So, only two partitions (p0, p1) have been created internally. On this table there are 3 triggers which execute ...
0
votes
0answers
40 views

Copying a table with its structure, indexes, and associated triggers?

I have studied this post on Stack Overflow: Fastest way to copy a table in MySQL. But no answer is copying everything like the table's structure, data, indexes as well as associated triggers. Is it ...
3
votes
2answers
64 views

mysql - is it possible to create a partition expression based on two keys / indexes?

if i have a table with, lets say 500 million rows, and among others there are two main individual indexes on that table. the table looks like: CREATE TABLE `t1` ( id_1 bigint unsigned not null, ...
0
votes
1answer
49 views

mysql dump a table to remote host

i want to dump a table from localhost host (with public ip), here is the command so far, the command will be run on localhost mysqldump --opt --host localhost --user=username --password=password ...
-3
votes
1answer
29 views

MYSQL Combine two results into one

I have two mysql queries first query having results bookmark_id, is_fav, title and second query have results like this bookmark_id, is_deleted Sometimes both have same results or may have ...
0
votes
0answers
19 views

MySQL - How many slaves can be handled by a master? [duplicate]

Can someone please put some highlights on this, How many slaves can be handled by Master. I have 100+slaves for a single master, how this process will work?
1
vote
2answers
42 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
41 views

Should I add new column or new table

I have an existing table called exampletable1, now i have been given a new list of data called "medicines" it only have: male,female as its value. I'm having a problem whether to add another varchar ...
1
vote
1answer
15 views

How do you remove reported non existing tables from mysql?

I was trying to export a mysqldump --all-databases and noted errors. A subsequent mysqlcheck --all-databases revealed several non existent tables like the following Error : Table ...
0
votes
1answer
91 views

MySQL 5.1 -> 5.6 problem, select distinct order by limit query stops using index

I'm testing a MySQL 5.1 -> 5.6 upgrade, and I've run into an issue. I have a query that hits a ~30 million row table, selecting distinct IDs with an order by/limit restriction. Sample query is ...
1
vote
1answer
30 views

How to select the line with the max(date)?

the data looks as following: id group date 1 a 2013-01-01 1 b 2014-01-01 2 a 2012-01-01 2 b 2013-01-01 My database is mysql, for each id, I need to select the line with ...
0
votes
1answer
42 views

Need to delete consecutive, unchanged records

I have a rather large MySQL db that is tracking pricing changes for a large number of products. Each entry has a SKU a datetime and a price. Sometimes, there is no change in the price from one time ...
0
votes
1answer
33 views

CASE conditions in SELECT statement

I have the following SELECT statement: SELECT expt_id, iso_stain, test_stain, stain_type CASE WHEN stain_type = 'Blue' THEN iso_stain = 'NA' CASE WHEN statin_type = 'Hematoxylin' THEN ...
0
votes
1answer
27 views

Set MySQL option to restore MySQL server after deleting ibdata1

ON an Ubuntu Server 12.10 machine, I had attempted to delete the ibdata1 file before reading Rolando's explanation about how exactly to do so. Specifically, I had not set innodb_fast_shutdown = 0 ...
0
votes
1answer
19 views

How to access another Connect server table in my Local server in SQLyog 7.01?

In same server name using same database select * from area;(Working Good) Same Servername but Different Database select * from mysql.event(Working Good) Now i am connection TWO Different server ...
0
votes
0answers
8 views

Database for large dynamic network graph - best practise?

I am constructing a MySql database which is to contain data for a large network graph. There will be thousands of nodes and ten thousands of edges. The nodes and edges will be assigned a category. ...
0
votes
0answers
30 views

Previously inserted rows disappear at call of PHP script

I am developing a server-side portion of a sport manager application using MySQL (5.1.67), with InnoDB as storage engine, and PHP (5.3.3). When I try to execute a script containing any kind of query ...
0
votes
1answer
24 views

Cross tab query with concat field

I want to create a cross tab query for my table.but when i am using concat i am unable to get ouput succesfully.can any one help me with my query. SET @sql = NULL; SELECT ...
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 ...
0
votes
1answer
22 views

How to restore data after MySQL Upgrade?

I upgraded from MySQL 5.5 to 5.6 by uninstalling 5.5 and then installing MySQL 5.6. Now that MySQL 5.6 is running, none of my DBs or data is present/visible in MySQL Workbench. I also see that I ...
1
vote
2answers
58 views

MySQL performance problem using indexed datetime column

I tried to solve the following problem for about one hour now and still didn't get any further with it. Okay, I have a table (MyISAM): ...
1
vote
0answers
27 views

One table for article comments, video comments and photo comments or multiple tables?

First DBA question. Greetings people. I hope I'm not duplicating a question but it's very hard finding database design related questions here (for multiple comment tables), as all 'design' questions ...
0
votes
0answers
20 views

self join of single table to find time difference of adjacent rows of a single column which are not in sequence

I want to find the time difference between two adjacent rows of a single table which are not in sequence.i made a self join of table and tried but i did not achieve what i need .can anyone please help ...
0
votes
0answers
12 views

Load data from file only for specific column based on other column value

I am familiar with basic usage of 'load data local infile' and importing specific columns only. What I want is however to import a single column from file based/related on data from another column. So ...
0
votes
0answers
66 views

hot/live sql backup

please excuse my ignorant question as I am still fairly new to database things so I have a mysql database and a part of it has file with average size of 10M I store them in their own directory and ...
3
votes
2answers
101 views

How to optimize this slow SQL query

I have a slow SQL query on my Drupal website and I don't know how to optimize it. Here is the query that Views produces: http://pastebin.com/StAEiXbY SQL fiddle: http://sqlfiddle.com/#!2/20081/1/0 ...
0
votes
1answer
43 views

Query a database for empty tables

In my system there are two tables called ‘faculty’ and ‘faculty_assets’. I need to delete faculty by faculty from the ‘faculty’ table. But system should allow deleting the faculty when only ...
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 ...
0
votes
1answer
69 views

MySQL - Indexes deleted, reduced column size but table size increases

Here is the original table definition which contains 107547934 (~107.5M) rows CREATE TABLE `twitter_tweets` ( `id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ...
1
vote
1answer
19 views

How to put this particular constraint on this link table in MySQL? [duplicate]

I have the following three tables in MySQL: Store, Groups, StoreGroup. A store can belong to several groups. StoreGroup is the link table that records the groups a store belongs to. The Groups table ...
2
votes
1answer
28 views

Is there a difference between RANGE and LIST method for partitioning by consecutive integer values?

I have a table with a year column that has consecutive values -- 2000, 2001, and so on. It's a read-heavy MyISAM table, with 25+ million rows for every value of year and a comparable set inserted in ...
1
vote
1answer
34 views

MySQL consistent nonlocking reads vs. INSERT … SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...