MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
15 views

MySQL queries taking very long/not finishing

I just started working with databases and have a problem with queries taking very long. My table has the following form CREATE TABLE table (column1 INT(10) AUTO_INCREMENT PRIMARY KEY, column2 ...
0
votes
0answers
7 views

CALL MySQL Stored Procedure in a C application crashes the application with segmentation fault

I want to use CURSOR of MySQL in an application written in C. I created a stored procedure CREATE PROCEDURE curdemo(IN id INT) BEGIN DECLARE a INT; DECLARE flag BOOL DEFAULT FALSE; ...
2
votes
1answer
28 views

How to create a relatively complex MySQL query

I'm not good with MySQL but have been trying to create a query that would join from three tables where: Table 1 (macs) is like this: ipAddr, vlanID, macAddress, interfaceName Table 2 ...
0
votes
0answers
22 views

Is mysqlreport still used?

The mysqlreport main page doesn't exist anymore, and the tool has not been updated since 2008. However, the tool is still available via package managers in Linux. On the other hand, it's difficult to ...
2
votes
0answers
15 views

Mysql History Trigger Auto Increment bug or problem

I want to log history of a table, I created a history table and triggers for update,insert and delete operations. I do my test on realtime replication slave. When a record inserted master's main ...
0
votes
0answers
45 views

Mysql query performance is really bad

I have a very simple query in MySQL which is taking hell lot of time. It ran for 18 hours and didn't complete Update OrderDetail OD Join TEMP_PARTMASTER_LATEST_UPDATES PM Using ...
0
votes
0answers
32 views

MySQL: Slow Query Performance - Parameter Sniffing?

For a personal weather project, I am trying to define and run the following stored procedure: DROP PROCEDURE IF EXISTS history_between$$ CREATE PROCEDURE history_between (IN date_start DATETIME, ...
0
votes
0answers
16 views

Using mysqldump and command line client

Very new to db admin. I am using Windows Server 2012 R2 in a testing environment, trying to teach myself basically. Looking into db replication. I have a very simple and newbie question. I am ...
2
votes
1answer
29 views

How to:: Master-Master replication

I have setup Master-Master replication on two servers. Now, my question is that if I need to have the second master as only read-only, what should I do? I tried doing mysql> flush tables with ...
1
vote
0answers
53 views

Space usage when copying

Short version Copy table took up more space than I expected or tested Why would that be? Could it be the replication / binlog? If so, what then is written to the binlog during this query: INSERT ...
0
votes
0answers
21 views

Mysql terribly slow on Xampp

I've looked if it was Apache, but on websites without a database, it loads pretty fast, but on my Joomla site the localhost takes like 15 seconds or more to load a page. I edited the hosts file of ...
0
votes
0answers
23 views

Doubt on Master-Master replication

I'm setting up a master-master replication where in the second master is only read only there by reducing the issues of conflicts. My doubt is that to reduce the downtime when my master goes down I ...
0
votes
1answer
31 views

Insert but Don't Commit MySql

I am pretty new to MySql and Databases, and I was wondering in python sometimes I forgot to commit after an insert, and then when I do then next insert and commit that one the, auto-incrementing id ...
1
vote
1answer
29 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 ...
3
votes
1answer
46 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 [on hold]

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
49 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
31 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
55 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
159 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
31 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
57 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
24 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 ...
1
vote
0answers
55 views

Schema question

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
53 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
24 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
44 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, ...
-2
votes
1answer
37 views

How to handle huge amount of data in Mysql [closed]

i want to develop a quotation management system,which receives 30-40 quotations per day. we need to handle nearly 1 lac records per month we need to insert quotations using excel sheet as input. how ...
1
vote
1answer
176 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
30 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
36 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
52 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
15 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
70 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
38 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
18 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
51 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
40 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
57 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
9 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
60 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
32 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
12 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. ...