MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

4
votes
3answers
166 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
25 views

mysql trigger / stored procedure - update another table row by row

I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this. this will be a little long, be patient please. First of all, I am trying ...
2
votes
1answer
26 views

MySQL not updating a row (zero row(s) affected) [closed]

I cannot figure out why MySQL 5.6.25 will not update an existing row: SELECT lcl_booking.file_number_id, lcl_booking.entered_datetime, lcl_booking.modified_datetime FROM lcl_booking WHERE ...
0
votes
3answers
65 views

If in mysql I have string `xxx yyy` - how can I SELECT this if user input `xxxyyy`?

My online-shop in PHP (OpenCart). On site we have product-search. For example product have name iSport 220, and if user search input string is iSport220 we must show this product. And vice versa. ...
4
votes
1answer
70 views

How to model “Courses prerequisites” for a university?

I am trying to model a courses structure and dependencies of a university. For example, in the Computer Engineering department you need to do Data Structures before doing Artificial Intelligence. But ...
1
vote
3answers
69 views

How can i return the date of max id?

I want to select the max id_product and date that corresponds to this max id. my problem is that it returns the max id_ product with a different date. I can't use the max(date) because it will return ...
1
vote
0answers
48 views

DatabaseRestart

I have strange problem, we are using circulation replication, between 2 different network with 5.6.25 and 5.6.22 version of MySQL and RAM is 64GB with 45GB innodb_buffer_pool Master Server is running ...
2
votes
1answer
52 views

cannot delete db table [closed]

I tried to use a DROP statement to delete a table but I could not.Instead I tried the following....I deleted the .frm and .ibd files from of the respective table from the directory. Then I used a ...
0
votes
1answer
34 views

What is the best way to take backup for Remote DB Server

I am building a software, which will supports multiple database like MySQL, Oracle and PostgreSQL. These databases are installed on remote machines. i wanted to use java technology in a non-database ...
0
votes
2answers
28 views

Basic SQL question about setting constraints

In code such as the following, what is the need to write constraint pk_dept primary key (deptno) if just simply primary key (deptno) works? Is pk_dept something the database uses internally, since it ...
1
vote
0answers
11 views

Optiming ACT_RU_JOB Query

We're using Activiti version 5.15.1 with timers in our workflow which sends reminders to users who are in the workflow. Problem is that we're creating jobs in ACT_RU_JOB table and even though there ...
2
votes
0answers
21 views

Replace part of URL only if doesn't contain a given part [migrated]

I need to replace all URLs containing 31.media.tumblr.com with 41.media.tumblr.com. But if URL 31.media.tumblr.com has .gif in it, it must not get changed. here's my code;, but it changes everything ...
1
vote
1answer
29 views

how to use a variable as a part of a field name in mysql trigger declaration [duplicate]

Here is what I want to do (a part of my trigger): BEGIN DECLARE updatecount INT default 0; declare number int; declare column varchar(10); declare i int default 1; declare j int default 1; while ...
0
votes
0answers
32 views

MySQL database and MySQL Client

My database version is 5.6.25 Community edition and my client version which is on a remote server is 5.6.24-2+deb.sury.org~vivid+2. First thing, is it okay to use a client lower version than ...
1
vote
1answer
25 views

Extract new lines from log file to MYSQL

everyone. I am new here, I was recommended by Onare, welp, this will be my first question. I have a file for ex. "file.log" that contains printing logs. LINE1: PRINTERNAME | userwhichprinted | ...
0
votes
1answer
42 views

How to get last messge between two friends in the messge table having id in descending order?

Hi friends help me to solve this problem ," display all last messages of a user from all his/her friends" My sample message table is --------------------------------------------------- | id | msg ...
2
votes
2answers
56 views

Cannot build correct SELECT in MySql

Please help me to create a correct MySql query, which I failed to do. I have 2 tables in our DB: CREATE TABLE IF NOT EXISTS `user_audit` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
40 views

Mysql trough access

EDIT I solved the half, that i made a pass through query, so now I only need this: (Licenszek.verz_id)=[Forms]![Hasznalat]![verzio]; to be implemented To the pass trough, ot be the passtrough and a ...
1
vote
1answer
15 views

Default directory for MySQL databases

Is it recommended to have the MySQL database location as the default one which is /varlib/mysql ? Are there any risks in having the databases in the specified location?
1
vote
1answer
40 views

Clarification on mysql deadlock situation

Following deadlock is encountered while load testing a application. After going through MySQL documentation and other online resources what I figured out was that 1) Transaction (2) holds exclusive ...
4
votes
1answer
62 views

When does a MySQL session end?

I have a stored procedure as described below. DROP PROCEDURE `GetVoteID`// CREATE DEFINER=`u1037413_manager`@`%` PROCEDURE `GetVoteID`(IN minimum_votes INT(11), IN current_round INT(11), INOUT ...
2
votes
1answer
30 views

unable to shutdown mysql server

I am using yosemite, if that matters. I have tried using the system preferences and then shutting down mysql there, but to no avail. I also tried typing killall -9 and -11. Both failed :(. I also ...
2
votes
1answer
69 views

Personal finance database design

I'm making a personal finance management application in PHP and MySQL, for learning myself some more PHP and MySQL. This system is currently using 4 tables: users, this table holds all the user ...
2
votes
1answer
45 views

Active-active Cluster In MySQL

I'm planning to go for a 3-node active-active MySQL cluster which will not have more 100 databases. Many experts here have already told there are issues with MySQL 5.6 and 5.7 where circular ...
1
vote
1answer
49 views

ERROR 1146 (42S02): Table 'db.tablename' doesn't exist [closed]

I have a two databases, when I prompt SHOW DATABASES I can see a list of the databases, including mysql, performance_schema and informations_schema. I can prompt 'SELECT DATABASE_NAME' on both of ...
7
votes
3answers
112 views

Why NOT partition?

When would one NOT want to partition a database? (thinking MySQL partitioning) In my case I'll be starting with a couple millions rows, it should grow from there. Primary key on a character field ...
2
votes
1answer
34 views

Help wanted: Special case of changing columns and rows MySQL

Im beginner in MySQL and want to convert this table Any help? Thanks
1
vote
0answers
24 views

Can an insert procedure trigger unintentional loops?

Is it possible for a stored procedure like this to produce a loop of unintentional inserts? I'm asking since this exact procedure produces this resulting loop. (I'm pretty convinced it isn't within ...
0
votes
0answers
11 views

Spreading out iops use at Aws

Is there a way to store event info, such as those triggered by a listened to Dom event, and feed that info into a database at a specific rate? Using Aws databases usually means provisioning a set ...
0
votes
1answer
23 views

mysql max_binlog_size is not working

I have a weird problem. I am currently using mysql and have defined 100M of max_binlog_size. However, I have hundreds of files created every day and each file is of no more then 2MB or 3MB each and ...
1
vote
0answers
25 views

Speeding Up Query - Hangs at Sending Data - Indexed Correctly

I am looking for ways to speed up the below query. I currently have indices on FIRST, LAST, UNIT_PARENT, HomeAddressLine1, HomeAddressLine2, HomeCityNAme, HomeStateAbbreviation, HomeZipCode, HomeZip4, ...
3
votes
2answers
94 views

View with parameter IN

I'm creating a view in which I have a where with an in clause and I want it to be a parameter. Unfortunately views can't be created with parameters, so I tried replacing it with functions, but ...
1
vote
1answer
28 views

Allow multiple users to see procedures and functions

I have GRANT ALL PRIVILEGES ON `test1`.* TO 'admin'@'%', GRANT ALL PRIVILEGES ON `test1`.* TO 'devel'@'%' (from SHOW GRANTS FOR some_user) and I have created simple procedure as admin in test1. And ...
1
vote
0answers
27 views

Taking Backup of a database in mysql

I have database but somehow it has been deleted. Can i take the back-up of deleted database. Please Help. Thanks In Advance
1
vote
2answers
139 views

Sending a database dump

Quite new to db admin here and I have a quick question. I am collaborating with another colleague off site and they would like me to send them a dump of our (1.5GB sql db) database via email as they ...
1
vote
3answers
55 views

How to merge two large mysql tables into one with similar data keeping the latest record

I have two similar large tables with below table structure CREATE TABLE tableA ( col1 varchar(20) NOT NULL, col2 varchar(40) NOT NULL, date date NOT NULL, time time NOT NULL, PRIMARY KEY ...
0
votes
1answer
33 views

Count in an other table and show only theese records in query which has more than they used in other table

I have for example 2 tables: used(us_id, code_id); codes(code_id, amount) I want something like this: If for example I have 5 eggs, and egg code is 1, then if the used table have reached 5 of ID ...
2
votes
1answer
36 views

How to suppress specific warnings in mysql error log when ignoring a table to replicate

Is there a way to make the mysql error log less chatty with certain warnings? I just threw in the replicate-ignore-table option in my.cnf so our health check table doesn't get replicated. However now ...
2
votes
2answers
93 views

Query: How to get both Question and multi Answers in one query

For this question, Question and Answer are just my example tables for one-many relationship. For example: Question(QuestionID, QuestionTitle, QuestionContent) and Answer(AnswerID, AnswerContent, ...
2
votes
2answers
30 views

Information schema not showing reliable information?

I'm currently migrating from SQL Server to MySQL. When i try to match the number of rows i query information_Schema.tables. I noticed that there are some differences between the migrated information ...
1
vote
1answer
56 views

Index based on another index [closed]

Assume a mysql table with columns A, B, C, ... . There are one composite index based on columns A+B+C. Combination of values A, B and C is unique, so this index forms the primary key. Now, a second ...
0
votes
0answers
25 views

Setting up mysql replication (Master->Slave one direction)

I am struggling with the setup of a one way replication for a single mysql table. What I have done so far: Master: Added this into my my.cnf: server-id=5668439 log-bin=mysql-bin ...
1
vote
1answer
24 views

Unable to remove permission for mysql.user

I am trying to remove permission from mysql user to not be able to delete specific database. But if i run this command: revoke all privileges on test.* from 'demo'@'localhost'; or this revoke drop ...
1
vote
3answers
34 views

To trigger or not to trigger- That is the question

I have a mysql table that tracks page views. Each row includes a date stamp. I want to remove all rows that are over 30 days old on a regular basis. Could, or should, I trigger an action on each ...
1
vote
1answer
33 views

Hardware specs for MySQL database with millions of records

For 35 million records I have this table CREATE TABLE IF NOT EXISTS `records` ( `code` varchar(12) NOT NULL, `type` varchar(255) NOT NULL, `used` set('y','n') NOT NULL default 'n', PRIMARY ...
1
vote
1answer
26 views

How should I design a specific needs mysql table

I am planning out a mysql table to record page views. None of my options seem very efficient. I plan to have the pages send a +1 update to the table when loaded. What I want to be able to do is query ...
3
votes
1answer
41 views

Allow multiple users to change functions in MySQL

I want to make multiple users and allow them to change procedures,functions and triggers in same DB. There must be user devel for developers and admin for admin, both can change developers DB, but ...
2
votes
1answer
21 views

MYSQL: log the query executed on certain table

I am writing a trigger that suppose to fire on inserts for a table and log the exact query used to insert. However, using below SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST ...
0
votes
0answers
29 views

MySQL Db design: Optional data in separate table or as NULL values in original table

I am having following problem: User fills out the form, which consists of two sets of fields - header (basic information about biological observation) and detail (measurements and advanced ...
1
vote
2answers
35 views

MYSQL: Creating a table with same columns on remote machine [closed]

I am trying to create a table on my local machine which has the same description as some other table on a remote machine. I just want to create the table with same columns, don't worry about the row ...