All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

2
votes
1answer
47 views

REGEXP does not show the right result?

I have in the name column: 3 boxes of frogs 7 boxes of frogs 48 boxes of frogs When I execute: SELECT name FROM items WHERE name REGEXP "[^1-5] boxes" ...the result is: 7 boxes of frogs 48 ...
0
votes
4answers
42 views

MySQL: Delete all rows older than 30 days, but only if count more than one

I need to cleanup my table of over 14.000.000 rows inside. I want to delete all entries, that are older than 30 days, but only if number of grouped items is larger than one. So, at least one item will ...
1
vote
1answer
30 views

weekly moving average from current_date to the last 52 weeks

I'm a new head to the mysql programming. I have to write a procedure for calculating a weekly moving average grouped by week from 'monday-friday'. Criteria is : current_date is the end date. start ...
0
votes
1answer
34 views

(mysql) Database replication between 2 DB while 3rd DB as backup

I am not an advanced DB admin or designer. I am sure there are articles on this kind of scenario, probably too many. So I got confused and not sure which solution is appropriate/best for me. I am ...
1
vote
1answer
41 views

Setup MySQL Master to Master replication/automatic failover when each Master also has a Slave

Thanks in advance. I have always worked with SQL Server and never really had any exposure to MySQL. I now have to determine the preferred setup for MySQL when there are four servers involved. ...
0
votes
2answers
21 views

MySQL create index with LOCK=NONE still locks a table

I have the following MySQL RoR Migrations: class ReindexRpushNotification < ActiveRecord::Migration def up execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;") ...
1
vote
1answer
25 views

Get previous and next row from result set help

I have to get "relevant" results from a table by joining it on another table and filtering the results on parent id and current id, this all goes fine until your current id is either the first or the ...
1
vote
2answers
46 views

Should MySQL database be in dedicated server?

The application has about 10 databases. Both application and databases are in the same server since is client does not want any time delay that could possibly occur if the database is in a dedicated ...
0
votes
1answer
11 views

MySQL Proxy on Current User

I'm trying to grant permissions to the current user logged in depending on their role, but it doesn't seem possible to have a select statement at the end of the GRANT PROXY # TO # command The code I ...
-1
votes
1answer
23 views

systems hangs after specific time

I am having 2 applications (Java and PHP). Both are sharing same DB Server. After specific time, both systems stops working due to "Too many connections". I tried so many settings (increased ...
0
votes
1answer
45 views

mysql crashed, innodb fails to recover

On Mysql 5.0 on CentOS 5.11, mysqld service has crashed and fails to start. Adding innodb_force_recovery=3 in /etc/my.cnf, creates the following error, which repeats with all table names of database ...
2
votes
2answers
50 views

Migrate SQL Server backup to MySQL for a Wordpress site

I have a client with an old .Net site that used SQL Server. For lots of reasons they decided to switch to Wordpress. They had a bunch of news articles on that old site that they want turned into posts ...
0
votes
0answers
33 views

MySQL: Calculations and Triggers across multiple tables

I'm new to sql and I'm having trouble with a calculation involving two of my tables. I have a table, payroll, which looks something like this: CREATE TABLE payroll ( id INT(6) UNSIGNED AUTO_INCREMENT ...
0
votes
0answers
29 views

Mysql checksum for data only

I have some groups of two tables, each group table having the same structure. (dev & prod server) Their data will never change from user action, only from admin when a change occurs. I want to ...
0
votes
1answer
30 views

Writing a query to COUNT all the services involving a particular

Learning PHP and SQL on an online MOOC, this is a homework that I've got to do. I need to write a query in SQL to count all the services which involve a particular person in my database. The ...
3
votes
2answers
453 views

Which one is an ER diagram?

I have started to study ER diagrams. When I browse through the ER diagram tutorials, I found something like figure 1: Figure 1 I tried to create a sample ER Diagram in MySQL Workbench like: ...
0
votes
1answer
29 views

Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

I have the several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution. 1:N related tables SO and SO_ITEMS (SO is just one example I have several) that both ...
0
votes
0answers
45 views

Mysql failed to start, InnoDB Error: page log sequence number is in the future

I had issue in starting mysql after machine crash. Looking in mysqld.log, I found below errors: 160511 09:50:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 160511 9:50:40 ...
1
vote
1answer
40 views

varchar datatype accepting integer values

below is the test table created. As data type is set to varchar it shouldn’t accept int values. desc c\G *************************** 1. row *************************** Field: t Type: int(6) ...
1
vote
2answers
36 views

Insert and select the row autoincremented id

What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment. I know how to do it with a Stored Procedure. Will this line of code work: BEGIN ...
0
votes
1answer
28 views

How to use FULLTEXT in mysql innoDB?

Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance. I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference ...
0
votes
0answers
20 views

Loading Data from url

I am trying to create a plan for data load and wanted to bounce off best practice ideas and learn about amazon RDS. I have multiple url sources of data and a RDS MYSQL instance to store them. I want ...
0
votes
1answer
46 views

InnoDB files created even innodb_file_per_table is OFF in mysql-5.7

We have installed mysql 5.7 in ubuntu 16.04 and we got big surprise because databases are being stored individually in each folder even parameter innodb_file_per_table is 0. We decided to change ...
1
vote
2answers
23 views

MySQL stored procedure returns null

I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried call NewProc('tableName','colName') it returned 1064 - You have an error in your SQL ...
0
votes
1answer
35 views

table is — is full mysql

MySQL 5.7 on windows'12 server, table which I am getting error crossed 2 GB in size, server memory is 4 Gb, MySQL used about 90% of memory.. Now any insertion into this table causing table full Once ...
0
votes
0answers
33 views

Unable to change innodb_log_file_size in MariaDB Galera Cluster 10.0

I have a MariaDB Galera cluster (v. 10.0.19) with 3 nodes. I want to increase the innodb_log_file_size (i.e. the size of the InnoDB redo log files) on one of the nodes to match what it is on the other ...
0
votes
2answers
21 views

check when last password changed in my user db/table

i have radius billing system with mysql as DB. I want to know which user 'password' was changed in last 5 minutes So that I can fetch such user mobile number from the DB and send SMS Alert about the ...
0
votes
0answers
23 views

mysql duplicate key error during restoration through mysqldump data

I have a mysql database which is having one table for geo location and saves latitude and longitude values of users. The fields are as followed CREATE TABLE `USER_LOCATION` ( `LATITUDE` float NOT ...
1
vote
1answer
28 views

MySql LIMIT + JOIN is stopping data from being returned?

I originally posted this question on stackoverflow, but then remembered there's a stackexchange specifically for databases! I've got two tables and am trying to return all profiles that are assigned ...
4
votes
2answers
107 views

find the beginning and ending values of sequential runs

This article does the inverse of what I need: Find which numbers in [1, 161] are not in the result set? Given the sample data they used: mysql> CREATE TABLE wp_blogs -> ( -> ...
0
votes
0answers
28 views

Set private key password for mysql using ssl

2016-05-09T20:05:05.772747Z 0 [ERROR] SSL error: Unable to get private key from 'server-key.pem' 2016-05-09T20:05:05.772764Z 0 [Warning] Failed to set up SSL because of the following SSL library ...
0
votes
2answers
28 views

How to Add AFTER Delete Trigger in My case?

I am Making a Database Management System of Hospital in MySQL WorkBench, and i Want to Make a After Delete Trigger in my case. The scenario is i have two Tables 1) The Patients which are currently ...
0
votes
1answer
32 views

Update a valid column when another column has changed

We have a valid_phone column that just stores 1 or 0 for valid or not valid. When the user changes their phone number we want that field to go back to 0 so they will have to validate their number ...
0
votes
0answers
15 views

Is MySQL Database Replication Single Path or Multi-Path?

I read here that NIC Bonding is only useful when the IO is multi-path. Is MySQL Database Replication single or multi-path?
0
votes
1answer
37 views

Database convert character_set_server latin1 to utf8

I am writing about a problem on the character encoding that i have on my site with Magento. In practice, the special characters do not take utf8 and gives me the classic non-coded characters: Ã. ...
-1
votes
0answers
11 views

Sequilize association for 2 foriegn key of same model [migrated]

I need to execute below query using sequelize SELECT * FROM haulerrfquotes LEFT JOIN quotes ON quotes.jobId = haulerrfquotes.jobId AND haulerrfquotes.haulerId = quotes.haulerId WHERE ...
0
votes
1answer
29 views

Mysql Query: how to get last login time of each ID through IN clause

How to get last login time of selected ID's. Table | user_id | lastlogin | 100 | 2016-03-05 | 110 | 2016-03-21 | 100 | 2016-03-29 | 100 | 2016-04-03 | 110 | ...
1
vote
2answers
48 views

MySQL 5.6 Aborted_connects increase without error log

MySQL server version: 5.6.26 Linux: Linux 2.6.32-431.11.7.el6.ucloud.x86_64 #1 SMP Wed Jan 7 23:42:27 CST 2015 x86_64 x86_64 x86_64 GNU/Linux Situation: Aborted_connects increase 5-10 every minutes ...
2
votes
1answer
70 views

MySQL running delete trigger on truncate

I have a table that has an on delete trigger that updates another table. THe table I'm trying to truncate has 130,000 rows. I have duplicate schema and data on my dev server as well as the client's ...
1
vote
1answer
30 views

Subquery inside a select vs left join

Often I have to return the count of of some row in a query that fetch other non related rows. For example A table User a table Review and a Table picture User: id nickname Review: id to_user_id ...
0
votes
1answer
40 views

Is this the correct way of moving MySQL data directory, (on Centos)?

I need to move my data directory to a bigger drive on the same server. # stop mysql /etc/init.d/mysql stop # copy the data over cp -R -p /var/lib/mysql /home/mysql #edit my.cnf # and add a ...
1
vote
2answers
54 views

Calculate Growth rate

I'm trying to calculate purchase/profit growth rate in different time periods, This is the query i've come up with (View in SQLFiddle): Schema: CREATE TABLE `profit_log` ( `market_id` smallint(6) ...
0
votes
1answer
12 views

How to run Trigger only on certain updates

I have the following two tables: CREATE TABLE IF NOT EXISTS `Product` ( `ProductID` VARCHAR(16), `ProductName` TINYTEXT NOT NULL, `CategoryID` VARCHAR(3) REFERENCES Category (CategoryID) ...
0
votes
0answers
51 views

Query issue to get total

All of the post time save in DB Table in UTC timezone. I want to get total in PST timezone for today, tomorrow, this week and this month. Here is my SQL Query. Today Query: SELECT COUNT(*) FROM ...
0
votes
1answer
46 views

How to enrich event based data per row based on future events

I'm trying to figure out a not too terribly hacky way to solve the following issue. This data is currently available both in MySQL and RedShift, so a solution for either is fine though RedShift is ...
1
vote
0answers
17 views

Conceptual data model for warehousing objects [duplicate]

I'm student and new to databases. I must conceptualize a database model for warehousing video games using a relational database management system (MySQL). Every objects in the warehouse have common ...
2
votes
0answers
51 views

Why does score for mysql boolean match give different score?

I am attempting to use MySQL's fulltext search using Boolean match on multiple columns, with multiple search phrases. I cannot understand the score returned though. The score returned is typically an ...
0
votes
0answers
39 views

How to give a prefix to all columns of a JOINed table in MySQL?

I have a data schema like this: car id type -> ENUM('Tesla', 'AstonMartin', 'Bmw') common_attribute car_tesla car_id specific_attribute_a specific_attribute_b ...
2
votes
1answer
45 views

How to JOIN a table with a name that equals to a determined value in MySQL?

The model of the (PHP & MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here. To map this to the database I’m using the ...
0
votes
1answer
82 views

MYSQL refuses to set password for new user

I'm trying to create a MySQL user/password. And every time I create the user - mysql does not honor my password. It's just blank. mysql> select Host, User, password_expired, Password from ...