MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
32 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
22 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
16 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
26 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
15 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
20 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
44 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
votes
0answers
21 views

Does mariadb or percona have the same standard mysql index lock problem on innodb tables? [closed]

Mysql has a problem with index lock (see http://dom.as/2011/07/03/innodb-index-lock/ and google innodb index lock for more details). Does percona and mariadb also issue a table lock when updating ...
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
14 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
8 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
95 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
0answers
43 views

Updating data sets from Online databases/Webservice [closed]

I want to know as to how to update the data regularly from the source, if its stored locally in your server or local machine.Lets say MySQL database. Source:It will be a webservice data repository. ...
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
36 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
64 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
27 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 ...
0
votes
0answers
22 views

choose data from JSON [closed]

I have some data like : id name ccode json 1 john 231 {"age": 12,"score": 90} 2 danny 231 {"age": 22,"score": 87} 3 danniel 231 {"age": 18,"score": 48} 4 sara 431 {"age": ...
1
vote
1answer
30 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 ...
0
votes
0answers
11 views

database result encoding changed

In my web site the content from the database is suddenly appearing in the wrong encoding! When I restart mysql the encoding is fixed, but after while it is again changed to the wrong encoding and I ...
2
votes
3answers
116 views

MySQL Explain has different row count than slow query log

I have this entry in slow query log: # User@Host: user[host] @ [ip] # Thread_id: 1514428 Schema: db Last_errno: 0 Killed: 0 # Query_time: 2.795454 Lock_time: 0.000116 Rows_sent: 15 ...
0
votes
1answer
39 views

Update query in a trigger

I have a configuration table: Table: refdatecol - id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
3
votes
4answers
192 views

Best practice for a Table with private columns

I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ...
0
votes
1answer
25 views

Simple SELECT query doesn't return FALSE if no results

This is probably a simple fix, but I can't figure it out. This is the MySQL query I have: SELECT (`clockcards`.`date` IS NOT NULL) AS `clockedin` FROM `users` JOIN `clockcards` ON ...
0
votes
0answers
53 views

Finding the right MariaDB configuration

I’ve read several articles about how to configure MySQL/MariaDB, but now I’m kind of confused how to find the right values. Some articles suggest not choosing to high values, others recommend adapting ...
5
votes
1answer
268 views

Is it Possible to Recover Data from a BLACKHOLE table? [closed]

I had created table with engine BLACKHOLE basically the BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty ...
0
votes
1answer
31 views

How do I count the number of different child id's belonging to a parent record?

For instance I have two tables in a music database. Table album and table track. Each track record has (amongst other things), an album_id and an artist_id Each album record has a unique album_id ...
0
votes
1answer
15 views

MySQL grant privileges has no effect

I've executed the following queries: GRANT USAGE ON my_db.* TO my_user@127.0.0.1 IDENTIFIED BY 'pass'; GRANT ALL ON my_db.* TO my_user@127.0.0.1; GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO ...
-2
votes
1answer
21 views

using ubuntu 14.04 when i use this command getting the error

just installed mysql server using sudo apt-get install mysql server when i try to display the database in terminal getting this type of error SHOW DATABASE; ERROR 1064 (42000): You have an error in ...
0
votes
1answer
16 views

mysql replication on windows server

How to setup replication(Master/slave) in MySQL 5.6 while both servers are windows based? All the things i have fond are Linux based replication. kindly someone help who experienced replication with ...
1
vote
1answer
25 views

Why/when does creating a mysql trigger require SUPER privileges?

I'm attempting to parse and understand what the MySQL doc says about when SUPER privileges are required for creating triggers. They say: If binary logging is enabled, CREATE TRIGGER might require ...
0
votes
1answer
47 views

group by clause without aggregate function

I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and ...
0
votes
1answer
33 views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
1
vote
1answer
37 views

MySQL Replication Lag Almost 23 Hours

I came into a new environment where the production DB was running on MySQL 4. Replication is usually about an hour behind but in the last few days it has been well over 24 hours behind. I'm not ...
0
votes
2answers
24 views

Creating a database with only MySQL command line?

The linux server on which I'm attempting to create a MySQL database has many difficult to work with permissions. My supervisor has tried to install MySQL Workbench on the server, but has been unable ...
0
votes
1answer
29 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning ... Actualy the tunning of my.cnf is like below My database is in MyISAM | Mysql 5.1.73 key_buffer = 500M ...
2
votes
2answers
165 views

UPDATE query with JOIN taking forever

I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would ...
0
votes
1answer
26 views

MySQL data transfer to different schema

We have two servers: one for the developper (me) and one for the tester. While working on the app, I sometimes make little changes in tables's schemas. (Like a new field, or a field type) I'm trying ...
1
vote
1answer
28 views

Change column without interrupting service

We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with ...
0
votes
1answer
20 views

Slow query log of wp_options WHERE autoload = 'yes'

Slow query log: # User@Host: # Query_time: 181.648376 Lock_time: 2.114138 Rows_sent: 1425838 Rows_examined: 1663532 SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'; Table ...
0
votes
1answer
32 views

MySQL Insert with Select

Trying to insert data to a table with values from different tables. INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref) select (select class.id as class_ref from class ...
2
votes
1answer
22 views

Is it possible to import a single mysql database directory?

I am somewhat new to mysql. I created some databases recently and, to back them up, I just copied the folders with those database names in the mysql data directory (i.e containing only db.opt, .frm, ...
3
votes
3answers
181 views

MySQL buffer pool usage + high load

We've got a quite powerful server: HP ProLiant DL360p Gen8, 2xIntel Xeon E5-2630 2.30GHz, 128GB of RAM. The whole data set should fit in memory: mysql> SELECT ...
2
votes
2answers
54 views

Should I drop a foreign key if I use a soft delete?

"ON UPDATE" scenario never happens in my application. As soft delete is used, "ON DELETE" cascade and restrict scenario never happen as well now. Then, should I just drop foreign key because I have ...
0
votes
0answers
17 views

Mysql query with LIMIT much slower under RDS (5.6) than old 5.1 system

We inherited an old legacy system running on mysql 5.1, and moved it to RDS running 5.6. One query is running much slower - it can take up to 20 minutes to run, where previously it took only a few ...
1
vote
1answer
20 views

MySQL InnoDB table accepts Non-Existent Foreign Key

Using EasyPHP 14.1 VC11 and phpMyAdmin (4.1.4) to create a website project in my home computer. The MySQL version is 5.6.15 according to the EasyPHP administration page. Working on Windows 7 64-bit. ...