MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
124 views

Why is my stored procedure very much slower than the same MYSQL query?

My stored procedure is very slow compared to a normal query or a temporary table. How can I optimize my stored procedure? CREATE DEFINER=`datasll`@`...` PROCEDURE `product`() BEGIN EXPLAIN ...
0
votes
2answers
58 views

Is it redundant to set your primary key as a unique key?

I'm putting tables together in MySQL workbench and I'm currently ticking my primary keys to also be uniquely indexed. Is this a redundant configuration?
0
votes
0answers
33 views

Design database schema for membership application with fees and purchases

My membership application needs to record payment of membership fee, card fee and misc. fee for each member. I also need to record purchases of each members and payments for the purchases.
0
votes
2answers
28 views

EC2 and setting up MYSQL Replication

my database size is very big around 100gb and when I restore it takes 3 days to restore. I am using amazon ec2. My question is I want to setup a new slave to MYSQL master, can I take a snapshot of ...
0
votes
1answer
26 views

query to join three tables in mysql [closed]

I have four tables, one of them combines the three remaining tables, it is like a parent of them, so I want to select and insert in second table or third or fourth by using first one. How can do it? ...
2
votes
1answer
18 views

Starting MySQL with SSL option using systemd

Can someone explain how to start a MySQL server with SSL options enabled using systemd? I've generated certificates and edited the my.cnf file to enable SSL but I'm not sure how to restart MySQL so ...
2
votes
0answers
37 views

Storing phone number in E.164

TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field? Have been reading up on format/storage of ...
1
vote
0answers
16 views

Unique ID for related records in a MySQL Table

I have a requirement to allocate a unique RecordID (ImportID) to multiple rows in a table. We are importing a text file which contain related transactional information but variable record types and I ...
2
votes
0answers
38 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
3
votes
1answer
38 views

Confused by GRANT ALL PRIVILEGES ON *.* (…) WITH GRANT OPTION

Normally I manage users on my databases via phpMyAdmin, but since I do everything else in standalone programs such as SQLyog or HeidiSQL, I wanted the ability to manage users remotely from these ...
2
votes
1answer
45 views

Query to determine start and end dates based on time overlap

Given the following data: id | user_id | started | closed | dead ------------------------------------------------------------------------------------------- 7714 ...
1
vote
1answer
20 views

Cleanup orphaned records

I have two tables in MySQL: orders and line items. When orders are deleted, the line items are orphaned. Thus, I need a query that will delete the line items that were associated with the order ...
0
votes
1answer
14 views

SQL BCP Utility Writing to SSMS Insead of .csv File

I have a stored procedure in SSMS call sp_testInventoryIssues as follows: USE [ADMIN] GO /****** Object: StoredProcedure [dbo].[sp_testInventoryIssues] Script Date: 06/19/2015 16:02:29 ******/ ...
2
votes
1answer
49 views

Table structure for voting app

I have an app where a user votes between two local profiles on which one is better. I want to ensure that the same user cannot vote more than once between the same two profiles (say the user votes on ...
0
votes
1answer
24 views

store text differences

I want to store text differences in MySQL. Let's say I have a text, 500 characters or so, and then someone edits the text. I want to be able to reconstruct both the old and new text. From a database ...
3
votes
1answer
25 views

Get problem with Binlog_Do_DB in Master configuration

I have followed all the rules for replication. The steps are I have changed the my.cnf file. My db name is new_db. server-id = 1 log_bin = D:/xampp/mysql/mysql-bin.log binlog_do_db = new_db Grant ...
1
vote
1answer
54 views

How to select second last row from table?

Table Name :- ModelParam_EMS Primary Key :- ParameterID No of Rows :- 500 want to select 499th row from table only. Note :- No. of rows can be less or more but not fixed. Create Table :- ...
0
votes
1answer
42 views

.ibd file is missing and alter table discard tablespace crashing the server

Data file(.ibd) for one of my mysql table is missing in data directory. As read in some articles i want to resolve the issue by discarding tablespace first ,import the tablespace (cfg file of said ...
1
vote
1answer
53 views

How to select max value faster from large size table

I have query SELECT max(ParameterID) FROM modelparam_ems Table Name: modelparam_ems No Of Rows: 9000 It takes time to retrieve max value of parameterId, ParameterId in Unique value. Output Time ...
0
votes
1answer
40 views

How to select users that doesn't in the role

I have 4 roles which are Admin, Procurement, Accounting, HR roles and 3 tables which are Users, Roles and Userroles. They user can have many roles as well as the roles can have many users. the problem ...
1
vote
2answers
32 views

Is there risk importing data with auto increment id #'s that are higher than the current numbers?

I have to import data into a new database. That new database already has a small set of data. I have two large tables I need to import in there. Is there any risk of a higher id getting over ...
0
votes
1answer
24 views

Mysql Replication - How it works offline?

i have a mysql server on a local machine but the connection is not got for users outside the local network. I want that database to be available on another server wich can handle way more requests and ...
1
vote
2answers
31 views

Auto Incrementing column based on another mysql

I have an application which generates invoice_id's. These id's need to be unique. The issue is that a client can have more than one invoice depending on what plan they are on. It could be 2,3 or 4. ...
2
votes
3answers
58 views

Updating/Inserting 2000 entries takes over 12 hours to complete

I have three tables of around 10 million rows each in MySQL on Amazon RDS and I am finding the insert/update performance to be VERY slow. Each table contains unrelated data. Each table is regularly ...
2
votes
2answers
24 views

Keep local variable after rollback in mysql

I wish to keep local variable from stored procedure even if rollback occurres. In following procedure, i "register" numbers to users. The procedure takes user_id, value_from and value_to as ...
2
votes
1answer
34 views

Strategy for setting up a 60gb MySQL database from a dump?

I have been trying to set up a 60gb MySQL database from a dump. I loaded it into MySQL Workbench on my standard-issue dual-core 2.4 Ghz, 4gb RAM, 2011 Macbook Pro. After roughly 24h, it had only been ...
1
vote
2answers
38 views

mysql master slave replication scenario

We are creating a new mysql slave, but for dump we are taking from a backup server which is a slave of current master. Now to enable replication we want to connect it with the master so should I take ...
0
votes
1answer
26 views

Problem using multiple JOINs

I have a list of customer IDs in table newsletter_subscriber. I have an EAV table that contains a lot of information about these customers, table customer_entity_varchar. For every entry in ...
0
votes
0answers
30 views

db design/layout

I have an objective to record and store test data. A secondary objective is to track the progress of the test programme (it is a large programme). So I want to use MySQL (not access or doors) to ...
0
votes
0answers
24 views

Mysql Full Text Search with word delimiter

I have the following paragraph, Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. <h1>Methodology</h1> Lorem ...
4
votes
2answers
85 views
+50

MySQL: delete…where..in( ) vs delete..from..join, and locked tables on delete with subselect

Disclaimer: please excuse my lack of knowledge about database internals. Here it goes: We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
2
votes
2answers
162 views

Advice on a basic design, first time database design

I am taking a course to become a Java developer. The course involves using databases but we never really design any unfortunately. Most of the time we get pre-made databases and we have to implement ...
2
votes
1answer
38 views

Is it secure to set default value for password field? [closed]

I'm looking at an application where admins can "invite" users by email and invited users. Invited users then are sent a unique URL and are inserted into a MySQL database with status pending and an ...
0
votes
1answer
18 views

How can I view when mysql mixed mode replication is logging a statement using row or statement format?

I have a couple of servers running in MIXED mode replication and I do maintenance regularly on them deleting a decent sized set of data. There is one table that had no primary key and is just used for ...
0
votes
1answer
19 views

what could cause a mysql process to close (clean)

We are using mysql master-master (version 5.5.32) for an API manager called Layer7. The 2 hosts holding the instances are in the same ESX From time to time we have mysql doing a clean shutdown for ...
-1
votes
1answer
31 views

Mysql InnoDB vs Mongodb write performance

I need to increment a counter each time a web page is rendered. When I use mongodb to do that, I can do about 16000 writes per second on a 4 cores/8 threads CPU on a regular disk. When I use Mysql ...
0
votes
2answers
28 views

MySQL Upgrade on Windows

I am having MySQL 5.1 installed on windows box. Now I would like to upgrade to latest MySQL 5.6 version. I am planning to install MySQL 5.6 in the same server with same port 3306. After installation, ...
0
votes
0answers
20 views

MySQL server installation file download for windows

I need to install latest MySQL community server 5.6.25 in windows box. MySQL site is giving complete MySQL installer, but not particular MySQL server installation file. Where can I get this ...
0
votes
2answers
33 views

mysql table replication on just some records?

I have a big database which contain a table with about 26 million records. Now i need to make some reports on it from a special period of time. So how to make a replication from that table from a ...
0
votes
0answers
15 views

migrating MYSQL 5.5 to percona xtradb cluster 5.6

if we have to migrate MYSQL 5.5 using statement based replication to percona xtradb 5.6 would that be possible. What we want to do is to make percona xtradb cluster a slave of our current MYSQL and ...
1
vote
0answers
42 views

Select last row for a certain group

I have two tables: Table users columns | type | notes --------|--------|------- id | bigint | PK ... | ... | ... Table events columns | type | notes ...
0
votes
1answer
28 views

mysql variables are inconsistent

Just started out with MySQL 5.5 on Ubuntu Server 14.04 and ran into a strange issue. I always show the settings of the variables by typing mysql --help. Just to get familiar with MySQL-Server I ...
0
votes
2answers
41 views

Killing a process without login to MySQL

Here is the situation: I was running a very complex query on a database which was expected to execute within a few hours over night(server configuration is not good and data set is large). This ...
0
votes
0answers
9 views

Mysql Cluster Don't save data on data node

I am using mysql cluster. This is my setup [ndbd(NDB)] 2 node(s) id=2 @192.168.64.236 (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *) id=3 @192.168.64.235 (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0) ...
0
votes
0answers
41 views

How to make search faster using LIKE in mysql with over 3m row?

I looking for a solution to solve this. Any idea help me plz? My table over 3m record Data in search column like '0123456789' => cannot use Full Text Search. I try put innodb_buffer_pool higher ...
3
votes
1answer
45 views

Adding NotNull FK field in existing DB

Context: We’re building a web application to manage business-data. Each “client” of the application has a separate DB (this wont change), and each DB is based off a model (so they are all identical in ...
1
vote
0answers
20 views

Get n data based on the same day (timestamp) [migrated]

I have this table "sensor" which has these fields : id (int,PK) timestamp (timestamp) latitude (varchar) longitude (varchar) data (float) As in one query I want to get 5 rows that were on the ...
2
votes
1answer
46 views

MySQL Performance: conditionnal left outer join vs where only

I have three tables: FORUM ( id PRIMARY KEY, public BOOLEAN ) MESSAGE ( id PRIMARY KEY, content VARCHAR(255), user_id NULL FOREIGNKEY user.id, forum_id FOREIGNKEY forum.id) USER( id PRIMARY KEY, ...
0
votes
2answers
41 views

9 node percona xtradb cluster

We are looking to launch a 9 node percona xtradb cluster on Amazon where each ec2 node uses 20k iops disks. I was afraid if a 9 node cluster will work and mostly what I see is 3 node clusters. We ...
0
votes
1answer
10 views

Aruba-MySQL: can't create/change table to engine=INNODB

We have our database stored in aruba (mysql.aruba.it) where there is a table called "task". Because of many changes in the requirements we decided to drop the table and create it again from 0 with ...