MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
149 views

Relaying changes to a slave from another master with Tungsten on MySQL

I am trying to setup a multi-master type of replication with each master having an additional slave using Tungsten. As an example, lets say I have 4 hosts (A,B,C,D) and hosts A and C are the masters, ...
4
votes
0answers
48 views

MySQL Commited data not seen to select query

Context: The framework used is Spring and all queries are run with JdbcTemplate. Mysql Server version is 5.6.19. The table is an InnoDB table and defaults like auto commit and isolation level ...
0
votes
0answers
13 views

How to sort new data for display?

I am new to SQL. I have a table called Categories with 3 columns: id int(25) category varchar(25) subcategory varchar(25) I would like to add a new subcategory "Real Estate Agency" in the category ...
0
votes
2answers
3k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
0
votes
2answers
18 views

Generating a sequence of values

How can I change 2,2,2,1,2 into 201,202,203,101,204 in MySQL? I have a table with data in the format of: +---+---+---+ | U | C | R | +---+---+---+ | 1 | 2 | 1 | | 1 | 2 | 2 | | 1 | 2 | 3 | | 1 | 1 | ...
0
votes
1answer
6 views

What is happening when Seconds_Behind_Master oscillates between two very different sets ov values?

I have a Percona 5.6 slave, newly set up, that is replicating from a server that is itself a slave to a third. The middle server (dbS2, the master for dbS3) is currently ~260000 seconds behind the ...
3
votes
3answers
280 views

How to duplicate related rows

Tables: Payments with auto-increment ID and one-to-many relationship to Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects. Now next month, ...
1
vote
0answers
16 views

How do you output field changes in mysql the most efficiently?

I have a rather large employee feed coming in and each employee has about 50 fields. I take the employee data and add on a bunch of other stuff and this gets outputted to one of our vendors. Most ...
0
votes
1answer
135 views

How to merge two databases, both with primary keys

I have two databases, both with the same tables, but the data is out of sync. I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a ...
1
vote
3answers
286 views

Table Design for Student Attendance hourly for each day

I have created a table (attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly (1-8hrs) every day. I have the student details in separate ...
1
vote
3answers
983 views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
2
votes
2answers
18 views

Should the Redo log on a production MySQL server be configured to another location?

Concerning the innoDB storage engine, should the innodb_log_group_home_dir option (The directory path to the InnoDB log files) be configured to another disk, other than the default directory, in order ...
1
vote
1answer
51 views

Tuning MySQL query from explain

I am working on a search query in MySQL. I am getting this from explain: Original screenshot These are my indexes: Table definition: Original screenshot Query: SELECT `t`.`id` AS ...
0
votes
2answers
33 views

Mac OS X 10.10 mySQL 5.6.26 accessible from CLI but not port 3306

I did a fresh install of mySQL 5.6.26 on my Mac today. I was able to access mySQL from Terminal.app as expected, however I could not get mySQL workbench to attach to 127.0.0.1 port 3306. What really ...
1
vote
2answers
19 views

MYSQL find if a default value has been set for a table (DateTime Specifically)

I am trying to internationalize a database, but there are places where the timestamp that gets set is using NOW() instead of UTC_TIMESTAMP(), and it's using Amazon's RDS with multiple MySQL servers. ...
0
votes
1answer
35 views

MySQL: is it more efficient to query for duplicate row or insert(update) on duplicate?

Just curious on resource efficiency. If I have a list of inserts that need to be accomplished repeatedly which may contain duplicates (there is no way around this, the API I have to work with sucks), ...
1
vote
1answer
32 views

MySQL Query with good execution plan is very slow due to Sending data operation

I have the following query: SELECT a.borrowerId AS borrower_id, a.created, NULL AS initiator_team_id, NULL AS initiator_user_id, NULL AS old_team_id, NULL AS old_user_id, ...
0
votes
1answer
13 views

Error 1241 When Calculating Percentages

I am trying to get my percentages to equal 100%. As of right now they far surpass 100%. I need to account for the percentages in two different tables, ua_money_allocation and ua_expenses_by_school. ...
0
votes
1answer
8 views

mysqlbinlog: unknown variable ssl_key or ssl_cert or ssl_ca

I am trying to recover binlog from a remote server using mysqlbinlog client program, however it appears that mysqlbinlog doesn't recognize the SSL options that's required to establish SSL connect to ...
2
votes
1answer
20 views

How to write a query that counts rows by date?

I have a database named emp1. There is a table named tx_feeder. I want to get the count of rows from that table. I have used the following query to get the data of single day, select count (*) ...
0
votes
0answers
12 views

How can I associate different not done continuation blocks with two cursors in the same scope?

If I have two cursors declared in the same block, how can I associate different not done continuation handler actions that are independent of one another and only execute with the cursor they're ...
0
votes
1answer
261 views

Update MySQL Stored Procedure Definers on shared environment

I am accessing a MYSQL database through PHPMyADMIN. My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL ...
1
vote
1answer
11 views

Innobackupex: How to extract individual files from xbstream compressed backups

We are scripting a mix of full and incremental backup setup for our prodcut backend with sunday triggering full backup and incremental backup on all the remaining days. In this process, we should be ...
1
vote
0answers
11 views

Select records where difference between datetime column1 and column 2 is greater than X amount of time

As the title indicates, I'm trying to return records where the datetime difference between two datetime columns is greater than X amount of time. I'm having trouble wrapping my head around this ...
2
votes
1answer
21 views

Recover mysql using data folder

One of our MySQL servers died but somehow we were able to recover all the files from it. This server was a slave at one time however we stopped it being a slave so we could use it for housekeeping ...
2
votes
1answer
48 views

Get values from Select statement and Insert them in Temp table Stored procedure [on hold]

Here is my stored procedure. I want to select data from a table, save it in a variable then insert in other variable. However, my select statement has where. In it the value for the day should come ...
0
votes
1answer
121 views

What's the approach to figure out reason for mysql crash?

The database I'm using crashes a lot recently. And I can't find a decent way to dig into the issue. I looked into mysql error log, but don't know how to start. I know there was a "load data infile" ...
2
votes
1answer
35 views

MariaDB Spider Engine Out of Memory

I had almost standard MariaDB with InnoDB engine working fine but I wanted to test out the Spider Engine + Sharding/Partitioning. I had a query which was passing fine on the MariaDB InnoDB for ...
0
votes
2answers
109 views

MySQL Monitoring Script in Shell or Perl for Linux

I need a MySQL Monitoring Script in Bash or Perl for Linux. I found some but they are quite big with a lots of checks that are not useful at all for me and in this case I should customise the ...
2
votes
1answer
92 views

Two queries usually run fast but get slow when joined

There are those 2 tables CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
0
votes
1answer
141 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
1answer
36 views

Is my shopping list database schema good enough?

So Im wondering if my schema is correct? It's basically suppose to hold shopping list items for each user, a user belongs to a household. Items will be added to the household. The stocklist is ...
-2
votes
0answers
42 views

How to use multiple conditions using AND and OR in WHERE statement? [on hold]

how can I apply a multiple conditions in my WHERE clause using AND and OR? What is the best way to apply this? My scenario looks something like this. ...
0
votes
1answer
156 views

increasing disk space for mysql data volume

I have a master mysql server which has approached 80% of the available disk space. Total disk space size is 200GB. Though, it's not an immediate concern but I have to deal with at some point soon. the ...
1
vote
2answers
37 views

HeidiSQL: Import CSV

A few lines of my CSV: "id","date","date_","pow","ene","reac" "ES0134000563024539AM0F","2013-05-02","2013-06-05","62","43","43" "ES0134000573024539AM0F","2013-05-02","2013-06-05","62","43","43" ...
0
votes
1answer
1k views

MySQL Workbench Cannot start SSH tunnel manager [on hold]

I don't know why this error is happening when I try to connect by MySQL Workbench. Cannot start SSH tunnel manager. I'm using slackware 14.1 and installed all dependencies. I already installed the ...
0
votes
1answer
1k views

How to configure my.cnf for Mysql 5.1, which is using too much CPU?

I am new Database engineer. I am having trouble in my server which is using too much CPU on database query(simple count statement). I have googled about it, so i found out it depends on system ...
2
votes
1answer
21 views

Plotting graphs for MYSQL

I was executing following command mysqladmin ext -i1 -c3600 It produces valuable output but I want to use the output to plot graphs so that I can visualize inserts, updates, buffer and other stuff. ...
0
votes
1answer
130 views

ubuntu - Pm2 mysql high cpu usage

Not sure what is going on here but would love some help if any can. I am running on digital ocean 1GB ram ubuntu server a node.js application that is is launched with pm2 and that uses a mysql ...
0
votes
1answer
169 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
0
votes
0answers
15 views

MariaDB multi-master replication (star topology) - Bizarre out-of-sync behavior

This may look familiar, I've been learning about this stuff for a few days - but this is a new question. I have a setup like this: Every node is a master, and I have them all replicating to each ...
0
votes
3answers
243 views

What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE?

What's best performance wise, two separate UPDATEs: UPDATE table1 SET enabled = 0 WHERE code NOT IN ( SELECT id FROM table2 ); UPDATE table1 SET enabled = 1 WHERE ...
0
votes
1answer
182 views

MySQL Subquery Returns Null In Stored Procedure, Works Fine In Query

I have a subquery in a MySQL Stored Procedure where the value returns null even though the data is clearly there. Pulling the subquery out and running it works fine. I've checked the parameter values ...
0
votes
2answers
138 views

Recommendation for the mysqldump?

I have approx 90 GB of database, I need to schedule a mysqldump backup , thats why I Required a recommendation how much time it takes for the full hot backup? Because I didn't taken that much size of ...
3
votes
2answers
27 views

MySQL Multi-Master Replication Topology

Disclaimer: I've never worked with replication before, but I'm doing all my research. Not really having a bunch of in-depth knowledge about MySQL makes a lot of the documentation hard to understand. ...
0
votes
1answer
242 views

MySQL data transfer to different schema

We have two servers: one for the developer (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 ...
4
votes
3answers
161 views

MySql Invoice and Subscription Schema

I have an ecommerce db built and I'm adding subscriptions. See the simplified structure below. orders id, customer_id order_items id, order_id, product_id invoices id, order_id subscriptions id, ...
1
vote
1answer
36 views

Which technology to synchronise SQL Server and MySQL data?

Which technology should be used if I want to move my data from SQL Server to MySQL server when data is entered to SQL Server row by row and getting feedback from MySQL server? Should it be 1. ...
1
vote
1answer
28 views

MySQL connectivity error

I have a MySQL database on a Linux server that I am able to access through putty logging into servers terminal. When I try to connect to the MySQL database remotely, I get the error: Lost ...
0
votes
0answers
14 views

Write operations on the RDS Mysql Database becomes Slow during long run load test [on hold]

I'm having serious performance problems with MySQL and the InnoDB engine in long run load test. Even the simplest table makes writing operations.I'm using Amazon RDS m3.xlarge instance for performance ...