MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
35 views

Best way to store data in tables

I've been thinking on how to store data efficiently in database tables, I'll give the examples in visual look. See pictures below. I've come to think to store data in database using compressed values ...
4
votes
1answer
53 views

identify gaps and islands

I have a database that has the following structure +---------------------+------------+---------------+ | timestamp | EngOilP_sd | CompOilLVL_sd | ...
1
vote
1answer
30 views

Losing the last column of data

After Loading data into the table and running a query on it, it tends to lose the last column of it. Like somewhere in import process it lost it. The file i was using was UTF-8 .csv format. A Is ...
-3
votes
0answers
20 views

duplicates of part.num to be summed and add another column called user [closed]

There are 3 columns part num, Qty and MO. Each MO has part numbers.So there might be same part numbers in MO's. Each part num has qty. So, if I group by part num, I get Qty. 1.There are duplicates of ...
0
votes
1answer
69 views

compare results between two select queries mysql

i'm making test application where users will input their select queries in input box and the application have to compare them if they are identical with "correct answer" query. "Correct answer" query ...
2
votes
1answer
32 views

Unique Index/Constraint with multiple columns, one column is nullable

My questions is whether it's possible to have unique index comprised of multiple columns where one of the columns may contain NULL. Example: A table named 'regulated_person' has a number of columns ...
0
votes
2answers
60 views

How to know which user update/insert or delete in MYSQL database?

Using the triggers I could make the establishment of a historic recordings in MYSQL tables where i detect all changes in my database. My problem is that I want to know which user made ​​the change in ...
6
votes
3answers
190 views

Implementing versioning system with MySQL

I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help. Initially I had my blogstories table with this structure: | Column ...
0
votes
1answer
34 views

Mysql create trigger if not exist

I have a script that creates triggers, I would like to run it multiple times and if a trigger already exist it needs to skip the creation part. Is there a "create if not exist" in mysql triggers? I ...
1
vote
3answers
62 views

MySQL restore old backup over new data

We have a MySQL backup. Did some changes on our database and some data was lost. In the mean time new records were added. We have to restore the mysql backup over our latest data. Some of the tables ...
0
votes
1answer
29 views

replication filtering replicate-rewrite and do-table Mysql 5.6 (innodb engine )

i have a master / slave replication setup based on statement based replication. On master database name DB1 , i have 5 tables and i want to replicate only two table on slave's database name DB2. Also ...
4
votes
2answers
188 views

Potential schema for product failures and repairs

I'm creating a schema for product failures and repairs and would appreciate feedback on the schema. The database is intended to capture all product failures and corresponding repairs. A product can ...
0
votes
2answers
54 views

Remove duplicates from multiple joins

I'm using WordPress $wpdb->get_results for this query (it is simplified for question purposes): SELECT customer.id, customer.name, address.address appt.appointment_date, pay.date FROM customers ...
0
votes
1answer
25 views

Based from experience, is dreamfactory better paired with a RDBMS like MySQL or NoSQL like MongoDB

This is our first time to create a data logging web application and we have chosen to use Dreamfactory as the backend framework. We have been used to with MySQL from the past with small amount of data ...
0
votes
1answer
50 views

Mysql Slow when join query running increaed

Our Mysql dedicated server box always have around 40 - 80 active user connection , so the query speed very good and Cpu usage low . (around 4 - 8 core from 24 core) but sometimes when some wordpress ...
0
votes
0answers
32 views

MySQL drops every connection, even locally

Every time I run a MySQL query it has to reconnect mysql> show variables like '%timeout%'; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 60 ...
2
votes
0answers
27 views

Getting error 1064 in automatically-generated code from MySQL workbench [duplicate]

I have started using mySQL workbench (Version 6.3.4.0) and I have used the EER Diagram tool to design a schema. So far, so good. However, when I try and run the Forward Engineering, I get the error ...
2
votes
2answers
42 views

Finding differences between two tables takes too long

I'm trying to find the diff of two tables using a left join. My query is as follows: CREATE TABLE my_diff (INDEX my_index (name, type)) SELECT AA.name, AA.type FROM AA LEFT JOIN BB USING (name, ...
1
vote
1answer
188 views

How do you calculate how much hardware resources you need for a database?

We are in the process of scaling our database servers. I was wondering how should we calculate how much hardware resources we need for our databases? Here is a little bit information about our ...
0
votes
0answers
33 views

call a stored procedure from sql window in PHPMyAdmin

I have a simple stored procedure that selects rows from a table. In phpMyAdmin, in the list of my procedures, if I click the Execute option, it executes properly, returning the desired rows, and ...
1
vote
1answer
39 views

Mysql triggers for auditing

We have a requirement to fulfill - we need an auditing table for all of our entities. We wonder if creating the following triggers will be a good practice: CREATE DEFINER=`root`@`localhost` TRIGGER ...
1
vote
0answers
43 views

create a trigger to alter table add column based on inserted row [migrated]

I have two tables in a database named as follows: 1. state_master +---------------+-------------------+ | state_id | state_name | +---------------+-------------------+ | 1 | ...
0
votes
0answers
20 views

mysql master master replication, master log position not changing

I have setup master-master replication on database set on two servers (say 1.1.1.1 & 2.2.2.2), I have edited the my.cnf file to: server-id = 1 log_bin = ...
4
votes
0answers
54 views

Find Shoe Size Selection

I'm developing a feature that allows users to input their foot dimensions and have the proper shoe size returned to them. I'm having difficulty deciding on the best way to implement this feature. The ...
1
vote
0answers
16 views

Several Data Importation Illogical Latency in MySQL [closed]

I am used to importing a 4GB of data from an .sql file towards a testing server (hardware). I tweaked the database to speed up inserts, and, so far, the script takes 6 hours, more or less. We were ...
2
votes
0answers
19 views

Issues with mysql enterprise backup, assertion failure on restore

I'm trying to restore a backup to a new server, but having some issues with corruption. The source server has a 2.5T table which is very active. I copied the latest backup over to a new server and ...
4
votes
1answer
73 views

Better database design

I'm using a MySQL database to store data from my java application. In java I have two classes: The VocabularyList class has a name and holds a list of vocabularies. A Vocabulary has a vocabulary ...
2
votes
1answer
39 views

MySQL Import Super Slow

I'm running MySQL on a HyperV virtual machine. The host has 384Gb of RAM and is nowhere near being fully committed. I've tried giving the VM 128G of RAM and 8 CPU and I've tried 2 CPU's and 32GB of ...
1
vote
0answers
20 views

Update remote MySQL database upon changes in local SQL Server database

I have a Web store running with MySQL on the remote host. And I have inventory management software using Microsoft SQL Server on my local system. I want to update product stock in the web store when ...
2
votes
1answer
63 views

Stuck in “query end” status every 5 minutes

I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about. An UPDATE query that is running up to tens of times per second is sometimes really slow. Here is ...
0
votes
0answers
42 views

mysql - suggestions for table design with huge number of columns

We have a table that consists of a huge number of columns (usually between 500 to 1000, depends on our clients websites). We are suffering from speed issues due to this, and we're looking for the best ...
3
votes
1answer
67 views

4m Rows in MySQL, ~5 Minutes to Run Query

I have 2 tables, a users table and a log table which literally logs each time a user signs in. What I'm trying to do is grab a list of all users with a certain rank who have not logged in for over a ...
1
vote
1answer
10 views

Automatic replication on slave node after service restart

I am new to mysql administration. we have master - slave replication environment. I added a 2nd slave node to existing env [master-slave] and replication is successful. Whenever I restart the mysqld ...
2
votes
1answer
61 views

MySQL - Ordering 'featured' items first

I need to have the results of a query show featured items first for a given date before listing the day's other items. You can ignore the categories that are being pulled from another table. ...
2
votes
3answers
33 views

Are parallel queries with mysqlnd actually possible?

So I have been playing with asynchronous database requests in PHP using mysqlnd in an attempt to achieve parallel database requests and cut down total execution time i.e. rather than: total time = ...
0
votes
0answers
16 views

innodb_autoinc_lock_mode

We have a scenario. We are migrating from master running MYSQL 5.5 statement based replication with innodb_autoinc_lock_mode=1. We have made our new master (in RDS MYSQL 5.6) a slave of old master. ...
0
votes
1answer
14 views

Changing Data Directory on MySQL DB Server

I have installed mysql server 5.6.24 on Windows Server 2012 R2. I imported dump files and the data files are stored in C:\ProgramData\MySQL\MySQL Server 5.6\data I want to change directory like ...
0
votes
1answer
29 views

MongoDB to MySQL Replication Automatically

i have Mongodb server and mysql server. i want to use mongodb for "write" and mysql for "read". so i am looking for some tool or some process by which we can tranfer mongodb to mysql server on ...
1
vote
2answers
44 views

group_concat in separate columns

I have a players table id name -- ---- 1 jack 2 john 3 mike 4 pikachu And I have a matches table where these players can be matched together. id pair_id player_id -- ------- --------- 1 ...
1
vote
1answer
45 views

Slow MySQL query when the IN list is long

I have 3 tables, namely: product, product_has_category, category: CREATE TABLE IF NOT EXISTS `product` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `des` text COMMENT 'description', ...
1
vote
0answers
48 views

MySQL server slow due to too many information_schema queries

Recently our server has been slow due to too many slow information_schema queries, which I am unable to kill. Please see the screenshot below: These queries are taking over an hour to run each. ...
0
votes
1answer
26 views

MySQL Trigger On UPDATE “Unknown Column” Error

I want to log a table when its updating a row which is column "insertDate" or "updateDate" value is before than 3 month. I wrote this trigger: DELIMITER /// CREATE TRIGGER ...
0
votes
2answers
31 views

Upgrading the mysql server from 5.1 to 5.6?

My server have CentOS6. Currently I'm are using the Percona 5.1.47 version. Would like to upgrade to 5.6. What is the best way to do, and what are the prerequisites to keep in mind before doing ...
0
votes
0answers
25 views

MySQL: write in one connection then read in another

Say I have a library function, exec_sql(some_sql), every time it's been called, it obtains a connection from a connection pool, execute the sql using that connection, and then release the connection. ...
2
votes
1answer
63 views

Why does coalescing make this query quicker?

I have the following query SELECT SQL_NO_CACHE `table1`.* FROM `table1` LEFT JOIN `table2` ON table2.id = table1.table2_id WHERE (`table2`.`date_assigned` >= '2015-06-21') AND ( ...
1
vote
2answers
59 views

Tips about MySQL installation

I'm starting to prepare my server for a classic configuration Apache PhP MySQL. I installed Apache 2.2.24 without problems. (Output "It works"). I installed php 5.2.1 - win32 - installer. (launching ...
1
vote
0answers
26 views

Search a value from multiple column and multiple table [closed]

I have to search a value from multiple column and multiple table and each table is linked by foreign key and private key (in parent table , there is only one parent table and rest is child table of ...
1
vote
2answers
47 views

How to optimise MySQL query that uses dependent subquery

I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score. There are only 1000 rows ...
0
votes
1answer
40 views

Use WHERE on result of SUM

So, I have this query working great SELECT u.username, SUM(kort) as total FROM user as u LEFT JOIN userresult as r on u.userID = r.userID WHERE r.date >= '$date' group by u.userID order by ...
0
votes
1answer
30 views

mysql MATCH relavancy score

I thought this would be an easy question to google for; but I couldn't not find it. Given the following query: SELECT name, MATCH (name) AGAINST ('vision black*' IN BOOLEAN MODE) as rel FROM ...